Malek Damiri

Build a date spine so empty days count

·

Group revenue by day and the days with no orders disappear. The chart skips them, and every average built on top quietly assumes they never happened. The fix is a date spine: one row per day, joined to the data, so an empty day shows up as zero.

The problem

Five orders across a seven-day week:

create table orders as select * from (values
  (date '2026-09-01', 120.00), (date '2026-09-01', 80.00),
  (date '2026-09-02', 200.00),
  (date '2026-09-04', 150.00),
  (date '2026-09-07', 90.00)
) t(order_date, amount);

select order_date, sum(amount) as revenue
from orders
group by order_date
order by order_date;
order_date   revenue
2026-09-01    200.00
2026-09-02    200.00
2026-09-04    150.00
2026-09-07     90.00

Four rows for a seven-day week. Average them and you get 160.00 a day, which is the average of the days that had orders, not of the week.

The fix

with spine as (
  select cast(d as date) as day
  from generate_series(date '2026-09-01', date '2026-09-07', interval 1 day) as g(d)
)
select s.day, coalesce(sum(o.amount), 0) as revenue
from spine as s
left join orders as o on o.order_date = s.day
group by s.day
order by s.day;
day          revenue
2026-09-01    200.00
2026-09-02    200.00
2026-09-03      0.00
2026-09-04    150.00
2026-09-05      0.00
2026-09-06      0.00
2026-09-07     90.00

Seven rows, and the daily average is 91.43. Same orders, a very different story about the week.

Two details matter. The spine goes on the left of a left join, so every day survives. And coalesce turns the empty sums into zero; without it, the empty days come back as null and most averages skip them again.

In Snowflake

Snowflake has no generate_series. The usual pattern is a generator with row_number(), because seq4() on its own isn't guaranteed to be gap-free:

select dateadd(day, row_number() over (order by seq4()) - 1, '2026-09-01'::date) as day
from table(generator(rowcount => 7));

Where it bites

Daily revenue charts, rolling seven-day averages, "days since last order", and any cohort grid where a missing cell should read as zero. If you build these often, keep a permanent date table in the warehouse and join to it, instead of generating a spine in every query.

Every query here ran in DuckDB 1.5.1 and returned the output shown. This note was written with AI assistance.