Средний CPC по категориям вымышленного мебельного магазина за последний месяц
SQL, по которому посчитано
WITH last_month AS (
SELECT date_trunc('month', max(date)) AS month_start
FROM ads
),
agg AS (
SELECT
a.category,
sum(a.cost) AS cost,
sum(a.clicks) AS clicks,
sum(a.orders) AS orders,
sum(a.revenue) AS revenue
FROM ads a
CROSS JOIN last_month m
WHERE a.date >= m.month_start
AND a.date < m.month_start + INTERVAL 1 MONTH
GROUP BY a.category
HAVING sum(a.clicks) > 0
)
SELECT
category,
round(cost) AS cost_rub,
clicks,
round(cost / clicks, 2) AS cpc,
orders,
round(cost / nullif(orders, 0), 2) AS cpo,
round(revenue) AS revenue_rub
FROM agg
ORDER BY cpc DESC
LIMIT 30