How to write a cohort retention query in SQL (that actually runs)

Cohort retention is one of those metrics everyone wants and almost no one can write from memory. You group users by the month they first showed up, then track how many come back in month 1, month 2, and so on. Simple idea, fiddly query.

Here is a version that runs, explained step by step. Schema: an orders table with customer_id and order_date. PostgreSQL syntax, with dialect notes at the end.

Step 1: find each customer's cohort

A customer's cohort is the month of their first order.

SELECT customer_id,
       date_trunc('month', MIN(order_date)) AS cohort_month
FROM orders
GROUP BY customer_id;

MIN(order_date) is their first-ever order; date_trunc('month', ...) snaps it to the first of that month so everyone from, say, March 2026 lands in the same bucket.

Step 2: tag every order with its customer's cohort

Now join each order back to that cohort and record which month the order happened in.

WITH first_orders AS (
    SELECT customer_id,
           date_trunc('month', MIN(order_date)) AS cohort_month
    FROM orders
    GROUP BY customer_id
)
SELECT o.customer_id,
       f.cohort_month,
       date_trunc('month', o.order_date) AS active_month
FROM orders o
JOIN first_orders f ON f.customer_id = o.customer_id;

Every order now carries two dates: the customer's cohort month and the month the order actually happened.

Step 3: turn dates into a "months since signup" number

Retention tables read as "month 0, month 1, month 2...", so convert the two dates into an offset.

WITH first_orders AS (
    SELECT customer_id,
           date_trunc('month', MIN(order_date)) AS cohort_month
    FROM orders
    GROUP BY customer_id
),
activity AS (
    SELECT o.customer_id,
           f.cohort_month,
           date_trunc('month', o.order_date) AS active_month
    FROM orders o
    JOIN first_orders f ON f.customer_id = o.customer_id
)
SELECT
    cohort_month,
    (EXTRACT(YEAR  FROM active_month) - EXTRACT(YEAR  FROM cohort_month)) * 12
  + (EXTRACT(MONTH FROM active_month) - EXTRACT(MONTH FROM cohort_month)) AS month_offset,
    COUNT(DISTINCT customer_id) AS customers
FROM activity
GROUP BY cohort_month, month_offset
ORDER BY cohort_month, month_offset;

That year-times-12-plus-month arithmetic is the reliable way to count whole months between two dates. COUNT(DISTINCT customer_id) counts how many people from each cohort were active at each offset.

Step 4 (optional): read it as percentages

Absolute counts are fine, but retention is usually shown as "% of the cohort still active". Divide each row by the cohort's month-0 size using a window function:

-- wrap the query from step 3 as `r`
SELECT
    cohort_month,
    month_offset,
    customers,
    ROUND(
        customers * 100.0 /
        MAX(customers) FILTER (WHERE month_offset = 0)
            OVER (PARTITION BY cohort_month),
        1
    ) AS retention_pct
FROM r
ORDER BY cohort_month, month_offset;

MAX(...) FILTER (WHERE month_offset = 0) OVER (PARTITION BY cohort_month) grabs each cohort's starting size and reuses it on every row, so month 0 is always 100% and the rest fall from there.

Dialect notes

• MySQL: no date_trunc or FILTER. Use DATE_FORMAT(order_date, '%Y-%m-01') for the month, TIMESTAMPDIFF(MONTH, cohort_month, active_month) for the offset, and a CASE-based conditional aggregate instead of FILTER.
• BigQuery: use DATE_TRUNC(order_date, MONTH) and DATE_DIFF(active_month, cohort_month, MONTH).
• Snowflake: DATE_TRUNC('month', order_date) and DATEDIFF('month', cohort_month, active_month) both work.

Once step 3 runs, you have a cohort table. Pivot it in your BI tool (cohort down the side, offset across the top) and you get the triangle chart everyone recognizes.

---

This walkthrough is one recipe from The Real-World SQL Cookbook — 75 tested queries (funnels, retention, window functions, data-quality checks) that all run against one small database included in the download. If the step-by-step style clicked for you: sofrito.gumroad.com/l/sqlcookbook