Paytm: February LPG Cylinder Bookings by Gas Provider
Customers book cooking-gas cylinders from Indane, HP Gas and Bharat Gas through the app. The bill-payments team reviews each provider's month.
- Difficulty: Easy
- Topics: Aggregation, Dates
- Dialect: MySQL
- Problem: #852
Problem statement
Customers book cooking-gas cylinders from Indane, HP Gas and Bharat Gas through the app. The bill-payments team reviews each provider's month.
For every provider with at least one confirmed booking made in February 2025 (by booked_at), return provider, bookings (the number of such bookings) and total_amount (their amounts added up). Failed and refunded bookings do not count. Return the rows ordered by total_amount descending, then by provider.
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: LpgBooking
| Column | Type |
|---|---|
| booking_id | int |
| consumer_no | varchar |
| provider | enum(Indane, HP Gas, Bharat Gas) |
| booked_at | datetime |
| amount | int |
| status | enum(confirmed, failed, refunded) |
Primary key: booking_id.
One row per cylinder booking paid through the app; consumer_no is the household's gas connection number.
Examples
Example 1
LpgBooking
| booking_id | consumer_no | provider | booked_at | amount | status |
|---|---|---|---|---|---|
| 1 | IN7781203 | Indane | 2025-02-03 09:12:40 | 903 | confirmed |
| 2 | HP4410921 | HP Gas | 2025-02-14 18:30:00 | 905 | confirmed |
| 3 | IN7781203 | Indane | 2025-01-31 23:59:59 | 903 | confirmed |
| 4 | BG2219034 | Bharat Gas | 2025-02-20 11:05:10 | 902 | failed |
| 5 | IN6630118 | Indane | 2025-02-28 23:10:00 | 903 | confirmed |
| 6 | HP4410921 | HP Gas | 2025-03-01 00:00:00 | 905 | confirmed |
| 7 | BG2219034 | Bharat Gas | 2025-02-21 08:00:00 | 902 | refunded |
| 8 | HP5501277 | HP Gas | 2025-02-09 07:45:00 | 905 | confirmed |
Output
| provider | bookings | total_amount |
|---|---|---|
| HP Gas | 2 | 1810 |
| Indane | 2 | 1806 |
How to solve Paytm: February LPG Cylinder Bookings by Gas Provider
Filter, then group. The WHERE clause keeps confirmed bookings made in February; GROUP BY provider then gives one row per provider, with COUNT(*) for the bookings and SUM(amount) for the money. A provider whose February bookings all failed loses every row in WHERE, so it forms no group and does not appear — as asked.
The month test is a half-open range: >= '2025-02-01 00:00:00' and < '2025-03-01 00:00:00'. That keeps a booking at 23:59:59 on the 28th and rejects one at midnight on 1 March, and it can use an index on booked_at. YEAR(...) = 2025 AND MONTH(...) = 2 or DATE_FORMAT(booked_at, '%Y-%m') = '2025-02' say the same thing more readably but wrap the column in a function, which prevents an index range scan.
The order is total descending with the provider name as the tie-breaker, because cylinder prices are the same across many bookings and equal totals are common.
Reference solution (MySQL)
SELECT provider, COUNT(*) AS bookings, SUM(amount) AS total_amount
FROM LpgBooking
WHERE status = 'confirmed'
AND booked_at >= '2025-02-01 00:00:00'
AND booked_at < '2025-03-01 00:00:00'
GROUP BY provider
ORDER BY total_amount DESC, providerAnother way
SELECT provider, COUNT(booking_id) AS bookings, SUM(amount) AS total_amount FROM LpgBooking WHERE status = 'confirmed' AND YEAR(booked_at) = 2025 AND MONTH(booked_at) = 2 GROUP BY provider ORDER BY total_amount DESC, providerAnother way
SELECT provider, COUNT(*) AS bookings, SUM(amount) AS total_amount FROM LpgBooking WHERE status = 'confirmed' AND DATE_FORMAT(booked_at, '%Y-%m') = '2025-02' GROUP BY provider ORDER BY 3 DESC, 1← Paytm: Wallet Users by KYC Level in Each City · Paytm: January FASTag Toll Spend for Each Vehicle →