Paytm: Flight Cancellation Refunds Under the Free Window
Flight bookings carry a free cancellation window: a ticket cancelled within 48 hours of booking is refunded in full, but only when the flight departs at…
- Difficulty: Medium
- Topics: Conditional Logic, Dates, Joins
- Dialect: MySQL
- Problem: #862
Problem statement
Flight bookings carry a free cancellation window: a ticket cancelled within 48 hours of booking is refunded in full, but only when the flight departs at least 7 days after the booking time. Any other cancellation is refunded the fare minus the airline's cancellation_fee, never below 0.
For every cancelled booking (cancelled_at is not NULL), return booking_id, airline_name, fare, refund_type (full or fee_deducted) and refund. Both boundaries are inclusive: exactly 48 hours after booking and a departure exactly 7 days after booking still qualify. Return the rows ordered by booking_id.
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: Airline
| Column | Type |
|---|---|
| airline_code | char |
| airline_name | varchar |
| cancellation_fee | int |
Primary key: airline_code.
One row per airline sold on the app, with its flat domestic cancellation fee in rupees.
Table: FlightBooking
| Column | Type |
|---|---|
| booking_id | int |
| airline_code | char |
| booked_at | datetime |
| departs_at | datetime |
| fare | int |
| cancelled_at | datetime |
Primary key: booking_id.
One row per ticket. cancelled_at is NULL for a ticket that was flown or is still live. Times are whole minutes.
Examples
Example 1
Airline
| airline_code | airline_name | cancellation_fee |
|---|---|---|
| 6E | IndiGo | 3000 |
| AI | Air India | 3500 |
| QP | Akasa Air | 2500 |
FlightBooking
| booking_id | airline_code | booked_at | departs_at | fare | cancelled_at |
|---|---|---|---|---|---|
| 1 | 6E | 2025-05-01 10:00:00 | 2025-05-20 06:00:00 | 5400 | 2025-05-02 09:00:00 |
| 2 | 6E | 2025-05-01 10:00:00 | 2025-05-20 06:00:00 | 5400 | 2025-05-03 10:01:00 |
| 3 | AI | 2025-05-01 10:00:00 | 2025-05-06 10:00:00 | 6200 | 2025-05-01 12:00:00 |
| 4 | AI | 2025-05-01 10:00:00 | 2025-05-08 10:00:00 | 4800 | 2025-05-03 10:00:00 |
| 5 | QP | 2025-05-02 08:00:00 | 2025-05-04 08:00:00 | 2100 | 2025-05-03 08:00:00 |
| 6 | QP | 2025-05-02 08:00:00 | 2025-05-25 08:00:00 | 3900 | NULL |
Output
| booking_id | airline_name | fare | refund_type | refund |
|---|---|---|---|---|
| 1 | IndiGo | 5400 | full | 5400 |
| 2 | IndiGo | 5400 | fee_deducted | 2400 |
| 3 | Air India | 6200 | fee_deducted | 2700 |
| 4 | Air India | 4800 | full | 4800 |
| 5 | Akasa Air | 2100 | fee_deducted | 0 |
How to solve Paytm: Flight Cancellation Refunds Under the Free Window
Every cancelled booking gets one of two refunds, decided by a single condition, so the core of the query is a CASE. The free window has two parts that must both hold: the cancellation came no later than 48 hours after booking (cancelled_at <= DATE_ADD(booked_at, INTERVAL 48 HOUR)) and the flight leaves no earlier than 7 days after booking (departs_at >= DATE_ADD(booked_at, INTERVAL 7 DAY)). Both comparisons are inclusive, which is why booking 4 — exactly 48 hours and exactly 7 days — gets its whole fare back, while booking 2, one minute late, does not.
Outside the window the refund is the fare less the airline's fee, joined from Airline. GREATEST(fare - fee, 0) stops a cheap fare from producing a negative refund (booking 5). Bookings never cancelled are filtered out with cancelled_at IS NOT NULL.
The alternative computes the condition once as a 0/1 column with TIMESTAMPDIFF(MINUTE, …) (2,880 minutes and 10,080 minutes) in a derived table and reuses it with IF. With whole-minute timestamps the two forms agree exactly.
Reference solution (MySQL)
SELECT b.booking_id, a.airline_name, b.fare,
CASE WHEN b.cancelled_at <= DATE_ADD(b.booked_at, INTERVAL 48 HOUR)
AND b.departs_at >= DATE_ADD(b.booked_at, INTERVAL 7 DAY) THEN 'full' ELSE 'fee_deducted' END AS refund_type,
CASE WHEN b.cancelled_at <= DATE_ADD(b.booked_at, INTERVAL 48 HOUR)
AND b.departs_at >= DATE_ADD(b.booked_at, INTERVAL 7 DAY) THEN b.fare
ELSE GREATEST(b.fare - a.cancellation_fee, 0) END AS refund
FROM FlightBooking b
JOIN Airline a ON a.airline_code = b.airline_code
WHERE b.cancelled_at IS NOT NULL
ORDER BY b.booking_idAnother way
SELECT booking_id, airline_name, fare, IF(free, 'full', 'fee_deducted') AS refund_type, IF(free, fare, IF(fare > cancellation_fee, fare - cancellation_fee, 0)) AS refund
FROM (
SELECT b.booking_id, a.airline_name, b.fare, a.cancellation_fee,
TIMESTAMPDIFF(MINUTE, b.booked_at, b.cancelled_at) <= 2880 AND TIMESTAMPDIFF(MINUTE, b.booked_at, b.departs_at) >= 10080 AS free
FROM FlightBooking b JOIN Airline a ON a.airline_code = b.airline_code
WHERE b.cancelled_at IS NOT NULL
) x
ORDER BY booking_id← Paytm: Busiest Day for Each EDC Card Machine · Paytm: Mobile Numbers That Switched Operator Between Recharges →