Paytm: FASTag Reads Charged Off the Plaza's Axle-Band Rate

Hard
867.

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. 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

ColumnType
tag_idint
vehicle_novarchar
axlesint

Primary key: tag_id.

One row per FASTag sticker with the axle count on the vehicle's registration.

Table: TollBand

ColumnType
band_idint
plazavarchar
min_axlesint
max_axlesint
rateint

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

ColumnType
read_idint
tag_idint
plazavarchar
crossed_atdatetime
chargedint

Primary key: read_id.

One row per debited toll read; tag_id always names a tag.

Example

The rows must come back in the order shown.

FastagTag

tag_idvehicle_noaxles
1MH12AB12342
2KA05MN43213
3DL8CAF00996
4TN09BX77889
5RJ14CD55664

TollBand

band_idplazamin_axlesmax_axlesrate
1Kherki Daula2280
2Kherki Daula33130
3Kherki Daula46270
4Kherki Daula7NULL350
5Vashi2340
6Vashi46130

TollRead

read_idtag_idplazacrossed_atcharged
11Kherki Daula2025-03-01 08:00:0080
22Kherki Daula2025-03-01 09:10:00270
33Kherki Daula2025-03-01 10:20:00270
44Kherki Daula2025-03-01 11:30:00270
55Vashi2025-03-02 07:00:00130
62Vashi2025-03-02 07:40:0040
71Lonavala2025-03-02 12:00:00320
83Vashi2025-03-02 18:15:0040
94Vashi2025-03-03 06:30:00130

Output

read_idvehicle_noplazachargedexpected_tollverdict
2KA05MN4321Kherki Daula270130overcharged
4TN09BX7788Kherki Daula270350undercharged
7MH12AB1234Lonavala320NULLno_band
8DL8CAF0099Vashi40130undercharged
9TN09BX7788Vashi130NULLno_band
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: 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

ColumnType
tag_idint
vehicle_novarchar
axlesint

Primary key: tag_id.

One row per FASTag sticker with the axle count on the vehicle's registration.

Table: TollBand

ColumnType
band_idint
plazavarchar
min_axlesint
max_axlesint
rateint

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

ColumnType
read_idint
tag_idint
plazavarchar
crossed_atdatetime
chargedint

Primary key: read_id.

One row per debited toll read; tag_id always names a tag.

Examples

Example 1

FastagTag

tag_idvehicle_noaxles
1MH12AB12342
2KA05MN43213
3DL8CAF00996
4TN09BX77889
5RJ14CD55664

TollBand

band_idplazamin_axlesmax_axlesrate
1Kherki Daula2280
2Kherki Daula33130
3Kherki Daula46270
4Kherki Daula7NULL350
5Vashi2340
6Vashi46130

TollRead

read_idtag_idplazacrossed_atcharged
11Kherki Daula2025-03-01 08:00:0080
22Kherki Daula2025-03-01 09:10:00270
33Kherki Daula2025-03-01 10:20:00270
44Kherki Daula2025-03-01 11:30:00270
55Vashi2025-03-02 07:00:00130
62Vashi2025-03-02 07:40:0040
71Lonavala2025-03-02 12:00:00320
83Vashi2025-03-02 18:15:0040
94Vashi2025-03-03 06:30:00130

Output

read_idvehicle_noplazachargedexpected_tollverdict
2KA05MN4321Kherki Daula270130overcharged
4TN09BX7788Kherki Daula270350undercharged
7MH12AB1234Lonavala320NULLno_band
8DL8CAF0099Vashi40130undercharged
9TN09BX7788Vashi130NULLno_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_id

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