Scenario: Brightlane's sales team needs a running view of order activity through the first week of January 2024 — both the daily counts and how many orders have accumulated since the start of the week.
Task: Write a query to return each date from January 1, 2024 through January 7, 2024 alongside the number of orders placed on that date and the total number of orders placed from January 1, 2024 through that date inclusive.
Assumptions:
- The
orderstable holds one row per placed order, with the placement timestamp stored inordered_at. - Some dates in the range have no recorded
orders; those dates must still appear in the result with a daily count of zero. - The cumulative value on each date covers every order placed from January 1, 2024 through that date inclusive.
Output:
- One row per date in the range, including dates with no
orders. - Columns in this order:
day,daily_orders,cumulative_orders. - Sorted by
dayascending.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
WITH
spine AS (
SELECT
GENERATE_SERIES('2024-01-01'::date, '2024-01-07'::date, '1 day'::INTERVAL)::date AS DAY
)
SELECT
s.day,
COUNT(o.id) AS daily_orders,
SUM(COUNT(o.id)) OVER (
ORDER BY
s.day
) AS cumulative_orders
FROM
spine s
LEFT JOIN orders o ON o.ordered_at::date = s.day
GROUP BY
s.day
ORDER BY
s.dayThe shape
A cumulative count on top of zero-filled daily counts is exactly what aggregate window functions do — SUM(COUNT(o.id)) OVER (ORDER BY s.day) runs the aggregate per group and then a running sum across those grouped rows in a single pass. The spine guarantees the running line advances day by day even when the daily count is zero.
Clause by clause
WITH spine AS (SELECT generate_series('2024-01-01'::date, '2024-01-07'::date, '1 day'::interval)::date AS day)builds the seven-row backbone.COUNT(o.id) AS daily_ordersis the per-day aggregate.COUNTofo.idignores nulls, so days with no matching order report zero.SUM(COUNT(o.id)) OVER (ORDER BY s.day) AS cumulative_orderswraps the daily count in a windowed sum. The innerCOUNTruns once per group; the outerSUM ... OVER (ORDER BY s.day)then accumulates those group totals in date order. By default, an aggregate window function withORDER BYuses a range frame that includes every row from the start through the current row — exactly the running-total shape the prompt asks for.FROM spine s LEFT JOIN orders o ON o.ordered_at::date = s.dayattaches each placed order to its day. TheLEFT JOINkeeps every spine row.GROUP BY s.daycollapses the joined rows back to one row per spine date.ORDER BY s.dayreturns the seven dates in calendar order.
Why this and not a self-join or a correlated subquery
Either alternative would work — a LEFT JOIN orders on dates less than or equal to the current spine date, then a count — but both re-read the data once per output row and scale poorly. The window function visits each row exactly once. On a daily series across a year, the difference between 365 sequential reads and 365² is the difference between a query that returns instantly and one that hangs in a dashboard.
The trap
Reaching for a separately-computed cumulative — running the daily count first, then trying to layer a window on top — usually leads to the wrong shape because the daily count and the running total live in the same SELECT list, not in two stacked queries. Aggregate-over-aggregate-window-function looks unusual the first time you see it, but it's the canonical form: SUM(COUNT(...)) OVER (ORDER BY ...). The inner aggregate collapses each group; the outer windowed aggregate accumulates the collapsed values.
You practiced layering a running total on top of zero-filled daily counts, so the cumulative line advances continuously across days that had no orders.
