Date spine

You meet it when#

A line chart skips days, a week-over-week comparison is off by one, or a "daily average" is quietly computed over only the days that had data.

The version that looks right#

select pickup_at::date as day, count(*) as trips
from ride_hail_trips
group by 1
order by 1;

Days with zero trips produce no row. The chart draws a straight line across the gap, which reads as steady activity rather than no activity. And avg(trips) over that result divides by the number of days that had trips, not the number of days.

Zero and missing are different, and a line chart renders them identically.

The pattern#

with spine as (
    select generate_series(date '2026-01-01',
                           date '2026-03-31',
                           interval '1 day')::date as day
)
select s.day,
       coalesce(count(t.trip_id), 0) as trips
from spine s
left join ride_hail_trips t on t.pickup_at::date = s.day
group by s.day
order by s.day;

The spine is on the left. Reverse it and you are back where you started.

Syntax varies — generate_series in Postgres and DuckDB, GENERATE_DATE_ARRAY in BigQuery, a recursive CTE or a numbers table elsewhere. Better still, if your warehouse has a dim_date, use it: it already knows your fiscal calendar and your holidays.

The version with groups#

A spine over dates alone still drops a category that had no activity. Cross join the spine to the categories you want represented:

with spine as (select generate_series(...)::date as day),
     cats  as (select distinct channel from retail_orders)
select s.day, c.channel, coalesce(sum(o.revenue_usd), 0) as revenue
from spine s
cross join cats c
left join retail_orders o
       on o.ordered_at::date = s.day and o.channel = c.channel
group by 1, 2;