Средняя цена проданной единицы по категориям, 12 месяцев
SQL, по которому посчитано
WITH monthly AS (
SELECT
CAST(date_trunc('month', date) AS DATE) AS month,
category,
SUM(revenue - returned_revenue) / NULLIF(SUM(units - returned_units), 0) AS avg_price
FROM sales
WHERE date >= (SELECT CAST(date_trunc('month', max(date)) - INTERVAL '11 months' AS DATE) FROM sales)
GROUP BY 1, 2
)
SELECT
month,
ROUND(MAX(CASE WHEN category = 'Диваны' THEN avg_price END), 0) AS price_divany,
ROUND(MAX(CASE WHEN category = 'Кровати' THEN avg_price END), 0) AS price_krovati,
ROUND(MAX(CASE WHEN category = 'Кухни' THEN avg_price END), 0) AS price_kuhni,
ROUND(MAX(CASE WHEN category = 'Шкафы' THEN avg_price END), 0) AS price_shkafy,
ROUND(MAX(CASE WHEN category = 'Освещение' THEN avg_price END), 0) AS price_osveshchenie
FROM monthly
GROUP BY month
ORDER BY month