Department Running Totals

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L5

Asked in

Goldman Sachs

Problem

The employee_metrics table holds one row per metric reading, tagged with its department, fiscal year and quarter; a department often logs several readings in the same quarter, and a reading with no value adds nothing. Return every reading with an extra column holding its department's metric_value accumulated through the end of that reading's fiscal quarter, left empty while nothing has been recorded yet, listed by department alphabetically and then earliest fiscal period first.

Compute cumulative metric values within each department using window operations.

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 per-department cumulative sum wearing a budget-review costume, and keeping the rows is the easy half. What separates candidates is deciding what counts as one step. A department logs several readings in the same quarter, so the figure has to advance a whole quarter at a time: all four Data readings in Q2 2024 show 138.8, not four different subtotals. Collapse to one row per department and you return 10 rows instead of 200. Sequence by quarter alone and every Q1 sums three years together.

Getting to the query

Step 1: Keep every reading as its own row

The ask is the 200 readings, not a department summary. A GROUP BY department with SUM(metric_value) answers a different question: 10 rows, each holding the department's grand total. The figure has to ride alongside each row, which is what an aggregate with OVER (...) does: it computes across rows without folding them.

Step 2: Scope the sum to the department

PARTITION BY department restarts the accumulation for each of the ten departments. Leave it out and every reading accumulates across all departments at once, so a figure that should read 76.0 for Engineering's first quarter carries the whole company's history instead.

Step 3: Sequence by year, then quarter

ORDER BY fiscal_year, fiscal_quarter inside the window. The quarters are stored as text, and 'Q1' through 'Q4' happen to sort in calendar order, so no mapping is needed. Drop fiscal_year and the three years' Q1 readings land in a single step.

Step 4: Let same-quarter readings share the figure

With an ORDER BY and no explicit frame, the window frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and a range frame includes every row tied on the sort keys. That is exactly 'through the end of the quarter': each reading sees every reading in its own quarter, earlier or not.

SELECT
    metric_id,
    department,
    metric_name,
    metric_value,
    fiscal_quarter,
    fiscal_year,
    SUM(metric_value) OVER (
        PARTITION BY department
        ORDER BY fiscal_year, fiscal_quarter
    ) AS running_total
FROM employee_metrics
ORDER BY department, fiscal_year, fiscal_quarter

Common follow-up questions

• What changes if the figure should restart every fiscal year? (Add `fiscal_year` to `PARTITION BY` and order the window by `fiscal_quarter` alone.)
• Finance now wants readings inside a quarter to accumulate one at a time. What do you need? (A `ROWS` frame plus a unique tiebreaker such as `metric_id` in the window order, or the result is not deterministic.)
• How would you show each department's cumulative average instead? (`AVG(metric_value)` over the same window; note it averages only the recorded readings.)