B2b claims by month

1-- core.claimlane.b2b_claims_by_month
2-- Retailer claims per brand and claim type per month: volume, the supplier's
3-- decisions, and the acceptance rate.
4-- Grain: month x claimlane_account x brand_name x claim_type.
5-- Depends on: core.claimlane.b2b_claim_lines
6--
7-- Counted in claim LINES and units, because decisions are per line. claims
8-- counts distinct claims for context.
9--
10-- Lines land in the month the claim was created, whatever their decision date,
11-- so recent months always carry more open lines than old ones. That is backlog,
12-- not a drop in acceptance.
13--
14-- acceptance_rate is accepted lines over DECIDED lines (accepted + declined).
15-- Open lines are left out of the denominator; counting them as not-accepted
16-- makes every recent month look like a collapse. Lines with status 'other' are
17-- left out too, because Claimlane does not say which way they went.
18--
19-- Built on a month spine per brand and claim type, from the first month the
20-- combination appears to the current month.
21
22WITH lines AS (
23    SELECT
24        *,
25        DATE_TRUNC(created_date, MONTH)         AS month,
26        COALESCE(brand_name, '(no brand)')      AS brand_label,
27        COALESCE(claim_type, '(none)')          AS claim_type_label
28    FROM {{core.claimlane.b2b_claim_lines}}
29    WHERE created_date IS NOT NULL
30),
31
32series AS (
33    SELECT
34        claimlane_account,
35        brand_label,
36        claim_type_label,
37        MIN(month) AS first_month,
38        MAX(month) AS last_month
39    FROM lines
40    GROUP BY 1, 2, 3