Показы и клики по источникам за последний месяц
SQL, по которому посчитано
WITH month_start AS (
SELECT CAST(date_trunc('month', max(date)) AS DATE) AS start_date
FROM ads
)
SELECT
source,
SUM(impressions) AS impressions,
SUM(clicks) AS clicks,
ROUND(SUM(clicks)::DOUBLE / NULLIF(SUM(impressions), 0), 4) AS click_share,
SUM(clicks) - SUM(impressions) AS click_excess,
CASE
WHEN SUM(clicks) > SUM(impressions) THEN 'клики больше показов'
ELSE 'в пределах нормы'
END AS check_result
FROM ads
CROSS JOIN month_start
WHERE date >= month_start.start_date
AND date < CAST(month_start.start_date + INTERVAL '1 month' AS DATE)
GROUP BY source
ORDER BY click_excess DESC, source
LIMIT 30