Paytm: FASTag Reads Charged Off the Plaza's Axle-Band Rate
Toll at a plaza depends on the vehicle's number of axles, in bands such as 2, 3, 4–6 and 7 or more.
- Difficulty: Hard
- Topics: Joins, Conditional Logic
- Dialect: MySQL
- Problem: #867
Problem statement
Toll at a plaza depends on the vehicle's number of axles, in bands such as 2, 3, 4–6 and 7 or more. The FASTag disputes team audits every toll read against the plaza's band table.
A read's expected toll is the rate of the band at the same plaza whose range holds the vehicle's axles (min_axles to max_axles inclusive; a NULL max_axles has no upper limit). Bands of one plaza never overlap. Return every read whose charge does not match, with read_id, vehicle_no, plaza, charged, expected_toll and verdict: overcharged or undercharged when a band was found, no_band (with expected_toll NULL) when the plaza has no band for that vehicle. Reads charged exactly the band rate are left out. Return the rows ordered by read_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: FastagTag
| Column | Type |
|---|---|
| tag_id | int |
| vehicle_no | varchar |
| axles | int |
Primary key: tag_id.
One row per FASTag sticker with the axle count on the vehicle's registration.
Table: TollBand
| Column | Type |
|---|---|
| band_id | int |
| plaza | varchar |
| min_axles | int |
| max_axles | int |
| rate | int |
Primary key: band_id.
One row per axle band at a plaza; the whole toll for a vehicle in the band, in rupees (not added up band by band).
Table: TollRead
| Column | Type |
|---|---|
| read_id | int |
| tag_id | int |
| plaza | varchar |
| crossed_at | datetime |
| charged | int |
Primary key: read_id.
One row per debited toll read; tag_id always names a tag.
Examples
Example 1
FastagTag
| tag_id | vehicle_no | axles |
|---|---|---|
| 1 | MH12AB1234 | 2 |
| 2 | KA05MN4321 | 3 |
| 3 | DL8CAF0099 | 6 |
| 4 | TN09BX7788 | 9 |
| 5 | RJ14CD5566 | 4 |
TollBand
| band_id | plaza | min_axles | max_axles | rate |
|---|---|---|---|---|
| 1 | Kherki Daula | 2 | 2 | 80 |
| 2 | Kherki Daula | 3 | 3 | 130 |
| 3 | Kherki Daula | 4 | 6 | 270 |
| 4 | Kherki Daula | 7 | NULL | 350 |
| 5 | Vashi | 2 | 3 | 40 |
| 6 | Vashi | 4 | 6 | 130 |
TollRead
| read_id | tag_id | plaza | crossed_at | charged |
|---|---|---|---|---|
| 1 | 1 | Kherki Daula | 2025-03-01 08:00:00 | 80 |
| 2 | 2 | Kherki Daula | 2025-03-01 09:10:00 | 270 |
| 3 | 3 | Kherki Daula | 2025-03-01 10:20:00 | 270 |
| 4 | 4 | Kherki Daula | 2025-03-01 11:30:00 | 270 |
| 5 | 5 | Vashi | 2025-03-02 07:00:00 | 130 |
| 6 | 2 | Vashi | 2025-03-02 07:40:00 | 40 |
| 7 | 1 | Lonavala | 2025-03-02 12:00:00 | 320 |
| 8 | 3 | Vashi | 2025-03-02 18:15:00 | 40 |
| 9 | 4 | Vashi | 2025-03-03 06:30:00 | 130 |
Output
| read_id | vehicle_no | plaza | charged | expected_toll | verdict |
|---|---|---|---|---|---|
| 2 | KA05MN4321 | Kherki Daula | 270 | 130 | overcharged |
| 4 | TN09BX7788 | Kherki Daula | 270 | 350 | undercharged |
| 7 | MH12AB1234 | Lonavala | 320 | NULL | no_band |
| 8 | DL8CAF0099 | Vashi | 40 | 130 | undercharged |
| 9 | TN09BX7788 | Vashi | 130 | NULL | no_band |
How to solve Paytm: FASTag Reads Charged Off the Plaza's Axle-Band Rate
This is a band lookup: each read has to find the one row of TollBand whose range contains the vehicle's axle count at that plaza. The join condition is therefore a range test — t.axles >= b.min_axles AND (b.max_axles IS NULL OR t.axles <= b.max_axles) — plus equality on the plaza. The NULL branch is essential: the 9-axle truck at Kherki Daula falls in the open-ended "7 or more" band, and a plain t.axles <= b.max_axles would be NULL there and lose it. Because a plaza's bands never overlap, each read matches at most one band. The rate is the whole toll for the band — nothing is added across lower bands.
A LEFT JOIN keeps reads that match no band: Lonavala has no table at all, and Vashi's bands stop at 6 axles, so the truck read there gets NULL. Those are the no_band disputes. The WHERE clause keeps no-band reads and reads whose charge differs; a correct charge is dropped. The verdict CASE tests the NULL case first, then the direction of the difference.
The alternative looks the rate up with a correlated scalar subquery (COALESCE(max_axles, axles) stands in for "no upper limit") and drops correct charges with the NULL-safe <=>, which treats a NULL rate as a mismatch. Both cost one probe of the band table per read.
Reference solution (MySQL)
SELECT r.read_id, t.vehicle_no, r.plaza, r.charged, b.rate AS expected_toll,
CASE WHEN b.rate IS NULL THEN 'no_band'
WHEN r.charged > b.rate THEN 'overcharged'
ELSE 'undercharged' END AS verdict
FROM TollRead r
JOIN FastagTag t ON t.tag_id = r.tag_id
LEFT JOIN TollBand b
ON b.plaza = r.plaza
AND t.axles >= b.min_axles
AND (b.max_axles IS NULL OR t.axles <= b.max_axles)
WHERE b.rate IS NULL OR r.charged <> b.rate
ORDER BY r.read_idAnother way
SELECT read_id, vehicle_no, plaza, charged, expected_toll,
IF(expected_toll IS NULL, 'no_band', IF(charged > expected_toll, 'overcharged', 'undercharged')) AS verdict
FROM (
SELECT r.read_id, t.vehicle_no, r.plaza, r.charged,
(SELECT b.rate FROM TollBand b WHERE b.plaza = r.plaza AND t.axles BETWEEN b.min_axles AND COALESCE(b.max_axles, t.axles)) AS expected_toll
FROM TollRead r JOIN FastagTag t ON t.tag_id = r.tag_id
) x
WHERE NOT (charged <=> expected_toll)
ORDER BY read_id← Paytm: Gold Buyers Whose Weighted Cost Beat the Simple Average · Paytm Money: SIP Investor Retention Three Months Later by Cohort →