Scenario: Brightlane's data pipeline runs a date-spine validation step before publishing reports. A configuration error has reversed the parameters so the run is asked to build a daily series starting on March 15, 2024 and ending on March 1, 2024.
Task: Write a query to return each date in the series — starting on March 15, 2024 and ending on March 1, 2024 — alongside the number of orders placed on that date.
Assumptions:
- The
orderstable holds one row per placed order, with the placement timestamp stored inordered_at. - The series starts on March 15, 2024 and ends on March 1, 2024. Because the start date falls after the end date, no calendar dates fall within the series; the result is empty.
Output:
- One row per date in the series. The series contains no dates, so the result is empty.
- Columns in this order:
day,order_count. - 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-03-15'::date, '2024-03-01'::date, '1 day'::INTERVAL)::date AS DAY
)
SELECT
s.day,
COUNT(o.id) AS order_count
FROM
spine s
LEFT JOIN orders o ON o.ordered_at::date = s.day
GROUP BY
s.day
ORDER BY
s.dayThe shape
generate_series walks from the start argument toward the end argument by the step argument. When the step is positive and the start is later than the end, the very first step already overshoots the end and the generator stops without producing a single row. The spine is empty, so the surrounding query has nothing to join to and returns nothing.
Clause by clause
WITH spine AS (SELECT generate_series('2024-03-15'::date, '2024-03-01'::date, '1 day'::interval)::date AS day)asks the generator to step forward by one day from March 15 toward March 1. Forward from March 15 is March 16, March 17, and so on — never March 1. The first comparison against the end value already fails, so zero rows are produced.SELECT s.day, COUNT(o.id) AS order_countdescribes the per-day output shape. With no spine rows to drive the result,SELECThas no input rows to evaluate.FROM spine s LEFT JOIN orders o ON o.ordered_at::date = s.dayattaches each order to its day. ALEFT JOINfrom an empty left table is itself empty — aLEFT JOINkeeps every left-side row, and there are no left-side rows to keep.GROUP BY s.daywould collapse the joined rows back to one row per spine date. With no joined rows, it produces no groups.ORDER BY s.daywould sort the result. With no result, sorting is a no-op.
Why this and not "swap the arguments to fix it"
A pipeline that received a reversed range may have done so deliberately — for instance, to signal an invalid configuration that should produce no output rather than be quietly corrected. Silently swapping the bounds would mask the upstream configuration error. The right behavior for the pure spine query is to let the empty series propagate; whatever is calling this query gets to decide whether an empty result means "no activity" or "bad input."
The trap
generate_series does not raise an error on a reversed range. It returns zero rows, the LEFT JOIN returns zero rows, and the final query returns zero rows — all without a single warning. A pipeline that consumes this output sees an empty result and may interpret it as "no orders that week," which is the wrong reading. Any time a date spine produces an empty result, check the order of the start and end arguments before checking the fact data.
You practiced reasoning about a date spine whose start follows its end — the generator produces an empty series, so the surrounding query returns no results.
