First response by month

1-- core.freshdesk.first_response_by_month
2-- First response time (mean and median, calendar minutes) and first-response SLA
3-- attainment for the tickets created in each month: the "First response time"
4-- and "SLA" panels of Freshdesk's Helpdesk In-depth report.
5-- Grain: created month x freshdesk_account x group x channel.
6-- Depends on: core.freshdesk.ticket_lifecycle
7--
8-- POPULATION: inbound tickets created in the month. Outbound email tickets are
9-- left out, because an agent wrote them and there is nothing to respond to.
10-- Tickets created this month that have not been answered yet count in
11-- tickets_created and not in tickets_responded, so the current month's
12-- response rate rises as replies come in.
13--
14-- MEDIANS do not add up across groups or channels. Sum the _sum_ and count
15-- columns for a mean at any level; query ticket_lifecycle for a median at a
16-- coarser grain. Medians use APPROX_QUANTILES.
17
18SELECT
19    DATE_TRUNC(created_date, MONTH)                           AS month,
20    freshdesk_account,
21    group_id,
22    ANY_VALUE(group_name)                                     AS group_name,
23    channel,
24
25    COUNT(*)                                                  AS tickets_created,
26    COUNTIF(first_response_at IS NOT NULL)                    AS tickets_responded,
27    COUNTIF(is_resolved)                                      AS resolved_now,
28    COUNTIF(NOT is_resolved)                                  AS unresolved_now,
29
30    SUM(first_response_minutes)                               AS first_response_minutes_sum,
31    AVG(first_response_minutes)                               AS first_response_minutes_avg,
32    APPROX_QUANTILES(first_response_minutes, 2)[SAFE_OFFSET(1)] AS first_response_minutes_median,
33    APPROX_QUANTILES(first_response_minutes, 10)[SAFE_OFFSET(9)] AS first_response_minutes_p90,
34
35    COUNTIF(is_first_response_within_sla)                     AS first_response_within_sla,
36    COUNTIF(is_first_response_within_sla IS NOT NULL)         AS first_response_sla_measured,
37    SAFE_DIVIDE(COUNTIF(is_first_response_within_sla),
38                COUNTIF(is_first_response_within_sla IS NOT NULL)) AS first_response_sla_rate,
39
40    SUM(agent_replies)                                        AS agent_replies,