Daily balance activity

1-- core.paypal.daily_balance_activity
2-- Daily settlement summary: what moved the PayPal balance each day, by currency.
3-- Recreates the totals of PayPal's balance and activity reports.
4-- Grain: day (UTC) x currency. Only balance-affecting records are included.
5--
6-- The columns add up:
7--   payments_received + payments_sent + refunds + chargebacks_and_reversals
8--   + fees + currency_conversions + bank_deposits + bank_withdrawals
9--   + holds_and_releases + other = net_balance_change
10--
11-- Payment, refund and chargeback columns are GROSS. Every fee lands in `fees`:
12-- the fee charged on each record plus standalone fee records (T01). Fees are
13-- negative; fees returned on refunds are positive. Refunds, chargebacks, payments
14-- sent and withdrawals are negative.
15--
16-- Currencies are never mixed: each PayPal currency balance is its own row. Moving
17-- money between them shows up as currency_conversions, negative in one currency
18-- and positive in the other. Convert to a reporting currency downstream with your
19-- own rates if you need one total.
20
21SELECT
22    transaction_date,
23    currency,
24    SUM(IF(category = 'payment_received', COALESCE(gross_amount, 0), 0))        AS payments_received,
25    SUM(IF(category = 'payment_sent', COALESCE(gross_amount, 0), 0))            AS payments_sent,
26    SUM(IF(category = 'refund', COALESCE(gross_amount, 0), 0))                  AS refunds,
27    SUM(IF(category = 'chargeback_or_reversal', COALESCE(gross_amount, 0), 0))  AS chargebacks_and_reversals,
28    SUM(fee_amount) + SUM(IF(category = 'fee', COALESCE(gross_amount, 0), 0))   AS fees,
29    SUM(IF(category = 'currency_conversion', COALESCE(gross_amount, 0), 0))     AS currency_conversions,
30    SUM(IF(category = 'bank_deposit', COALESCE(gross_amount, 0), 0))            AS bank_deposits,
31    SUM(IF(category = 'bank_withdrawal', COALESCE(gross_amount, 0), 0))         AS bank_withdrawals,
32    SUM(IF(category = 'hold_or_release', COALESCE(gross_amount, 0), 0))         AS holds_and_releases,
33    SUM(IF(category = 'other', COALESCE(gross_amount, 0), 0))                   AS other,
34    SUM(net_amount)                                                AS net_balance_change,
35    COUNTIF(category = 'payment_received')                         AS payments_received_count,
36    COUNTIF(category = 'refund')                                   AS refunds_count,
37    COUNTIF(category = 'chargeback_or_reversal')                   AS chargebacks_and_reversals_count
38FROM {{core.paypal.transactions}}
39WHERE is_balance_affecting
40GROUP BY transaction_date, currency