Paytm: February LPG Cylinder Bookings by Gas Provider

Easy
852.

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.

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

ColumnTypeValues
booking_idint
consumer_novarchar
providerenumIndane, HP Gas, Bharat Gas
booked_atdatetime
amountint
statusenumconfirmed, failed, refunded

Primary key: booking_id.

One row per cylinder booking paid through the app; consumer_no is the household's gas connection number.

Example

The rows must come back in the order shown.

LpgBooking

booking_idconsumer_noproviderbooked_atamountstatus
1IN7781203Indane2025-02-03 09:12:40903confirmed
2HP4410921HP Gas2025-02-14 18:30:00905confirmed
3IN7781203Indane2025-01-31 23:59:59903confirmed
4BG2219034Bharat Gas2025-02-20 11:05:10902failed
5IN6630118Indane2025-02-28 23:10:00903confirmed
6HP4410921HP Gas2025-03-01 00:00:00905confirmed
7BG2219034Bharat Gas2025-02-21 08:00:00902refunded
8HP5501277HP Gas2025-02-09 07:45:00905confirmed

Output

providerbookingstotal_amount
HP Gas21810
Indane21806
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: 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

ColumnType
booking_idint
consumer_novarchar
providerenum(Indane, HP Gas, Bharat Gas)
booked_atdatetime
amountint
statusenum(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_idconsumer_noproviderbooked_atamountstatus
1IN7781203Indane2025-02-03 09:12:40903confirmed
2HP4410921HP Gas2025-02-14 18:30:00905confirmed
3IN7781203Indane2025-01-31 23:59:59903confirmed
4BG2219034Bharat Gas2025-02-20 11:05:10902failed
5IN6630118Indane2025-02-28 23:10:00903confirmed
6HP4410921HP Gas2025-03-01 00:00:00905confirmed
7BG2219034Bharat Gas2025-02-21 08:00:00902refunded
8HP5501277HP Gas2025-02-09 07:45:00905confirmed

Output

providerbookingstotal_amount
HP Gas21810
Indane21806

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

Another 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, provider

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