Лишние строки в данных по кампаниям за последний квартал
SQL, по которому посчитано
WITH base AS (
SELECT source, campaign, date, category
FROM ads
WHERE date >= (SELECT date_trunc('quarter', max(date)) FROM ads)
),
per_key AS (
SELECT source, campaign, date, category, count(*) AS rows_cnt
FROM base
GROUP BY source, campaign, date, category
)
SELECT source || ' / ' || campaign AS campaign_key,
count(*) AS key_count,
sum(rows_cnt) AS row_count,
sum(rows_cnt) - count(*) AS duplicate_rows,
round((sum(rows_cnt) - count(*)) / sum(rows_cnt), 3) AS duplicate_share
FROM per_key
GROUP BY source, campaign
ORDER BY duplicate_rows DESC, row_count DESC
LIMIT 30