Доля категорий в выручке и в проданных штуках за 12 месяцев
SQL, по которому посчитано
WITH base AS (
SELECT category, sku, units, revenue
FROM sales
WHERE date >= (
SELECT date_trunc('month', max(date)) - INTERVAL 11 MONTH FROM sales
)
),
agg AS (
SELECT category,
count(DISTINCT sku) AS sku_count,
sum(units) AS units_sold,
sum(revenue) AS revenue
FROM base
GROUP BY category
),
totals AS (
SELECT *,
sum(revenue) OVER () AS total_revenue,
sum(units_sold) OVER () AS total_units
FROM agg
)
SELECT category,
sku_count,
units_sold,
round(revenue, 0) AS revenue_rub,
round(revenue / total_revenue, 4) AS revenue_share,
round(units_sold * 1.0 / total_units, 4) AS units_share
FROM totals
ORDER BY revenue_rub DESC