Last Rider Into the Ropeway Cabin — SQL Medium Problem

Problem statement

A hill-station ropeway cabin can carry at most 350 kg. Riders board strictly in queue order; the cabin leaves as soon as the next rider in the queue would push the total above 350 kg — nobody further back may skip ahead, even if they would fit. A total of exactly 350 kg is allowed, and every rider weighs at most 350 kg, so the first rider always boards.

Return the name of the last rider to board the cabin, in a single column named rider_name. If the queue is empty, return no rows.

Tables

Table: CabinQueue

Primary key: rider_id.

One row per rider waiting. queue_pos is the place in the queue — 1 boards first — and runs 1, 2, 3, … with no repeats.

Examples

Example 1

CabinQueue

Output

Example 2

CabinQueue

Output

How to solve Last Rider Into the Ropeway Cabin

Riders board in queue_pos order and nobody may skip, so a rider boards exactly when the running total of weights up to and including them is at most 350 kg. Because every weight is positive, the running total grows along the queue: once it passes 350 it never comes back, so the riders who board form a prefix of the queue, and the last rider to board is the one with the largest queue_pos among those whose running total is ≤ 350.

SUM(weight_kg) OVER (ORDER BY queue_pos) computes that running total in one pass (the default frame of an ordered window runs from the first row to the current one, and positions are unique, so there are no peer rows to fold in). Filter on_board <= 350, sort by queue_pos descending and take one row. The <= keeps a cabin that is filled to exactly 350 kg. The skipping trap — a light rider further back who would fit in the leftover space — is handled automatically, because their running total includes everyone ahead of them.

Without windows, the running total is a self join on b.queue_pos <= a.queue_pos grouped by rider, or a correlated SUM per rider. Both are quadratic in the queue length; the window version is a single sort. An empty queue produces no rows in all three.

Reference solution (MySQL)

SELECT rider_name
FROM (
  SELECT rider_name, queue_pos,
         SUM(weight_kg) OVER (ORDER BY queue_pos) AS on_board
  FROM CabinQueue
) q
WHERE on_board <= 350
ORDER BY queue_pos DESC
LIMIT 1

Another way

SELECT a.rider_name
FROM CabinQueue a
JOIN CabinQueue b ON b.queue_pos <= a.queue_pos
GROUP BY a.rider_id, a.rider_name, a.queue_pos
HAVING SUM(b.weight_kg) <= 350
ORDER BY a.queue_pos DESC
LIMIT 1

Another way

SELECT c.rider_name
FROM CabinQueue c
WHERE (SELECT SUM(d.weight_kg) FROM CabinQueue d WHERE d.queue_pos <= c.queue_pos) <= 350
ORDER BY c.queue_pos DESC
LIMIT 1