Volunteers Who Fell Short of Their Pledge
Problem
A community center asks each volunteer to pledge a number of hours per month. Every time a volunteer signs in and out, a shift row is recorded with `clock_in` and `clock_out` timestamps.
For billing fairness, the minutes of each shift are rounded UP to the next whole minute (a shift of 59 minutes and 1 second counts as 60 minutes).
Write a query that returns the `volunteer_id` of every volunteer whose total recorded minutes are strictly less than their pledged minutes (`pledged_hours * 60`). A volunteer with no shifts at all has 0 recorded minutes and should be reported if their pledge is greater than 0.
Return the result in any order. The only column is `volunteer_id`.
Tables
Example rows — the live problem includes the full dataset.
| volunteer_id | pledged_hours |
|---|---|
| 1 | 20 |
| 2 | 12 |
| 3 | 2 |
| volunteer_id | clock_in | clock_out |
|---|---|---|
| 1 | 2023-04-01 09:00:00 | 2023-04-01 17:00:00 |
| 1 | 2023-04-06 09:05:04 | 2023-04-06 17:09:03 |
| 1 | 2023-04-12 23:00:00 | 2023-04-13 03:00:01 |
Expected output
Your answer should return 2 rows with the columns volunteer_id.
Starter code (SQL)
SELECT *
FROM Volunteer;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