Detect Rapid Repeat Events

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

Problem

You are analysing vehicle movement through an Uber toll plaza. toll_log holds every crossing.

For each crossing, report the same vehicle's previous crossing, how many hours elapsed since it, and whether that makes this a repeat crossing within 4 hours.

Tables

Table: toll_log

| Column | Type |
| --- | --- |
| toll_id | INT |
| vehicle_id | VARCHAR |
| toll_time | DATETIME |

Rules

• previous_toll_time is the same vehicle's immediately preceding crossing. Other vehicles are irrelevant.
• hours_since_previous is the elapsed hours, rounded to 2 decimals.
• repeat_within_4_hours is 'YES' when the gap is 4 hours or less, otherwise 'NO'. Exactly 4 hours qualifies.
• A vehicle's first crossing has NULL for both previous_toll_time and hours_since_previous, and 'NO' for the flag.
• Every input row appears in the output. Nothing is filtered.

Careful: the flag is decided from the timestamps, not from the rounded

hours_since_previous. A gap of 4 hours and 1 second rounds to 4.00 but is

still 'NO'. Deriving the flag from the rounded column is the classic way to get

this wrong.

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 11:00:00 |

Expected Output

| vehicle_id | toll_time | previous_toll_time | hours_since_previous | repeat_within_4_hours |
| --- | --- | --- | --- | --- |
| V101 | 2024-01-15 08:00:00 | NULL | NULL | NO |
| V101 | 2024-01-15 10:30:00 | 2024-01-15 08:00:00 | 2.50 | YES |
| V101 | 2024-01-15 15:00:00 | 2024-01-15 10:30:00 | 4.50 | NO |
| V102 | 2024-01-15 09:00:00 | NULL | NULL | NO |
| V102 | 2024-01-15 14:00:00 | 2024-01-15 09:00:00 | 5.00 | NO |
| V103 | 2024-01-15 07:00:00 | NULL | NULL | NO |
| V103 | 2024-01-15 11:00:00 | 2024-01-15 07:00:00 | 4.00 | YES |

Explanation

V101 returns after 2h30m -> 2.50, YES. Its next gap is 4h30m -> 4.50,

NO.

V102 waits 5 hours -> 5.00, NO.

V103 returns after exactly 4 hours -> 4.00, YES, because the boundary

is inclusive.

Every vehicle's first crossing has nothing before it, so both time columns are

NULL and the flag is NO.

Constraints

• Must use a window function -- LAG() to reach the previous crossing
• Must PARTITION BY vehicle_id
• Do not use a self-join
• Do not remove any toll transactions
• Sort by vehicle_id ASC, then toll_time ASC

What this SQL challenge teaches you

“Detect Rapid Repeat Events” is a medium-level SQL challenge focused on Window Functions. Working through it gives you hands-on practice with LAG, window functions, PARTITION BY, TIMESTAMPDIFF, ROUND, NULL handling — 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

• LAG
• window functions
• PARTITION BY
• TIMESTAMPDIFF
• ROUND
• NULL handling

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. Each row needs the row before it for the SAME vehicle. That is LAG() -- and the constraints rule out a self-join anyway.
2. LAG(toll_time) OVER (PARTITION BY vehicle_id ORDER BY toll_time) is the previous crossing. PARTITION BY keeps vehicles apart; ORDER BY defines what 'previous' means.
3. For the elapsed hours, work in SECONDS and divide: ROUND(TIMESTAMPDIFF(SECOND, prev, toll_time) / 3600, 2). TIMESTAMPDIFF(HOUR, ...) truncates -- it would turn 2h30m into 2, not 2.50.
4. For the flag, compare the timestamps directly: prev >= toll_time - INTERVAL 4 HOUR. Use >= so exactly 4 hours counts as YES.
5. Do NOT write `hours_since_previous <= 4`. A gap of 4h 1s rounds to 4.00 and would wrongly pass. The rounded column is for display; the decision belongs to the raw timestamps.

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