Data engineering interviews at companies such as Uber, Amazon, and Walmart rarely focus only on writing simple SQL queries. Instead, they test whether candidates can translate real-world business rules into scalable data logic, optimize queries for performance, and explain design decisions clearly.

One common interview-style problem involves calculating toll revenue based on vehicle type and time-based pricing rules.

Problem Statement

You are given a table named toll_log that records every vehicle crossing a toll plaza. Each row represents one crossing event.

The table contains columns such as:

txn_id

vehicle_no

vehicle_type

crossing_time

Each vehicle crossing must be charged a toll depending on the vehicle type.

Toll Pricing Rules

Motorcycle → Free

Car → ₹40

Bus → ₹70

Truck → ₹80

However, there is an additional rule. If the same vehicle crosses the toll plaza again within 4 hours, a reduced toll is applied.

Reduced toll charges:

Car → ₹20

Bus → ₹30

Truck → ₹40

If the vehicle returns after more than 4 hours, the full toll charge applies again.

The objective is to calculate the total toll collected per day.

Key Insight

To solve this problem efficiently, we need to perform several steps:

➤ Identify the previous crossing time for each vehicle.

➤ Calculate the time difference between consecutive crossings.

➤ Determine whether the vehicle qualifies for a reduced toll.

➤ Aggregate the final toll amount by day.

This type of problem is best solved using window functions, rather than joins.

SQL Solution

The SQL solution uses a window function to retrieve the previous crossing time for each vehicle. Once that information is available, we apply conditional logic to determine the correct toll amount.

WITH base AS (

   SELECT

       txn_id,

       vehicle_no,

       vehicle_type,

       crossing_time,

       DATE(crossing_time) AS crossing_date,

       LAG(crossing_time) OVER (

           PARTITION BY vehicle_no

           ORDER BY crossing_time

       ) AS prev_crossing_time

   FROM toll_log

),

toll_calc AS (

   SELECT *,

       CASE

           WHEN vehicle_type = 'motorcycle' THEN 0

           WHEN prev_crossing_time IS NOT NULL

            AND TIMESTAMPDIFF(HOUR, prev_crossing_time, crossing_time) <= 4

           THEN CASE

                   WHEN vehicle_type = 'car' THEN 20

                   WHEN vehicle_type = 'bus' THEN 30

                   WHEN vehicle_type = 'truck' THEN 40

                END

           ELSE CASE

                   WHEN vehicle_type = 'car' THEN 40

                   WHEN vehicle_type = 'bus' THEN 70

                   WHEN vehicle_type = 'truck' THEN 80

                END

       END AS toll_amount

   FROM base

)

SELECT

   crossing_date,

   SUM(toll_amount) AS total_toll

FROM toll_calc

GROUP BY crossing_date;

This query works efficiently because the LAG() function retrieves the previous crossing per vehicle without requiring expensive joins. The result is a single-pass solution with linear complexity.

PySpark Solution

In distributed data environments, the same logic can be implemented using PySpark.

The approach is similar: calculate the previous crossing time using a window function, compute the time difference, determine the toll amount, and finally aggregate by date.

from pyspark.sql import functions as F

from pyspark.sql.window import Window

w = Window.partitionBy("vehicle_no").orderBy("crossing_time")

df2 = df.withColumn(

   "prev_crossing_time",

   F.lag("crossing_time").over(w)

)

df3 = df2.withColumn(

   "hours_diff",

   (F.col("crossing_time").cast("long") -

    F.col("prev_crossing_time").cast("long")) / 3600

)

df4 = df3.withColumn(

   "toll_amount",

   F.when(F.col("vehicle_type") == "motorcycle", 0)

    .when(

       (F.col("hours_diff") <= 4) & F.col("prev_crossing_time").isNotNull(),

       F.when(F.col("vehicle_type") == "car", 20)

        .when(F.col("vehicle_type") == "bus", 30)

        .when(F.col("vehicle_type") == "truck", 40)

    )

    .otherwise(

       F.when(F.col("vehicle_type") == "car", 40)

        .when(F.col("vehicle_type") == "bus", 70)

        .when(F.col("vehicle_type") == "truck", 80)

    )

)

result = df4.groupBy(

   F.to_date("crossing_time").alias("crossing_date")

).agg(

   F.sum("toll_amount").alias("total_toll")

)

result.show()

This solution scales well in distributed systems because it partitions the computation by vehicle number and processes records sequentially.

Why Window Functions Are Important?

Interviewers often expect candidates to recognize that window functions are more efficient than self-joins for time-based comparisons.

Self-joins can cause large data shuffles and increased memory consumption. Window functions, on the other hand, process data in a single pass and scale better for large datasets.

Scalability Considerations

At production scale, several additional challenges must be addressed.

One common issue is data skew, where certain vehicles (such as fleet vehicles or taxis) cross the toll many times in a short period. This can cause uneven partition sizes during window operations.

A useful optimization is to limit the window scope by including the date in the partition key, for example partitioning by both vehicle number and crossing date. This reduces the amount of data processed in each window.

Another optimization involves converting timestamps into numeric values (such as epoch seconds) before performing calculations. Numeric operations are typically faster than repeated timestamp function calls.

Configuration-Driven Pricing

In real production systems, toll prices are rarely hardcoded.

A more scalable approach is to store pricing rules in a dimension table and join it with the event data. This allows pricing changes without modifying the code.

In distributed systems like Spark, the pricing table can be broadcast to avoid expensive joins.

Incremental Processing

Reprocessing the entire dataset every day can become expensive as data volume grows.

A better approach is incremental processing. Instead of recomputing the full history, the pipeline can store the last crossing time per vehicle and process only newly arriving data.

This reduces computation from processing all historical data to processing only the latest records.

Data Quality Considerations

Production pipelines must also handle data quality issues such as late-arriving events, duplicate records, or out-of-order timestamps.

Common mitigation techniques include deduplicating records using composite keys such as vehicle number and timestamp, enforcing event ordering using row numbering, and validating timestamps before calculations.

Conclusion

This toll plaza revenue problem illustrates how data engineering interview questions combine business logic with scalable data processing techniques.

The solution requires understanding window functions, time-based comparisons, and efficient aggregation strategies. It also encourages candidates to think beyond the immediate query and consider real-world production challenges such as scalability, skew handling, and incremental processing.

Problems like this test not only SQL or Spark knowledge but also a candidate’s ability to design reliable and scalable data systems.