Your question is Track Conversion Defects by Batch. Start with the requirements and the four tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
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.
| Column | Type | Description |
|---|---|---|
| batch_idPK | INT | Primary key for the conversion batch |
| batch_name | VARCHAR(100) | Human-readable batch name |
| completed_date | DATE | Date the conversion batch was completed |
| Column | Type | Description |
|---|---|---|
| source_idPK | INT | Primary key for the source record |
| batch_id | INT | Batch to which the source record belongs |
| source_amount | NUMERIC(10,2) | Amount in the source system |
| Column | Type | Description |
|---|---|---|
| converted_idPK | INT | Primary key for the converted record |
| source_id | INT | Source record that was converted |
| converted_amount | NUMERIC(10,2) | Amount after conversion |
| conversion_status | VARCHAR(20) | Status of the conversion attempt |
| Column | Type | Description |
|---|---|---|
| defect_idPK | INT | Primary key for the defect |
| batch_id | INT | Batch associated with the defect |
| defect_type | VARCHAR(30) | Type of defect found during validation |
| defect_status | VARCHAR(20) | Current status of the defect |