Brightlane's finance team needs total order revenue for each day from '2024-01-15' through '2024-01-19'. Every date in that range must appear, including days with no orders.
Write a query to return every date in that five-day range and its total order revenue. Days with no orders should appear with a missing revenue value.
Assumptions:
- The
orderstable has one row per order with anordered_attimestamp and atotal_amount. - The five dates form a contiguous calendar sequence; every date in the range must appear in the result.
- For each date, the daily revenue is the combined
total_amountacross orders whoseordered_at::dateequals that date. Days with no orders show a missing revenue value (not0).
Output:
- One row per date in the five-day range, with columns
dayanddaily_revenue.
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-15'::date, '2024-01-19'::date, INTERVAL '1 day')::date AS DAY
)
SELECT
s.day,
SUM(o.total_amount) AS daily_revenue
FROM
spine s
LEFT JOIN orders o ON o.ordered_at::date = s.day
GROUP BY
s.dayThe shape
The spine CTE emits each of the five dates as its own row, and the LEFT JOIN keeps every spine day in the result regardless of whether orders match. SUM(o.total_amount) over zero matching rows returns NULL, which is the missing-revenue display the finance team asked for.
Clause by clause
WITH spine AS (SELECT generate_series('2024-01-15'::date, '2024-01-19'::date, interval '1 day')::date AS day)builds the five-day spine and casts each generated value back todateso it matches the cast applied toordered_atin the join.SELECT s.day, SUM(o.total_amount) AS daily_revenuereturns the date and the summed order revenue. With no matching orders,SUMis taken over an empty set of rows, which returns NULL by definition.FROM spine s LEFT JOIN orders o ON o.ordered_at::date = s.daypairs each generated day with the orders placed that day. Days with no orders still appear in the join result, with NULL in everyo.*column.GROUP BY s.dayproduces one row per date in the spine.
Why this and not COALESCE(SUM(...), 0)
The prompt asks for a missing value on quiet days, not a zero, so the bare SUM is correct. If the requirement had been "show 0 for quiet days," wrapping the aggregate as COALESCE(SUM(o.total_amount), 0) would convert the NULL to a zero. The two outputs look almost identical but mean different things: NULL says no revenue data existed for that day; 0 says the data existed and summed to zero. On a date spine, only NULL communicates "no activity" honestly.
The trap
SUM and COUNT behave differently on empty groups. COUNT returns 0 over zero rows; SUM returns NULL. A query author who solved the zero-fill problem by relying on COUNT returning 0 will see NULLs reappear the moment the aggregate switches to SUM. The fix depends on what the prompt asks for: leave the NULL, or wrap it in COALESCE.
You practiced the date-spine pattern with SUM instead of COUNT — the empty-day distinction matters: COUNT returns 0 over zero records, SUM returns missing.
