Correspondents monthly

1-- core.gmail.correspondents_monthly
2-- Who the mailbox talks to, per month: messages received from and sent to each address.
3--
4-- Grain: one row per month x correspondent address.
5-- Depends on: core.gmail.messages
6--
7-- Received mail is counted against the sender. Sent mail is counted against every To and
8-- Cc address, so one message to three people adds one to each of them; `messages_sent`
9-- therefore sums to more than the number of messages sent. The mailbox's own address is
10-- left out.
11
12WITH received AS (
13    SELECT
14        DATE_TRUNC(date, MONTH)             AS month,
15        from_address                        AS correspondent,
16        1                                   AS is_received
17    FROM {{core.gmail.messages}}
18    WHERE direction = 'received'
19      AND from_address IS NOT NULL
20),
21
22sent AS (
23    SELECT
24        DATE_TRUNC(m.date, MONTH)           AS month,
25        recipient                           AS correspondent,
26        0                                   AS is_received
27    FROM {{core.gmail.messages}} m,
28         UNNEST(ARRAY_CONCAT(m.to_addresses, m.cc_addresses)) AS recipient
29    WHERE m.direction = 'sent'
30      AND recipient IS DISTINCT FROM m.mailbox_address
31)
32
33SELECT
34    month,
35    correspondent,
36    REGEXP_EXTRACT(correspondent, r'@(.+)$')    AS correspondent_domain,
37    COUNTIF(is_received = 1)                    AS messages_received,
38    COUNTIF(is_received = 0)                    AS messages_sent
39FROM (
40    SELECT * FROM received