# Metric Volatility Gap

> Stable metrics are boring. Volatile ones need attention.

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

Domain: SQL · Difficulty: easy · Seniority: L4

## Problem

HR suspects some departments have wild performance swings while others are stable. Show each department's metric value spread (highest minus lowest), with the most volatile departments first.

## Worked solution and explanation

### Why this problem exists in real interviews

Against the employee_metrics table, grouping and gap-and-island on department values is the key operation. Interviewers favor this as a fundamentals check because it exposes whether candidates handle ties, NULLs, and ordering edge cases correctly.

---

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

**Spread as volatility proxy**

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

> **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. Walking through comparison logic step by step, rather than writing it in one pass, demonstrates structured thinking.

> **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 employee_metrics has duplicate timestamps for the same entity, how does the gap detection break? _(Tests awareness of the row-number gap technique's sensitivity to duplicates.)_
- 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_volatility_gap)
- [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.