Top Metric Values

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
Siemens

Problem

We're putting together a leaderboard of top performance scores from employee metrics, where a score that shows up more than once still counts only once. List the five highest such scores, ordered from the top score down to the lowest.

The five highest numbers. No duplicates.

Tables

employee_metrics

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

employee_metrics: 10 of 200 rows
metric_iddepartmentmetric_namemetric_valuefiscal_quarterfiscal_year
141Productattrition_rate7.3Q22025
182Designavg_tenure_months14.6Q32026
223Marketingsatisfaction_score21.9Q42024
264Salestraining_hours29.2Q12025
305HRpromotions36.5Q22026
346Financeopen_positions43.8Q32024
387Legaltime_to_hire_days51.1Q42025
428Operationsdiversity_ratio58.4Q12026
469Dataremote_percentage65.7Q22024
510Engineeringheadcount73Q32025

Expected output

First 5 of 5 rows
metric_value
99.3
98.6
97.1
96.4
94.9

Worked solution and explanation

What this really is

This is a top-N over values, not over rows, dressed up as a leaderboard. Every score in employee_metrics sits on exactly two rows, so the first five rows by metric_value hold only three different numbers: 99.3, 99.3, 98.6, 98.6, 97.1. Anyone can write ORDER BY metric_value DESC LIMIT 5. What separates candidates is collapsing repeats before the cut, because LIMIT counts rows, not scores. Miss it and 96.4 and 94.9 never make the board.

Getting there

Step 1: Decide what one entry on the board is

The statement says a repeated score counts once, so an entry is a value, not a row. Project only metric_value. Bring metric_id along and every row is unique again, because each row carries its own id.

Step 2: Collapse repeats with DISTINCT before counting

SELECT DISTINCT metric_value folds the 200 rows into 95 values: 94 scores plus one NULL. The dedup happens on the projected set before LIMIT does its counting, so the five rows that survive are five different numbers.

Step 3: Sort high to low, then take five

ORDER BY metric_value DESC puts 99.3 first and LIMIT 5 stops at 94.9. The cut falls between two different values, so no tiebreaker decides who gets the last slot.

The solution

Five highest unique scores
SELECT DISTINCT metric_value
FROM employee_metrics
ORDER BY metric_value DESC
LIMIT 5

Dedup on metric_value, sort it high to low, keep five.

Rows, then cut
ORDER BY metric_value DESC LIMIT 5 returns 99.3, 99.3, 98.6, 98.6, 97.1: five rows, three scores.
Values, then cut
SELECT DISTINCT metric_value ... LIMIT 5 returns 99.3, 98.6, 97.1, 96.4, 94.9: the board the statement asks for.
The rows-first version
SELECT metric_value
FROM employee_metrics
ORDER BY metric_value DESC
LIMIT 5

Looks right and returns 99.3 twice and 98.6 twice: wrong on this data.

Common follow-up questions