Накопленная доля выручки по покупателям и границы групп A, B и C
SQL, по которому посчитано
WITH cust AS (
SELECT
customer_id,
SUM(amount) AS revenue
FROM deals
WHERE status = 'Выиграна'
AND date > (SELECT max(date) FROM sales) - INTERVAL 12 MONTH
GROUP BY customer_id
),
ranked AS (
SELECT
customer_id,
revenue,
SUM(revenue) OVER (ORDER BY revenue DESC, customer_id) AS cum_revenue,
SUM(revenue) OVER () AS total_revenue
FROM cust
)
SELECT
customer_id,
round(revenue, 0) AS revenue,
round(revenue / total_revenue, 4) AS share,
round(cum_revenue / total_revenue, 4) AS cum_share,
CASE
WHEN cum_revenue / total_revenue <= 0.8 THEN 'A'
WHEN cum_revenue / total_revenue <= 0.95 THEN 'B'
ELSE 'C'
END AS abc_group
FROM ranked
ORDER BY revenue DESC
LIMIT 30