CODE WORKSPACE

Simple cohort retention slice

Return cohort_day, day_offset, and active_buyers: group buyers by their first order day (cohort), then count distinct active buyers at each whole-day offset from that cohort day. Order by cohort_day, day_offset.

first_order (date(MIN(created_at)) per buyer) joined to activity (DISTINCT buyer_id, date(created_at)); offset via julianday difference.

Sample input

ordersCohort = each buyer’s first order day. Buyers 1 and 3 both first ordered on 2026-01-12.
order_idbuyer_idcreated_at
100112026-01-12 08:40:00
100412026-01-15 14:20:00
100232026-01-12 10:02:00
100342026-01-13 11:15:00
100522026-01-18 09:05:00

Expected output

Expected outputFirst 6 of 11 rows. day_offset 0 is the cohort day itself; the 01-12 cohort has 2 buyers active at offset 0.
cohort_dayday_offsetactive_buyers
2026-01-1202
2026-01-1231
2026-01-12101
2026-01-12161
2026-01-12241
2026-01-1301

Constraints

Build a first_order CTE (date(MIN(created_at)) per buyer) and an activity CTE (DISTINCT buyer_id, date(created_at)). Join on buyer_id, compute day_offset with CAST(julianday(activity_day) - julianday(cohort_day) AS INTEGER), and COUNT(DISTINCT buyer_id). Order by cohort_day, day_offset.

Expected skills

Cohort first-event logic, day offsets, and distinct counting.

SQL
Loading...

AI evaluation

Run the SQL query to inspect preview rows.