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
| Column | Type |
|---|---|
| tag_id | int |
| vehicle_no | varchar |
| vehicle_class | enum(car, lcv, bus, truck) |
Primary key: tag_id.
One row per FASTag sticker; each vehicle_no has one tag.
Table: TollCrossing
| Column | Type |
|---|---|
| crossing_id | int |
| tag_id | int |
| plaza | varchar |
| crossed_at | datetime |
| toll_amount | int |
| status | enum(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_id | vehicle_no | vehicle_class |
|---|---|---|
| 1 | MH12AB1234 | car |
| 2 | KA05MN4321 | truck |
| 3 | DL8CAF0099 | car |
| 4 | TN09BX7788 | bus |
TollCrossing
| crossing_id | tag_id | plaza | crossed_at | toll_amount | status |
|---|---|---|---|---|---|
| 1 | 1 | Lonavala | 2025-01-04 08:10:00 | 320 | debited |
| 2 | 1 | Vashi | 2025-01-04 10:02:00 | 40 | debited |
| 3 | 2 | Electronic City | 2025-01-15 02:30:00 | 205 | debited |
| 4 | 2 | Paranur | 2025-01-31 23:55:00 | 155 | debited |
| 5 | 3 | Kherki Daula | 2025-02-01 00:05:00 | 80 | debited |
| 6 | 3 | Kherki Daula | 2025-01-20 19:00:00 | 80 | declined |
| 7 | 4 | Paranur | 2024-12-31 23:50:00 | 410 | debited |
| 8 | 1 | Lonavala | 2025-01-28 18:40:00 | 320 | debited |
Output
| vehicle_no | crossings | total_toll |
|---|---|---|
| MH12AB1234 | 3 | 680 |
| KA05MN4321 | 2 | 360 |
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_noAnother 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 →