Доля кликов, завершившихся заказом, по кампаниям за последний месяц
SQL, по которому посчитано
WITH month_bounds AS (
SELECT date_trunc('month', max(date)) AS month_start
FROM ads
)
SELECT
a.source || ' / ' || a.campaign AS campaign,
sum(a.clicks) AS clicks,
sum(a.orders) AS orders,
round(sum(a.orders)::DOUBLE / nullif(sum(a.clicks), 0), 4) AS cr
FROM ads a
CROSS JOIN month_bounds b
WHERE a.date >= b.month_start
AND a.date < b.month_start + INTERVAL 1 MONTH
GROUP BY a.source, a.campaign
HAVING sum(a.clicks) > 0
ORDER BY cr DESC
LIMIT 30