Waitlist Positions on a Train per Class — SQL Problem
Waitlisted rail tickets move up as others confirm or cancel.
- Difficulty: Medium
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #534
Problem statement
Waitlisted rail tickets move up as others confirm or cancel. A ticket's current WL position is its place among the tickets that are still waitlisted for the same train_no, journey_date and travel_class, ordered by booked_at (earliest first); two tickets booked in the same second are ordered by the smaller pnr first.
Return pnr, train_no, journey_date, travel_class and wl_position for every ticket whose status is WL. Confirmed and cancelled tickets take no position. Return the rows in any order.
Tables
Table: TicketRequest
| Column | Type |
|---|---|
| pnr | bigint |
| train_no | int |
| journey_date | date |
| travel_class | enum(SL, 3A, 2A) |
| booked_at | datetime |
| status | enum(WL, CNF, CAN) |
Primary key: pnr.
One passenger per PNR. status is the ticket's state now: still waitlisted, confirmed, or cancelled.
Examples
Example 1
TicketRequest
| pnr | train_no | journey_date | travel_class | booked_at | status |
|---|---|---|---|---|---|
| 4810023311 | 12627 | 2024-10-30 | SL | 2024-09-01 08:00:04 | CNF |
| 4810023390 | 12627 | 2024-10-30 | SL | 2024-09-01 08:00:09 | WL |
| 4810023355 | 12627 | 2024-10-30 | SL | 2024-09-01 08:00:09 | WL |
| 4810024102 | 12627 | 2024-10-30 | SL | 2024-09-02 19:41:20 | CAN |
| 4810024567 | 12627 | 2024-10-30 | SL | 2024-09-03 10:12:00 | WL |
| 4810025001 | 12627 | 2024-10-30 | 3A | 2024-09-03 11:30:45 | WL |
| 4810025102 | 12627 | 2024-10-31 | SL | 2024-09-02 07:15:00 | WL |
Output
| pnr | train_no | journey_date | travel_class | wl_position |
|---|---|---|---|---|
| 4810025001 | 12627 | 2024-10-30 | 3A | 1 |
| 4810023355 | 12627 | 2024-10-30 | SL | 1 |
| 4810023390 | 12627 | 2024-10-30 | SL | 2 |
| 4810024567 | 12627 | 2024-10-30 | SL | 3 |
| 4810025102 | 12627 | 2024-10-31 | SL | 1 |
How to solve Waitlist Positions on a Train per Class
A waitlist position is a running number within a queue, and the queue is one train on one date in one class — so it is ROW_NUMBER() partitioned by (train_no, journey_date, travel_class) and ordered by booked_at. RANK() would be wrong here: two tickets booked in the same second still occupy two different places, which is why the order adds pnr as a tie-breaker and the statement says which goes first.
The filter status = 'WL' sits in WHERE, and WHERE runs before window functions. That is exactly what is wanted: a confirmed or cancelled ticket is gone from the queue and must not push anyone back, so it is removed before the numbering starts.
Without window functions, count the waitlisted tickets in the same queue that were booked earlier, or at the same time with a smaller-or-equal PNR — the correlated subquery in the alternative. It is quadratic per queue, whereas the window needs one sort of the waitlisted rows.
Reference solution (MySQL)
SELECT pnr, train_no, journey_date, travel_class,
ROW_NUMBER() OVER (PARTITION BY train_no, journey_date, travel_class ORDER BY booked_at, pnr) AS wl_position
FROM TicketRequest
WHERE status = 'WL'Another way
SELECT t.pnr, t.train_no, t.journey_date, t.travel_class,
(SELECT COUNT(*) FROM TicketRequest u
WHERE u.status = 'WL' AND u.train_no = t.train_no AND u.journey_date = t.journey_date AND u.travel_class = t.travel_class
AND (u.booked_at < t.booked_at OR (u.booked_at = t.booked_at AND u.pnr <= t.pnr))) AS wl_position
FROM TicketRequest t
WHERE t.status = 'WL'← On-Time Arrival Performance by Airline · Top Two Agencies by Commission Each Quarter →