# Best in Show > Every department has a peak. Name it, once each. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 ## 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. ## 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 > **Trap 1: a top-N tool that breaks ties** > > The instinct is `ORDER BY metric_value DESC LIMIT 1`, or `ROW_NUMBER()` filtered to 1. Both pick exactly one row per department, so when two metrics tie for the top of a department, one is thrown away with no warning. RANK() gives every tied top value rnk = 1, so they all survive the `WHERE rnk = 1` filter. > **Trap 2: duplicate rows double-count** > > The raw table logs the same reading more than once, so identical (department, metric_name, metric_value) rows exist. Rank them and every duplicate still carries rnk = 1, so they all pass the filter and the winner appears two or three times. DISTINCT on the final projection collapses them back to one entry. This is the trap the naive query walks straight into. --- ### 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 **Rank per department, keep rank 1, dedup** ```sql 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_name ``` > **Cost Analysis** > > The window scans the 12K-row table once and partitions by department (cardinality 10), so the sort work is cheap and bounded. An index on (department, metric_value DESC) lets the engine feed the partitions pre-sorted; the outer DISTINCT then operates on the tiny rnk = 1 slice, not the full table. > **Interviewers Watch For** > > The tell is whether PARTITION BY department is present and whether the candidate reaches for RANK over ROW_NUMBER. Dropping the partition gives a single global winner; using ROW_NUMBER quietly drops tied co-leaders. Both read as someone who has memorized the pattern without understanding what it does to ties. --- ## Common 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/best_in_show) - [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.