Daily settlement activity

1-- core.klarna.daily_settlement_activity
2-- Sales, returns, fees and the rest of the settlement ledger, per day.
3-- Grain: day x klarna_account x merchant_id x currency_code.
4-- Currency: the transaction's own. Never sum across currency_code.
5-- Depends on: staging.klarna.transaction
6--
7-- WHICH DAY A TRANSACTION LANDS ON. activity_date is sale_date, the date Klarna
8-- records for the movement itself, falling back to capture_date. Both are UTC
9-- timestamps and are truncated in UTC; shift them first if you report in a local
10-- day. This is a transaction-date view: a sale captured on the 30th and paid out on
11-- the 3rd shows on the 30th here and on the 3rd in payout_reconciliation. Expect
12-- the two to disagree at any date boundary and to agree over a closed month of
13-- payouts.
14--
15-- ONLY SETTLED MONEY. Transactions are synced through payouts, so a sale
16-- appears here once it is in a payout, not when the customer checked out. The last
17-- few days are always incomplete. For order-time revenue use your shop platform.
18--
19-- Columns are positive amounts per type (ABS), plus net_settled_amount, the signed
20-- sum of every type whose direction Klarna's API makes unambiguous. See
21-- staging.klarna.transaction for the direction table.
22--
23-- Built on a day spine per account, merchant and currency, so a day with no
24-- settled activity returns zeros instead of vanishing from a line chart.
25
26WITH txn AS (
27    SELECT
28        *,
29        DATE(COALESCE(sale_at, capture_at)) AS activity_date
30    FROM {{staging.klarna.transaction}}
31),
32
33series AS (
34    SELECT
35        klarna_account,
36        merchant_id,
37        currency_code,
38        MIN(activity_date) AS first_date,
39        MAX(activity_date) AS last_date
40    FROM txn