# The Slow Build

> Month over month, the number grows. Track how the average moves with it.

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

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

For each month in 2026, show the total revenue and a running average of all months up to and including the current one, rounded to the nearest whole number.

## Worked solution and explanation

### What this problem really is

Underneath the revenue-dashboard costume, this is a two-level aggregation: an average of monthly totals, not an average of transactions. The skill being probed is whether you can layer a cumulative window on top of an already-grouped result and pin the frame down explicitly. The trap that separates candidates: they average the raw total_amount rows instead of the per-month sums, or they try to nest SUM and AVG in one flat SELECT. Do either and the cumulative number comes out off by orders of magnitude, quietly, with no error to warn you.

---

### Break down the requirements

#### Step 1: Aggregate revenue per year-month

Per-month revenue: filter transactions to 2026 via strftime('%Y', transaction_date), bucket by strftime('%Y-%m', transaction_date), and SUM(total_amount). No product join is needed since revenue lives entirely on transactions.

#### Step 2: Wrap the aggregate in a subquery

Wrap that year-month aggregate as a subquery so the monthly totals become rows a window function can read. This is the crux: the average has to sit on top of the grouped totals, not the raw transactions.

#### Step 3: Compute the running average with an explicit ROWS frame

Compute the running average with AVG(monthly_revenue) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW): a cumulative frame from the first month up to and including the current one.

#### Step 4: Round to a whole number and cast to REAL

Wrap the averaged value in ROUND, then CAST it AS REAL so the output is a whole number carried as a float, matching the expected cumulative_avg column.

---

### The solution

**Year-month aggregate plus a ROWS-framed cumulative AVG**

```sql
SELECT month, monthly_revenue,
  CAST(ROUND(AVG(monthly_revenue) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS REAL) AS cumulative_avg
FROM (
  SELECT strftime('%Y-%m', transaction_date) AS month,
         SUM(total_amount) AS monthly_revenue
  FROM transactions
  WHERE strftime('%Y', transaction_date) = '2026'
  GROUP BY strftime('%Y-%m', transaction_date)
)
ORDER BY month
```

> **Cost Analysis**
>
> transactions has 120,000,000 rows. The strftime filter on transaction_date is non-sargable, so without an index this is a full scan. After aggregation, the result is at most 12 rows per year, so the window function is essentially free. The dominant cost is reading and grouping 120M rows; production systems would precompute monthly_revenue into a small rollup table.

> **Interviewers Watch For**
>
> They want strftime (not Postgres TO_CHAR or DATE_TRUNC), they want the explicit ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW frame, and they want the inner subquery handling the GROUP BY. A candidate who tries to nest SUM and AVG in a single SELECT without a subquery (or who reaches for a self-join sum-of-prior-months pattern) is signaling weak window-function fluency.

> **Common Pitfall**
>
> Omitting ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW falls back to the RANGE default, which can include rows tied on the ORDER BY value. With unique year-month strings the result happens to match here, but the habit is dangerous and older SQLite versions had bugs around RANGE frames. Forgetting to CAST the ROUND result AS REAL returns an INTEGER, and downstream schemas may reject it.

---

## Common follow-up questions

- How would the answer change if you needed a 3-month rolling average instead of cumulative? _(Switches the frame to ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. The candidate should note that the first two months are computed over fewer values and decide whether to NULL them or accept the partial window.)_
- What if the spec said 'across all years in the table' rather than just one year? _(Drops the year filter and makes the cumulative average span every month in transactions. The candidate should mention adding PARTITION BY strftime('%Y', month) if the average should reset each year.)_
- Why ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW instead of RANGE? _(Tests frame semantics. ROWS counts physical rows; RANGE counts logical values tied on the ORDER BY key. With unique month strings they match, but the candidate should explain why ROWS is the safer default for cumulative aggregates.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/the_slow_build)
- [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). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.