Dates, Cohorts, and Retention
Date truncation and spines, month-over-month growth, the cohort retention grid, and why BETWEEN on a timestamp column loses the last day of the range.
Date questions look like SQL and are mostly calendar arithmetic. Two mistakes account for most wrong answers: comparing a timestamp to a date, and letting empty periods disappear.
The BETWEEN trap
CREATE TEMP TABLE events (id INTEGER, ts TIMESTAMP);
INSERT INTO events VALUES
(1, TIMESTAMP '2024-03-01 00:00:00'),
(2, TIMESTAMP '2024-03-15 12:30:00'),
(3, TIMESTAMP '2024-03-31 09:00:00'),
(4, TIMESTAMP '2024-03-31 23:59:59'),
(5, TIMESTAMP '2024-04-01 00:00:01');
SELECT 'BETWEEN dates' AS method, count(*) FROM events
WHERE ts BETWEEN DATE '2024-03-01' AND DATE '2024-03-31'
UNION ALL
SELECT 'half-open', count(*) FROM events
WHERE ts >= DATE '2024-03-01' AND ts < DATE '2024-04-01'
UNION ALL
SELECT 'BETWEEN + 23:59', count(*) FROM events
WHERE ts BETWEEN TIMESTAMP '2024-03-01 00:00:00' AND TIMESTAMP '2024-03-31 23:59:59';┌─────────────────┬──────────────┐
│ method │ count_star() │
│ varchar │ int64 │
├─────────────────┼──────────────┤
│ BETWEEN dates │ 2 │
│ half-open │ 4 │
│ BETWEEN + 23:59 │ 4 │
└─────────────────┴──────────────┘BETWEEN with dates finds two of the four March events. DATE '2024-03-31' becomes
2024-03-31 00:00:00, so both events later that day fall outside.
The 23:59:59 fix works here and fails on sub-second timestamps — 2024-03-31 23:59:59.5 is
still excluded. Half-open is the version with no edge cases: >= start AND < next_start.
It needs no knowledge of month lengths, handles leap years, and is correct for dates,
timestamps, and timestamps with time zones alike.
“I use half-open ranges for anything time-based. BETWEEN on a timestamp column silently drops the last day, and the 23:59:59 workaround breaks on fractional seconds.”
Do not wrap the column in a function
EXPLAIN SELECT count(*) FROM orders WHERE date_trunc('month', order_date) = DATE '2024-03-01';
EXPLAIN SELECT count(*) FROM orders WHERE order_date >= DATE '2024-03-01'
AND order_date < DATE '2024-04-01';-- wrapped: the filter is a computed expression, no index can serve it
│ Filters: date_trunc('month', order_date)=2024-03-01
-- half-open: a range the planner can push into an index scan
│ Filters: order_date>=2024-03-01 AND order_date<2024-04-01Both return the same three orders. Only the second can use an index on order_date.
This is the most common cause of a slow date query in production, and volunteering it is a
strong signal in the “awareness” part of the round. A functional index on
date_trunc('month', order_date) is the other fix when the expression is genuinely needed
often.
Truncation and the parts
SELECT order_date,
date_trunc('month', order_date)::DATE AS month,
date_trunc('week', order_date)::DATE AS week_start,
extract(year FROM order_date) AS yr,
extract(month FROM order_date) AS mo,
extract(dow FROM order_date) AS day_of_week,
strftime(order_date, '%Y-%m') AS ym_label
FROM orders ORDER BY order_date LIMIT 4;┌────────────┬────────────┬────────────┬───────┬───────┬─────────────┬──────────┐
│ order_date │ month │ week_start │ yr │ mo │ day_of_week │ ym_label │
├────────────┼────────────┼────────────┼───────┼───────┼─────────────┼──────────┤
│ 2024-03-01 │ 2024-03-01 │ 2024-02-26 │ 2024 │ 3 │ 5 │ 2024-03 │
│ 2024-03-15 │ 2024-03-01 │ 2024-03-11 │ 2024 │ 3 │ 5 │ 2024-03 │
│ 2024-03-18 │ 2024-03-01 │ 2024-03-18 │ 2024 │ 3 │ 1 │ 2024-03 │
│ 2024-04-20 │ 2024-04-01 │ 2024-04-15 │ 2024 │ 4 │ 6 │ 2024-04 │
└────────────┴────────────┴────────────┴───────┴───────┴─────────────┴──────────┘Two portability notes worth stating:
- Week start differs by engine and locale. DuckDB and PostgreSQL start weeks on Monday; other systems and some locales use Sunday. If a question involves weeks, ask.
extract(dow)numbering differs — 0 for Sunday in PostgreSQL, and ISO numbering elsewhere. Do not encode a weekday number without checking.
Dialect names for the same operations: PostgreSQL and DuckDB use date_trunc, MySQL uses
DATE_FORMAT, SQL Server uses DATETRUNC (2022+) or DATEADD/DATEDIFF tricks before that.
Monthly aggregation, and the months that disappear
SELECT date_trunc('month', order_date)::DATE AS month,
count(*) AS orders,
sum(amount) AS revenue
FROM orders GROUP BY 1 ORDER BY 1;┌────────────┬────────┬─────────┐
│ month │ orders │ revenue │
├────────────┼────────┼─────────┤
│ 2024-03-01 │ 3 │ 510.50 │
│ 2024-04-01 │ 2 │ 245.00 │
│ 2024-05-01 │ 2 │ 249.99 │
│ 2024-06-01 │ 1 │ 75.00 │
└────────────┴────────┴─────────┘Four months, and the dataset starts in January (signups) — January and February are missing entirely because no orders exist for them. A chart drawn from this silently compresses the timeline and makes March look like the start of the business.
The fix is a spine:
WITH months AS (
SELECT range::DATE AS month
FROM range(DATE '2024-01-01', DATE '2024-07-01', INTERVAL 1 MONTH)
),
monthly AS (
SELECT date_trunc('month', order_date)::DATE AS month,
count(*) AS orders, sum(amount) AS revenue
FROM orders GROUP BY 1
)
SELECT m.month,
coalesce(x.orders, 0) AS orders,
coalesce(x.revenue, 0) AS revenue
FROM months m LEFT JOIN monthly x USING (month)
ORDER BY m.month;┌────────────┬────────┬─────────┐
│ month │ orders │ revenue │
├────────────┼────────┼─────────┤
│ 2024-01-01 │ 0 │ 0.00 │
│ 2024-02-01 │ 0 │ 0.00 │
│ 2024-03-01 │ 3 │ 510.50 │
│ 2024-04-01 │ 2 │ 245.00 │
│ 2024-05-01 │ 2 │ 249.99 │
│ 2024-06-01 │ 1 │ 75.00 │
└────────────┴────────┴─────────┘range() is DuckDB; PostgreSQL spells it generate_series(start, stop, interval); elsewhere a
recursive CTE or a permanent calendar table does the job. Production warehouses usually have a
dim_date table for exactly this reason — mentioning that reads as having shipped something.
Month-over-month growth
WITH monthly AS (
SELECT date_trunc('month', order_date)::DATE AS month, sum(amount) AS revenue
FROM orders GROUP BY 1
)
SELECT month, revenue,
lag(revenue) OVER (ORDER BY month) AS prev_revenue,
revenue - lag(revenue) OVER (ORDER BY month) AS change,
round(100.0 * (revenue - lag(revenue) OVER (ORDER BY month))
/ nullif(lag(revenue) OVER (ORDER BY month), 0), 1) AS pct_change
FROM monthly ORDER BY month;┌────────────┬─────────┬──────────────┬─────────┬────────────┐
│ month │ revenue │ prev_revenue │ change │ pct_change │
├────────────┼─────────┼──────────────┼─────────┼────────────┤
│ 2024-03-01 │ 510.50 │ NULL │ NULL │ NULL │
│ 2024-04-01 │ 245.00 │ 510.50 │ -265.50 │ -52.0 │
│ 2024-05-01 │ 249.99 │ 245.00 │ 4.99 │ 2.0 │
│ 2024-06-01 │ 75.00 │ 249.99 │ -174.99 │ -70.0 │
└────────────┴─────────┴──────────────┴─────────┴────────────┘nullif(prev, 0) is the division-by-zero guard: a month with zero revenue would otherwise throw
or return infinity. It is a one-word defensive habit worth having by reflex.
Whether the spine is applied changes this answer. Without it, lag compares March to the
previous row, not the previous month — so a gap of two quiet months would be invisible and
the growth figure would be wrong. Combining the spine with lag is the correct shape and worth
saying explicitly.
Cohort retention
The canonical analytics question: group users by when they first appeared, then measure how many came back.
WITH first_order AS (
SELECT customer_id,
date_trunc('month', min(order_date))::DATE AS cohort_month
FROM orders WHERE customer_id IS NOT NULL
GROUP BY customer_id
),
activity AS (
SELECT o.customer_id, f.cohort_month,
date_trunc('month', o.order_date)::DATE AS active_month,
datediff('month', f.cohort_month, date_trunc('month', o.order_date)) AS month_number
FROM orders o JOIN first_order f USING (customer_id)
)
SELECT cohort_month,
count(DISTINCT customer_id) FILTER (WHERE month_number = 0) AS m0,
count(DISTINCT customer_id) FILTER (WHERE month_number = 1) AS m1,
count(DISTINCT customer_id) FILTER (WHERE month_number = 2) AS m2,
count(DISTINCT customer_id) FILTER (WHERE month_number = 3) AS m3
FROM activity GROUP BY cohort_month ORDER BY cohort_month;┌──────────────┬───────┬───────┬───────┬───────┐
│ cohort_month │ m0 │ m1 │ m2 │ m3 │
├──────────────┼───────┼───────┼───────┼───────┤
│ 2024-03-01 │ 3 │ 2 │ 1 │ 0 │
│ 2024-05-01 │ 1 │ 0 │ 0 │ 0 │
└──────────────┴───────┴───────┴───────┴───────┘Read the March cohort: three customers placed a first order in March, two of them ordered again in April, one in May, none in June.
Four details that decide whether the query is right:
min(order_date)per customer defines the cohort. Using signup date instead is a different metric — ask which the question means.month_numberas a difference, not an absolute month, is what lines the cohorts up so column m1 means “one month later” for every row.count(DISTINCT customer_id)— a customer with three April orders must count once.month_number = 0is the cohort size by definition, so m0 always equals the cohort. If it does not, the first-order CTE is wrong.
For a retention rate rather than a count, divide by m0:
round(100.0 * count(DISTINCT customer_id) FILTER (WHERE month_number = 1)
/ count(DISTINCT customer_id) FILTER (WHERE month_number = 0), 1) AS m1_pct┌──────────────┬───────┬────────┐
│ cohort_month │ m0 │ m1_pct │
├──────────────┼───────┼────────┤
│ 2024-03-01 │ 3 │ 66.7 │
│ 2024-05-01 │ 1 │ 0.0 │
└──────────────┴───────┴────────┘A hardcoded m0–m3 is fine for an interview. In production this is a pivot, and saying “I’d generate the columns dynamically or return it long and pivot in the BI layer” is the right follow-up.
Rolling windows over time
WITH daily AS (
SELECT order_date, sum(amount) AS revenue FROM orders GROUP BY 1
)
SELECT order_date, revenue,
sum(revenue) OVER (ORDER BY order_date
RANGE BETWEEN INTERVAL 30 DAYS PRECEDING AND CURRENT ROW) AS rolling_30d
FROM daily ORDER BY order_date;┌────────────┬─────────┬─────────────┐
│ order_date │ revenue │ rolling_30d │
├────────────┼─────────┼─────────────┤
│ 2024-03-01 │ 120.00 │ 120.00 │
│ 2024-03-15 │ 80.50 │ 200.50 │
│ 2024-03-18 │ 310.00 │ 510.50 │
│ 2024-04-02 │ 45.00 │ 435.50 │
│ 2024-04-20 │ 200.00 │ 245.00 │
│ 2024-05-05 │ 99.99 │ 299.99 │
│ 2024-05-30 │ 150.00 │ 249.99 │
│ 2024-06-02 │ 75.00 │ 225.00 │
└────────────┴─────────┴─────────────┘RANGE BETWEEN INTERVAL 30 DAYS PRECEDING is a value-based frame: it means “the last 30
calendar days”, which is what “30-day rolling revenue” means. ROWS BETWEEN 30 PRECEDING would
mean “the last 30 rows”, and with gaps in the data those are entirely different windows.
This is the case where RANGE is the right default and ROWS is the mistake — the reverse of the running-total example in the window functions lesson.
Recognising it
QUESTION SHAPE
"revenue per month" date_trunc + GROUP BY
"including months with no activity" generate a spine, LEFT JOIN
"month-over-month growth" lag() over the spine, nullif on the divisor
"how many came back after N months" cohort: first-activity CTE + month difference
"30-day rolling total" RANGE BETWEEN INTERVAL ... PRECEDING
"active in the last 7 days" half-open range on the raw column
"time between events per user" lag(ts) OVER (PARTITION BY user ORDER BY ts)
filtering a timestamp by day/month half-open, never BETWEEN with dates
a date filter that is slow the column is wrapped in a functionPractice
1. Filter a timestamp column with BETWEEN two dates.
BETWEEN dates: 2 rows half-open: 4 rowsDATE '2024-03-31' is midnight, so the whole last day is lost. Half-open has no edge cases and
needs no month-length arithmetic.
2. Aggregate by month without a spine.
January and February vanish — no rows exist to group.Generate the month range and LEFT JOIN. Without it, lag() also compares the wrong periods.
3. EXPLAIN a filter that wraps the date column in date_trunc.
Filters: date_trunc('month', order_date)=2024-03-01 — no index can serve itRewrite as a half-open range on the raw column. The most common cause of a slow date query.
4. Build a cohort grid and check that m0 equals the cohort size.
2024-03 m0=3 m1=2 m2=1 m3=0m0 is the cohort by definition. If it does not match, the first-activity CTE is wrong — and
count(DISTINCT customer_id) is required so a customer with three orders counts once.
Next: gaps and islands — consecutive streaks, the hardest-looking pattern with the simplest trick.