# The Loudest Signals

> Only the extremes make the list.

Canonical URL: <https://datadriven.io/problems/the_loudest_signals>

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The analytics team is scanning employee_metrics for its standout readings and wants the most extreme ones, with repeated values collapsed to a single entry. Surface the five highest metric values, listed from highest to lowest.

## Worked solution and explanation

Strip the business costume and this is a top-N-distinct-values lookup, nothing more. The whole problem lives in four words of the prompt: five, highest, repeated-collapsed, and the implicit ordering. The trap is not the SQL, it is the temptation to overbuild: candidates reach for a subquery, a window function, or a GROUP BY when the answer is one flat SELECT. The other half who get it wrong botch the operator ORDER: applying LIMIT before the sort, or de-duplicating after slicing, both of which quietly return the wrong rows.

### Why collapse repeats first

The phrase 'repeated values collapsed to a single entry' is the only clause that demands DISTINCT. If two departments both report a metric_value of 99.3, you want that number to occupy ONE of your five slots, not two. The seed data happens to have all-unique values, so a query without DISTINCT passes the preview by luck, then fails the moment the grader's hidden data contains a tie. Read the requirement, not just the sample rows.

#### Step 1: Pull the single column you actually need

Only metric_value appears in the expected output, so SELECT just that. Dragging along department or metric_name would change the grain: DISTINCT over (metric_value, department) deduplicates pairs, not values, and you would get repeated numbers back. The narrow projection is what makes DISTINCT mean what you want.

#### Step 2: Sort highest-first, then take five

ORDER BY metric_value DESC establishes the ranking; LIMIT 5 slices the top of that ordered stream. The order matters: SQL evaluates DISTINCT and ORDER BY across the whole table, and LIMIT is the last thing applied. That is exactly why you can trust it to return the five largest distinct numbers and not five arbitrary ones.

**Top five distinct metric values**

```sql
SELECT DISTINCT metric_value
FROM employee_metrics
ORDER BY metric_value DESC
LIMIT 5
```

*One pass, no subquery: DISTINCT de-duplicates, ORDER BY DESC ranks, LIMIT slices.*

> **LIMIT does not run before ORDER BY**
>
> A surprising number of candidates mentally apply LIMIT first, picturing 'grab 5 rows then sort them.' SQL does the opposite: the full result set is ordered, and LIMIT takes the top of that. If you ever see only-sometimes-correct output, it is almost always because you sorted a sample instead of the whole set.

> **The tell of seniority here**
>
> On an easy problem, the interviewer is watching whether you reach for the SIMPLEST correct tool. Writing a window function (ROW_NUMBER over the table) for a plain top-5 is a yellow flag: it works but signals you do not calibrate effort to the task. The candidate who says 'DISTINCT plus ORDER BY plus LIMIT, done' and then mentions the tie edge case reads as more senior than the one who builds a CTE.

**Overbuilt**

SELECT metric_value FROM (SELECT metric_value, ROW_NUMBER() OVER (ORDER BY metric_value DESC) rn FROM employee_metrics) t WHERE rn <= 5. Works, but does not even de-duplicate, and is far more machinery than the task needs.

**Right-sized**

SELECT DISTINCT metric_value ... ORDER BY ... LIMIT 5. Fewer moving parts, correctly handles ties via DISTINCT, and the optimizer can satisfy it with a simple sort-and-truncate.

> **It is just a top-N sort**
>
> With an index on metric_value the engine can read the tail of the index descending and stop after five distinct values, never sorting the full table. Even without an index, a top-N heap sort is O(n log 5), effectively linear. There is no cheaper plan to chase; this query is already at the floor.

## Common follow-up questions

- How would you also return WHICH department reported each of the top five values? _(Forces them to confront that adding a column breaks the DISTINCT grain, pushing toward a window function or a join back to the table.)_
- What changes if the requirement becomes the five LOWEST values instead? _(Checks they understand ORDER BY direction is the only lever, not a different operator.)_
- If two values tie for fifth place, should both appear, making six rows? _(Probes whether they know LIMIT is arbitrary on ties and that RANK/DENSE_RANK is the tool when tie inclusion matters.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/the_loudest_signals)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.