Оборачиваемость запасов по категориям, оборотов в год
SQL, по которому посчитано
WITH p AS (
SELECT (SELECT max(date) FROM sales) - INTERVAL 12 MONTH AS start_date,
(SELECT max(date) FROM sales) AS end_date
),
sp AS (
SELECT s.category AS category,
sum(s.cost_of_goods) AS cogs_12m,
sum(s.units) AS units_sold
FROM sales s CROSS JOIN p
WHERE s.date > p.start_date AND s.date <= p.end_date
GROUP BY s.category
),
st_month AS (
SELECT k.category AS category,
k.date AS date,
sum(k.value) AS stock_value
FROM stock k CROSS JOIN p
WHERE k.date > p.start_date AND k.date <= p.end_date
GROUP BY k.category, k.date
),
st AS (
SELECT category,
avg(stock_value) AS avg_stock_value
FROM st_month
GROUP BY category
)
SELECT sp.category AS category,
round(sp.cogs_12m) AS cogs_12m_rub,
round(st.avg_stock_value) AS avg_stock_value_rub,
round(sp.cogs_12m / nullif(st.avg_stock_value, 0), 2) AS turnover_times,
round(365 / nullif(sp.cogs_12m / nullif(st.avg_stock_value, 0), 0)) AS days_of_stock,
sp.units_sold AS units_sold
FROM sp
JOIN st ON sp.category = st.category
ORDER BY turnover_times
LIMIT 30