Latest Metric Values

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Microsoft

Problem

Each department and metric name in employee_metrics is recorded on many rows as its value was revised, and the highest metric_value among them is the current one. List every department and metric name with its current value, departments from A to Z.

Stale records hiding in the metrics.

Tables

employee_metrics

Columns: metric_id INTEGER, department TEXT, metric_name TEXT, metric_value REAL, fiscal_quarter TEXT, fiscal_year INTEGER

Expected output

Worked solution and explanation

This is a greatest-per-group collapse dressed up as a data cleanup. Every department and metric pair sits on 20 rows of employee_metrics, and the answer wants one row per pair carrying its peak metric_value. Anyone can see the duplicates. What separates candidates is the key: collapse on exactly department and metric_name, keep the maximum, and 200 rows fold to 10. Reach for SELECT DISTINCT, or let metric_id or the fiscal period into the key, and every stale lower value survives as its own row.

Getting to the query

Step 1: Name the key the answer lives at

The result has one row per (department, metric_name) pair, so those two columns and nothing else form the key. metric_id is unique per row, and fiscal_quarter and fiscal_year change inside a pair. Any of them in the key splits a pair back into its source rows.

Step 2: Let the value win by size, not by date

Current means highest here, not most recent, so MAX(metric_value) per pair is the whole computation. The fiscal columns play no part, and that matters: the latest fiscal period for Data holds 30.7 while its peak is 84.7.

Step 3: Sort by department

Each department pairs with exactly one metric on this table, so department appears once in the result and sorting on it alone fixes the order with no tiebreaker.

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

Removing duplicate rows

SELECT DISTINCT department, metric_name, metric_value dedupes whole tuples. Each pair holds about ten different values, so it returns roughly 100 rows, stale ones included.

Collapsing on the key

GROUP BY department, metric_name with MAX(metric_value) makes the pair the unit. Ten rows come out, each with its peak.

SELECT DISTINCT department, metric_name, metric_value
FROM employee_metrics
ORDER BY department

Common follow-up questions

• What if the business wants the whole row behind the peak, `metric_id` and fiscal period included? (Moves from a plain aggregate to picking one row per group, with a tie rule, since copies share the peak.)
• What if current means the latest `fiscal_year` and `fiscal_quarter` instead of the highest value? (Ordering a period split across an integer year and a text quarter.)
• How would you report only the pairs whose peak is above 50? (Filtering groups after they are formed rather than rows before.)