The Accumulator

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

L4

Asked in

Sanofi

Problem

What did the cloud bill come to on each billing date in cloud_costs, and how much had been spent in total up to and including that date? A FinOps analyst is charting how spend builds over time and wants the series from the earliest billing date forward.

A total that builds row by row. Structure the query to match.

Tables

cloud_costs

Columns: cost_id INTEGER, bill_date TEXT, amount REAL, provider TEXT, svc_name TEXT, region TEXT, acct_id TEXT

Expected output

Worked solution and explanation

This is a two-grain problem dressed up as a FinOps chart. The cost lines in cloud_costs sit at one grain, several per bill_date. The answer sits at another, one row per date, and the cumulative only means something at that second grain. Everyone knows the windowed SUM. What separates candidates is collapsing to the date grain before accumulating. Skip that and the dashboard gets 200 rows where it wanted 57, with every date repeated once per cost line.

Two grains, two steps

Step 1: Roll each date up to one figure

GROUP BY bill_date with SUM(amount) turns the several lines on a date into a single daily_spend. Negative amounts are credits and belong in the sum. A line with a missing amount adds nothing, because SUM skips NULLs, so its date still appears.

Step 2: Accumulate over the daily figures, not the raw lines

SUM(daily_spend) OVER (ORDER BY bill_date) reads the daily CTE. With an ORDER BY and no explicit frame, the window covers every earlier date plus the current one, so each row carries spend through and including its date. That is the figure the question asks for.

Step 3: Sort the series oldest first

The ORDER BY inside OVER decides what gets summed, not how rows come back. The outer ORDER BY bill_date is a separate promise to the reader. With one row per date it is already total, so no tiebreaker is needed.

WITH daily AS (
    SELECT bill_date, SUM(amount) AS daily_spend
    FROM cloud_costs
    GROUP BY bill_date
)
SELECT bill_date, daily_spend, SUM(daily_spend) OVER (ORDER BY bill_date) AS cumulative_spend
FROM daily
ORDER BY bill_date

Window over raw lines

SUM(amount) OVER (ORDER BY bill_date) straight on cloud_costs emits one row per cost line. With the default frame, peers on a date share the same cumulative, so the numbers look right while each date appears three or four times. Switch to a ROWS frame and the repeats turn into partial sums partway through the date.

Window over daily totals

GROUP BY bill_date first, then the window over daily_spend. One row per date, each carrying the full date total and the full cumulative through it. The shape the chart needs.

SELECT bill_date, amount, SUM(amount) OVER (ORDER BY bill_date) AS cumulative_spend
FROM cloud_costs
ORDER BY bill_date

Common follow-up questions

• How would you restart the cumulative at the start of each calendar year? (Partitioning the window by a year derived from `bill_date`.)
• Finance wants the cumulative per provider, but `provider` holds both 'aws' and 'AWS'. What changes? (Normalizing case before grouping, then partitioning the window by the cleaned provider.)
• Billing dates land on the first of each month. How would you show a trailing three-month total instead of all-time spend? (An explicit frame such as `ROWS BETWEEN 2 PRECEDING AND CURRENT ROW`, and when `ROWS` and `RANGE` disagree.)
• Late cost lines arrive for a month already reported. How do you update the series without recomputing all of it? (Incremental maintenance: recompute that date's total and shift every later cumulative by the difference.)