Строки рекламы вне последнего месяца по источникам
SQL, по которому посчитано
WITH bounds AS (
SELECT
date_trunc('month', max(date))::DATE AS month_start,
(date_trunc('month', max(date)) + INTERVAL 1 MONTH)::DATE AS month_end
FROM ads
),
outside AS (
SELECT
a.source,
a.date,
CASE
WHEN a.date < b.month_start THEN 'before'
ELSE 'after'
END AS zone
FROM ads a
CROSS JOIN bounds b
WHERE a.date < b.month_start OR a.date >= b.month_end
)
SELECT
source,
count(*) FILTER (WHERE zone = 'before') AS rows_before,
count(*) FILTER (WHERE zone = 'after') AS rows_after,
count(DISTINCT date) FILTER (WHERE zone = 'before') AS days_before,
count(DISTINCT date) FILTER (WHERE zone = 'after') AS days_after
FROM outside
GROUP BY source
ORDER BY source
LIMIT 30