Индекс сезонности выручки по категориям мебели по месяцам
SQL, по которому посчитано
WITH monthly AS (
SELECT
date_trunc('month', date) AS month,
category,
sum(revenue - returned_revenue) AS net_revenue
FROM sales
WHERE date > (SELECT max(date) FROM sales) - INTERVAL 12 MONTH
GROUP BY 1, 2
),
category_avg AS (
SELECT
category,
avg(net_revenue) AS avg_revenue
FROM monthly
GROUP BY category
),
indexed AS (
SELECT
m.month,
m.category,
m.net_revenue / a.avg_revenue AS season_index
FROM monthly m
JOIN category_avg a ON a.category = m.category
)
SELECT
month,
round(max(CASE WHEN category = 'Диваны' THEN season_index END), 2) AS divany,
round(max(CASE WHEN category = 'Кровати' THEN season_index END), 2) AS krovati,
round(max(CASE WHEN category = 'Кухни' THEN season_index END), 2) AS kuhni,
round(max(CASE WHEN category = 'Шкафы' THEN season_index END), 2) AS shkafy,
round(max(CASE WHEN category = 'Освещение' THEN season_index END), 2) AS osveshchenie
FROM indexed
GROUP BY month
ORDER BY month