Category stats daily

1-- core.sendgrid.category_stats_daily
2-- SendGrid's Category Stats, rebuilt: the same funnel as email_stats_daily, split by the
3-- category labels you attach when sending (for example "welcome", "receipt",
4-- "newsletter-2026-10").
5--
6-- Grain: one row per day x category.
7-- Depends on: staging.sendgrid.legacy_category_stat
8--
9-- Categories do not add up to the account total. A message can carry several categories
10-- and is counted under each one, and a message with none is in no row here. Use
11-- email_stats_daily for account totals.
12--
13-- Rates are per row, with the same definitions as email_stats_daily. Re-derive them from
14-- summed counts when you aggregate.
15
16SELECT
17    date,
18    category,
19    requests,
20    processed,
21    delivered,
22    deferred,
23    bounces,
24    blocks,
25    invalid_emails,
26    COALESCE(bounce_drops, 0) + COALESCE(spam_report_drops, 0)
27        + COALESCE(unsubscribe_drops, 0)                        AS total_drops,
28    opens,
29    unique_opens,
30    clicks,
31    unique_clicks,
32    spam_reports,
33    unsubscribes,
34
35    SAFE_DIVIDE(delivered, requests)                            AS delivery_rate,
36    SAFE_DIVIDE(bounces, requests)                              AS bounce_rate,
37    SAFE_DIVIDE(unique_opens, delivered)                        AS unique_open_rate,
38    SAFE_DIVIDE(unique_clicks, delivered)                       AS unique_click_rate,
39    SAFE_DIVIDE(spam_reports, delivered)                        AS spam_report_rate,
40    SAFE_DIVIDE(unsubscribes, delivered)                        AS unsubscribe_rate