Waitlist Positions on a Train per Class

Medium
534.

Waitlist Positions on a Train per Class

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

ColumnTypeValues
pnrbigint
train_noint
journey_datedate
travel_classenumSL, 3A, 2A
booked_atdatetime
statusenumWL, CNF, CAN

Primary key: pnr.

One passenger per PNR. status is the ticket's state now: still waitlisted, confirmed, or cancelled.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

TicketRequest

pnrtrain_nojourney_datetravel_classbooked_atstatus
4810023311126272024-10-30SL2024-09-01 08:00:04CNF
4810023390126272024-10-30SL2024-09-01 08:00:09WL
4810023355126272024-10-30SL2024-09-01 08:00:09WL
4810024102126272024-10-30SL2024-09-02 19:41:20CAN
4810024567126272024-10-30SL2024-09-03 10:12:00WL
4810025001126272024-10-303A2024-09-03 11:30:45WL
4810025102126272024-10-31SL2024-09-02 07:15:00WL

Output

pnrtrain_nojourney_datetravel_classwl_position
4810025001126272024-10-303A1
4810023355126272024-10-30SL1
4810023390126272024-10-30SL2
4810024567126272024-10-30SL3
4810025102126272024-10-31SL1
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
pnrbigint
train_noint
journey_datedate
travel_classenum(SL, 3A, 2A)
booked_atdatetime
statusenum(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

pnrtrain_nojourney_datetravel_classbooked_atstatus
4810023311126272024-10-30SL2024-09-01 08:00:04CNF
4810023390126272024-10-30SL2024-09-01 08:00:09WL
4810023355126272024-10-30SL2024-09-01 08:00:09WL
4810024102126272024-10-30SL2024-09-02 19:41:20CAN
4810024567126272024-10-30SL2024-09-03 10:12:00WL
4810025001126272024-10-303A2024-09-03 11:30:45WL
4810025102126272024-10-31SL2024-09-02 07:15:00WL

Output

pnrtrain_nojourney_datetravel_classwl_position
4810025001126272024-10-303A1
4810023355126272024-10-30SL1
4810023390126272024-10-30SL2
4810024567126272024-10-30SL3
4810025102126272024-10-31SL1

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 →