Task
You are given source records, converted records, and defect logs from a data conversion process tracked in Accenture myWizard. Write a PostgreSQL query that returns one row per conversion batch for batches completed in March 2024. For each batch, show the total source records, successfully converted records, records with data mismatches, open defects, and a batch status of Resolved or Needs Attention. A batch should be Needs Attention if it has at least one open defect or at least one mismatched record; otherwise mark it Resolved.
Schema
conversion_batches
| column | type | description |
|---|
| batch_id | INT | Batch identifier |
| batch_name | VARCHAR(100) | Conversion batch name |
| completed_date | DATE | Date the batch finished |
| | |
source_records
| column | type | description |
|---|
| source_id | INT | Source record identifier |
| batch_id | INT | Related batch |
| source_amount | NUMERIC(10,2) | Amount in source system |
| | |
converted_records
| column | type | description |
|---|
| converted_id | INT | Converted record identifier |
| source_id | INT | Related source record |
| converted_amount | NUMERIC(10,2) | Amount after conversion |
| conversion_status | VARCHAR(20) | Conversion result |
| | |
defect_log
| column | type | description |
|---|
| defect_id | INT | Defect identifier |
| batch_id | INT | Related batch |
| defect_type | VARCHAR(30) | Defect category |
| defect_status | VARCHAR(20) | Current defect status |
| | |
Sample data
Representative rows include batch Claims Wave 1, source record 102 with amount 250.00, converted amount 245.00, and an Open accuracy defect on the same batch.
Expected output
| batch_id | batch_name | total_source_records | converted_success_records | mismatched_records | open_defects | batch_status |
|---|
| 2 | Billing Wave 1 | 3 | 2 | 0 | 1 | Needs Attention |
| 1 | Claims Wave 1 | 3 | 2 | 1 | 1 | Needs Attention |
| 4 | Provider Wave 1 | 2 | 2 | 0 | 0 | Resolved |
