Структура рекламных расходов по источникам за последний месяц
SQL, по которому посчитано
WITH bounds AS (
SELECT date_trunc('month', max(date)) AS month_start
FROM ads
),
by_source AS (
SELECT
source,
sum(cost) AS cost_rub,
sum(orders) AS orders,
sum(revenue) AS revenue_rub
FROM ads
WHERE date >= (SELECT month_start FROM bounds)
GROUP BY source
)
SELECT
source,
round(cost_rub, 0) AS cost_rub,
round(cost_rub / sum(cost_rub) OVER (), 4) AS cost_share,
orders,
round(revenue_rub, 0) AS revenue_rub,
round(cost_rub / nullif(revenue_rub, 0), 4) AS drr
FROM by_source
ORDER BY cost_rub DESC