Cumulative Sum with Monthly Reset
Return each order with a cumulative monthly amount in chronological order.
- Window functions
- Aggregation
- Date analysis
- Type conversion
- Sorting
Exercise brief
Understand the request
Finance operations lead A transaction ledger needs a running amount that restarts at each calendar month.
A transaction ledger needs a running amount that restarts at each calendar month. Return each order with a cumulative monthly amount in chronological order.
Return
- Return order_date, order_total, cumulative_amount_month in this exact left-to-right order.
Constraints
- Partition the running total by year-month.
- Use an explicit ROWS frame.
- Use order_id as a stable tie-breaker within a date.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
orders
order_idINTEGERorder_dateDATEorder_totalDECIMAL
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
A running total that resets monthly = PARTITION BY the month key.
Hint 2
Build the month key with strftime('%Y-%m', order_date) (or DATE_TRUNC / DATE_FORMAT).
Hint 3
ORDER BY within the window controls the accumulation order.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT order_date, order_total, CAST(SUM(order_total) OVER (PARTITION BY strftime('%Y-%m', order_date) ORDER BY order_date, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS INTEGER) AS cumulative_amount_month FROM orders ORDER BY order_date, order_id;Why this works
Partitioning a windowed SUM by the month key restarts the accumulation each month — a common 'within-period running total'. The only engine difference is how you derive the month-bucket key for PARTITION BY. With distinct order dates the default frame is fine; for tied dates add an explicit ROWS frame.
Success check
Every order remains at transaction grain and each month begins a new deterministic cumulative total.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| order_date | order_total | cumulative_amount_month |
|---|---|---|
| 2023-01-10 | 200 | 200 |
| 2023-02-15 | 340 | 340 |
| 2024-01-15 | 250 | 250 |
| 2024-01-20 | 180 | 430 |
| 2024-02-10 | 320 | 320 |
| 2024-02-25 | 150 | 470 |
| 2024-03-05 | 420 | 420 |
| 2024-03-15 | 280 | 700 |
| 2024-04-01 | 190 | 190 |
| 2024-04-10 | 350 | 540 |
Previewing 10 of 20 expected rows. Run the query in the editor to inspect the full result.
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
WHERE Clause & Filtering
Practice SQL WHERE clauses with realistic boundary, NULL, text, date, exclusion, and production-filtering problems.
SQL Aggregations
Build reliable SQL metrics from aggregate functions through grain, fan-out, weighted ratios, rollups, percentiles, and approximate counts.
CTEs & Window Functions
Practice modular CTE pipelines, deterministic window analytics, period comparisons, deduplication, frames, and gaps-and-islands.
Open the interactive workspace and practice across SQL topics.