Запас в днях по артикулам: самые большие остатки
SQL, по которому посчитано
WITH last_stock AS (
SELECT sku, category, units
FROM stock
WHERE date = (SELECT max(date) FROM stock)
),
demand AS (
SELECT
sku,
max(product) AS product,
sum(units - returned_units) AS net_units
FROM sales
WHERE date > (SELECT max(date) FROM stock) - INTERVAL 12 MONTH
AND date <= (SELECT max(date) FROM stock)
GROUP BY sku
)
SELECT
s.sku,
s.category,
d.product,
s.units AS stock_units,
round(d.net_units / 365.0, 2) AS avg_daily_units,
round(s.units / nullif(d.net_units / 365.0, 0), 0) AS days_of_supply
FROM last_stock s
JOIN demand d ON d.sku = s.sku
WHERE d.net_units > 0
ORDER BY days_of_supply DESC
LIMIT 30