Your question is Reconcile Ledger and Bank Transactions. Start with the requirements and the two tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
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.
| Column | Type | Description |
|---|---|---|
| ledger_txn_idPK | INT | Primary key for the ledger record |
| account_id | INT | Account tied to the transaction |
| txn_date | DATE | Date the transaction was posted |
| reference_code | VARCHAR(20) | Reconciliation reference code |
| ledger_amount | NUMERIC(12,2) | Amount recorded in the ledger |
| status | VARCHAR(20) | Posting status such as posted or pending |
| Column | Type | Description |
|---|---|---|
| bank_txn_idPK | INT | Primary key for the bank record |
| account_id | INT | Account tied to the cleared transaction |
| cleared_date | DATE | Date the bank cleared the transaction |
| reference_code | VARCHAR(20) | Reference code used to match the ledger |
| bank_amount | NUMERIC(12,2) | Amount cleared by the bank |
| source_system | VARCHAR(20) | Feed source for the bank record |