Активные рекламные кампании по месяцам и источникам
SQL, по которому посчитано
WITH last_month AS (
SELECT CAST(date_trunc('month', max(date)) AS DATE) AS month_start
FROM ads
),
campaign_months AS (
SELECT
CAST(date_trunc('month', a.date) AS DATE) AS month,
a.source,
a.campaign
FROM ads a
WHERE a.impressions > 0
AND a.date >= (SELECT CAST(month_start - INTERVAL '2 months' AS DATE) FROM last_month)
GROUP BY 1, 2, 3
)
SELECT
month,
source,
COUNT(*) AS active_campaigns
FROM campaign_months
GROUP BY 1, 2
ORDER BY 1, 2