Running total of revenue by day, plus each day's cumulative share of the year
Table daily_revenue(day, revenue) holds one row per day for a single year. For each day return its revenue, the running total up to and including that day, and that running total as a percent of the whole year's revenue.
-- per day: revenue, running total, and cumulative share of the year totalWrite the query.
A cumulative SUM(revenue) OVER (ORDER BY day) gives the running total — its default frame runs from the start to the current row. Divide it by the year total from SUM(revenue) OVER (), whose frame is every row.
- ✗Swapping which window (ordered vs unordered) accumulates day by day
- ✗Believing a window frame cannot produce a running total
- ✗Adding
PARTITION BY day, which isolates each day and breaks accumulation
- →What is the default frame of
SUM(...) OVER (ORDER BY day)? - →How would you reset the running total at the start of each month?
The running total is an ordered window sum; its default frame RANGE UNBOUNDED PRECEDING AND CURRENT ROW accumulates from the start to the current row. The year total is the unordered window OVER (), which spans every row:
SELECT day,
revenue,
SUM(revenue) OVER (ORDER BY day) AS running_total,
ROUND(100.0 * SUM(revenue) OVER (ORDER BY day)
/ SUM(revenue) OVER (), 2) AS cumulative_pct
FROM daily_revenue
ORDER BY day;The key is the contrast: ORDER BY day gives an accumulating frame, while an empty OVER () gives the full partition total. PARTITION BY day would be a mistake — it isolates each day, collapsing the running total to that one day's revenue.
