Best in Show
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
- L4
- Asked in
- Asana
Problem
We log a range of performance metrics for each department, and the same reading sometimes gets recorded more than once. For each department, surface its highest metric value as a single entry, showing the department, the metric name, the value, and its standing within the department. Walk through the results alphabetically by department, and by metric name wherever a department contributes more than one entry.
Every department has a peak. Name it, once each.
Tables
employee_metrics
Columns: metric_id INTEGER, department TEXT, metric_name TEXT, metric_value REAL, fiscal_quarter TEXT, fiscal_year INTEGER
| 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
| department | metric_name | metric_value | rnk |
|---|---|---|---|
| Data | remote_percentage | 84.7 | 1 |
| Design | avg_tenure_months | 98.6 | 1 |
| Engineering | headcount | 92 | 1 |
| Finance | open_positions | 89.8 | 1 |
| HR | promotions | 93.5 | 1 |
Worked solution and explanation
What this really is
Strip the business costume and this is a per-partition maximum with two quiet traps sitting on top of it. Anyone can find the highest metric_value per department; what separates candidates is keeping the tied winners AND folding away the duplicate rows the raw table carries. Miss the first and a co-leader silently vanishes. Miss the second and every winner shows up twice, which is exactly the 20-rows-instead-of-10 result that fails the check.
The two traps
Building it
Step 1: Rank within each department
RANK() OVER (PARTITION BY department ORDER BY metric_value DESC) numbers rows inside each department, biggest value first. RANK (not ROW_NUMBER) is deliberate: equal values receive the same rnk, so a department's co-leaders both land on 1.
Step 2: Keep the top standing
Wrap the windowed query in a subquery and keep WHERE rnk = 1. Because ties share rank 1, this keeps every top entry per department rather than an arbitrary single winner.
Step 3: Collapse duplicates and order
Apply DISTINCT to the selected columns so identical logged rows collapse into one entry, then ORDER BY department, metric_name for a stable, readable result.
The solution
SELECT DISTINCT department, metric_name, metric_value, rnk FROM (SELECT department, metric_name, metric_value, RANK() OVER (PARTITION BY department ORDER BY metric_value DESC) AS rnk FROM employee_metrics) WHERE rnk = 1 ORDER BY department, metric_nameCommon follow-up questions
- If two metrics in one department share the highest value, which of `ROW_NUMBER`, RANK, and `DENSE_RANK` keep both, and why? (Tests understanding that `ROW_NUMBER` = 1 returns one arbitrary row, while RANK or `DENSE_RANK` = 1 returns all tied winners.)
- What in the raw data forces the DISTINCT, and what happens to the output if you drop it? (Tests whether the candidate can articulate why DISTINCT is load-bearing here rather than cosmetic.)
- How would you extend this to the top 3 values per department, and which ranking function would you choose? (Tests changing the filter from rnk = 1 to rnk <= 3, and the tie implications of `DENSE_RANK` vs RANK at that threshold.)