B2c claims by product

1-- core.claimlane.b2c_claims_by_product
2-- Which products come back, why, and what they cost in refunds.
3-- Grain: claimlane_account x organization_id x currency x product_sku x issue_tag.
4-- All time, with a last-90-days column so a fixed problem does not stay on top.
5-- Depends on: core.claimlane.b2c_claim_lines
6--
7-- Keyed on SKU, falling back to product_id when a shop sends no SKU, because a
8-- product's name changes and its SKU usually does not. product_name is the most
9-- recent name seen for the key.
10--
11-- WHAT THIS CANNOT GIVE YOU: a return RATE. Claimlane only knows the products
12-- that were claimed, not the ones that were sold. Divide claimed_quantity by
13-- units sold from your shop platform, on the same SKU, to get one.
14--
15-- issue_tag values are configured per Claimlane account and kept as text.
16-- Lines without a reason are grouped under '(none)' rather than dropped.
17
18WITH lines AS (
19    SELECT
20        *,
21        COALESCE(product_sku, product_id, '(unknown product)') AS product_key
22    FROM {{core.claimlane.b2c_claim_lines}}
23)
24
25SELECT
26    claimlane_account,
27    organization_id,
28    currency,
29    product_key                                           AS product_sku,
30    ARRAY_AGG(product_name IGNORE NULLS ORDER BY created_at DESC LIMIT 1)[SAFE_OFFSET(0)]
31                                                          AS product_name,
32    COALESCE(issue_tag, '(none)')                         AS issue_tag,
33    COUNT(DISTINCT ticket_id)                             AS tickets,
34    SUM(claimed_quantity)                                 AS claimed_quantity,
35    SUM(claimed_value)                                    AS claimed_value,
36    SUM(refunded_quantity)                                AS refunded_quantity,
37    SUM(refunded_amount)                                  AS refunded_amount,
38    SAFE_DIVIDE(SUM(refunded_amount), SUM(claimed_value)) AS refund_rate_of_claimed_value,
39    SUM(IF(created_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY), claimed_quantity, 0))
40                                                          AS claimed_quantity_last_90_days,