Task
You are given monthly financial reporting data from Sunshine Health. Write a PostgreSQL query that validates each posted report month by comparing the reported total in financial_reports with the sum of underlying transactions in gl_entries. Return one row per report for January 2024, including the report month, business unit, reported amount, calculated ledger amount, variance, and a validation status of MATCH, MISMATCH, or MISSING_LEDGER_DATA. Treat only POSTED reports as in scope, and only include ledger rows whose account maps to the Revenue category in account_map.
Schema
| Column | Type | Description |
|---|
| financial_reports.report_id | INT | Primary key for the posted report |
| financial_reports.report_month | DATE | First day of the reporting month |
| financial_reports.business_unit | VARCHAR(50) | Sunshine Health business unit |
| financial_reports.reported_revenue | DECIMAL(12,2) | Revenue amount shown in the report |
| financial_reports.status | VARCHAR(20) | Report status |
| gl_entries.entry_id | INT | Primary key for ledger entry |
| gl_entries.posted_date | DATE | Ledger posting date |
| gl_entries.business_unit | VARCHAR(50) | Business unit on the ledger entry |
| gl_entries.account_code | VARCHAR(20) | General ledger account |
| gl_entries.amount | DECIMAL(12,2) | Signed ledger amount |
| account_map.account_code | VARCHAR(20) | General ledger account |
| account_map.account_category | VARCHAR(30) | Reporting category for the account |
Sample data
| report_id | report_month | business_unit | reported_revenue | status |
|---|
| 101 | 2024-01-01 | Medicare | 1500.00 | POSTED |
| 102 | 2024-01-01 | Marketplace | 1200.00 | POSTED |
| 103 | 2024-01-01 | Medicaid | 900.00 | POSTED |
Expected output
| report_month | business_unit | reported_revenue | calculated_revenue | variance | validation_status |
|---|
| 2024-01-01 | Marketplace | 1200.00 | 1100.00 | 100.00 | MISMATCH |
| 2024-01-01 | Medicaid | 900.00 | 900.00 | 0.00 | MATCH |
| 2024-01-01 | Medicare | 1500.00 | 1500.00 | 0.00 | MATCH |
| 2024-01-01 | Specialty | 700.00 | 0.00 | 700.00 | MISSING_LEDGER_DATA |
