Paytm: January FASTag Toll Spend for Each Vehicle

Easy
853.

Paytm: January FASTag Toll Spend for Each Vehicle

The FASTag team sends each vehicle owner a monthly toll statement.

For every vehicle with at least one debited toll crossing in January 2025 (by crossed_at), return vehicle_no, crossings (the number of such crossings) and total_toll (their toll added up). Declined crossings — the tag had too little balance — do not count. Return the rows ordered by total_toll descending, then by vehicle_no.

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

ColumnTypeValues
tag_idint
vehicle_novarchar
vehicle_classenumcar, lcv, bus, truck

Primary key: tag_id.

One row per FASTag sticker; each vehicle_no has one tag.

Table: TollCrossing

ColumnTypeValues
crossing_idint
tag_idint
plazavarchar
crossed_atdatetime
toll_amountint
statusenumdebited, declined

Primary key: crossing_id.

One row per time a tag was read at a toll plaza. tag_id always names a tag.

Example

The rows must come back in the order shown.

FastagTag

tag_idvehicle_novehicle_class
1MH12AB1234car
2KA05MN4321truck
3DL8CAF0099car
4TN09BX7788bus

TollCrossing

crossing_idtag_idplazacrossed_attoll_amountstatus
11Lonavala2025-01-04 08:10:00320debited
21Vashi2025-01-04 10:02:0040debited
32Electronic City2025-01-15 02:30:00205debited
42Paranur2025-01-31 23:55:00155debited
53Kherki Daula2025-02-01 00:05:0080debited
63Kherki Daula2025-01-20 19:00:0080declined
74Paranur2024-12-31 23:50:00410debited
81Lonavala2025-01-28 18:40:00320debited

Output

vehicle_nocrossingstotal_toll
MH12AB12343680
KA05MN43212360
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: January FASTag Toll Spend for Each Vehicle

The FASTag team sends each vehicle owner a monthly toll statement.

  • Difficulty: Easy
  • Topics: Joins, Aggregation, Dates
  • Dialect: MySQL
  • Problem: #853

Problem statement

The FASTag team sends each vehicle owner a monthly toll statement.

For every vehicle with at least one debited toll crossing in January 2025 (by crossed_at), return vehicle_no, crossings (the number of such crossings) and total_toll (their toll added up). Declined crossings — the tag had too little balance — do not count. Return the rows ordered by total_toll descending, then by vehicle_no.

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

ColumnType
tag_idint
vehicle_novarchar
vehicle_classenum(car, lcv, bus, truck)

Primary key: tag_id.

One row per FASTag sticker; each vehicle_no has one tag.

Table: TollCrossing

ColumnType
crossing_idint
tag_idint
plazavarchar
crossed_atdatetime
toll_amountint
statusenum(debited, declined)

Primary key: crossing_id.

One row per time a tag was read at a toll plaza. tag_id always names a tag.

Examples

Example 1

FastagTag

tag_idvehicle_novehicle_class
1MH12AB1234car
2KA05MN4321truck
3DL8CAF0099car
4TN09BX7788bus

TollCrossing

crossing_idtag_idplazacrossed_attoll_amountstatus
11Lonavala2025-01-04 08:10:00320debited
21Vashi2025-01-04 10:02:0040debited
32Electronic City2025-01-15 02:30:00205debited
42Paranur2025-01-31 23:55:00155debited
53Kherki Daula2025-02-01 00:05:0080debited
63Kherki Daula2025-01-20 19:00:0080declined
74Paranur2024-12-31 23:50:00410debited
81Lonavala2025-01-28 18:40:00320debited

Output

vehicle_nocrossingstotal_toll
MH12AB12343680
KA05MN43212360

How to solve Paytm: January FASTag Toll Spend for Each Vehicle

Join the tags to their crossings, filter, and group by vehicle. The WHERE clause keeps debited crossings whose crossed_at lies in [2025-01-01 00:00:00, 2025-02-01 00:00:00); a declined read moved no money and a crossing on New Year's Eve belongs to December's statement. Grouping by the tag (and its vehicle number, so the column can be selected) then gives COUNT(*) crossings and SUM(toll_amount) per vehicle.

Because the join is an inner join, a vehicle with no qualifying crossing has no rows left after the filter and is not listed — the statement only wants vehicles that were charged. The order is spend descending with vehicle_no breaking ties.

The alternative aggregates TollCrossing first in a derived table — tested with LEFT(crossed_at, 7) = '2025-01' — and then joins the much smaller per-tag result to the tags. Both are a single scan of the crossings; pre-aggregating is the better habit when the dimension table is wide.

Reference solution (MySQL)

SELECT t.vehicle_no, COUNT(*) AS crossings, SUM(c.toll_amount) AS total_toll
FROM FastagTag t
JOIN TollCrossing c ON c.tag_id = t.tag_id
WHERE c.status = 'debited'
  AND c.crossed_at >= '2025-01-01 00:00:00'
  AND c.crossed_at < '2025-02-01 00:00:00'
GROUP BY t.tag_id, t.vehicle_no
ORDER BY total_toll DESC, t.vehicle_no

Another way

SELECT t.vehicle_no, s.crossings, s.total_toll
FROM FastagTag t
JOIN (SELECT tag_id, COUNT(*) AS crossings, SUM(toll_amount) AS total_toll FROM TollCrossing
      WHERE status = 'debited' AND LEFT(crossed_at, 7) = '2025-01' GROUP BY tag_id) s ON s.tag_id = t.tag_id
ORDER BY s.total_toll DESC, t.vehicle_no

← Paytm: February LPG Cylinder Bookings by Gas Provider · Paytm: Tatkal Train Bookings Made in the First Five Minutes →