cohort_report.sql in SQL

Signup cohorts and the share of each still ordering months later.

-- Cohort retention: share of each signup month still active later.
WITH cohorts AS (
    SELECT
        id AS customer_id,
        STRFTIME('%Y-%m', created_at) AS cohort_month
    FROM customers
),
sizes AS (
    SELECT
        cohort_month,
        COUNT(*) AS cohort_size
    FROM cohorts
    GROUP BY cohort_month
),
activity AS (
    SELECT
        c.cohort_month,
        c.customer_id,
        (
            CAST(STRFTIME('%Y', o.placed_at) AS INTEGER) * 12
            + CAST(STRFTIME('%m', o.placed_at) AS INTEGER)
        ) - (
            CAST(SUBSTR(c.cohort_month, 1, 4) AS INTEGER) * 12
            + CAST(SUBSTR(c.cohort_month, 6, 2) AS INTEGER)
        ) AS month_number
    FROM cohorts AS c
    JOIN orders AS o
        ON o.customer_id = c.customer_id
    WHERE o.status = 'paid'
),
retained AS (
    SELECT
        cohort_month,
        month_number,
        COUNT(DISTINCT customer_id) AS active
    FROM activity
    WHERE month_number BETWEEN 0 AND 6
    GROUP BY cohort_month, month_number
)
SELECT
    r.cohort_month,
    s.cohort_size,
    r.month_number,
    r.active,
    ROUND(r.active * 100.0 / NULLIF(s.cohort_size, 0), 1) AS retention_pct
FROM retained AS r
JOIN sizes AS s
    ON s.cohort_month = r.cohort_month
ORDER BY r.cohort_month DESC, r.month_number;

How it works

The first CTE stamps every customer with the month they joined.
Activity is bucketed by months since that cohort month.
Dividing by the cohort size turns counts into retention rates.

Keywords and builtins used here

AND
AS
BETWEEN
BY
CAST
COUNT
DESC
DISTINCT
FROM
GROUP
INTEGER
JOIN
NULLIF
ON
ORDER
SELECT
WHERE
WITH
c

The run, in numbers

Lines: 49
Characters to type: 1063
Tokens: 242
Three-star pace: 100 tpm

At the three-star pace of 100 tokens a minute, this run takes about 145 seconds.

Type this snippet

Step 1 of 2 in Encore, step 23 of 24 in Analytics & reporting.

← Previous Next →