Покупатели по давности и частоте
SQL, по которому посчитано
WITH won AS (
SELECT customer_id, date, amount FROM deals WHERE status = 'Выиграна'
),
customers AS (
SELECT customer_id,
date_diff('day', max(date), (SELECT max(date) FROM deals)) AS recency,
count(*) AS frequency
FROM won
GROUP BY customer_id
)
SELECT CASE WHEN recency <= 180 THEN 'R3, до полугода' WHEN recency <= 365 THEN 'R2, до года' ELSE 'R1, больше года' END AS recency_group,
CASE WHEN frequency >= 3 THEN 'F3, 3 и больше' WHEN frequency = 2 THEN 'F2, 2 покупки' ELSE 'F1, 1 покупка' END AS frequency_group,
count(*) AS customers
FROM customers
GROUP BY ALL
ORDER BY recency_group DESC, frequency_group DESC