Task
You are given ledger entries and bank transactions for a monthly financial model. Write a PostgreSQL query that reconciles the two sources by account and month, returning only accounts where the booked amount and bank amount do not match. Include the booked total, bank total, and the variance for each mismatched account-month pair.
Schema
| Table | Column | Type | Description |
|---|
ledger_entries | entry_id | INT | Primary key for each ledger row |
ledger_entries | account_code | VARCHAR(20) | Financial account code |
ledger_entries | entry_date | DATE | Posting date |
ledger_entries | amount | DECIMAL(12,2) | Signed ledger amount |
ledger_entries | source_system | VARCHAR(50) | Origin of the entry |
bank_transactions | transaction_id | INT | Primary key for each bank row |
bank_transactions | account_code | VARCHAR(20) | Financial account code |
bank_transactions | transaction_date | DATE | Bank transaction date |
bank_transactions | amount | DECIMAL(12,2) | Signed bank amount |
bank_transactions | status | VARCHAR(20) | Transaction status |
Sample data
| ledger_entries.entry_id | account_code | entry_date | amount | source_system |
|---|
| 1 | 4000 | 2024-01-03 | 1200.00 | ERP |
| 2 | 4000 | 2024-01-18 | -200.00 | ERP |
| 3 | 5000 | 2024-01-05 | 800.00 | ERP |
| 4 | 5000 | 2024-02-02 | 300.00 | ERP |
| 5 | 6000 | 2024-02-10 | 450.00 | ERP |
| 6 | 7000 | 2024-02-12 | 100.00 | ERP |
| 7 | 8000 | 2024-01-20 | 50.00 | ERP |
| 8 | 9000 | 2024-02-28 | 75.00 | ERP |
| bank_transactions.transaction_id | account_code | transaction_date | amount | status |
|---|
| 101 | 4000 | 2024-01-04 | 1000.00 | posted |
| 102 | 4000 | 2024-01-19 | NULL | pending |
| 103 | 5000 | 2024-01-06 | 800.00 | posted |
| 104 | 5000 | 2024-02-03 | 250.00 | posted |
| 105 | 6000 | 2024-02-11 | 450.00 | posted |
| 106 | 7000 | 2024-02-13 | 100.00 | posted |
| 107 | 8000 | 2024-01-21 | 0.00 | posted |
| 108 | 9100 | 2024-02-28 | 75.00 | posted |
Expected output
| account_code | month | booked_total | bank_total | variance |
|---|
| 4000 | 2024-01 | 1000.00 | 1000.00 | 0.00 |
| 5000 | 2024-02 | 300.00 | 250.00 | 50.00 |
| 8000 | 2024-01 | 50.00 | 0.00 | 50.00 |
| 9000 | 2024-02 | 75.00 | 0.00 | 75.00 |
