Распределение клиентов по числу категорий за 12 месяцев
SQL, по которому посчитано
WITH last_date AS (
SELECT max(date) AS d FROM deals
),
customer_categories AS (
SELECT customer_id, count(DISTINCT category) AS categories_count
FROM deals
WHERE status = 'Выиграна'
AND date >= (SELECT d - INTERVAL '12 months' FROM last_date)
GROUP BY customer_id
),
distribution AS (
SELECT categories_count, count(*) AS customers
FROM customer_categories
GROUP BY categories_count
)
SELECT
categories_count,
customers,
round(sum(categories_count * customers) OVER () * 1.0 / sum(customers) OVER (), 2) AS avg_categories
FROM distribution
ORDER BY categories_count
LIMIT 30