# Department Running Totals

> Compute cumulative metric values within each department using window operations.

Canonical URL: <https://datadriven.io/problems/department_running_totals>

Domain: SQL · Difficulty: medium · Seniority: L5

## Problem

Ahead of the annual budget cycle, the CFO wants to see how each department's metric values accumulate quarter over quarter. Return every metric record with an additional column showing the running total of metric_value within each department, sequenced by fiscal year and quarter.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests running totals with SUM() OVER (PARTITION BY ... ORDER BY ...). The requirement to accumulate metric values within each department probes partitioned window function fluency.

---

### Break down the requirements

#### Step 1: Select all metric columns

Return the full row including department, metric_name, metric_value, fiscal_quarter, fiscal_year.

#### Step 2: Add running total

`SUM(metric_value) OVER (PARTITION BY department ORDER BY fiscal_year, fiscal_quarter)` computes the cumulative total within each department.

---

### The solution

**Partitioned running total**

```sql
SELECT
    *,
    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
```

> **Cost Analysis**
>
> Scan of 12K rows with window function. Trivially fast at this scale.

> **Interviewers Watch For**
>
> Whether the candidate orders by both fiscal_year and fiscal_quarter in the window function. Ordering by just quarter would interleave years.

> **Common Pitfall**
>
> Omitting `fiscal_year` from the ORDER BY would mix Q1 from different years, producing incorrect running totals across year boundaries.

---

## Common follow-up questions

- What if you needed to reset the running total each fiscal year? _(Add fiscal_year to the PARTITION BY clause.)_
- How would you compute a running average instead of a running total? _(Use AVG() instead of SUM() with the same window frame.)_
- What if metric_value contains NULLs? _(SUM ignores NULLs, so the running total skips them. Tests NULL awareness.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/department_running_totals)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.