DEV Community: sofrito

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

sofrito — Thu, 30 Jul 2026 12:42:52 +0000

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

5 SQL queries every data analyst ends up googling

sofrito — Mon, 20 Jul 2026 10:25:41 +0000

You know the pattern. A stakeholder asks a simple-sounding question, you open your editor confident, and thirty seconds later you are back on Google typing "sql running total" for the hundredth time.

Here are five of the queries analysts re-google the most, written so you can actually run them. All examples use a small e-commerce schema (customers, orders, order_items, products) and PostgreSQL syntax, with notes where other dialects differ.

1. Running total (cumulative sum)

The classic "revenue to date" line on every dashboard.

SELECT
    order_date,
    daily_revenue,
    SUM(daily_revenue) OVER (ORDER BY order_date) AS running_total
FROM (
    SELECT order_date::date AS order_date,
           SUM(total_amount)  AS daily_revenue
    FROM orders
    GROUP BY order_date::date
) d
ORDER BY order_date;

The trick is the window function SUM(...) OVER (ORDER BY ...). No self-join, no correlated subquery. It sums every row up to and including the current one.

2. Keep only the latest row per group (deduplicate)

You have multiple rows per customer and want just their most recent order.

SELECT *
FROM (
    SELECT o.*,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id
               ORDER BY order_date DESC, id DESC
           ) AS rn
    FROM orders o
) t
WHERE rn = 1;

ROW_NUMBER() numbers each customer's rows newest-first, then you keep number 1. This is the pattern behind "latest status", "most recent login", "current price", and a hundred others.

3. Month-over-month growth

Revenue this month versus last, as a percentage, in one query.

WITH monthly AS (
    SELECT date_trunc('month', order_date) AS month,
           SUM(total_amount)               AS revenue
    FROM orders
    GROUP BY 1
)
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY month))
        / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100, 1
    ) AS growth_pct
FROM monthly
ORDER BY month;

LAG() pulls the previous row's value onto the current row so you can compare periods. NULLIF(..., 0) quietly saves you from a divide-by-zero on the first month.

4. Top N per group

Top 3 products in every category, not just top 3 overall.

SELECT category, product_name, revenue
FROM (
    SELECT
        p.category,
        p.name AS product_name,
        SUM(oi.quantity * oi.unit_price) AS revenue,
        ROW_NUMBER() OVER (
            PARTITION BY p.category
            ORDER BY SUM(oi.quantity * oi.unit_price) DESC
        ) AS rn
    FROM order_items oi
    JOIN products p ON p.id = oi.product_id
    GROUP BY p.category, p.name
) t
WHERE rn <= 3
ORDER BY category, revenue DESC;

Same ROW_NUMBER() idea as #2, but PARTITION BY category restarts the count for each group. Change <= 3 to whatever N you need.

5. Find the rows that are missing (anti-join)

Customers who signed up but never ordered. The thing a plain JOIN will never show you.

SELECT c.id, c.email
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

LEFT JOIN keeps every customer; the WHERE ... IS NULL filter then keeps only the ones with no matching order. This is how you find gaps: unpaid invoices, unshipped orders, users with no activity.

The pattern behind the patterns

Four of these five lean on window functions (SUM OVER, ROW_NUMBER, LAG). If there is one thing worth drilling until it is muscle memory, it is those. Most "advanced" analytics SQL is a variation on the three moves above.

These five are pulled from The Real-World SQL Cookbook 75 tested, copy-paste queries like this, all runnable against one small database you load once and follow along with. If this saved you a search or two, the other 70 probably will too: sofrito.gumroad.com/l/sqlcookbook