Peak Metric Per 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

Asked in

Forbes

Problem

Across every metric HR tracks in employee_metrics, what is the single highest value each department has ever recorded? HR's review reads the departments from the highest peak down.

Peak metrics for the quarterly deck.

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-number-per-group summary dressed up as an HR review. Nearly everyone finds the largest metric_value. What separates answers is what the sort runs on. employee_metrics has 200 rows over ten departments, and every row has a clone. If you sort rows instead of peaks, HR's list opens with Product's 99.3 twice before any other department appears. If you collapse the rows but sort by department, Product ends up behind Data and Design.

Getting there

Step 1: Fix the grain at one row per department

The question asks for one figure per department, so ten rows come back, not 200. Group on department alone. Adding metric_name would return one peak per metric, which is the reading 'across every metric' rules out.

Step 2: Take MAX(metric_value) as the peak

The peak is the largest value from any quarter or year, so there is no filter on fiscal_year or fiscal_quarter. Twelve rows have a NULL metric_value. MAX skips them, and every department still has real values, so no department drops out.

Step 3: Sort on the peak, not on a stored column

Name the aggregate peak_value and sort on that alias, highest first. The sort runs after the grouping, so it orders departments by their peaks rather than ordering rows by their values.

SELECT department, MAX(metric_value) AS peak_value
FROM employee_metrics
GROUP BY department
ORDER BY peak_value DESC

Sorting rows

SELECT department, metric_value FROM employee_metrics ORDER BY metric_value DESC returns 200 rows. Product shows up twice at 99.3, then Design twice at 98.6, and every department repeats down the list.

Sorting peaks

Grouping first leaves ten rows. ORDER BY peak_value DESC then puts Product, Design and Legal at the top, one line each.

Common follow-up questions

• HR now wants to know which `metric_name` produced each department's peak. How would you return it next to `peak_value`? (Tests carrying a non-grouped column from the winning row, which is a per-group top-1.)
• What would happen to a department whose every `metric_value` is `NULL`? (Tests knowing that `MAX` returns `NULL` for an all-`NULL` group, and where that row lands in a descending sort.)
• How would you compare departments fairly when the metrics sit on different scales? (Tests normalising within each `metric_name` before comparing across departments.)