Response times

1-- core.gmail.response_times
2-- How long received email waits for a reply from this mailbox.
3--
4-- Grain: one row per received message that opens a wait: the first received message in
5-- a thread after the start or after the mailbox's last reply. Follow-ups that arrive
6-- before you answer belong to the same wait and are not counted again.
7-- Depends on: core.gmail.messages
8--
9-- `replied_at` is the first message sent from this mailbox in the same thread after the
10-- wait opened. A wait with no reply yet has `replied_at` NULL and stays open, so averages
11-- should be taken over `is_replied` rows, and the open rows read as the backlog.
12--
13-- The clock is wall time, not business hours: a message received on Friday evening and
14-- answered on Monday morning shows the whole weekend. Mail from automated senders
15-- (newsletters, notifications) inflates the unreplied count; filter on `inbox_category`
16-- or `counterpart_domain` to restrict to the conversations you care about.
17
18WITH ordered AS (
19    SELECT
20        message_id,
21        thread_id,
22        sent_or_received_at,
23        direction,
24        counterpart,
25        counterpart_domain,
26        subject,
27        inbox_category,
28        LAG(direction) OVER (
29            PARTITION BY thread_id ORDER BY sent_or_received_at, message_id) AS previous_direction,
30        MIN(IF(direction = 'sent', sent_or_received_at, NULL)) OVER (
31            PARTITION BY thread_id ORDER BY sent_or_received_at, message_id
32            ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)                AS next_sent_at
33    FROM {{core.gmail.messages}}
34)
35
36SELECT
37    message_id,
38    thread_id,
39    sent_or_received_at                                             AS received_at,
40    DATE(sent_or_received_at)                                       AS received_date,