Free SQL template
Cohort retention
Customers grouped by the month of their first order: how many come back in each later month.
It opens with this template loaded and runs in your browser. You don't need an account to use it; a free account saves your work and exports it.
The job it does
When someone asks "are new customers sticking around?" or you need a retention chart for a review. It comes with its own checks, so after you change it you can see at a glance that it still does the job.
How to use it with your own data
Import a CSV of orders with a customer id and an order date, and rename the two columns in the first CTE.
What's inside
This is the template exactly as the SQL Lab opens it, read-only. Open it to run it, change it and export it.
- Runs on the retail sample database (Mangrove Market: stores, customers, orders, payments)
- 1 tab
- PostgreSQL in your browser
Are new customers sticking around? Group customers by the month of their first order (their cohort) and count how many ordered again in each later month.
Read it as a triangle: months_after = 0 is 100%, and each later row is the share of the cohort that came back that month. Export the result as CSV to chart it.
Your own data: import a CSV of orders with a customer id and an order date, and change the table and column names in the CTEs.
- Has the columns cohort, months_after, customers, retained_pct
- Returns between 1 and 2,000 rows
- Does not change any data
-- Cohort retention: customers grouped by the month of their first order,
-- and how many of them ordered again 1, 2, 3… months later.
WITH first_orders AS (
SELECT customer_id, date_trunc('month', min(ordered_at))::date AS cohort
FROM orders
WHERE status <> 'cancelled'
GROUP BY customer_id
),
cohort_size AS (
SELECT cohort, count(*) AS size FROM first_orders GROUP BY cohort
),
activity AS (
SELECT DISTINCT customer_id, date_trunc('month', ordered_at)::date AS month
FROM orders
WHERE status <> 'cancelled'
)
SELECT f.cohort,
(extract(year FROM age(a.month, f.cohort)) * 12 + extract(month FROM age(a.month, f.cohort)))::int AS months_after,
count(*) AS customers,
round(100.0 * count(*) / max(c.size), 1) AS retained_pct
FROM first_orders f
JOIN activity a ON a.customer_id = f.customer_id
JOIN cohort_size c ON c.cohort = f.cohort
GROUP BY f.cohort, months_after
ORDER BY f.cohort, months_after;The lesson behind it
Learn why it works, with a podcast and a quiz for each lesson.
More SQL templates
Data-quality checks
Duplicates, missing values, orphans and impossible values, each as one row with a count.
Schema design for a new feature
Tables, keys and constraints for a new feature, sample rows, and the queries the feature needs.
Monthly sales report
Revenue, orders and average order value per month, with the change on the month before.