Metric Range by Department

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

Domain

SQL

Difficulty

easy

Seniority

L3

Problem

An HR analyst wants to see how the values recorded in employee_metrics spread out within each department: the average, the lowest and the highest. Show the departments with the highest average first.

Where each team's numbers sit, low to high.

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

This is a one-pass profile per department, and the only real decision is the grain. employee_metrics packs ten departments and ten metric names into 200 rows, and both columns look like something you could summarise by; "within each department" picks one. Anyone can write AVG, MIN and MAX. What separates candidates is keeping all three in a single GROUP BY department and sorting on the computed average. Summarise at the wrong level and HR gets ten tidy rows about metrics instead of departments, and nothing in the output looks broken.

Getting to the query

Step 1: Name the grain before writing anything

One row per department. Say it out loud. metric_name also has ten values, so a query grouped by it returns the same row count and survives a quick glance; only the first column gives it away.

Step 2: Compute the three statistics in the same pass

AVG(metric_value), MIN(metric_value) and MAX(metric_value) read the same group, so they belong in one SELECT. Three separate queries joined back on department do the same work three times.

Step 3: Let the missing values stay missing

12 rows have a NULL metric_value. AVG, MIN and MAX all skip NULL, which is what you want: a value nobody recorded is not a zero. Wrapping the column in COALESCE(metric_value, 0) would pull those departments' averages down and set their minimum to 0.

Step 4: Round only the average, then sort by its alias

The average comes out with a long decimal tail, so ROUND(AVG(metric_value), 2) matches the two places the recorded values carry; the minimum and maximum are recorded values already. ORDER BY avg_value DESC sorts on the computed figure, which exists nowhere in the table.

The solution

SELECT
  department,
  ROUND(AVG(metric_value), 2) AS avg_value,
  MIN(metric_value) AS min_value,
  MAX(metric_value) AS max_value
FROM employee_metrics
GROUP BY department
ORDER BY avg_value DESC

Summarised by metric

GROUP BY metric_name returns one row per metric, such as 'headcount': ten rows, the right columns, the wrong question.

Summarised by department

GROUP BY department returns one row per department, such as 'Product' or 'Legal', which is the profile HR asked for.

Common follow-up questions

• How would you show each department's profile per `metric_name` instead of across all metrics? (Moving the grain to a composite key while keeping the output readable.)
• HR wants the number of recorded values next to each average. Which count do you use? (`COUNT(metric_value)` versus `COUNT(*)` when 12 values are missing.)
• How would you add a median alongside the average? (Ordered-set aggregates, or a window-based fallback where the engine lacks one.)