Department Snapshot

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L3

Problem

Build a department snapshot from employee_metrics showing how many readings actually have a value on file, alongside the lowest, highest and average of those values and the gap between the lowest and highest. Readings that were never recorded still sit in the table with no value, and they stay out of every figure, the count included.

Who is underperforming and who is excelling?

Tables

employee_metrics

Columns: metric_id INTEGER, department TEXT, metric_name TEXT, metric_value REAL, fiscal_quarter TEXT, fiscal_year INTEGER

Expected output

Worked solution and explanation

The count is the trap, not the average

This looks like a tidy department roll-up, but the real test hides in one column: the reading count. MIN, MAX and AVG all skip a missing metric_value for free, so a department's low, high, average and spread come out right whether or not you thought about nulls. The count gets no such courtesy. Write COUNT(*) over the raw table and every unrecorded row, the ones sitting in employee_metrics with no value on file, is tallied as though it were a real reading. The snapshot then claims a department is better sampled than it actually is.

Walk the requirements

Step 1: Drop the unrecorded readings

WHERE metric_value IS NOT NULL removes the rows that were never scored. This is the one line that controls the reading count; drop it and COUNT(*) starts counting placeholders.

Step 2: Aggregate per department

GROUP BY department, then MIN(metric_value), MAX(metric_value), AVG(metric_value) and COUNT(*) all resolve in a single pass over the filtered rows.

Step 3: Derive the spread inline

MAX(metric_value) - MIN(metric_value) reuses the same extremes already in the SELECT, so no subquery or extra scan is needed.

The solution

SELECT
    department,
    COUNT(*) AS valid_readings,
    MIN(metric_value) AS min_metric,
    MAX(metric_value) AS max_metric,
    AVG(metric_value) AS avg_metric,
    MAX(metric_value) - MIN(metric_value) AS spread
FROM employee_metrics
WHERE metric_value IS NOT NULL
GROUP BY department

Common follow-up questions

• How would you also report the median value per department? (Tests an ordered-set aggregate rather than a plain `AVG`.)
• What if departments with too few valid readings should be hidden entirely? (Introduces a `HAVING` threshold on the valid-reading count.)
• How would you flag departments whose spread is unusually wide? (Compares each group's spread against an overall benchmark.)