Riders per Ferry I

SQLMediumMid level~15 min

Problem

A harbour logs ferry departures in `ferries` with columns `(ferry_id, depart_time)` (`ferry_id` is the primary key) and rider arrivals at the dock in `riders` with columns `(rider_id, arrive_time)` (`rider_id` is the primary key). Times are integers.

Each rider boards the next ferry that departs at a time greater than or equal to the rider's arrival time. Report `ferry_id` and `rider_count` (how many riders board it) for every ferry, including ferries that nobody boards. Return the rows ordered by `ferry_id` ascending.

Tables

Example rows — the live problem includes the full dataset.

ferries

ferry_id	depart_time
1	2
2	4
3	7

riders

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.

Solve it now → Create free account

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.