Peak Satisfaction

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

Problem

The employee_metrics table keeps ten different HR metrics in one shared value column, so an analyst building the engagement report can only trust numbers taken from the satisfaction_score readings. She needs the highest and lowest satisfaction score in each fiscal year, along with how many measurements were recorded that year.

Which departments are winning on satisfaction?

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

What this problem is really testing

Strip off the engagement-report costume and this is a filtered group-wise aggregation: keep one metric's rows, collapse them to one row per fiscal_year, and carry three aggregates at once. Anyone can write MAX(metric_value). What separates candidates is noticing that metric_value is a shared column holding ten unrelated metrics, so WHERE metric_name = 'satisfaction_score' has to run before any grouping. Skip it and every year's peak and floor come from whatever metric runs hottest, and the count balloons tenfold.

Walking to the answer

Step 1: Restrict to satisfaction_score first

employee_metrics is a long, narrow table: the metric's identity lives in metric_name, not in the column name. The filter has to sit in WHERE so the other nine metrics never reach the aggregates.

Step 2: Group by fiscal_year

The report wants a per-year view, so fiscal_year is the grain. One GROUP BY gives one row per year with no extra passes over the table.

Step 3: Emit peak, floor and count together

MAX(metric_value), MIN(metric_value) and COUNT(*) all ride on the same grouping. Writing three subqueries for three numbers is the shape interviewers mark down.

SELECT
    fiscal_year,
    MAX(metric_value) AS max_score,
    MIN(metric_value) AS min_score,
    COUNT(*) AS measurement_count
FROM employee_metrics
WHERE metric_name = 'satisfaction_score'
GROUP BY fiscal_year

Common follow-up questions

• `metric_value` can be `NULL`. How would `MAX`, `MIN` and `COUNT(*)` treat those rows, and when would you switch to `COUNT(metric_value)`? (Tests how aggregates skip NULLs and whether the count should mean rows or recorded scores.)
• HR now wants the same summary for every metric at once. How does the query change? (Probes moving `metric_name` from the filter into the grouping key.)
• How would you add a breakdown by `fiscal_quarter` within each year without scanning the table twice? (Checks comfort with multi-column grouping at a finer grain.)