# Latest Metric Values

> Stale records hiding in the metrics.

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

Domain: SQL · Difficulty: easy · Seniority: L3 · Asked in: Microsoft

## 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.

## Tables

### employee_metrics

- metric_id INTEGER
- department TEXT
- metric_name TEXT
- metric_value REAL
- fiscal_quarter TEXT
- fiscal_year INTEGER

Sample rows (10 of 200):

| metric_id | department | metric_name | metric_value | fiscal_quarter | fiscal_year |
| --- | --- | --- | --- | --- | --- |
| 141 | Product | attrition_rate | 7.3 | Q2 | 2025 |
| 182 | Design | avg_tenure_months | 14.6 | Q3 | 2026 |
| 223 | Marketing | satisfaction_score | 21.9 | Q4 | 2024 |
| 264 | Sales | training_hours | 29.2 | Q1 | 2025 |
| 305 | HR | promotions | 36.5 | Q2 | 2026 |
| 346 | Finance | open_positions | 43.8 | Q3 | 2024 |
| 387 | Legal | time_to_hire_days | 51.1 | Q4 | 2025 |
| 428 | Operations | diversity_ratio | 58.4 | Q1 | 2026 |
| 469 | Data | remote_percentage | 65.7 | Q2 | 2024 |
| 510 | Engineering | headcount | 73 | Q3 | 2025 |

## Expected output

First 5 of 10 rows:

| department | metric_name | metric_value |
| --- | --- | --- |
| Data | remote_percentage | 84.7 |
| Design | avg_tenure_months | 98.6 |
| Engineering | headcount | 92 |
| Finance | open_positions | 89.8 |
| HR | promotions | 93.5 |

## 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

Group the rows by the unique (department, `metric_name`) combination so each combination collapses to a single output row.

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

Within each group take MAX(`metric_value`) to treat the highest value as the current one; ties naturally resolve to that single max value.

#### Step 3: Order the final output

Order the result by department ascending as the prose requires.

---

### The solution

**`ROW_NUMBER` partitioned by metric for latest value**

```sql
SELECT department, metric_name, MAX(metric_value) AS metric_value
FROM employee_metrics
GROUP BY department, metric_name
ORDER BY department ASC
```

> **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). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.