Ticket volume by day

1-- core.zendesk.ticket_volume_by_day
2-- Tickets created, solved and left unsolved per day: Zendesk's "Tickets" tab.
3-- Grain: day x zendesk_account x group x channel.
4-- Depends on: core.zendesk.ticket_lifecycle
5--
6-- Built on a day spine, so a group or channel with a quiet day still returns a
7-- row of zeros and its backlog line keeps going instead of disappearing.
8--
9-- BACKLOG is every ticket created on or before the day that had not been
10-- resolved by the end of it: running created minus running solved. Two limits,
11-- both because the connector syncs a ticket's current state and not its history:
12--   * A ticket is counted in the group and channel it has NOW. Reassigning a
13--     ticket moves its whole history to the new group.
14--   * Only the latest solve is known. A ticket solved in March, reopened in May
15--     and solved again in June counts as unsolved from creation to June.
16-- The last day of the series ties exactly to Zendesk's current unsolved count
17-- (see dbt/tests/assert_zendesk__backlog_ties_to_unsolved.sql); earlier days are a
18-- reconstruction. For an exact history, materialise this model daily and keep
19-- the runs.
20--
21-- tickets_solved counts tickets by their latest solve date. Explore's "Solved
22-- tickets" counts solve events, so a ticket solved twice counts twice there.
23
24WITH tickets AS (
25    SELECT
26        zendesk_account,
27        IFNULL(group_id, '')  AS group_key,
28        group_id,
29        group_name,
30        channel,
31        created_date,
32        resolved_date
33    FROM {{core.zendesk.ticket_lifecycle}}
34    WHERE created_date IS NOT NULL
35),
36
37day_spine AS (
38    SELECT day
39    FROM UNNEST(GENERATE_DATE_ARRAY(
40        (SELECT MIN(created_date) FROM tickets),