Cohort retention analysis

Cohort retention analysis groups users by when they first showed up, then tracks what percentage of each group came back in later periods.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

Cohort retention

WITH first_seen AS (SELECT customer_id, MIN(order_date) AS first_at FROM orders GROUP BY customer_id) SELECT DATE_TRUNC('month', f.first_at) AS cohort, DATE_TRUNC('month', t.order_date) AS active_month, COUNT(DISTINCT t.customer_id) FROM orders t JOIN first_seen f USING (customer_id) GROUP BY cohort, active_month ORDER BY cohort, active_month

Result:

cohort	active_month	distinct_customers
2026-01	2026-01	2
2026-01	2026-02	1
2026-01	2026-03	1
2026-02	2026-02	1

Step by step

WITH lets you name a sub-query so the main query can reference it.

WITH first_seen AS (SELECT customer_id, MIN(order_date) AS first_at FROM orders GROUP BY customer_id)

AS gives a column a friendlier name in the result.

WITH first_seen AS (SELECT customer_id, MIN(order_date) AS first_at FROM orders GROUP BY customer_id)
SELECT DATE_TRUNC('month', f.first_at) AS cohort, DATE_TRUNC('month', t.order_date) AS active_month, COUNT(DISTINCT t.customer_id)

FROM tells SQL which table to pull the data from.

WITH first_seen AS (SELECT customer_id, MIN(order_date) AS first_at FROM orders GROUP BY customer_id)
SELECT DATE_TRUNC('month', f.first_at) AS cohort, DATE_TRUNC('month', t.order_date) AS active_month, COUNT(DISTINCT t.customer_id)
FROM orders t

Practice

Try each one in your head first, then open the answer.

1. On orders, build a cohort retention report: group order_ids by their first-order_date month and count distinct order_ids active in each subsequent month.

WITH first_seen AS (SELECT order_id, MIN(order_date) AS first_at FROM orders GROUP BY order_id) SELECT DATE_TRUNC('month', f.first_at) AS cohort, DATE_TRUNC('month', t.order_date) AS active_month, COUNT(DISTINCT t.order_id) FROM orders t JOIN first_seen f USING (order_id) GROUP BY cohort, active_month ORDER BY cohort, active_month

2. On store_sales, build a cohort retention report: group sale_ids by their first-sale_date month and count distinct sale_ids active in each subsequent month.

WITH first_seen AS (SELECT sale_id, MIN(sale_date) AS first_at FROM store_sales GROUP BY sale_id) SELECT DATE_TRUNC('month', f.first_at) AS cohort, DATE_TRUNC('month', t.sale_date) AS active_month, COUNT(DISTINCT t.sale_id) FROM store_sales t JOIN first_seen f USING (sale_id) GROUP BY cohort, active_month ORDER BY cohort, active_month

3. On employees, build a cohort retention report: group employee_ids by their first-hire_date month and count distinct employee_ids active in each subsequent month.

WITH first_seen AS (SELECT employee_id, MIN(hire_date) AS first_at FROM employees GROUP BY employee_id) SELECT DATE_TRUNC('month', f.first_at) AS cohort, DATE_TRUNC('month', t.hire_date) AS active_month, COUNT(DISTINCT t.employee_id) FROM employees t JOIN first_seen f USING (employee_id) GROUP BY cohort, active_month ORDER BY cohort, active_month

4. On reviews, build a cohort retention report: group review_ids by their first-review_date month and count distinct review_ids active in each subsequent month.

WITH first_seen AS (SELECT review_id, MIN(review_date) AS first_at FROM reviews GROUP BY review_id) SELECT DATE_TRUNC('month', f.first_at) AS cohort, DATE_TRUNC('month', t.review_date) AS active_month, COUNT(DISTINCT t.review_id) FROM reviews t JOIN first_seen f USING (review_id) GROUP BY cohort, active_month ORDER BY cohort, active_month

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed