Riders per Ferry II
Problem
A harbour logs ferry departures in `ferries` with columns `(ferry_id, depart_time, capacity)` (`ferry_id` is the primary key) and rider arrivals in `riders` with columns `(rider_id, arrive_time)` (`rider_id` is the primary key). Times are integers.
Ferries leave in order of `depart_time`. When a ferry departs, riders who have already arrived and are still waiting board it in arrival order until either everyone waiting is aboard or the ferry reaches its `capacity`; whoever is left keeps waiting for a later ferry. Report `ferry_id` and `rider_count` (how many riders actually board it) for every ferry, ordered by `ferry_id` ascending.
Tables
Example rows — the live problem includes the full dataset.
| ferry_id | depart_time | capacity |
|---|---|---|
| 1 | 2 | 1 |
| 2 | 4 | 10 |
| 3 | 7 | 2 |
| rider_id | arrive_time |
|---|---|
| 1 | 1 |
| 2 | 1 |
| 3 | 5 |
Expected output
Your answer should return 3 rows with the columns ferry_id, rider_count.
Starter code (SQL)
SELECT *
FROM ferries;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