# Top Metric per Department

> Peak performer in every department.

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

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

For each department, find the entry with the highest metric value, showing the department, metric name, value, and its rank within the department.

## Worked solution and explanation

### Why this problem exists in real interviews

The `employee_metrics` table is the foundation for this per-group ranking via `ROW_NUMBER()` or `DENSE_RANK()` partitioned by a grouping key problem. It tests whether you can compose a CTE or subquery that aggregates before ranking, then filter to the desired slice.

---

### 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 within each department

Use `ROW_NUMBER() OVER (PARTITION BY department ORDER BY aggregate DESC)` to rank entries within each partition.

#### Step 3: Filter to top entries

Wrap in a subquery and filter `WHERE rn <= N` to keep only the top entries per group.

---

### The solution

**Rank metric entries within each department and pick the top per group**

```sql
SELECT department, metric_name, total_fiscal_year
FROM (
    SELECT
        department,
        metric_name,
        SUM(fiscal_year) AS total_fiscal_year,
        ROW_NUMBER() OVER (
            PARTITION BY department
            ORDER BY SUM(fiscal_year) DESC
        ) AS rn
    FROM employee_metrics
    GROUP BY department, metric_name
) ranked
WHERE rn <= 10
ORDER BY department, total_fiscal_year DESC
```

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

> **Interviewers Watch For**
>
> Interviewers specifically test whether you use `PARTITION BY` in the window function. Omitting it gives a global ranking instead of per-group, which is at its core different.

> **Common Pitfall**
>
> Using `ORDER BY ... LIMIT` instead of a window function for per-group ranking. LIMIT gives N rows globally, not per group. Per-group top-N always requires a window function.

---

## Common follow-up questions

- If two entries in the same department share the highest metric_value, does ROW_NUMBER pick one arbitrarily? _(Tests that ROW_NUMBER = 1 returns only one; RANK or DENSE_RANK with rank = 1 returns both ties.)_
- Does the rank column in the output reflect ranking across all departments or within each department? _(Tests PARTITION BY placement; the prompt says 'within the department', so PARTITION BY department is required.)_
- How would you extend this to show the top 3 metrics per department instead of just the top 1? _(Tests changing the filter from rank = 1 to rank <= 3.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/top_metric_per_department)
- [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.