# Metric Range Per Group

> The spread within each group.

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

Domain: SQL · Difficulty: easy · Seniority: L5

## Problem

HR suspects some departments have much wider performance variance than others. For each department, compute the spread between its highest and lowest metric values, ordered alphabetically by department.

## Worked solution and explanation

### Why this problem exists in real interviews

The interviewer wants to see you apply grouping to employee_metrics.department while accounting for the distribution of metric_value. This surfaces as a fundamentals check because small logic errors produce results that look correct at a glance.  Getting the column references right on the first try is what interviewers watch for.

---

### Break down the requirements

#### Step 1: Aggregate with MIN/MAX

Group by the output grain and apply `MIN()` to compute the metric. The `GROUP BY` must match exactly what the output needs: one row per group key.

#### Step 2: 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

**MAX minus MIN for spread calculation**

```sql
SELECT department, MAX(metric_value) - MIN(metric_value) AS metric_spread
FROM employee_metrics
GROUP BY department
ORDER BY department
```

> **Cost Analysis**
>
> The query scans 8K rows from `employee_metrics`. CTEs in most engines are optimization fences. For production workloads, consider inlining or materializing the intermediate results.

> **Interviewers Watch For**
>
> Naming the output grain ("one row per X") before writing the GROUP BY shows you think about data shape, not just syntax. Breaking complex logic into named CTEs shows the interviewer you prioritize readability and debuggability.

> **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.)_
- How would you verify that your aggregation on employee_metrics.metric_id is not double-counting due to duplicate rows? _(Tests data quality awareness and deduplication strategies.)_
- 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/metric_range_per_group)
- [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.