B2c claims by month

1-- core.claimlane.b2c_claims_by_month
2-- Consumer claim volume and refunds per month: how many tickets, of which type,
3-- how they were settled, and how much money went back.
4-- Grain: month x claimlane_account x organization_id x ticket_type x currency.
5-- Currency: the ticket's. Never sum amounts across currency.
6-- Depends on: staging.claimlane.b2c_ticket, core.claimlane.b2c_claim_lines
7--
8-- Tickets land in the month they were CREATED. There is no resolution date in
9-- the synced data, so refunds are reported against the month the claim came in,
10-- not the month the money left; a March claim refunded in April counts in March.
11--
12-- TICKETS ARE COUNTED FROM THE TICKET TABLE, amounts from the line model. A
13-- ticket with three products is one ticket here, not three, and a ticket whose
14-- products did not sync still counts as a ticket.
15--
16-- refund_rate_of_claimed_value is a ratio of the month's sums, so a large order
17-- weighs what it is worth. It is below 1 when claims end in exchanges, store
18-- credit, partial refunds or rejection.
19--
20-- Built on a month spine per organisation, ticket type and currency, from the
21-- first month the combination appears to the current month.
22
23WITH tickets AS (
24    SELECT
25        claimlane_account,
26        organization_id,
27        COALESCE(ticket_type, '(none)')                  AS ticket_type,
28        currency,
29        DATE_TRUNC(DATE(created_at), MONTH)              AS month,
30        COUNT(*)                                         AS tickets,
31        COUNTIF(return_type = 'refund')                  AS refund_tickets,
32        COUNTIF(return_type = 'exchange')                AS exchange_tickets,
33        COUNTIF(return_type = 'storeCredit')             AS store_credit_tickets,
34        COUNTIF(return_type = 'keepProduct')             AS keep_product_tickets,
35        COUNTIF(return_automation_status = 'automated')  AS automated_tickets,
36        COUNTIF(entire_order_returned)                   AS entire_order_tickets
37    FROM {{staging.claimlane.b2c_ticket}}
38    WHERE created_at IS NOT NULL
39    GROUP BY 1, 2, 3, 4, 5
40),