A Full Month of Order Counts — Date Spines in SQL

The problem

Scenario: Brightlane's fulfillment operations team is preparing a January 2024 monthly summary and needs every calendar date in the month present in the day-by-day breakdown, even dates with no activity.

Task: Write a query to return each date from January 1, 2024 through January 31, 2024 alongside the number of orders placed on that date.

Assumptions:

The orders table holds one row per placed order, with the placement timestamp stored in ordered_at.
Some dates in the range have no recorded orders; those dates must still appear in the result with a count of zero.

Output:

One row per date in the range, including dates with no orders.
Columns in this order: day, order_count.
Sorted by day ascending.

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-31'::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.day

The shape

A January monthly summary needs every one of the thirty-one calendar days present, even days with no orders, so the spine generates every date in the month and orders is left-joined onto it. The thirty-one rows come from the spine, not from the fact table.

Clause by clause

WITH spine AS (SELECT generate_series('2024-01-01'::date, '2024-01-31'::date, '1 day'::interval)::date AS day) generates one row for each calendar day from January 1 through January 31. The outer ::date cast strips off the timestamp produced by generate_series so the join key is a clean date.
SELECT s.day, COUNT(o.id) AS order_count returns the spine's date and the count of matched orders for that date. Because COUNT(o.id) ignores nulls, an unmatched spine row reports zero.
FROM spine s LEFT JOIN orders o ON o.ordered_at::date = s.day attaches each placed order to its day. The LEFT JOIN keeps every spine row even when no orders match — that's what surfaces every empty day in the month with order_count = 0.
GROUP BY s.day collapses the joined rows back to one row per spine date. Grouping on the spine's day is what guarantees one output row per generated calendar date.
ORDER BY s.day returns the thirty-one dates in calendar order.

The trap

A learner reaching for a date range often writes generate_series('2024-01-01'::date, '2024-01-31'::date, '30 days'::interval), expecting one row per day. That step is the gap between values, not the count of values — a thirty-day step from January 1 only produces January 1 and January 31. The step has to be '1 day' for one row per calendar date.

You practiced building a month-long date spine and left-joining fact data onto it so every calendar day appears in the output, even days with no order placed.

Return each date from January 1, 2024 through January 31, 2024 alongside the number of `orders` placed on that date

The shape

Clause by clause

The trap

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