lesson 56 Advanced product analysis · advanced

Revenue & payment reconciliation

Reconcile revenue with payments and identify differences.

the idea

Reconciliation compares two records of the same money. Bring each side to the same grain (one row per settlement), join them, and classify every row: matched, missing on one side, or different amounts. Unmatched rows are findings, not errors to filter away.

Dataset: Finance / Payments

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Summarize March ledger activity. Run this. Posted card purchases and other types in March 2025—the bank side of reconciliation.the pattern

   example.sql Run

   sql›

   SELECT
     transaction_type,
     status,
     COUNT(*) AS entries,
     SUM(amount_cents) AS cents
   FROM transactions
   WHERE posted_at >= '2025-03-01' AND posted_at < '2025-04-01'
   GROUP BY transaction_type, status;

2. 2

   Line up settlement and posting. Run this. Each settlement ties to card_purchase rows through authorization_id so you can compare amounts row by row.

   step-2.sql Run

   sql›

   SELECT
     s.settlement_id,
     s.amount_cents,
     t.transaction_id,
     t.amount_cents AS ledger_cents
   FROM settlements s
   LEFT JOIN transactions t
     ON t.authorization_id = s.authorization_id
     AND t.transaction_type = 'card_purchase'
   WHERE s.settlement_id <= 5;

3. 3

   Add partial refunds per charge. Run this. Several refund rows can share one settlement_id—sum them before you net against settled cents.

   step-3.sql Run

   sql›

   SELECT
     settlement_id,
     COUNT(*) AS refunds,
     SUM(amount_cents) AS refunded_cents
   FROM refunds
   WHERE settlement_id <= 300
   GROUP BY settlement_id;

4. 4

   Compare month totals. Run this. settled_cents from the card processor should line up with negated posted card_purchase cents when nothing is missing.

   step-4.sql Run

   sql›

   SELECT
     (
       SELECT SUM(amount_cents)
       FROM settlements
       WHERE settled_at >= '2025-02-01' AND settled_at < '2025-03-01'
     ) AS settled_cents,
     (
       SELECT -SUM(amount_cents)
       FROM transactions
       WHERE transaction_type = 'card_purchase'
         AND status = 'posted'
         AND posted_at >= '2025-02-01'
         AND posted_at < '2025-03-01'
     ) AS ledger_cents;

common mistake

An inner join between settlements and ledger postings silently drops the settlements that never posted, which are exactly the problems to find.