Товары в матрице ABC-XYZ
SQL, по которому посчитано
WITH items AS (
SELECT product, sum(revenue - returned_revenue) AS revenue
FROM sales
WHERE date > (SELECT max(date) FROM sales) - INTERVAL 12 MONTH
GROUP BY product
),
abc AS (
SELECT product,
CASE WHEN sum(revenue) OVER (ORDER BY revenue DESC ROWS UNBOUNDED PRECEDING) / sum(revenue) OVER () <= 0.8 THEN 'A'
WHEN sum(revenue) OVER (ORDER BY revenue DESC ROWS UNBOUNDED PRECEDING) / sum(revenue) OVER () <= 0.95 THEN 'B'
ELSE 'C' END AS abc
FROM items
),
monthly AS (
SELECT product, date_trunc('month', date) AS month, sum(units) AS units
FROM sales
WHERE date > (SELECT max(date) FROM sales) - INTERVAL 12 MONTH
GROUP BY ALL
),
xyz AS (
SELECT product,
CASE WHEN stddev_pop(units) / avg(units) <= 0.1 THEN 'X' WHEN stddev_pop(units) / avg(units) <= 0.25 THEN 'Y' ELSE 'Z' END AS xyz
FROM monthly
GROUP BY product
)
SELECT xyz.xyz, abc.abc, count(*) AS products
FROM abc
JOIN xyz USING (product)
GROUP BY ALL
ORDER BY abc, xyz