Metric Range Per Group

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

L5

Asked in

JPMorgan Chase

Problem

Each row of employee_metrics is one reading tagged with its department, the metric it measures, and its fiscal quarter and year. Give every department's spread between its highest and lowest metric_value across all of its readings, with departments listed alphabetically.

The spread within each group.

Tables

employee_metrics

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

Expected output

Worked solution and explanation

This is a per-group spread in an HR costume. Every candidate can type MAX and MIN; what separates them is the scope those two read over. The extremes must come from inside each department, with every metric and every quarter pooled together, nothing narrower and nothing wider. Reach for AVG because the story said 'variance', or slice by metric_name as well, and you return numbers that describe something else: a typical value, or dozens of small per-metric ranges that no longer compare departments.

Step 1: Fix the grain at one row per department

The answer is a department list, so department is the only grouping key. metric_name, fiscal_quarter and fiscal_year all vary inside a department, and the spread is meant to pool them. Adding any of them to the key turns ten rows into dozens and shrinks every range.

Step 2: Take both extremes inside the same group

MAX(metric_value) - MIN(metric_value) is evaluated once per department, so the high and the low always come from the same set of rows. Name the result metric_spread so the column says what it holds.

Step 3: Sort by department name

The list reads A to Z, so ORDER BY department finishes the query. Each department appears exactly once, so there are no ties and no second key is needed.

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

Common follow-up questions

• How would you report the spread per department and per `metric_name`, so a headcount range is never compared with a rate? (Tests moving the grain on purpose and explaining what the new numbers mean.)
• Which `metric_name` produced each department's highest and lowest reading? (Tests carrying a row's attributes alongside an extreme, which a plain aggregate discards.)
• How would you keep only the departments whose spread is above the average spread across all departments? (Tests filtering on a value computed from the grouped result.)