Структура показов и кликов по источникам за последний месяц
SQL, по которому посчитано
WITH filtered AS (
SELECT source, impressions, clicks
FROM ads
WHERE date >= (SELECT date_trunc('month', max(date)) FROM ads)
),
by_source AS (
SELECT source, SUM(impressions) AS impressions, SUM(clicks) AS clicks
FROM filtered
GROUP BY source
),
grand AS (
SELECT SUM(impressions) AS total_impressions, SUM(clicks) AS total_clicks
FROM by_source
)
SELECT
source,
impressions,
clicks,
round(impressions::DOUBLE / NULLIF(total_impressions, 0), 4) AS impressions_share,
round(clicks::DOUBLE / NULLIF(total_clicks, 0), 4) AS clicks_share
FROM by_source
CROSS JOIN grand
ORDER BY impressions_share DESC, source