Lesson 11.3

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. Wrapping means putting the whole starter in parentheses after FROM and giving it a name, so the outer query can read its result like a table: the subqueries from Chapter 10, used as a table instead of a value.

Pattern

select date_trunc('month', signup_date) as cohort,
       count(*) as users
from accounts
group by cohort
order by cohort

In DuckDB, date_trunc returns a timestamp even when you give it a date, so each bucket shows as 2024-01-01 00:00:00. Cast it back to a date to show just the day: cast(date_trunc('month', some_date) as date), or date_trunc('month', some_date)::date.

Schema · Garden Shop Table · orders 6 columns · 24 rows

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

Your task

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.

Garden Shop · SQL engine loading · Ctrl/⌘ + Enter to run·

Run your query to inspect the result.

Expected answer

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.

Previous · 11.2 Percentages and rates

Next · 11.4 Window functions →

Reference

SQL Subqueries and EXISTS

Nested SELECTs, IN, EXISTS, and correlation.

Cheatsheet

SQL Window Functions Cheatsheet

Ranking, LAG/LEAD, and running totals at a glance.

Interview

SQL Window Function Interview Questions

Practice ranking, running totals, and top row per group.

Mistake