# Top Metric Values

> The five highest numbers. No duplicates.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

Surface the five highest unique metric values from employee metrics. If two entries share the same value, count that as one.

## Worked solution and explanation

### Why this problem exists in real interviews

Querying `employee_metrics` for top metric values requires filtering to the top rows after aggregation. Interviewers watch for whether the candidate aggregates first or tries to rank raw rows, which is the most common mistake.

---

### Break down the requirements

#### Step 1: Aggregate per metric_name

`GROUP BY metric_name` with the appropriate aggregate function produces one summary row per group from the `employee_metrics` table.

#### Step 2: Rank the results

`ORDER BY` the aggregate descending with `LIMIT` to surface the top entries.

---

### The solution

**Select distinct metric_value from employee_metrics ordered descending limit 5**

```sql
SELECT
    metric_name,
    SUM(fiscal_year) AS total_fiscal_year
FROM employee_metrics
GROUP BY metric_name
ORDER BY total_fiscal_year DESC
LIMIT 10
```

> **Cost Analysis**
>
> The GROUP BY reduces the 10K-row `employee_metrics` table to the number of distinct `metric_name` values. A covering index on `(metric_name, fiscal_year)` enables an index-only aggregate scan.

> **Interviewers Watch For**
>
> Interviewers verify you aggregate before sorting. Sorting raw rows gives per-row values, not group totals. The correct grain is one row per `metric_name`.

> **Common Pitfall**
>
> Using the wrong aggregate function. `SUM` gives totals, `COUNT` gives volume, `AVG` gives rates. Read the prompt to determine which metric is needed.

---

## Common follow-up questions

- If you write SELECT DISTINCT metric_value ORDER BY metric_value DESC LIMIT 5, does the DISTINCT apply before or after the ORDER BY? _(Tests SQL execution order; DISTINCT applies after ORDER BY, but logically deduplication happens on the projected set.)_
- Would DENSE_RANK on metric_value and filtering rank <= 5 achieve the same result? _(Tests alternative approaches; DENSE_RANK on the value itself groups identical values, giving the same top-5 unique values.)_
- If metric_value has NULLs, does DISTINCT treat all NULLs as one value, and where does NULL sort? _(Tests NULL behavior in DISTINCT and ORDER BY; NULLs collapse to one row and sort last (or first, depending on engine).)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/top_metric_values)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.