Прогноз выручки на 30 дней по категориям
SQL, по которому посчитано
WITH year_sales AS (
SELECT
category,
sum(units - returned_units) AS net_units_12m,
sum(revenue - returned_revenue) AS net_revenue_12m
FROM sales
WHERE date > (SELECT max(date) FROM sales) - INTERVAL 12 MONTH
GROUP BY category
),
recent_sales AS (
SELECT
category,
sum(units - returned_units) AS net_units_90d
FROM sales
WHERE date > (SELECT max(date) FROM sales) - INTERVAL 90 DAY
GROUP BY category
),
latest_stock AS (
SELECT
category,
sum(units) AS stock_units
FROM stock
WHERE date = (SELECT max(date) FROM stock)
GROUP BY category
),
calc AS (
SELECT
r.category,
r.net_units_90d / 90.0 AS avg_daily_units,
coalesce(s.stock_units, 0) AS stock_units,
y.net_revenue_12m / nullif(y.net_units_12m, 0) AS avg_price
FROM recent_sales r
LEFT JOIN latest_stock s ON s.category = r.category
LEFT JOIN year_sales y ON y.category = r.category
)
SELECT
category,
round(avg_daily_units, 2) AS avg_daily_units,
stock_units,
round(stock_units::DOUBLE / nullif(avg_daily_units, 0), 1) AS days_of_stock,
round(least(stock_units::DOUBLE, greatest(avg_daily_units, 0) * 30), 0) AS forecast_units_30d,
round(least(stock_units::DOUBLE, greatest(avg_daily_units, 0) * 30) * coalesce(avg_price, 0), 0) AS forecast_revenue_30d
FROM calc
ORDER BY forecast_revenue_30d DESC
LIMIT 30