Доля покупателей с одной покупкой за 12 месяцев
SQL, по которому посчитано
WITH customer_purchases AS (
SELECT
customer_id,
COUNT(*) AS purchases
FROM deals
WHERE status = 'Выиграна'
AND customer_id IS NOT NULL
AND date >= (
SELECT date_trunc('month', max(date)) - INTERVAL '11 months'
FROM deals
)
GROUP BY customer_id
),
grouped AS (
SELECT
CASE WHEN purchases = 1 THEN 'Одна покупка' ELSE 'Две и более' END AS customer_group,
COUNT(*) AS customers
FROM customer_purchases
GROUP BY 1
)
SELECT
customer_group,
customers,
round(customers * 1.0 / SUM(customers) OVER (), 4) AS share
FROM grouped
ORDER BY CASE WHEN customer_group = 'Одна покупка' THEN 0 ELSE 1 END