Scenario: Brightlane's finance team is tracking cumulative revenue from delivered orders only.
Task: Write a query to return each order month in which at least one delivered order was placed, the total delivered revenue for that month, and the running total of delivered revenue from the earliest such month through that month.
Assumptions:
- A delivered order has
statusequal to'delivered'. - An order month is identified by its first day.
- A month's
monthly_delivered_revenueis the combinedtotal_amountacross deliveredordersplaced in that month. - A month's
cumulative_delivered_revenueis the combinedmonthly_delivered_revenuefrom the earliest delivered-order month through that month inclusive. - The result covers only months containing at least one delivered order.
Output:
- One row per qualifying order month.
- Columns in this order:
order_month,monthly_delivered_revenue,cumulative_delivered_revenue. - Sorted by
order_monthascending.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
DATE_TRUNC('month', ordered_at) AS order_month,
SUM(total_amount) AS monthly_delivered_revenue,
SUM(SUM(total_amount)) OVER (
ORDER BY
DATE_TRUNC('month', ordered_at) ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS cumulative_delivered_revenue
FROM
orders
WHERE
status = 'delivered'
GROUP BY
DATE_TRUNC('month', ordered_at)
ORDER BY
order_monthThe shape
Filter to delivered orders first, then run the standard aggregate-and-accumulate pattern. WHERE status = 'delivered' removes every non-delivered order before the GROUP BY runs, so the monthly aggregate only sees delivered rows. The window function then accumulates those filtered monthly totals from the earliest delivered month forward.
Clause by clause
SELECT DATE_TRUNC('month', ordered_at) AS order_month, SUM(total_amount) AS monthly_delivered_revenueproduces one row per month with that month's delivered revenue. Only delivered orders contribute because theWHEREfilter has already eliminated the rest.SUM(SUM(total_amount)) OVER (ORDER BY DATE_TRUNC('month', ordered_at) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_delivered_revenueaccumulates the per-month delivered totals from the earliest delivered month through the current month.FROM orders WHERE status = 'delivered'reads only the delivered rows.WHEREruns beforeGROUP BY, so the monthly aggregate is computed on the filtered row set.GROUP BY DATE_TRUNC('month', ordered_at)aggregates the filtered rows to month level.ORDER BY order_monthsorts the final output chronologically.
Why WHERE and not a CASE expression inside SUM
WHERE filters at the row level before aggregation, so months with zero delivered orders never appear as rows in the output. The prompt asks for one row per qualifying month (only months containing a delivered order). Wrapping the sum in a CASE (SUM(CASE WHEN status = 'delivered' THEN total_amount ELSE 0 END)) would still produce a row for every month — including months with no delivered orders, where the monthly total would be zero and the cumulative would stall. The WHERE filter is what makes "only qualifying months" true.
The trap
The running total accumulates across months that appear in the output, not across calendar months. If a month has no delivered orders it has no row, so the cumulative jumps from the prior delivered month's total straight to the next delivered month. That is the intended behavior here because the prompt asks for qualifying months only. If the requirement were a continuous monthly series, a date spine joined to this aggregate with zero-fill would be needed before the accumulation.
You practiced applying a status restriction before the cumulative layer, so the running total advances only through months containing delivered revenue.
