Артикулы с отрицательным остатком по категориям
SQL, по которому посчитано
WITH monthly AS (
SELECT
date_trunc('month', s.date) AS month,
s.category,
s.sku,
s.units AS closing_units,
COALESCE(sa.sold_units, 0) AS sold_units,
COALESCE(sa.returned_units, 0) AS returned_units
FROM stock AS s
LEFT JOIN (
SELECT
date_trunc('month', x.date) AS month,
x.sku,
sum(x.units) AS sold_units,
sum(x.returned_units) AS returned_units
FROM sales AS x
GROUP BY 1, 2
) AS sa
ON sa.month = date_trunc('month', s.date)
AND sa.sku = s.sku
WHERE s.date >= (SELECT max(date) - INTERVAL 11 MONTH FROM stock)
)
SELECT
category,
count(DISTINCT CASE WHEN closing_units + sold_units - returned_units < 0 THEN sku END) AS skus_negative_opening,
count(DISTINCT CASE WHEN closing_units < 0 THEN sku END) AS skus_negative_closing
FROM monthly
GROUP BY category
ORDER BY skus_negative_opening DESC, category