Suppressions daily

1-- core.sendgrid.suppressions_daily
2-- New suppressions per day, by type: the list-hygiene view behind SendGrid's
3-- Suppressions pages (Bounces, Blocks, Invalid Emails, Spam Reports, Global Unsubscribes).
4--
5-- Grain: one row per day (UTC, from the suppression's `created` time) x suppression type.
6-- Depends on: staging.sendgrid.bounce, .block, .invalid_email, .spam_report,
7--             .global_unsubscribe
8--
9-- A rising spam-report or block count is an early warning of a sending-reputation problem,
10-- well before delivery rates move. Unsubscribes from individual unsubscribe groups are not
11-- in this model: group membership (`group_suppression`) is synced without a date.
12--
13-- `addresses` counts distinct addresses within the row. The same address can appear on
14-- several lists (a hard bounce that was also reported as spam), so summing `addresses`
15-- across types overstates the number of people.
16
17WITH suppressions AS (
18    SELECT email, created_at, suppression_type FROM {{staging.sendgrid.bounce}}
19    UNION ALL
20    SELECT email, created_at, suppression_type FROM {{staging.sendgrid.block}}
21    UNION ALL
22    SELECT email, created_at, suppression_type FROM {{staging.sendgrid.invalid_email}}
23    UNION ALL
24    SELECT email, created_at, suppression_type FROM {{staging.sendgrid.spam_report}}
25    UNION ALL
26    SELECT email, created_at, suppression_type FROM {{staging.sendgrid.global_unsubscribe}}
27)
28
29SELECT
30    DATE(created_at)                AS date,
31    suppression_type,
32    COUNT(*)                        AS suppressions,
33    COUNT(DISTINCT email)           AS addresses
34FROM suppressions
35WHERE created_at IS NOT NULL
36GROUP BY 1, 2