The Upper Rungs

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

A compensation team is setting reference points for a new salary-band ladder, where a value that appears more than once still counts only once. Return the five highest values from the employee metrics table, highest first.

The top rungs set the ceiling for everyone below.

Tables

employee_metrics

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

Expected output

Worked solution and explanation

What this really tests

This is a top-N over values wearing a compensation costume. Anyone can sort high to low and take five off the top. What separates candidates is noticing the clause that says a repeated value counts only once: you want the five highest VALUES, not the five highest ROWS. On this table every value is stored in two rows, so skip the dedupe and the ladder comes back with three real rungs and two echoes, without a single error being raised.

Building it up

Step 1: Collapse repeats first

SELECT DISTINCT metric_value collapses repeated values so an identical peak posted twice is treated as one level, not two. This is the line the prompt is really probing.

Step 2: Sort highest first

ORDER BY metric_value DESC puts the largest value on top. Descending is the whole point: the compensation team wants the ceiling, not the floor.

Step 3: Take the top five

LIMIT 5 keeps only the top five. If the table held fewer than five distinct values, LIMIT would quietly return whatever exists, which is exactly the desired behavior.

The solution

SELECT DISTINCT metric_value AS benchmark_value
FROM employee_metrics
ORDER BY metric_value DESC
LIMIT 5

Without DISTINCT

ORDER BY metric_value DESC LIMIT 5 over raw rows. Each value sits in two rows, so the result is 99.3, 99.3, 98.6, 98.6, 97.1: only three real levels.

With DISTINCT

SELECT DISTINCT first, then order and limit. The result is 99.3, 98.6, 97.1, 96.4, 94.9: five genuinely different benchmark levels.

Common follow-up questions

• How would you return the five highest distinct values within each `department` instead of across the whole table? (Tests generalizing top-N into a per-group ranking, which needs a window function.)
• `metric_value` is NULL in a dozen rows of this table. Where do those rows land in your descending sort, and what would you add so the answer holds on an engine that sorts NULLs first? (Tests knowing that NULL placement in a descending sort differs between engines, and guarding with `IS NOT NULL` or `NULLS LAST`.)
• What index would let this query avoid sorting the entire table to find the top five? (Tests awareness of the top-N index optimization versus a full sort.)