Detect Repeat Toll Crossings

SQL coding challenge · Difficulty: medium · Topic: Window Functions · +150 XP

Problem

You are on the team that monitors Uber's toll plazas. Every crossing writes one row into toll_log.

A vehicle that comes back through the same plaza soon after its last crossing is worth flagging -- it usually means a short round trip rather than a through journey.

For every crossing, report the vehicle's next crossing and whether that next crossing happened within 4 hours.

Tables

Table: toll_log

| Column | Type | Description |
| --- | --- | --- |
| toll_id | INT | Unique toll transaction id |
| vehicle_id | VARCHAR | Vehicle identifier |
| toll_time | DATETIME | When the vehicle crossed the plaza |

Rules

• next_toll_time is the vehicle's own next crossing in time order. Other vehicles are irrelevant.
• crossed_again_within_4_hours is 'YES' when the next crossing is 4 hours or less after this one, otherwise 'NO'.
• A gap of exactly 4 hours counts as YES. Four hours and one second is 'NO'.
• The last crossing of every vehicle still appears, with next_toll_time as NULL and the flag 'NO'.

Example Input

| toll_id | vehicle_id | toll_time |
| --- | --- | --- |
| 1 | V101 | 2024-01-15 08:00:00 |
| 2 | V101 | 2024-01-15 10:30:00 |
| 3 | V101 | 2024-01-15 15:00:00 |
| 4 | V102 | 2024-01-15 09:00:00 |
| 5 | V102 | 2024-01-15 14:00:00 |
| 6 | V103 | 2024-01-15 07:00:00 |
| 7 | V103 | 2024-01-15 10:59:00 |
| 8 | V103 | 2024-01-15 18:00:00 |

Expected Output

| vehicle_id | toll_time | next_toll_time | crossed_again_within_4_hours |
| --- | --- | --- | --- |
| V101 | 2024-01-15 08:00:00 | 2024-01-15 10:30:00 | YES |
| V101 | 2024-01-15 10:30:00 | 2024-01-15 15:00:00 | NO |
| V101 | 2024-01-15 15:00:00 | NULL | NO |
| V102 | 2024-01-15 09:00:00 | 2024-01-15 14:00:00 | NO |
| V102 | 2024-01-15 14:00:00 | NULL | NO |
| V103 | 2024-01-15 07:00:00 | 2024-01-15 10:59:00 | YES |
| V103 | 2024-01-15 10:59:00 | 2024-01-15 18:00:00 | NO |
| V103 | 2024-01-15 18:00:00 | NULL | NO |

Explanation

V101 crosses at 08:00 and again at 10:30 -- a gap of 2h30m, so YES. From

10:30 the next is 15:00, a gap of 4h30m, so NO. 15:00 is its last crossing,

so next_toll_time is NULL and the flag is NO.

V102 has a 5-hour gap, so both its rows are NO.

V103 goes 07:00 to 10:59 -- 3h59m, just inside the window, so YES.

Notes

• Return: vehicle_id, toll_time, next_toll_time, crossed_again_within_4_hours
• Sort by vehicle_id ASC, then toll_time ASC
• Every input row produces exactly one output row -- nothing is filtered out
• Function to use: LEAD(toll_time) OVER (PARTITION BY vehicle_id ORDER BY toll_time)

What this SQL challenge teaches you

“Detect Repeat Toll Crossings” is a medium-level SQL challenge focused on Window Functions. Working through it gives you hands-on practice with LEAD, window functions, PARTITION BY, INTERVAL, NULL handling, gap analysis — the kind of transformation you are asked to write in real data engineering work and in technical interviews. You can solve it directly in the browser: the dataset is pre-loaded, so you write the query or DataFrame code, run it, and compare your output against the expected result immediately.

Concepts covered

• LEAD
• window functions
• PARTITION BY
• INTERVAL
• NULL handling
• gap analysis

How to approach it

If you get stuck, work through these steps in order before looking at a full solution — each one narrows the problem down:

1. You need each row to see the NEXT row for the same vehicle. That is exactly what LEAD() does -- no self-join required.
2. Partition so each vehicle is looked at on its own, and order so 'next' means next in time: LEAD(toll_time) OVER (PARTITION BY vehicle_id ORDER BY toll_time).
3. For the flag, compare the LEAD value with toll_time + INTERVAL 4 HOUR. Use <= so a gap of exactly 4 hours counts as YES.
4. The last crossing of a vehicle has no next row, so LEAD returns NULL. Any comparison with NULL is NULL -- which is not true -- so a plain CASE ... ELSE 'NO' already prints NO for it. Do not filter those rows out; they belong in the result.

Where this comes up

Variations of this problem have been reported in interviews at Uber. Interviewers use it to check whether you can express the logic cleanly and reason about correctness on edge cases such as ties, nulls and empty groups.

How to practise it on PySpark.in

Open the challenge, write your SQL query in the editor and press Run to execute it against the sample dataset. Submitting checks your output against every test case, including hidden ones, so you find out straight away whether your logic holds up. You can retry as often as you like, and each solved challenge adds to your XP.

Related SQL challenges

• Top 3 Products per Category
• Running Total Revenue
• Median Salary per Department
• Latest Order Per Customer
• 3-Day Rolling Sum of Sales
• 7-Day Rolling Purchase Amount by Customer

Frequently asked questions

Do I need to install Spark or a database to solve this?

No. The SQL environment runs in your browser with the sample data already loaded, so there is nothing to install or configure.

Is this challenge free?

Yes - the problem, the sample dataset, the hints and unlimited test runs are free.

What level is it?

It is rated medium and covers Window Functions.

Solve this challenge free on PySpark.in