Messaging daily

1-- core.twilio.messaging_daily
2-- Messaging volume, delivery and spend per day: the numbers on Twilio's Messaging
3-- Insights overview.
4--
5-- Grain: one row per day (UTC) x account x messaging service x direction x currency.
6-- Depends on: staging.twilio.message, .messaging_service
7--
8-- Day is the send time, or the creation time for messages that were never sent (queued,
9-- failed before sending, scheduled). `direction` is `inbound`, `outbound-api`,
10-- `outbound-reply` or `outbound-call`.
11--
12-- Delivery rate follows Messaging Insights: delivered over outbound messages that reached
13-- a final state (delivered, undelivered, failed, read). `sent` means Twilio handed the
14-- message to the carrier and never got a receipt back, which is common on some routes; it
15-- is counted separately and left out of the rate's denominator. The rate is per row:
16-- sum the counts and divide again when you aggregate.
17--
18-- Segments are what Twilio bills SMS on: a long message is several segments.
19--
20-- `messaging_service_name` is the service's current name, looked up after aggregation so
21-- the join cannot change any count. Messages sent straight from a number have no service.
22
23WITH daily AS (
24    SELECT
25        DATE(COALESCE(sent_at, created_at))                         AS date,
26        account_sid,
27        messaging_service_sid,
28        direction,
29        currency,
30
31        COUNT(*)                                                    AS messages,
32        SUM(segments)                                               AS segments,
33        COUNTIF(media_count > 0)                                    AS messages_with_media,
34
35        COUNTIF(status IN ('delivered', 'read'))                    AS delivered,
36        COUNTIF(status = 'read')                                    AS read,
37        COUNTIF(status = 'sent')                                    AS sent_no_receipt,
38        COUNTIF(status = 'undelivered')                             AS undelivered,
39        COUNTIF(status = 'failed')                                  AS failed,
40        COUNTIF(status IN ('received', 'receiving'))                AS received,