Paytm: Return Flights Booked Within Ten Days — SQL Problem
The flights team wants to know how often a one-way ticket is followed by the trip home, to decide where to offer round-trip fares.
- Difficulty: Hard
- Topics: Joins, Window Functions, Dates
- Dialect: MySQL
- Problem: #872
Problem statement
The flights team wants to know how often a one-way ticket is followed by the trip home, to decide where to offer round-trip fares.
For every confirmed ticket (the outbound), find the same user's earliest confirmed ticket in the opposite direction (from the outbound's to_airport to its from_airport) that departs 1 to 10 days after the outbound's departs_on, inclusive. If several such tickets share the earliest date, take the smallest ticket_id. Return user_id, outbound_ticket, return_ticket and days_away (days between the two departures) for every outbound that has a return. Return the rows ordered by user_id, then outbound_ticket.
A practice scenario set at Paytm: the tables and data are invented, and this is not a question from, or endorsed by, Paytm.
Tables
Table: FlightTicket
| Column | Type |
|---|---|
| ticket_id | int |
| user_id | int |
| from_airport | char |
| to_airport | char |
| departs_on | date |
| status | enum(confirmed, cancelled) |
Primary key: ticket_id.
One row per one-way ticket bought on the app; airports are three-letter codes.
Examples
Example 1
FlightTicket
| ticket_id | user_id | from_airport | to_airport | departs_on | status |
|---|---|---|---|---|---|
| 1 | 1 | DEL | BOM | 2025-05-01 | confirmed |
| 2 | 1 | BOM | DEL | 2025-05-08 | confirmed |
| 3 | 1 | BOM | DEL | 2025-05-09 | confirmed |
| 4 | 2 | BLR | GOI | 2025-05-10 | confirmed |
| 5 | 2 | GOI | BLR | 2025-05-15 | cancelled |
| 6 | 2 | GOI | BLR | 2025-05-20 | confirmed |
| 7 | 3 | HYD | MAA | 2025-05-01 | confirmed |
| 8 | 3 | MAA | HYD | 2025-05-12 | confirmed |
| 9 | 4 | CCU | DEL | 2025-05-03 | confirmed |
| 10 | 4 | DEL | CCU | 2025-05-03 | confirmed |
| 11 | 2 | GOI | BLR | 2025-05-20 | confirmed |
Output
| user_id | outbound_ticket | return_ticket | days_away |
|---|---|---|---|
| 1 | 1 | 2 | 7 |
| 2 | 4 | 6 | 10 |
How to solve Paytm: Return Flights Booked Within Ten Days
Matching a ticket with a later ticket of the same user is a self join. The outbound copy o is any confirmed ticket; the return copy r must belong to the same user, fly the reverse route (r.from_airport = o.to_airport AND r.to_airport = o.from_airport), be confirmed, and depart between one and ten days after the outbound. Putting the date window in the join condition with DATE_ADD keeps same-day tickets out (user 4's two flights on 3 May) and stops at ten days inclusive (user 2's 20 May return is exactly ten days; user 3's eleven-day gap is too long). Cancelled tickets are excluded on both sides, so user 2's cancelled 15 May ticket cannot be the return.
An outbound can match several returns — user 1 has tickets home on 8 and 9 May, and user 2 has two on 20 May. ROW_NUMBER() OVER (PARTITION BY o.ticket_id ORDER BY r.departs_on, r.ticket_id) ranks them, and pick_no = 1 keeps the earliest date, smallest id on a tie. Every confirmed ticket is tried as an outbound, so a ticket home is itself checked for a return and simply finds none.
The alternative keeps a pair when no other qualifying return is earlier, or equally early with a smaller id — the NOT EXISTS form of "the first one". Both are a self join on (user_id, from_airport, to_airport).
Reference solution (MySQL)
WITH pairs AS (
SELECT o.user_id, o.ticket_id AS outbound_ticket, r.ticket_id AS return_ticket,
DATEDIFF(r.departs_on, o.departs_on) AS days_away,
ROW_NUMBER() OVER (PARTITION BY o.ticket_id ORDER BY r.departs_on, r.ticket_id) AS pick_no
FROM FlightTicket o
JOIN FlightTicket r
ON r.user_id = o.user_id
AND r.from_airport = o.to_airport
AND r.to_airport = o.from_airport
AND r.status = 'confirmed'
AND r.departs_on BETWEEN DATE_ADD(o.departs_on, INTERVAL 1 DAY) AND DATE_ADD(o.departs_on, INTERVAL 10 DAY)
WHERE o.status = 'confirmed'
)
SELECT user_id, outbound_ticket, return_ticket, days_away
FROM pairs
WHERE pick_no = 1
ORDER BY user_id, outbound_ticketAnother way
SELECT o.user_id, o.ticket_id AS outbound_ticket, r.ticket_id AS return_ticket, DATEDIFF(r.departs_on, o.departs_on) AS days_away
FROM FlightTicket o
JOIN FlightTicket r ON r.user_id = o.user_id AND r.from_airport = o.to_airport AND r.to_airport = o.from_airport
AND r.status = 'confirmed' AND DATEDIFF(r.departs_on, o.departs_on) BETWEEN 1 AND 10
WHERE o.status = 'confirmed'
AND NOT EXISTS (
SELECT 1 FROM FlightTicket x
WHERE x.user_id = o.user_id AND x.from_airport = o.to_airport AND x.to_airport = o.from_airport
AND x.status = 'confirmed' AND DATEDIFF(x.departs_on, o.departs_on) BETWEEN 1 AND 10
AND (x.departs_on < r.departs_on OR (x.departs_on = r.departs_on AND x.ticket_id < r.ticket_id))
)
ORDER BY o.user_id, o.ticket_id← Paytm: Spending Before and After Cashback for Each Campaign · PhonePe: UPI Lite Payments Above the Per-Payment Cap →