Повторные и однократные покупатели за 12 месяцев
| Доля покупателей | Покупателей |
|---|---|
| 84,2 % | 1 225 |
| 15,8 % | 230 |
SQL, по которому посчитано
WITH won AS (
SELECT customer_id, count(*) AS deals_cnt
FROM deals
WHERE status = 'Выиграна'
AND date > (SELECT max(date) FROM deals) - INTERVAL 12 MONTH
GROUP BY customer_id
),
segs AS (
SELECT CASE WHEN deals_cnt >= 2 THEN 'Повторные покупатели' ELSE 'Однократные покупатели' END AS segment,
count(*) AS customers
FROM won
GROUP BY 1
)
SELECT segment,
customers,
round(customers * 1.0 / sum(customers) OVER (), 3) AS share
FROM segs
ORDER BY customers DESC