Max Value Per Location

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: L5

Problem

HR is reviewing peak performance across the company, one department at a time. Show each department alongside its highest recorded metric value.

Every location has a peak.

Tables

employee_metrics

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

employee_metrics: 10 of 200 rows
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	max_value
Data	84.7
Design	98.6
Engineering	92
Finance	89.8
HR	93.5

Worked solution and explanation

What this really is

This is a one-row-per-department maximum wearing an HR leaderboard costume. Anyone can find the biggest number in employee_metrics; the skill is making MAX(metric_value) answer once per department instead of once for the whole table. Two shortcuts sink people. Reach for AVG because metric_value reads like a score you would average, and every department reports its mean, so all ten values come back wrong. Drop the GROUP BY and the query hands back a single 99.3 for all 8,000 rows: one row where HR asked for ten.

Building it

Step 1: Fix the grain before the metric

Say it out loud first: one row per department. That sentence is the GROUP BY department, and it also tells you what may appear in the SELECT: the key itself plus aggregates. Anything else, like metric_name or metric_id, would either split the groups or be rejected outright.

Step 2: Take the peak, not the typical value

HR asked for the highest recorded value, so the aggregate is MAX(metric_value). Alias it max_value so the output column says what it holds. No filter on metric_name: the ask is the single highest value the department ever recorded, whatever metric produced it.

Step 3: Present it biggest peak first

A leaderboard reads top-down, so finish with ORDER BY max_value DESC. Sorting by the alias works because ORDER BY runs after the SELECT list is computed, so you never repeat the MAX(...) expression.

Peak `metric_value` per `department`

SELECT department, MAX(metric_value) AS max_value
FROM employee_metrics
GROUP BY department

Ten groups in, ten rows out, strongest peak at the top.

No grouping: SELECT MAX(metric_value) FROM employee_metrics collapses all 8,000 rows into one bucket and returns a lone 99.3. It is the right number for Product and says nothing about the other nine departments.
Grouped by department: GROUP BY department splits the table into ten buckets first, so MAX(metric_value) runs inside each one and every department gets its own peak.

Common follow-up questions

Two departments hit the same `max_value`. How do you make the leaderboard order deterministic? (Tests whether the candidate adds a tiebreaker such as `department` to the `ORDER BY`.)
What does `MAX(metric_value)` return for a department whose values are all `NULL`? (Tests knowing that aggregates skip `NULL`s and an all-`NULL` group yields `NULL`.)
HR now wants the peak per department within each `metric_name`. What changes? (Tests moving the grain to `(department, metric_name)` and what that does to the row count.)
How would you also return which `metric_name` produced each department's peak? (Tests joining back to the max or using a per-department ordering to recover the source row.)