Scenario: Brightlane's revenue dashboard for the first week of January 2024 needs a complete day-by-day view, with quiet days marked as data-absent rather than as zero revenue.
Task: Write a query to return each date from January 1, 2024 through January 7, 2024 alongside the total order revenue for that date.
Assumptions:
- The
orderstable holds one row per placed order, with the placement timestamp stored inordered_atand the order amount stored intotal_amount. - Some dates in the range have no recorded
orders; those dates must still appear in the result, withdaily_revenuereported as a missing value rather than as zero.
Output:
- One row per date in the range, including dates with no
orders. - Columns in this order:
day,daily_revenue. - 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,
SUM(o.total_amount) AS daily_revenue
FROM
spine s
LEFT JOIN orders o ON o.ordered_at::date = s.day
GROUP BY
s.day
ORDER BY
s.dayThe shape
The dashboard distinguishes a quiet day from a zero-revenue day, so the canonical COALESCE(SUM(...), 0) wrapper is deliberately omitted. The LEFT JOIN still keeps every spine row, but SUM over no rows returns NULL — which is exactly the "no data" signal the report wants.
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 — one row for each calendar day from January 1 through January 7.SELECT s.day, SUM(o.total_amount) AS daily_revenuereturns the spine's date and the day's total revenue.SUMover zero matched rows returnsNULL— that's the absence signal flowing through to the output.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 even when noordersmatch; unmatched rows carryNULLin everyorderscolumn, includingtotal_amount.GROUP BY s.daycollapses the joined rows back to one row per spine date — seven rows out, regardless of how thin or rich the fact data is.ORDER BY s.dayreturns the dates in calendar order.
Why this and not COALESCE(SUM(o.total_amount), 0)
The reflex for a zero-fill query is to wrap the aggregate in COALESCE and substitute zero. The Output spec here calls for the opposite: a missing value, not zero. A NULL in the result tells the consumer "no orders existed on this day"; a 0 would say "orders existed and the total happened to be zero." Those are different facts, and the dashboard renders them differently. Leaving SUM alone preserves the distinction.
The trap
Switching the join to an INNER JOIN to "clean up" the nulls collapses the seven rows down to whatever days had revenue — January 5 only. The LEFT JOIN is what makes the rows appear; the NULL in daily_revenue is the intended representation, not a problem to be fixed.
You practiced left-joining facts onto a date spine while leaving empty days with a missing revenue value, not substituting zero.
