Your question is Debugging a Complex SQL Issue. Start with the requirements and the three 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.
Describe a time you used SQL to solve a complex data issue.
For this exercise, use the supplied shift, assignment, and payment tables to demonstrate a reconciliation query. Identify completed assignments where the recorded payment differs from the expected payment, including missing payment records.
assignment_id, worker_id, shift_date, expected_payment, recorded_payment, and discrepancy.shift_date ascending and assignment_id ascending.| Column | Type | Description |
|---|---|---|
| shift_idPK | INT | Unique shift identifier |
| shift_date | DATE | Date of the shift |
| status | VARCHAR(20) | Shift status |
| hourly_rate | NUMERIC(8,2) | Expected hourly payment rate |
| hours_worked | NUMERIC(5,2) | Hours worked on the shift |
| Column | Type | Description |
|---|---|---|
| assignment_idPK | INT | Unique assignment identifier |
| shift_id | INT | Referenced shift identifier |
| worker_id | INT | Worker identifier |
| Column | Type | Description |
|---|---|---|
| payment_idPK | INT | Unique payment record identifier |
| assignment_id | INT | Referenced assignment identifier |
| amount | NUMERIC(10,2) | Payment amount |