Доля топ 10 артикулов в выручке по месяцам
SQL, по которому посчитано
WITH monthly AS (
SELECT date_trunc('month', s.date) AS month,
s.sku,
sum(s.revenue) AS revenue
FROM sales AS s
WHERE s.date >= (SELECT date_trunc('month', max(s2.date)) FROM sales AS s2) - INTERVAL 11 MONTH
GROUP BY 1, 2
),
ranked AS (
SELECT month,
sku,
revenue,
row_number() OVER (PARTITION BY month ORDER BY revenue DESC) AS rn
FROM monthly
)
SELECT month,
round(sum(CASE WHEN rn <= 10 THEN revenue ELSE 0 END) / nullif(sum(revenue), 0), 4) AS top10_share
FROM ranked
GROUP BY month
ORDER BY month