Last Rider on the Gondola

A mountain gondola cabin can carry at most 600 kg. Riders board strictly in queue_pos order (1 first), and the operator stops boarding just before the rider who would push the total weight above 600 kg. Nobody behind that rider may skip ahead, even if they are light enough.

Return the name of the last rider who boards, as one row with one column rider_name. A total of exactly 600 kg is allowed. If the queue is empty or the first rider alone weighs more than 600 kg, return no rows.

Tables

gondola_queue — One row per rider waiting for the next cabin.

Example

gondola_queue

Expected output

Rows may be returned in any order. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Window Functions · problem 36 of 70 · SQLite

The idea

The rider at position p boards when the combined weight of riders 1 to p is at most 600 kg. That combined weight is a running total, SUM(weight_kg) OVER (ORDER BY queue_pos), and the order must be the queue position, not the ticket id.

Weights are positive, so the running total increases with every rider. That means the riders within the limit form an unbroken prefix of the queue: once someone does not fit, nobody behind them can either, which matches the no-skipping rule. The last boarder is therefore the rider with the highest queue_pos among those whose running total is <= 600: filter, sort descending, take one row.

If even the first rider is too heavy, or the queue is empty, the filter leaves nothing and the query returns no rows, as required.

The alternative computes the same running total without a window function, joining each rider to everyone at or ahead of them in the queue and summing per rider.

Solution

WITH boarding AS (
  SELECT
    rider_name,
    queue_pos,
    SUM(weight_kg) OVER (ORDER BY queue_pos) AS load_kg
  FROM gondola_queue
)
SELECT rider_name
FROM boarding
WHERE load_kg <= 600
ORDER BY queue_pos DESC
LIMIT 1;

Another way

SELECT q.rider_name
FROM gondola_queue AS q
JOIN gondola_queue AS ahead ON ahead.queue_pos <= q.queue_pos
GROUP BY q.rider_id, q.rider_name, q.queue_pos
HAVING SUM(ahead.weight_kg) <= 600
ORDER BY q.queue_pos DESC
LIMIT 1;

Why common answers fail

Accumulates weight in rider_id order, but rider_id is the ticket number, not the boarding order.

WITH boarding AS (
  SELECT rider_name, rider_id, SUM(weight_kg) OVER (ORDER BY rider_id) AS load_kg
  FROM gondola_queue
)
SELECT rider_name FROM boarding WHERE load_kg <= 600 ORDER BY rider_id DESC LIMIT 1;

Uses < instead of <=, so a rider who brings the load to exactly 600 kg is wrongly left behind.

WITH boarding AS (
  SELECT rider_name, queue_pos, SUM(weight_kg) OVER (ORDER BY queue_pos) AS load_kg
  FROM gondola_queue
)
SELECT rider_name FROM boarding WHERE load_kg < 600 ORDER BY queue_pos DESC LIMIT 1;

Checks the load before the rider steps on instead of after, so it returns the rider who overloads the cabin.

WITH boarding AS (
  SELECT rider_name, queue_pos, weight_kg, SUM(weight_kg) OVER (ORDER BY queue_pos) AS load_kg
  FROM gondola_queue
)
SELECT rider_name FROM boarding WHERE load_kg - weight_kg <= 600 ORDER BY queue_pos DESC LIMIT 1;

What the interviewer is checking

A compact running-total question: the interviewer checks the ordering column, the boundary (<= versus <), and that you reason about why the prefix property holds.

Mistakes to avoid

• Ordering the running total by an id that is not the queue order.
• Using < when the limit is inclusive.
• Testing the load before adding the current rider.

Follow-ups to expect

• Split the whole queue into successive cabin loads (recursive CTE that resets the total when it would exceed the limit).
• Return how many riders board and the total load (COUNT and MAX of the running total over the qualifying rows).
• Allow lighter riders to skip ahead if they fit (a greedy pass, easiest with a recursive CTE).

In real work

Running totals against a cap appear in shipping (fill a truck by weight), budgeting (fund requests until money runs out) and rate limiting.

Practice next