Fees by type

1-- core.klarna.fees_by_type
2-- What Klarna charged, broken down by fee type, per month, with the effective
3-- rate against that month's sales.
4-- Grain: month x klarna_account x merchant_id x currency_code x transaction_type
5--        x detailed_type.
6-- Currency: the transaction's own. Never sum across currency_code.
7-- Depends on: staging.klarna.transaction
8--
9-- Covers the cost side of the ledger: FEE, TAX (the VAT Klarna charges on its
10-- fees), FEE_REFUND and CHARGE. detailed_type is what makes this useful: it splits
11-- FEE into the percentage purchase fee, the fixed per-order fee, return fees,
12-- extra invoice fees and so on, which is how a merchant checks the bill against
13-- the contracted rate.
14--
15-- fee_rate_of_sales divides this row's cost by ALL of the month's sales for the
16-- same account, merchant and currency: a ratio of sums, so summing fee_amount over
17-- a month and dividing by monthly_sales gives the same answer as summing the
18-- rates. Do not average fee_rate_of_sales across rows; add them.
19--
20-- Month follows the same date rule as daily_settlement_activity: sale_date,
21-- falling back to capture_date, in UTC.
22
23WITH txn AS (
24    SELECT
25        *,
26        DATE_TRUNC(DATE(COALESCE(sale_at, capture_at)), MONTH) AS activity_month
27    FROM {{staging.klarna.transaction}}
28    WHERE COALESCE(sale_at, capture_at) IS NOT NULL
29),
30
31monthly_sales AS (
32    SELECT
33        klarna_account,
34        merchant_id,
35        currency_code,
36        activity_month,
37        SUM(ABS(amount)) AS monthly_sales
38    FROM txn
39    WHERE transaction_type = 'SALE'
40    GROUP BY 1, 2, 3, 4