Средний чек с рекламы по кампаниям за последний месяц, вымышленный мебельный магазин
SQL, по которому посчитано
WITH last_month AS (
SELECT date_trunc('month', max(date)) AS month_start
FROM ads
),
campaign_stats AS (
SELECT
a.source,
a.campaign,
sum(a.revenue) AS revenue,
sum(a.orders) AS orders
FROM ads a
CROSS JOIN last_month lm
WHERE a.date >= lm.month_start
AND a.date < lm.month_start + INTERVAL 1 MONTH
GROUP BY a.source, a.campaign
HAVING sum(a.orders) > 0
)
SELECT
source,
campaign,
source || ' / ' || campaign AS campaign_label,
round(revenue, 2) AS revenue,
orders,
round(revenue / orders, 2) AS avg_check
FROM campaign_stats
ORDER BY avg_check DESC
LIMIT 30