Agent performance by month

1-- core.freshdesk.agent_performance_by_month
2-- Per agent per month: replies sent, tickets they gave the first response on and
3-- how fast, tickets currently assigned to them, and the CSAT they received.
4-- Grain: month x freshdesk_account x agent.
5-- Depends on: core.freshdesk.ticket_lifecycle, staging.freshdesk.ticket_conversation,
6--             staging.freshdesk.satisfaction_rating, staging.freshdesk.agent
7--
8-- Each column is dated on its own event and attributed to the agent who did it:
9--   replies_sent          the reply's date, the agent who sent it
10--   first_responses       the first reply's date, the agent who sent it
11--   tickets_assigned      the ticket's creation date, its CURRENT assignee
12--                         (no assignment history is synced)
13--   csat_*                the survey answer's date, the agent on the response
14-- So never divide one column by another from a different line above and call it
15-- a rate. Medians do not roll up across months.
16
17WITH tz AS (
18    SELECT DISTINCT freshdesk_account, reporting_timezone
19    FROM {{staging.freshdesk.ticket}}
20),
21
22replies AS (
23    SELECT
24        c.freshdesk_account,
25        c.user_id                                       AS agent_id,
26        DATE_TRUNC(DATE(c.created_at, tz.reporting_timezone), MONTH) AS month,
27        COUNT(*)                                        AS replies_sent
28    FROM {{staging.freshdesk.ticket_conversation}} c
29    JOIN tz ON tz.freshdesk_account = c.freshdesk_account
30    JOIN {{staging.freshdesk.agent}} a
31      ON a.freshdesk_account = c.freshdesk_account
32     AND a.agent_id          = c.user_id
33    WHERE NOT COALESCE(c.is_incoming, FALSE)
34      AND NOT COALESCE(c.is_private, FALSE)
35      AND c.source_code IS DISTINCT FROM 2
36      AND NOT c.is_auto_response
37    GROUP BY 1, 2, 3
38),
39
40first_responses AS (