Paytm: Tatkal Train Bookings Made in the First Five Minutes

Easy
854.

Paytm: Tatkal Train Bookings Made in the First Five Minutes

EasyDates

MySQL

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

ColumnTypeValues
booking_idint
user_idint
quotaenumgeneral, tatkal, premium_tatkal
journey_datedate
booked_atdatetime

Primary key: booking_id.

One row per successful train booking made through the app.

Example

The rows must come back in the order shown.

TrainBooking

booking_iduser_idquotajourney_datebooked_at
121tatkal2025-04-122025-04-11 10:00:07
222tatkal2025-04-122025-04-11 09:59:58
323premium_tatkal2025-04-152025-04-14 10:04:59
424general2025-04-122025-04-11 10:01:00
525tatkal2025-04-152025-04-14 10:05:00
626tatkal2025-04-202025-04-18 10:02:00
727tatkal2025-05-012025-04-30 10:00:07

Output

booking_iduser_idseconds_after_open
1217
7277
323299
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: 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

ColumnType
booking_idint
user_idint
quotaenum(general, tatkal, premium_tatkal)
journey_datedate
booked_atdatetime

Primary key: booking_id.

One row per successful train booking made through the app.

Examples

Example 1

TrainBooking

booking_iduser_idquotajourney_datebooked_at
121tatkal2025-04-122025-04-11 10:00:07
222tatkal2025-04-122025-04-11 09:59:58
323premium_tatkal2025-04-152025-04-14 10:04:59
424general2025-04-122025-04-11 10:01:00
525tatkal2025-04-152025-04-14 10:05:00
626tatkal2025-04-202025-04-18 10:02:00
727tatkal2025-05-012025-04-30 10:00:07

Output

booking_iduser_idseconds_after_open
1217
7277
323299

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_id

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