Стоимость остатка по группам ABC и категориям
SQL, по которому посчитано
WITH latest AS (
SELECT sku, category, value
FROM stock
WHERE date = (SELECT max(date) FROM stock)
),
ranked AS (
SELECT
sku,
category,
value,
SUM(value) OVER (ORDER BY value DESC, sku ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_value,
SUM(value) OVER () AS total_value
FROM latest
),
classified AS (
SELECT
sku,
category,
value,
CASE
WHEN cumulative_value / NULLIF(total_value, 0) <= 0.8 THEN 'A'
WHEN cumulative_value / NULLIF(total_value, 0) <= 0.95 THEN 'B'
ELSE 'C'
END AS abc_group
FROM ranked
)
SELECT
abc_group,
category,
ROUND(SUM(value), 2) AS stock_value
FROM classified
GROUP BY abc_group, category
ORDER BY abc_group, SUM(value) DESC