Task
You are given a ledger of posted transactions and a bank feed of cleared transactions. Write a PostgreSQL query that helps you reconcile the two sources by returning every ledger transaction that is either missing from the bank feed or has a different cleared amount. Include the ledger amount, bank amount, and a discrepancy status so you can investigate exceptions quickly.
Schema
| Table | Column | Type | Description |
|---|
ledger_transactions | ledger_txn_id | INT | Primary key for the ledger record |
ledger_transactions | account_id | INT | Account tied to the transaction |
ledger_transactions | txn_date | DATE | Date the transaction was posted |
ledger_transactions | reference_code | VARCHAR(20) | Reconciliation reference code |
ledger_transactions | ledger_amount | NUMERIC(12,2) | Amount recorded in the ledger |
ledger_transactions | status | VARCHAR(20) | Posting status such as posted or pending |
bank_transactions | bank_txn_id | INT | Primary key for the bank record |
bank_transactions | account_id | INT | Account tied to the cleared transaction |
bank_transactions | cleared_date | DATE | Date the bank cleared the transaction |
bank_transactions | reference_code | VARCHAR(20) | Reference code used to match the ledger |
bank_transactions | bank_amount | NUMERIC(12,2) | Amount cleared by the bank |
bank_transactions | source_system | VARCHAR(20) | Feed source for the bank record |
Sample data
ledger_transactions
| ledger_txn_id | account_id | txn_date | reference_code | ledger_amount | status |
|---|
| 1 | 101 | 2024-04-01 | UM-1001 | 250.00 | posted |
| 2 | 101 | 2024-04-01 | UM-1002 | 125.00 | posted |
| 3 | 102 | 2024-04-02 | UM-1003 | 80.00 | posted |
| 4 | 103 | 2024-04-02 | UM-1004 | 410.00 | pending |
| 5 | 101 | 2024-04-03 | UM-1005 | 60.00 | posted |
| 6 | 104 | 2024-04-03 | UM-1006 | 0.00 | posted |
| 7 | 105 | 2024-04-04 | UM-1007 | 300.00 | posted |
| 8 | 102 | 2024-04-04 | UM-1008 | 175.00 | posted |
bank_transactions
| bank_txn_id | account_id | cleared_date | reference_code | bank_amount | source_system |
|---|
| 11 | 101 | 2024-04-01 | UM-1001 | 250.00 | fedwire |
| 12 | 101 | 2024-04-01 | UM-1002 | 120.00 | fedwire |
| 13 | 102 | 2024-04-02 | UM-1003 | 80.00 | ach |
| 14 | 103 | 2024-04-02 | UM-9999 | 410.00 | ach |
| 15 | 101 | 2024-04-03 | UM-1005 | 60.00 | fedwire |
| 16 | 104 | 2024-04-03 | UM-1006 | 5.00 | ach |
| 17 | 106 | 2024-04-04 | UM-1007 | 300.00 | fedwire |
| 18 | 102 | 2024-04-04 | UM-1008 | 175.00 | ach |
Expected output
| ledger_txn_id | account_id | reference_code | ledger_amount | bank_amount | discrepancy_status |
|---|
| 2 | 101 | UM-1002 | 125.00 | 120.00 | amount_mismatch |
| 4 | 103 | UM-1004 | 410.00 | NULL | missing_in_bank |
| 6 | 104 | UM-1006 | 0.00 | 5.00 | amount_mismatch |
| 7 | 105 | UM-1007 | 300.00 | NULL | missing_in_bank |
