Paytm: Tatkal Train Bookings Made in the First Five Minutes
Tatkal train tickets go on sale at 10:00 on the day before the journey, and the trains team measures how many of its users get a booking through in the…
- Difficulty: Easy
- Topics: Dates
- Dialect: MySQL
- Problem: #854
Problem statement
Tatkal train tickets go on sale at 10:00 on the day before the journey, and the trains team measures how many of its users get a booking through in the first five minutes.
Return every booking in the tatkal or premium_tatkal quota whose booked_at is on the day before its journey_date, at or after 10:00:00 and before 10:05:00. Return the columns booking_id, user_id and seconds_after_open (seconds from 10:00:00 that day to booked_at), ordered by seconds_after_open, then 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: TrainBooking
| Column | Type |
|---|---|
| booking_id | int |
| user_id | int |
| quota | enum(general, tatkal, premium_tatkal) |
| journey_date | date |
| booked_at | datetime |
Primary key: booking_id.
One row per successful train booking made through the app.
Examples
Example 1
TrainBooking
| booking_id | user_id | quota | journey_date | booked_at |
|---|---|---|---|---|
| 1 | 21 | tatkal | 2025-04-12 | 2025-04-11 10:00:07 |
| 2 | 22 | tatkal | 2025-04-12 | 2025-04-11 09:59:58 |
| 3 | 23 | premium_tatkal | 2025-04-15 | 2025-04-14 10:04:59 |
| 4 | 24 | general | 2025-04-12 | 2025-04-11 10:01:00 |
| 5 | 25 | tatkal | 2025-04-15 | 2025-04-14 10:05:00 |
| 6 | 26 | tatkal | 2025-04-20 | 2025-04-18 10:02:00 |
| 7 | 27 | tatkal | 2025-05-01 | 2025-04-30 10:00:07 |
Output
| booking_id | user_id | seconds_after_open |
|---|---|---|
| 1 | 21 | 7 |
| 7 | 27 | 7 |
| 3 | 23 | 299 |
How to solve Paytm: Tatkal Train Bookings Made in the First Five Minutes
The window is different for every booking, so it has to be built from the row itself. DATE_SUB(journey_date, INTERVAL 1 DAY) is the sale day, and CONCAT(..., ' 10:00:00') turns it into the opening moment. A booking qualifies when booked_at is at or after that moment and strictly before the same day at 10:05:00 — the half-open range keeps 10:04:59 and rejects 10:05:00 exactly, and it also rejects a booking at 10:02 two days before the journey, which a test on the clock time alone would wrongly keep.
TIMESTAMPDIFF(SECOND, opening, booked_at) gives the delay. The alternative splits the test differently: the calendar day of booked_at (CAST(booked_at AS DATE)) must be the sale day and the clock must read 10:00–10:04, and then the delay is simply MINUTE * 60 + SECOND. Both handle a journey on the 1st of a month, whose sale day is the last day of the previous month, because the date arithmetic is calendar-aware.
Order by the delay with the booking id as tie-breaker, as the statement fixes.
Reference solution (MySQL)
SELECT booking_id, user_id,
TIMESTAMPDIFF(SECOND, CONCAT(DATE_SUB(journey_date, INTERVAL 1 DAY), ' 10:00:00'), booked_at) AS seconds_after_open
FROM TrainBooking
WHERE quota IN ('tatkal', 'premium_tatkal')
AND booked_at >= CONCAT(DATE_SUB(journey_date, INTERVAL 1 DAY), ' 10:00:00')
AND booked_at < CONCAT(DATE_SUB(journey_date, INTERVAL 1 DAY), ' 10:05:00')
ORDER BY seconds_after_open, booking_idAnother way
SELECT booking_id, user_id, MINUTE(booked_at) * 60 + SECOND(booked_at) AS seconds_after_open
FROM TrainBooking
WHERE quota <> 'general'
AND CAST(booked_at AS DATE) = DATE_SUB(journey_date, INTERVAL 1 DAY)
AND HOUR(booked_at) = 10 AND MINUTE(booked_at) < 5
ORDER BY seconds_after_open, booking_id← Paytm: January FASTag Toll Spend for Each Vehicle · Paytm: Digital Gold Customers Holding at Least One Gram →