Metric Trend by Department

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

Asked in

BearingPoint

Problem

Our department scorecards show a single all-time average of the tracked metrics per team, which hides whether a department rose or fell from one fiscal year to the next. Rebuild the scorecard from employee_metrics with that year-by-year view, departments alphabetically and each one's earliest year first.

How each team's numbers moved, year over year.

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

This is a grain question dressed up as a trend report. The skill being probed: can you collapse employee_metrics to exactly one row per department per fiscal year? Everyone reaches for AVG(metric_value). What separates candidates is naming the grain before typing. Group by department alone and you hand back 10 blended all-time numbers instead of the 30-row year-by-year series, and the movement the scorecard exists to show disappears.

Getting to the answer

Step 1: Name the grain out loud

The ask is a year-by-year view per department, so one result row is one (department, fiscal_year) pair. On the seed that is 10 departments times 3 fiscal years (2024 to 2026), which is 30 rows. Say that number before writing SQL, because it is your check when the query returns.

Step 2: Blend every metric row in the cell

metric_name holds ten different measures (headcount, attrition_rate and more), and the scorecard wants one blended number per cell, so metric_name stays out of the grouping. AVG skips the 12 rows whose metric_value is NULL, which is what you want: a missing reading is not a zero.

Step 3: Sort the way the reader scans

Departments alphabetically, earliest year first: ORDER BY department, fiscal_year. Both sort keys are the grouping keys, so every row has its own position and no tiebreaker is needed.

SELECT department, fiscal_year, AVG(metric_value) AS avg_metric_value FROM employee_metrics GROUP BY department, fiscal_year ORDER BY department, fiscal_year

Grouped by department only

GROUP BY department returns 10 rows, each averaging all three years together. The 2024 and 2026 readings cancel into one number, and the year-over-year movement is gone.

Grouped by department and year

GROUP BY department, fiscal_year returns 30 rows, one per cell. A reader can scan Data from 44.2 in 2024 up to 57.7 in 2025 and back down to 30.7 in 2026.

Common follow-up questions

• How would you add each department's change from its previous fiscal year? (Tests reading the prior row inside each department's ordered series.)
• The scorecard now wants a separate average per `metric_name` as well. What happens to the grain and the row count? (Tests re-deriving the grain instead of patching the query.)
• What if `department` started arriving as both 'data' and 'Data'? (Tests awareness that unnormalized keys silently split groups.)
• How would you show a department-year with no logged metrics as an explicit empty cell? (Tests building the full department-by-year grid before averaging.)