Доля кампаний с ROAS ниже единицы по источникам
SQL, по которому посчитано
WITH last_month AS (
SELECT date_trunc('month', max(date)) AS month_start
FROM ads
),
campaigns AS (
SELECT
a.source,
a.campaign,
sum(a.cost) AS cost,
sum(a.revenue) AS revenue
FROM ads AS a
CROSS JOIN last_month AS lm
WHERE a.date >= lm.month_start
AND a.date < lm.month_start + INTERVAL '1 month'
GROUP BY a.source, a.campaign
HAVING sum(a.cost) > 0
),
flags AS (
SELECT
source,
campaign,
revenue / cost AS roas
FROM campaigns
)
SELECT
source,
count(*) AS campaigns,
sum(CASE WHEN roas < 1 THEN 1 ELSE 0 END) AS campaigns_below_roas,
round(sum(CASE WHEN roas < 1 THEN 1 ELSE 0 END)::DOUBLE / count(*), 4) AS share_below_roas
FROM flags
GROUP BY source
ORDER BY share_below_roas DESC, source