Сверка категорий: продажи сайта и выигранные сделки
| Категория | Разница к продажам | Разница в рублях | Сумма выигранных сделок | Выручка продаж сайта |
|---|---|---|---|---|
| Диваны | -39,8 % | -19 590 571 ₽ | 29 658 280 ₽ | 49 248 851 ₽ |
| Кровати | -57,7 % | -15 575 226 ₽ | 11 434 320 ₽ | 27 009 546 ₽ |
| Шкафы | -60,2 % | -13 279 082 ₽ | 8 796 080 ₽ | 22 075 162 ₽ |
| Освещение | -75,2 % | -6 423 257 ₽ | 2 122 670 ₽ | 8 545 927 ₽ |
| Кухни | -17,3 % | -5 464 613 ₽ | 26 035 880 ₽ | 31 500 493 ₽ |
SQL, по которому посчитано
WITH s AS (
SELECT category,
sum(revenue) AS sales_revenue,
sum(units) AS sales_units
FROM sales
WHERE channel = 'Сайт'
AND date >= (SELECT date_trunc('month', max(date)) FROM sales) - INTERVAL '11 months'
GROUP BY category
),
d AS (
SELECT category,
sum(amount) AS deals_revenue,
count(*) AS deals_count
FROM deals
WHERE status = 'Выиграна'
AND date >= (SELECT date_trunc('month', max(date)) FROM deals) - INTERVAL '11 months'
GROUP BY category
)
SELECT coalesce(s.category, d.category) AS category,
round(coalesce(s.sales_revenue, 0), 0) AS sales_revenue,
round(coalesce(d.deals_revenue, 0), 0) AS deals_revenue,
round(coalesce(d.deals_revenue, 0) - coalesce(s.sales_revenue, 0), 0) AS revenue_diff,
coalesce(d.deals_count, 0) AS deals_count,
coalesce(s.sales_units, 0) AS sales_units,
round((coalesce(d.deals_revenue, 0) - coalesce(s.sales_revenue, 0)) / nullif(s.sales_revenue, 0), 3) AS gap_share
FROM s
FULL OUTER JOIN d ON s.category = d.category
ORDER BY abs(coalesce(d.deals_revenue, 0) - coalesce(s.sales_revenue, 0)) DESC
LIMIT 30