Paytm: Flight Cancellation Refunds Under the Free Window

Medium
862.

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

ColumnType
airline_codechar
airline_namevarchar
cancellation_feeint

Primary key: airline_code.

One row per airline sold on the app, with its flat domestic cancellation fee in rupees.

Table: FlightBooking

ColumnType
booking_idint
airline_codechar
booked_atdatetime
departs_atdatetime
fareint
cancelled_atdatetime

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.

Example

The rows must come back in the order shown.

Airline

airline_codeairline_namecancellation_fee
6EIndiGo3000
AIAir India3500
QPAkasa Air2500

FlightBooking

booking_idairline_codebooked_atdeparts_atfarecancelled_at
16E2025-05-01 10:00:002025-05-20 06:00:0054002025-05-02 09:00:00
26E2025-05-01 10:00:002025-05-20 06:00:0054002025-05-03 10:01:00
3AI2025-05-01 10:00:002025-05-06 10:00:0062002025-05-01 12:00:00
4AI2025-05-01 10:00:002025-05-08 10:00:0048002025-05-03 10:00:00
5QP2025-05-02 08:00:002025-05-04 08:00:0021002025-05-03 08:00:00
6QP2025-05-02 08:00:002025-05-25 08:00:003900NULL

Output

booking_idairline_namefarerefund_typerefund
1IndiGo5400full5400
2IndiGo5400fee_deducted2400
3Air India6200fee_deducted2700
4Air India4800full4800
5Akasa Air2100fee_deducted0
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: 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

ColumnType
airline_codechar
airline_namevarchar
cancellation_feeint

Primary key: airline_code.

One row per airline sold on the app, with its flat domestic cancellation fee in rupees.

Table: FlightBooking

ColumnType
booking_idint
airline_codechar
booked_atdatetime
departs_atdatetime
fareint
cancelled_atdatetime

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_codeairline_namecancellation_fee
6EIndiGo3000
AIAir India3500
QPAkasa Air2500

FlightBooking

booking_idairline_codebooked_atdeparts_atfarecancelled_at
16E2025-05-01 10:00:002025-05-20 06:00:0054002025-05-02 09:00:00
26E2025-05-01 10:00:002025-05-20 06:00:0054002025-05-03 10:01:00
3AI2025-05-01 10:00:002025-05-06 10:00:0062002025-05-01 12:00:00
4AI2025-05-01 10:00:002025-05-08 10:00:0048002025-05-03 10:00:00
5QP2025-05-02 08:00:002025-05-04 08:00:0021002025-05-03 08:00:00
6QP2025-05-02 08:00:002025-05-25 08:00:003900NULL

Output

booking_idairline_namefarerefund_typerefund
1IndiGo5400full5400
2IndiGo5400fee_deducted2400
3Air India6200fee_deducted2700
4Air India4800full4800
5Akasa Air2100fee_deducted0

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_id

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