Balance reconciliation

1-- core.paypal.balance_reconciliation
2-- Ties the transaction history to the balance PayPal reports today.
3-- Grain: PayPal account x currency.
4--
5-- PayPal's transaction API only goes back three years, so the synced history
6-- starts part-way through the account's life. implied_opening_balance is the
7-- balance the account must have held just before the first synced transaction for
8-- today's balance to be right:
9--   implied_opening_balance = total_balance - sum of all balance-affecting net_amount
10-- Use it as the opening figure when building a running balance from
11-- daily_balance_activity. A negative or implausible value means records are
12-- missing or misclassified.
13--
14-- Amounts in major units of each currency. The balance snapshot is as of the last
15-- sync; transactions can lag PayPal by up to a few hours.
16
17WITH activity AS (
18    SELECT
19        currency,
20        MIN(transaction_date)    AS first_transaction_date,
21        MAX(transaction_date)    AS last_transaction_date,
22        SUM(net_amount)          AS net_change_since_first_transaction
23    FROM {{core.paypal.transactions}}
24    WHERE is_balance_affecting
25    GROUP BY currency
26)
27
28SELECT
29    b.paypal_account_id,
30    COALESCE(b.currency, a.currency)                  AS currency,
31    b.is_primary_currency,
32    b.as_of_at                                        AS balance_as_of,
33    b.total_balance,
34    b.available_balance,
35    b.withheld_balance,
36    a.first_transaction_date,
37    a.last_transaction_date,
38    a.net_change_since_first_transaction,
39    b.total_balance - COALESCE(a.net_change_since_first_transaction, 0) AS implied_opening_balance
40FROM {{staging.paypal.balance}} b