Reconcile Payouts

Every night a payments platform pays merchants out to their bank accounts. Its own ledger says what it meant to pay; the bank's file says what actually moved. Finance reconciles the two each morning, and every line that does not match is a phone call, so the report must list exactly the discrepancies and nothing else.

Tables

ledger_payouts

• payout_id: the payout's id.
• merchant_id: the merchant being paid.
• amount_cents: the amount, in the currency's minor unit.
• currency: a three-letter code such as USD.

bank_transfers

• transfer_id: the bank's id for the transfer.
• payout_id: the payout reference the bank echoed back. It is NULL when the bank lost it, and no two transfers share one.
• amount_cents, currency: what the bank actually transferred.

Task

List every discrepancy between the two, with its kind in the issue column:

• missing_transfer: a ledger payout that no bank transfer refers to.
• unknown_transfer: a bank transfer whose payout_id is NULL or is not in the ledger.
• mismatch: a payout and its transfer whose amounts or currencies differ.

Payouts and transfers that match exactly are not listed.

Example

In the sample, po_1002 was sent with 89,000 cents instead of 89,900, and po_1003 was sent in USD instead of EUR: both are mismatches. po_1004 never reached the bank (missing_transfer), and tr_509 arrived without a reference (unknown_transfer, with an empty payout_id).