Дни без данных по кампаниям за последний месяц
SQL, по которому посчитано
WITH bounds AS (
SELECT date_trunc('month', max(date)) AS month_start,
max(date) AS last_date
FROM ads
),
days AS (
SELECT CAST(unnest(generate_series(b.month_start, b.last_date, INTERVAL 1 DAY)) AS DATE) AS day_date
FROM bounds b
),
campaigns AS (
SELECT a.source,
a.campaign,
min(a.date) AS first_date
FROM ads a
CROSS JOIN bounds b
WHERE a.date >= b.month_start
GROUP BY a.source, a.campaign
),
grid AS (
SELECT c.source,
c.campaign,
d.day_date
FROM campaigns c
CROSS JOIN days d
WHERE d.day_date >= c.first_date
)
SELECT g.source,
g.campaign,
count(*) AS days_in_period,
count(*) FILTER (WHERE a.date IS NULL) AS missing_days,
round(count(*) FILTER (WHERE a.date IS NULL) * 1.0 / count(*), 3) AS missing_share
FROM grid g
LEFT JOIN ads a
ON a.source = g.source
AND a.campaign = g.campaign
AND a.date = g.day_date
GROUP BY g.source, g.campaign
ORDER BY missing_days DESC, g.campaign
LIMIT 30