Count Orders per Day Using a Series — generate

The problem

Brightlane's operations dashboard reports daily order volume for a complete date range, including days with zero orders so the dashboard never has gaps.

Write a query to return every date from '2024-01-01' through '2024-01-07' alongside the number of orders placed on that date. Days with no orders should appear with an order count of 0.

Assumptions:

The orders table has one row per order with an id and an ordered_at timestamp.
The seven dates form a contiguous calendar sequence; every date in the range must appear in the result regardless of whether any orders occurred that day.
For each date, the order count is the number of orders whose ordered_at::date equals that date. Dates with no orders show a count of 0.

Output:

One row per date in the seven-day range, with columns day and order_count.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

WITH
  spine AS (
    SELECT
      GENERATE_SERIES('2024-01-01'::date, '2024-01-07'::date, INTERVAL '1 day')::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

The shape

The spine CTE generates every day in the seven-day window as its own row, and the LEFT JOIN against orders keeps each generated day in the result regardless of whether any order matches. COUNT(o.id) ignores the NULL produced for unmatched days, so quiet days surface as a count of 0 without any explicit handling.

Clause by clause

WITH spine AS (SELECT generate_series('2024-01-01'::date, '2024-01-07'::date, interval '1 day')::date AS day) builds the date spine. The CTE names the generated column day and casts each value back to date so it joins cleanly against o.ordered_at::date.
SELECT s.day, COUNT(o.id) AS order_count returns the date and its order count. COUNT(o.id) only counts non-NULL values; unmatched spine rows have NULL in o.id, so those rows contribute 0 to the count automatically.
FROM spine s LEFT JOIN orders o ON o.ordered_at::date = s.day pairs each generated day with any orders placed that day. The LEFT JOIN is the load-bearing choice: it keeps every spine row even when no order matches, which is what gives quiet days a row in the output.
GROUP BY s.day collapses any multiple matches per day into a single row per date.

The trap

Reach for an INNER JOIN here and quiet days vanish silently. The dashboard would show only the days where at least one order existed, which is exactly the gap the spine pattern is built to close. The same trap surfaces in reverse if the filter WHERE o.id IS NOT NULL is added: that converts the LEFT JOIN back into an inner join after the fact and drops the zero-order days again. Keep the join LEFT, count on the right-side column, and let COUNT handle the NULL.

You practiced the zero-fill date-spine pattern — generate the spine, LEFT JOIN the fact table to it, and COUNT the related records; days with no orders produce COUNT = 0 automatically.

Return every date from `'2024-01-01'` through `'2024-01-07'` alongside the number of orders placed on that date. Days with no orders should appear with an order count of `0`

The shape

Clause by clause

The trap

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.