Средний дневной расход по кампаниям за последний месяц
SQL, по которому посчитано
WITH period AS (
SELECT
date_trunc('month', max(date))::DATE AS month_start,
(date_trunc('month', max(date)) + INTERVAL 1 MONTH)::DATE AS month_end
FROM ads
),
campaign_spend AS (
SELECT
a.source || ' / ' || a.campaign AS campaign,
SUM(a.cost) AS total_cost,
COUNT(DISTINCT a.date) AS active_days,
date_diff('day', p.month_start, p.month_end) AS days_in_month
FROM ads AS a
CROSS JOIN period AS p
WHERE a.date >= p.month_start
AND a.date < p.month_end
GROUP BY a.source, a.campaign, p.month_start, p.month_end
)
SELECT
campaign,
ROUND(total_cost / days_in_month, 2) AS avg_daily_cost,
ROUND(total_cost, 2) AS total_cost,
active_days
FROM campaign_spend
ORDER BY avg_daily_cost DESC
LIMIT 30