# Metric Range by Department > Where each team's numbers sit, low to high. Canonical URL: 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. ## Tables ### employee_metrics - metric_id INTEGER - department TEXT - metric_name TEXT - metric_value REAL - fiscal_quarter TEXT - fiscal_year INTEGER Sample rows (10 of 200): | metric_id | department | metric_name | metric_value | fiscal_quarter | fiscal_year | | --- | --- | --- | --- | --- | --- | | 141 | Product | attrition_rate | 7.3 | Q2 | 2025 | | 182 | Design | avg_tenure_months | 14.6 | Q3 | 2026 | | 223 | Marketing | satisfaction_score | 21.9 | Q4 | 2024 | | 264 | Sales | training_hours | 29.2 | Q1 | 2025 | | 305 | HR | promotions | 36.5 | Q2 | 2026 | | 346 | Finance | open_positions | 43.8 | Q3 | 2024 | | 387 | Legal | time_to_hire_days | 51.1 | Q4 | 2025 | | 428 | Operations | diversity_ratio | 58.4 | Q1 | 2026 | | 469 | Data | remote_percentage | 65.7 | Q2 | 2024 | | 510 | Engineering | headcount | 73 | Q3 | 2025 | ## Expected output First 5 of 10 rows: | department | avg_value | min_value | max_value | | --- | --- | --- | --- | | Product | 55.8 | 7.3 | 99.3 | | Design | 55.27 | 6.6 | 98.6 | | Finance | 50.67 | 8.8 | 89.8 | | Marketing | 50.4 | 5.9 | 94.9 | | Legal | 49.6 | 8.1 | 97.1 | ## 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 **One row per department, highest average first** ```sql 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 ``` > **Ten rows is not proof of the right grain** > > `GROUP BY metric_name` also returns exactly ten rows with sensible-looking numbers. Check the label column, not the row count, before you call it done. **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. > **Say the grain before the `SELECT`** > > Opening with "one row per department, three stats, highest average first" before you type shows you design from the output back, and it catches the `metric_name` mix-up before it is written. > **One scan, ten small accumulators** > > The engine reads `employee_metrics` once and keeps a running sum, count, min and max per department: ten tiny accumulators however many rows arrive. At a billion rows the cost is still one scan plus a sort of ten results. ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/metric_range_by_department) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.