Case resolution

1-- core.sprout_social.case_resolution
2-- One row per customer care case with its timings: how long it has been open, or
3-- how long it took to close. The case-level detail behind Sprout's case reporting.
4--
5-- Grain: one row per case.
6-- Depends on: staging.sprout_social.cases, .group, .message
7--
8-- hours_to_close uses the last close: a case that was reopened and closed again
9-- counts the full span. Open cases have NULL hours_to_close and a running
10-- hours_open. message_count only sees messages in the synced message table.
11
12WITH case_messages AS (
13    SELECT
14        case_id,
15        COUNT(*)              AS message_count,
16        COUNTIF(NOT is_sent)  AS received_messages,
17        COUNTIF(is_sent)      AS sent_messages
18    FROM {{staging.sprout_social.message}}
19    WHERE case_id IS NOT NULL
20    GROUP BY 1
21)
22
23SELECT
24    c.case_id,
25    g.customer_id,
26    c.group_id,
27    g.group_name,
28    c.queue_id,
29    c.case_type,
30    c.case_priority,
31    c.case_status,
32    c.case_status = 'CLOSED'                               AS is_closed,
33    c.created_at,
34    DATE(c.created_at)                                     AS created_date,
35    c.last_closed_at,
36    DATE(c.last_closed_at)                                 AS closed_date,
37    c.updated_at,
38    IF(c.case_status = 'CLOSED',
39       TIMESTAMP_DIFF(c.last_closed_at, c.created_at, MINUTE) / 60,
40       NULL)                                               AS hours_to_close,