Удержание клиентов по когортам первого месяца покупки

SQL, по которому посчитано

WITH won AS (
    SELECT
        customer_id,
        date,
        date_trunc('month', date) AS purchase_month
    FROM deals
    WHERE status = 'Выиграна'
      AND customer_id IS NOT NULL
),
bounds AS (
    SELECT
        date_trunc('month', max(date)) AS max_month,
        date_trunc('month', max(date)) - INTERVAL '12 months' AS min_cohort_month,
        date_trunc('month', max(date)) - INTERVAL '3 months' AS complete_3m_month
    FROM deals
),
first_purchase AS (
    SELECT
        customer_id,
        min(purchase_month) AS cohort_month
    FROM won
    GROUP BY customer_id
),
cohorts AS (
    SELECT
        f.cohort_month,
        count(*) AS customers
    FROM first_purchase f
    CROSS JOIN bounds b
    WHERE f.cohort_month >= b.min_cohort_month
      AND f.cohort_month < b.max_month
    GROUP BY f.cohort_month
),
returns AS (
    SELECT
        f.cohort_month,
        w.customer_id,
        date_diff('month', f.cohort_month, w.purchase_month) AS month_offset
    FROM first_purchase f
    JOIN won w ON w.customer_id = f.customer_id
    CROSS JOIN bounds b
    WHERE f.cohort_month >= b.min_cohort_month
      AND f.cohort_month < b.max_month
)
SELECT
    c.cohort_month,
    c.customers,
    round(
        count(DISTINCT CASE WHEN r.month_offset = 1 THEN r.customer_id END)::DOUBLE
        / c.customers,
        4
    ) AS retention_month_1,
    CASE
        WHEN c.cohort_month <= (SELECT complete_3m_month FROM bounds)
        THEN round(
            count(DISTINCT CASE WHEN r.month_offset = 3 THEN r.customer_id END)::DOUBLE
            / c.customers,
            4
        )
    END AS retention_month_3
FROM cohorts c
LEFT JOIN returns r ON r.cohort_month = c.cohort_month
GROUP BY c.cohort_month, c.customers
ORDER BY c.cohort_month