Calls daily

1-- core.twilio.calls_daily
2-- Voice volume, outcomes, talk time and spend per day: the summary in Twilio's Voice
3-- Insights and Call Logs.
4--
5-- Grain: one row per day (UTC, call start) x account x direction x currency.
6-- Depends on: staging.twilio.call
7--
8-- Every call leg is a row in staging, and both legs of a forwarded call are billed, so
9-- `call_legs` and `cost` count both. `parent_calls` counts only legs without a parent,
10-- which is the closer match to "conversations". `direction` is `inbound`, `outbound-api`
11-- or `outbound-dial` (the child leg of a <Dial>).
12--
13-- `billable_minutes` rounds each completed leg up to the next minute, which is how Twilio
14-- bills most voice usage. SIP and some international routes are billed per second, so
15-- treat it as an estimate and use `cost` for money.
16-- Answer rate is completed legs over all legs; the per-row rates should be re-derived
17-- from summed counts when you aggregate.
18
19SELECT
20    DATE(started_at)                                            AS date,
21    account_sid,
22    direction,
23    currency,
24
25    COUNT(*)                                                    AS call_legs,
26    COUNTIF(parent_call_sid IS NULL)                            AS parent_calls,
27    COUNTIF(status = 'completed')                               AS completed,
28    COUNTIF(status = 'busy')                                    AS busy,
29    COUNTIF(status = 'no-answer')                               AS no_answer,
30    COUNTIF(status = 'failed')                                  AS failed,
31    COUNTIF(status = 'canceled')                                AS canceled,
32    COUNTIF(answered_by LIKE 'machine%')                        AS answered_by_machine,
33    SAFE_DIVIDE(COUNTIF(status = 'completed'), COUNT(*))        AS answer_rate,
34
35    SUM(duration_seconds)                                       AS talk_seconds,
36    SUM(duration_seconds) / 60                                  AS talk_minutes,
37    SUM(IF(status = 'completed', CEIL(duration_seconds / 60), 0)) AS billable_minutes,
38    SAFE_DIVIDE(SUM(IF(status = 'completed', duration_seconds, 0)),
39                COUNTIF(status = 'completed'))                  AS avg_completed_call_seconds,
40