# Latest Metric Values

> Stale records hiding in the metrics.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

Some rows in the employee metrics table have outdated values for the same department and metric name. Treat the highest metric value as the current one, and keep only that row for each unique department and metric combination. If there are ties, return any one. Results should appear by department ascending.

## Worked solution and explanation

### Why this problem exists in real interviews

The employee_metrics table contains department and metric_name values that must be processed with row numbering. This appears as a fundamentals check to probe whether you reason about the correct aggregation grain before writing any window or GROUP BY clause.

> **Trick to Solving**
>
> Look for language about ties or "include all at position N." This signals `DENSE_RANK` over `ROW_NUMBER` or `LIMIT`.
> 
> 1. Identify tie-inclusion language in the prompt
> 2. Use `DENSE_RANK()` instead of `ROW_NUMBER()` or `LIMIT`
> 3. Aggregate to the correct grain before ranking

---

### Break down the requirements

#### Step 1: Filter to the target rows

Apply the `WHERE` filter to restrict the working set before aggregation. Filtering early reduces the number of rows that downstream operations process.

#### Step 2: Assign row numbers for deduplication

`ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ... DESC)` tags each row within its group. The outer query filters to `rn = 1` to keep only the target row.

#### Step 3: Order the final output

Apply `ORDER BY` as specified to produce the expected row sequence. When tied values exist, add a secondary sort column for determinism.

---

### The solution

**ROW_NUMBER partitioned by metric for latest value**

```sql
SELECT metric_id, department, metric_name, metric_value
FROM (
    SELECT metric_id, department, metric_name, metric_value,
        ROW_NUMBER() OVER (PARTITION BY metric_name ORDER BY fiscal_year DESC, fiscal_quarter DESC) AS rn
    FROM employee_metrics
) sub
WHERE rn = 1
```

> **Cost Analysis**
>
> The query scans 12K rows from `employee_metrics`.

> **Interviewers Watch For**
>
> Explaining why `ROW_NUMBER` is preferred over `DISTINCT` for deduplication shows you understand the difference between collapsing and selecting.

> **Common Pitfall**
>
> Comparing dates stored as TEXT without casting can produce lexicographic instead of chronological ordering. Always confirm the column type.

---

## Common follow-up questions

- If employee_metrics.metric_id could contain unexpected NULL values, how would your query behave? _(Tests NULL awareness even when the schema does not currently allow NULLs in metric_id.)_
- If two rows in employee_metrics have identical values in the ORDER BY columns, how does your ranking handle the tie? _(Tests understanding of RANK vs DENSE_RANK vs ROW_NUMBER tie-breaking behavior.)_
- What index would you add to employee_metrics to avoid a full table scan when filtering or sorting by metric_id? _(Tests practical indexing decisions for numeric filter columns.)_

## Related

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