Paytm: Return Flights Booked Within Ten Days

Hard
872.

Paytm: Return Flights Booked Within Ten Days

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

ColumnTypeValues
ticket_idint
user_idint
from_airportchar
to_airportchar
departs_ondate
statusenumconfirmed, cancelled

Primary key: ticket_id.

One row per one-way ticket bought on the app; airports are three-letter codes.

Example

The rows must come back in the order shown.

FlightTicket

ticket_iduser_idfrom_airportto_airportdeparts_onstatus
11DELBOM2025-05-01confirmed
21BOMDEL2025-05-08confirmed
31BOMDEL2025-05-09confirmed
42BLRGOI2025-05-10confirmed
52GOIBLR2025-05-15cancelled
62GOIBLR2025-05-20confirmed
73HYDMAA2025-05-01confirmed
83MAAHYD2025-05-12confirmed
94CCUDEL2025-05-03confirmed
104DELCCU2025-05-03confirmed
1 more row

Output

user_idoutbound_ticketreturn_ticketdays_away
1127
24610
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.

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

ColumnType
ticket_idint
user_idint
from_airportchar
to_airportchar
departs_ondate
statusenum(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_iduser_idfrom_airportto_airportdeparts_onstatus
11DELBOM2025-05-01confirmed
21BOMDEL2025-05-08confirmed
31BOMDEL2025-05-09confirmed
42BLRGOI2025-05-10confirmed
52GOIBLR2025-05-15cancelled
62GOIBLR2025-05-20confirmed
73HYDMAA2025-05-01confirmed
83MAAHYD2025-05-12confirmed
94CCUDEL2025-05-03confirmed
104DELCCU2025-05-03confirmed
112GOIBLR2025-05-20confirmed

Output

user_idoutbound_ticketreturn_ticketdays_away
1127
24610

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_ticket

Another 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 →