You are given two monthly financial reports that should align by report_date, account_id, and metric_name, but some rows do not match. Write a PostgreSQL query that compares the reports and returns every row where the records are missing on one side or the amounts differ.
For each discrepancy, return the key fields, the amount from each report, and a reconciliation_status showing whether the row is missing_in_report_a, missing_in_report_b, or amount_mismatch.
| table | column | type | description |
|---|---|---|---|
| report_a | report_date | DATE | Reporting date |
| report_a | account_id | INT | Account identifier |
| report_a | metric_name | VARCHAR(50) | Financial metric name |
| report_a | amount | NUMERIC(12,2) | Amount from report A |
| report_b | report_date | DATE | Reporting date |
| report_b | account_id | INT | Account identifier |
| report_b | metric_name | VARCHAR(50) | Financial metric name |
| report_b | amount | NUMERIC(12,2) | Amount from report B |
report_a
| report_date | account_id | metric_name | amount |
|---|---|---|---|
| 2024-01-31 | 101 | Revenue | 12000.00 |
| 2024-01-31 | 102 | Revenue | 8500.00 |
| 2024-01-31 | 103 | Revenue | 6400.00 |
| 2024-01-31 | 101 | Refunds | 300.00 |
| 2024-01-31 | 104 | Revenue | 4100.00 |
| 2024-02-29 | 101 | Revenue | 13000.00 |
report_b
| report_date | account_id | metric_name | amount |
|---|---|---|---|
| 2024-01-31 | 101 | Revenue | 12000.00 |
| 2024-01-31 | 102 | Revenue | 8600.00 |
| 2024-01-31 | 103 | Revenue | 6400.00 |
| 2024-01-31 | 105 | Revenue | 2200.00 |
| 2024-01-31 | 101 | Refunds | 250.00 |
| 2024-02-29 | 101 | Revenue | 13000.00 |
| report_date | account_id | metric_name | amount_a | amount_b | reconciliation_status |
|---|---|---|---|---|---|
| 2024-01-31 | 101 | Refunds | 300.00 | 250.00 | amount_mismatch |
| 2024-01-31 | 102 | Revenue | 8500.00 | 8600.00 | amount_mismatch |
| 2024-01-31 | 104 | Revenue | 4100.00 | NULL | missing_in_report_b |
| 2024-01-31 | 105 | Revenue | NULL | 2200.00 | missing_in_report_a |
