Your question is Cleaning a Dataset. Start with the requirements and the two 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.
How would you approach cleaning a dataset?
Using the provided shift and facility tables, write a query that returns valid, standardized, deduplicated shift records. Exclude incomplete, invalid, unmatched, or negative-pay records, and retain the most recently updated duplicate.
cleaned_shift_id, caregiver_name, facility_name, shift_start, shift_end, status, and pay_rate.shift_start, then cleaned_shift_id.| Column | Type | Description |
|---|---|---|
| record_idPK | INT | Unique source record identifier |
| caregiver_name | VARCHAR(150) | Caregiver name as received from the source system |
| facility_id | INT | Referenced facility identifier |
| shift_start | TIMESTAMP | Shift start timestamp |
| shift_end | TIMESTAMP | Shift end timestamp |
| status | VARCHAR(30) | Raw shift status |
| pay_rate | NUMERIC(8,2) | Hourly pay rate |
| updated_at | TIMESTAMP | Source record last-updated timestamp |
| Column | Type | Description |
|---|---|---|
| facility_idPK | INT | Unique facility identifier |
| facility_name | VARCHAR(150) | Facility display name |