Cohorts and Time Buckets in SQL
A cohort groups records by when they first appeared. date_trunc collapses dates into month buckets you can count.
Analysts love cohorts: groups of customers bucketed by when they first showed up. To build one, you need two layers: first find each customer's first order, then bucket those first orders by month and count them.
date_trunc('month', some_date) snaps a date down to the first of its month, which turns scattered dates into a handful of monthly buckets you can group on. The inner query (the starter) finds each customer's first order; wrap it to count per month.
select date_trunc('month', signup_date) as cohort,
count(*) as users
from accounts
group by cohort
order by cohortSchema · Garden ShopTable · orders6 columns · 24 rows
One row per order. Unshipped orders have a null shipped_date.
Count new customers by cohort month. Using each customer's first order, return cohort_month (the month of that first order) and new_customers (how many customers' first order fell in that month), oldest month first.
- Columns: cohort_month, new_customers.
- Rows: one row per month that gained its first customers.
- cohort_month is the first of the month (e.g. 2024-01-01); each customer is counted once, in the month of their very first order.