Lesson 21.2

Date Spines in SQL

GROUP BY can only report days that have rows. A date spine lists every day first, so the quiet days show up as 0.

A daily report built with GROUP BY order_date has one row per day that had an order. Days with none are not 0; they are missing. A chart drawn from it joins June 2 straight to June 5 and hides two empty days, and a moving average counts rows, not days. A date spine fixes it: a list of every date in the range, with the facts joined on.

Pattern

select d.day::date as order_day,
       count(o.order_id) as orders
from range(date '2024-06-01', date '2024-06-08', interval 1 day) as d(day)
left join orders as o on o.order_date = d.day::date
group by order_day
order by order_day

Generating the dates

range(start, stop, interval 1 day) is a table function: it returns one row per day, starting at start and stopping before stop, the same half-open range as Lesson 7.1 · Filtering by date range. Its values are timestamps, so cast them with ::date before comparing them with a date column. PostgreSQL's generate_series does the same job but includes the stop value.

Left join, then count a column

The spine goes on the left so every day survives the join. A day with no order gets one row of NULLs, which is why the pattern counts o.order_id: count(*) would count that NULL row and report 1. A sum over no rows is NULL rather than 0, so wrap sums in coalesce(…, 0).

Aggregate before you join

Join a second table to the spine and the rows multiply, exactly as in inflated counts after a join:

Order lines are not orders

select d.day::date as order_day,
       count(o.order_id) as order_rows,
       count(distinct o.order_id) as orders
from range(date '2024-06-01', date '2024-06-08', interval 1 day) as d(day)
left join orders as o on o.order_date = d.day::date
left join order_items as oi on oi.order_id = o.order_id
group by order_day
order by order_day

June 1's single order has two lines, so it counts as 2 order rows. The safe shape is the one in the exercise: total the facts per day in a CTE first, then join one row per day to the spine.

For weekly and monthly spines, week starts, and spines joined to timestamps, see the date buckets and spines reference.

Schema · Garden Shop Tables · orders, order_items 2 tables

Table · orders

One row per order. Unshipped orders have a null shipped_date.

6 columns · 24 rows

order_id int customer_id int order_date date shipped_date date status text coupon_code text

Table · order_items

One row per line item within an order.

5 columns · 48 rows

order_item_id int order_id int product_id int quantity int unit_price decimal

Syntax reference

Need syntax details? See the SQL date functions reference

Your task

The starter totals orders and revenue for each June day that had an order. Return every day from June 1 to June 14, 2024 instead: order_day, orders, and revenue, with 0 in both columns on days without orders. Sort by order_day.