Task
You are given a single reconciliation table that contains balance snapshots from two sources: ERP and a planning tool. Write a PostgreSQL query that returns each account with its ERP balance, planning balance, and the variance between them, then sort the accounts by the largest absolute variance. Treat missing source values as zero when calculating variance.
Schema
| Column | Type | Description |
|---|
| account_id | INT | Account identifier |
| account_name | VARCHAR(100) | Account name |
| source_system | VARCHAR(20) | Source of the balance, either ERP or Planning |
| as_of_date | DATE | Snapshot date |
| balance_amount | DECIMAL(12,2) | Reported balance for that source |
Sample data
| account_id | account_name | source_system | as_of_date | balance_amount |
|---|
| 101 | Cash | ERP | 2024-03-31 | 125000.00 |
| 101 | Cash | Planning | 2024-03-31 | 124500.00 |
| 102 | Accounts Receivable | ERP | 2024-03-31 | 86000.00 |
| 102 | Accounts Receivable | Planning | 2024-03-31 | 86000.00 |
| 103 | Inventory | ERP | 2024-03-31 | 54000.00 |
| 103 | Inventory | Planning | 2024-03-31 | 57500.00 |
| 104 | Prepaids | ERP | 2024-03-31 | 12000.00 |
| 104 | Prepaids | Planning | 2024-03-31 | NULL |
Expected output
| account_id | account_name | erp_balance | planning_balance | variance |
|---|
| 103 | Inventory | 54000.00 | 57500.00 | -3500.00 |
| 101 | Cash | 125000.00 | 124500.00 | 500.00 |
| 104 | Prepaids | 12000.00 | 0.00 | 12000.00 |
| 102 | Accounts Receivable | 86000.00 | 86000.00 | 0.00 |
