Metric Volatility Gap

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: L4
Asked in: JPMorgan Chase

Problem

Report how widely each department's performance swings in employee_metrics, measured as the gap between its highest and lowest metric value, most volatile department first. Departments with the same gap are listed alphabetically.

Stable metrics are boring. Volatile ones need attention.

Tables

employee_metrics

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

employee_metrics: 10 of 200 rows
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	volatility
Design	92
HR	92
Legal	89
Marketing	89
Data	81

Worked solution and explanation

What this really is

This is a per-group range dressed up as an HR volatility report. Each department needs its own highest and lowest metric_value, subtracted inside the department, and then the departments sorted by that gap. Anyone can write a max minus a min. What separates candidates is keeping the extremes scoped to the department and noticing that the gap ties: Design and HR both swing by 92.0, and Legal and Marketing by 89.0. Skip the alphabetical tiebreak and those pairs come back in whatever order the engine likes, and the answer grades wrong.

Step 1: Scope the extremes to the department

GROUP BY department gives each of the ten departments its own MAX(metric_value) and MIN(metric_value). Leave the grouping out and you get one spread for the whole table. Add metric_id to it and every group is a single row whose spread is 0.

Step 2: Subtract inside the aggregated row

The swing is MAX(metric_value) - MIN(metric_value), computed once per department and named volatility so the sort can refer to it by name. An average would tell you where a department sits, not how far it moves.

Step 3: Sort by the gap, then by name

ORDER BY volatility DESC puts the widest swing first, and department breaks the 92.0 and 89.0 ties alphabetically, exactly as the preview lists Design before HR.

Spread as volatility

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

One row per department, widest volatility first, ties broken by department.

Averaging the values: AVG(metric_value) reports each department's typical level. A department bouncing between 6.6 and 98.6 and one parked near 50 can land on the same average, so the report cannot tell volatile from stable.
Taking the extremes: MAX(metric_value) - MIN(metric_value) is the distance between the best and worst reading, which is what a swing is. Design's 92.0 comes straight from its 98.6 high and 6.6 low.

Common follow-up questions

How would you report volatility per department per `fiscal_year`? (Tests moving the grain without losing the per-group extremes.)
One outlier reading can set the whole gap. How would you make the measure robust? (Tests reaching for a percentile range or a standard deviation instead of raw extremes.)
How would you return only the three most volatile departments, and what happens if the third and fourth tie? (Tests a cut that falls on a tie and needs a stated winner.)