Клики и заказы по источникам рекламы за последний месяц
SQL, по которому посчитано
WITH period AS (
SELECT date_trunc('month', max(date)) AS start_date,
max(date) AS end_date
FROM ads
)
SELECT
a.source AS source,
sum(a.impressions) AS impressions,
sum(a.clicks) AS clicks,
sum(a.orders) AS orders,
round(sum(a.cost), 0) AS cost,
round(sum(a.revenue), 0) AS revenue,
round(sum(a.orders) / nullif(sum(a.clicks), 0), 3) AS orders_per_click,
round(sum(a.cost) / nullif(sum(a.orders), 0), 0) AS cost_per_order
FROM ads a
CROSS JOIN period p
WHERE a.date >= p.start_date AND a.date <= p.end_date
GROUP BY a.source
ORDER BY sum(a.orders) DESC