The Loudest Signals

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

Problem

Readings in employee_metrics often repeat the same value, and the analytics team's shortlist of standout highs should list each value only once. Surface the five highest metric values, listed from highest to lowest.

Only the extremes make the list.

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 top five over unique values dressed up as a scan for standout readings. Everyone writes ORDER BY metric_value DESC LIMIT 5. What separates candidates is seeing that the five slots belong to five different numbers, not five rows. In employee_metrics every filled reading is stored twice, so the query without DISTINCT returns 99.3, 99.3, 98.6, 98.6, 97.1. That is three numbers, with 96.4 and 94.9 silently pushed off the shortlist.

Why collapse repeats first

The prompt's line about repeated values is the only clause asking for de-duplication, and on this table it is not hypothetical. 188 rows carry a metric_value, but they hold just 94 different numbers, each exactly twice. The first ten sample rows show no repeat because the copies are not stored next to each other. Read the requirement against the whole table, not the preview.

Step 1: Project only metric_value

The answer is a list of numbers, so select that one column. If you also select department or metric_name, DISTINCT de-duplicates pairs instead of values, and the same number can come back once per pair.

Step 2: De-duplicate, sort highest first, then cut at five

DISTINCT collapses the copies, ORDER BY metric_value DESC puts the largest first, and LIMIT 5 is applied last, to the sorted unique list. The 12 empty readings sort below every number in this descending sort, so they never reach the cut.

SELECT DISTINCT metric_value
FROM employee_metrics
ORDER BY metric_value DESC
LIMIT 5

Five rows

ORDER BY metric_value DESC LIMIT 5 with no DISTINCT returns 99.3, 99.3, 98.6, 98.6, 97.1. That is five rows but only three readings, and 96.4 and 94.9 never appear.

Five readings

SELECT DISTINCT metric_value with the same sort and cut returns 99.3, 98.6, 97.1, 96.4, 94.9. Every slot holds a different reading.

Common follow-up questions

• How would you also show which departments reported each of the top five values? (Adding `department` changes what `DISTINCT` collapses, so the clean answer finds the top values first and then joins back to `employee_metrics`.)
• What changes if the shortlist becomes the five lowest readings? (The sort direction flips, and in an ascending sort the 12 empty `metric_value` rows can land first, so they must be excluded explicitly.)
• What if this ran on an engine that sorts empty values first in a descending sort? (Postgres puts NULLs first under `DESC`. A portable answer filters them out or writes `NULLS LAST`.)