Brightlane's data quality team audits the order stream for coverage gaps. They need to identify which days in the first two weeks of January 2024 had zero orders placed.
Write a query to return every date from '2024-01-01' through '2024-01-14' for which no order is on record.
Assumptions:
- The
orderstable has one row per order with anidand anordered_attimestamp. - The fourteen dates form a contiguous calendar sequence.
- A date appears in the output only if zero orders have an
ordered_at::dateequal to that date.
Output:
- One row per qualifying date, with one column,
day, typed as a calendar date.
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-14'::date, INTERVAL '1 day')::date AS DAY
)
SELECT
s.day
FROM
spine s
LEFT JOIN orders o ON o.ordered_at::date = s.day
WHERE
o.id IS NULLThe shape
The spine CTE generates every date in the fourteen-day window, the LEFT JOIN attaches any matching order, and the WHERE o.id IS NULL filter keeps only the spine rows that had no match. This is the date-spine pattern inverted: instead of counting orders per day, the query reports the days where the count would have been zero.
Clause by clause
WITH spine AS (SELECT generate_series('2024-01-01'::date, '2024-01-14'::date, interval '1 day')::date AS day)builds the fourteen-day spine. Each value is cast back todateso the join againsto.ordered_at::datelines up on type.SELECT s.dayreturns just the date column; the report only needs the days where coverage is missing.FROM spine s LEFT JOIN orders o ON o.ordered_at::date = s.daypairs each generated day with any orders placed that day. Days with at least one matching order get one or more rows witho.idpopulated; days with no matching orders get a single row with NULL in everyo.*column.WHERE o.id IS NULLkeeps only the rows where the right side of theLEFT JOINcame up empty. These are exactly the spine dates with no matching order, which is the audit report.
Why this and not NOT EXISTS
SELECT s.day FROM spine s WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.ordered_at::date = s.day) returns the same dates. The two forms are logically equivalent and PostgreSQL often produces similar plans for them. The LEFT JOIN ... WHERE ... IS NULL form (sometimes called the "anti-join") is the canonical shape in the date-spine family because it shares its skeleton with the zero-fill report; the only change is the added IS NULL filter. Recognising one shape makes the other immediate.
The trap
The filter has to be on a column from the right table that cannot legitimately be NULL on its own — o.id is the safe choice because every order has an id. Filtering on a nullable column like o.shipped_at would mix two kinds of NULL: the NULL produced by the LEFT JOIN for unmatched days, and the NULL stored on actual orders that simply have not shipped. The query would then incorrectly flag any day whose orders have not shipped as a day with no orders at all. Pick a column from the right side that is NULL only when the row itself is absent, and the anti-join reads cleanly.
You practiced the anti-spine pattern — LEFT JOIN the fact table to the spine, then keep only spine rows where the related side is missing; the inverse of the zero-fill report.
