Выручка по группам A, B и C за последние 12 месяцев
SQL, по которому посчитано
WITH base AS (
SELECT
sku,
any_value(product) AS product,
sum(revenue) AS revenue
FROM sales
WHERE date > (SELECT max(date) FROM sales) - INTERVAL 12 MONTH
GROUP BY sku
),
totals AS (
SELECT sum(revenue) AS total_revenue FROM base
),
ranked AS (
SELECT
sku,
product,
revenue,
sum(revenue) OVER (ORDER BY revenue DESC, sku ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
/ (SELECT total_revenue FROM totals) AS cum_share
FROM base
)
SELECT
CASE
WHEN cum_share <= 0.8 THEN 'A'
WHEN cum_share <= 0.95 THEN 'B'
ELSE 'C'
END AS abc_group,
count(*) AS products,
round(sum(revenue), 0) AS revenue,
round(sum(revenue) / (SELECT total_revenue FROM totals), 4) AS share
FROM ranked
GROUP BY 1
ORDER BY 1