cohort_report.sql en SQL

Cohortes de registro y la proporción de cada una que sigue comprando meses después.

-- Retención por cohorte: parte de cada mes de alta que sigue activa después.
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;

Cómo funciona

La primera CTE marca a cada cliente con el mes en que se registró.
La actividad se segmenta por meses desde ese mes de cohorte.
Dividir entre el tamaño de la cohorte convierte conteos en tasas de retención.

Palabras clave y builtins usados aquí

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

El intento, en números

Líneas: 49
Caracteres a escribir: 1073
Tokens: 242
Ritmo de tres estrellas: 100 tpm

Al ritmo de tres estrellas de 100 tokens por minuto, este intento toma unos 145 segundos.

Escribe este fragmento

Paso 1 de 2 en Bis; paso 23 de 24 en Analítica y reportes.

← Anterior Siguiente →