Daily Order Counts Zero Filled — Date Spines in SQL

The problem

Scenario: Brightlane's fulfillment operations team is sizing daily staffing against last year's order volume and needs a complete view of the first week of January 2024.

Task: Write a query to return each date from January 1, 2024 through January 7, 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-07'::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

The seven-day range has to be present in the result even on days with zero orders, so the spine generates every date in the range and the orders table is left-joined onto it. The structural rows come from the spine, not from the fact data.

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 — one row for each calendar day from January 1 through January 7. The outer ::date cast strips the timestamp produced by generate_series back to a plain date so the join key lines up cleanly.
SELECT s.day, COUNT(o.id) AS order_count returns the spine's date and the count of matched orders for that date. COUNT(o.id) counts non-null id values, so unmatched spine rows contribute zero rather than one.
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 produces the zero rows for January 1 through 4, 6, and 7.
GROUP BY s.day collapses the joined rows back to one row per spine date. The grouping key is the spine column, not the fact column, which is what guarantees one output row per generated date.
ORDER BY s.day returns the seven dates in calendar order.

The trap

Joining orders to the spine instead of the spine to orders looks identical at a glance but produces a completely different result. An INNER JOIN or a fact-table-first join drops spine rows that have no matching orders — exactly the rows the pattern exists to keep. The spine has to be the left table.

You practiced anchoring the result to a generated date spine with a left-join so that quiet days appear with a count of zero instead of dropping out.

Return each date from January 1, 2024 through January 7, 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.