Изменение CTR по кампаниям за последний месяц
SQL, по которому посчитано
WITH bounds AS (
SELECT
date_trunc('month', max(date)) AS last_month_start,
date_trunc('month', max(date)) - INTERVAL '2 month' AS first_month_start
FROM ads
),
monthly AS (
SELECT
a.campaign,
date_trunc('month', a.date) AS month,
sum(a.clicks)::DOUBLE / nullif(sum(a.impressions), 0) AS ctr
FROM ads a
CROSS JOIN bounds b
WHERE a.date >= b.first_month_start
AND a.date < b.last_month_start + INTERVAL '1 month'
GROUP BY 1, 2
HAVING sum(a.impressions) > 0
),
with_prev AS (
SELECT
campaign,
month,
ctr,
lag(ctr) OVER (PARTITION BY campaign ORDER BY month) AS prev_ctr
FROM monthly
),
top_campaigns AS (
SELECT
a.campaign
FROM ads a
CROSS JOIN bounds b
WHERE a.date >= b.first_month_start
AND a.date < b.last_month_start + INTERVAL '1 month'
GROUP BY a.campaign
ORDER BY sum(a.cost) DESC
LIMIT 6
)
SELECT
w.campaign,
strftime(w.month, '%Y-%m') AS month,
round(w.ctr, 4) AS ctr,
round(w.prev_ctr, 4) AS prev_ctr,
round(w.ctr - w.prev_ctr, 4) AS ctr_change
FROM with_prev w
JOIN top_campaigns t ON w.campaign = t.campaign
WHERE w.month = (SELECT last_month_start FROM bounds)
AND w.prev_ctr IS NOT NULL
ORDER BY ctr_change ASC