Cohort Retention in SQL

Example query

This example assumes: orders(id, customer_id bigint, order_date date, total_amount numeric). It is written in PostgreSQL syntax, and your table and column names will differ from these.

WITH first_order AS (
  SELECT customer_id,
         DATE_TRUNC('month', MIN(order_date))::date AS cohort_month
  FROM orders
  GROUP BY customer_id
),
activity AS (
  SELECT DISTINCT customer_id,
         DATE_TRUNC('month', order_date)::date AS active_month
  FROM orders
),
cohort_size AS (
  SELECT cohort_month, COUNT(*) AS customers
  FROM first_order
  GROUP BY cohort_month
)
SELECT f.cohort_month,
       (EXTRACT(YEAR FROM a.active_month) - EXTRACT(YEAR FROM f.cohort_month)) * 12
         + (EXTRACT(MONTH FROM a.active_month) - EXTRACT(MONTH FROM f.cohort_month)) AS months_since_first,
       COUNT(DISTINCT a.customer_id) AS active_customers,
       ROUND(100.0 * COUNT(DISTINCT a.customer_id) / MAX(s.customers), 1) AS retention_pct
FROM first_order f
JOIN activity a ON a.customer_id = f.customer_id
JOIN cohort_size s ON s.cohort_month = f.cohort_month
GROUP BY f.cohort_month, 2
ORDER BY f.cohort_month, 2;

How it works

• first_order assigns each customer to the month of their first purchase, their cohort.
• activity lists each month in which a customer bought anything, once per customer per month.
• Joining them and counting distinct customers by months since the first purchase gives the cohort's active customers in each later month; dividing by the cohort size gives retention.

MySQL, BigQuery, SQL Server and SQLite differences

• DATE_TRUNC and EXTRACT are PostgreSQL forms. In MySQL use PERIOD_DIFF or TIMESTAMPDIFF(MONTH, ...); in BigQuery use DATE_DIFF(a, b, MONTH); in SQL Server use DATEDIFF(month, a, b).

Common mistakes

• Month 0 is always 100%; the interesting numbers start at month 1.
• Recent cohorts have fewer months of history, so their rows are shorter; do not read the blanks as zero.
• 'Active' here means any purchase; define it differently (a subscription still active, a login) when that is the real question.

Ask for it in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the query matches the definition your business uses.

• Show monthly cohort retention based on customers' first order.
• What share of January's new customers ordered again within 3 months?