Scenario: Brightlane's operations team is auditing the first week of January 2024 to identify dates with no recorded orders — possible signs of a system outage or an ingestion gap.
Task: Write a query to return every date from January 1, 2024 through January 7, 2024 on which no orders were placed.
Assumptions:
- The
orderstable holds one row per placed order, with the placement timestamp stored inordered_at. - Some dates in the range have at least one placed order on record; others have none. Only dates with no placed orders should appear in the result.
Output:
- One row per date in the range with no placed
orders. - Columns in this order:
day. - 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
FROM
spine s
LEFT JOIN orders o ON o.ordered_at::date = s.day
WHERE
o.id IS NULL
ORDER BY
s.dayThe shape
The spine is acting as the universe of dates the audit cares about, and the goal is the dates that have no match in orders. A LEFT JOIN plus an IS NULL filter on the right table's key gives exactly that: every spine date appears, the matched dates carry a real o.id, the unmatched dates carry NULL, and the WHERE keeps only the unmatched ones.
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 for the first week of January.SELECT s.dayreturns only the date column — the audit doesn't care how many orders are missing per day, only which dates are empty.FROM spine s LEFT JOIN orders o ON o.ordered_at::date = s.dayattaches each placed order to its day. After the join, days with no orders carryNULLacross everyo.*column; days with at least one order carry a realo.id.WHERE o.id IS NULLkeeps only the spine rows that found no match.o.idis non-nullable in the base table, so aNULLhere can only mean "this spine row was unmatched."ORDER BY s.dayreturns the surviving dates in calendar order.
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 six dates. Both forms are correct for this problem. The LEFT JOIN ... IS NULL pattern shows up so often as the canonical anti-join shape that it earns its place here, and it composes more naturally with the rest of the spine pattern — the join is already in the query for the standard zero-fill case, and the IS NULL filter is the only change.
The trap
Putting the IS NULL on the wrong column silently changes what gets returned. WHERE o.ordered_at IS NULL works on this schema because ordered_at is also non-nullable, but pick any nullable column on orders and the filter starts matching real orders whose value in that column happens to be NULL — alongside the truly unmatched rows. Always anchor the IS NULL check on a guaranteed-non-null column from the right table, typically its primary key. o.id is the right choice every time.
You practiced using a date spine and a left-join with IS NULL to surface the dates that have no matching fact records — a spine-based anti-join.
