Cheatsheet

SQL Date Ranges and Buckets Cheatsheet

Filter periods, group by week or month, and fill in the empty ones.

Ranges

Start inclusive, end exclusive: >= the first moment, < the first moment of the next period. It works for dates and timestamps alike.

Period	From (inclusive)	To (exclusive)
One day	`d`	`d + 1`
This month	`date_trunc('month', a)`	`date_trunc('month', a) + interval 1 month`
Last month	`date_trunc('month', a) - interval 1 month`	`date_trunc('month', a)`
Last 7 days, including today	`a - 6`	`a + 1`
Year to date	`date_trunc('year', a)`	`a + 1`

a is the anchor date: current_date in production, or a fixed date so a report can be rerun.

Last month, from a fixed anchor

with as_of as (select date '2024-06-14' as a)
select count(*) as orders
from orders, as_of
where order_date >= date_trunc('month', a) - interval 1 month
  and order_date <  date_trunc('month', a)

Buckets

Bucket	DuckDB
Day of a timestamp	`ts::date`
Week (Monday)	`date_trunc('week', d)::date`
Week (Sunday)	`date_trunc('week', d + 1)::date - 1`
Month, quarter, year	`date_trunc('month', d)::date`
Any width	`time_bucket(interval '15 minutes', ts)`
Month of the year, across years	`month(d)` (merges every January)
Day of the week	`isodow(d)`: 1 is Monday, 7 is Sunday

Orders and revenue per month

select date_trunc('month', o.order_date)::date as month,
       count(distinct o.order_id)         as orders,
       sum(oi.quantity * oi.unit_price)   as revenue
from orders as o
join order_items as oi on oi.order_id = o.order_id
group by month
order by month

Fill the empty periods

Spine, left join, count the right side

with weeks as (
  select w::date as week_start
  from range(date '2024-01-15', date '2024-06-17', interval 7 day) as t(w)
)
select w.week_start,
       count(o.order_id) as orders
from weeks as w
left join orders as o
  on date_trunc('week', o.order_date) = w.week_start
group by w.week_start
order by w.week_start

Spine	DuckDB
Days, end excluded	`range(start, stop, interval 1 day)`
Days, end included	`generate_series(start, stop, interval 1 day)`
Months	`range(first_of_month, stop, interval 1 month)`, from a 1st
Per group	Cross join the spine to the groups, then left join the data

Compare periods

Month over month change

with monthly as (
  select date_trunc('month', order_date)::date as month,
         count(*) as orders
  from orders
  group by month
)
select month, orders,
       lag(orders) over (order by month) as prev_orders,
       orders - lag(orders) over (order by month) as change
from monthly
order by month

lag takes the previous row, not the previous month. If a month can be missing, build the months from a spine first, or a gap turns into a two-month comparison.

Watch out

date_trunc, range, and generate_series return timestamps in DuckDB, even from dates: cast to ::date before joining or displaying. A date spine joined with = to a timestamp only matches midnight; join on ts::date or a range. A WHERE on the right-hand table removes the zero rows; put the condition in ON. And count(*) counts the empty period as 1. See the date buckets and spines reference for each.