Доля запасов в выручке по категориям за 12 месяцев
SQL, по которому посчитано
WITH bounds AS (
SELECT date_trunc('month', max(date)) AS month_start,
max(date) AS last_stock_date
FROM stock
),
rev AS (
SELECT category,
sum(revenue - coalesce(returned_revenue, 0)) AS net_revenue
FROM sales
WHERE date >= (SELECT month_start - INTERVAL '11 months' FROM bounds)
AND date < (SELECT month_start + INTERVAL '1 month' FROM bounds)
GROUP BY category
),
stock_avg AS (
SELECT category,
avg(value) AS avg_stock_value
FROM stock
WHERE date >= (SELECT month_start - INTERVAL '11 months' FROM bounds)
AND date <= (SELECT last_stock_date FROM bounds)
GROUP BY category
)
SELECT s.category,
round(s.avg_stock_value, 2) AS avg_stock_value,
round(r.net_revenue, 2) AS net_revenue_12m,
round(s.avg_stock_value / nullif(r.net_revenue, 0), 4) AS stock_to_revenue
FROM stock_avg s
JOIN rev r USING (category)
ORDER BY stock_to_revenue DESC
LIMIT 30