Повторные покупки по когортам
SQL, по которому посчитано
WITH won AS (
SELECT customer_id, date FROM deals WHERE status = 'Выиграна'
),
first_purchase AS (
SELECT customer_id, date_trunc('quarter', min(date)) AS cohort FROM won GROUP BY customer_id
),
activity AS (
SELECT DISTINCT first_purchase.cohort, won.customer_id,
(year(won.date) - year(first_purchase.cohort)) * 4 + quarter(won.date) - quarter(first_purchase.cohort) AS quarter_number
FROM won
JOIN first_purchase USING (customer_id)
),
sizes AS (SELECT cohort, count(*) AS customers FROM first_purchase GROUP BY cohort),
returns AS (
SELECT activity.cohort, activity.quarter_number, count(*) / any_value(sizes.customers) AS returned
FROM activity
JOIN sizes USING (cohort)
WHERE activity.quarter_number BETWEEN 1 AND 4
GROUP BY activity.cohort, activity.quarter_number
)
SELECT year(cohort) || ', ' || quarter(cohort) || ' кв.' AS cohort_label, 'Квартал ' || quarter_number AS quarter_label, round(returned, 4) AS returned
FROM returns
ORDER BY cohort, quarter_number