Overworked Hospital Staff
Problem
A hospital logs the hours of every shift worked. A standard work week runs Monday through Sunday and totals 50 scheduled hours, so a week is 'overworked' when a staff member's shift hours for that week exceed 50. Find every staff member who has at least two overworked weeks. Report the staff name and the count of overworked weeks. Sort by the count descending, then by staff name alphabetically.
Tables
Example rows — the live problem includes the full dataset.
| staff_id | staff_name | ward |
|---|---|---|
| 1 | Nadia Okafor | ICU |
| 2 | Sam Petrov | ER |
| 3 | Iris Wong | Pediatrics |
| shift_id | staff_id | shift_date | shift_kind | hours |
|---|---|---|---|---|
| 1 | 1 | 2024-04-01 | day | 30 |
| 2 | 1 | 2024-04-03 | night | 25 |
| 3 | 1 | 2024-04-08 | day | 28 |
Expected output
Your answer should return 2 rows with the columns staff_name, heavy_weeks.
Starter code (SQL)
SELECT *
FROM staff;Solve this SQL question free
Write SQL and run it instantly in your browser — even on your phone. No signup needed to try.
Solution & explanation
Create a free account to unlock the optimal solution, a step-by-step explanation, and the hidden test cases that grade your answer.
Sign up free to unlock