Identify every lane‑change event. A lane change occurs when the lane_id changes from one value to another and the new lane remains stable for at least 1 second. Return session_id, change_timestamp_ms, from_lane, to_lane, and speed_at_change. Additional challenge: include session_id, timestamp_ms, lane_id, and speed for all records.

Use a window function to detect lane_id changes and compute the time difference between consecutive rows. First, order the data by session_id and timestamp_ms. Then, for each row, compare lane_id to the previous row’s lane_id. When a change is detected, start a timer. Use a second window to find the first timestamp where the lane_id remains the same for at least 1,000 ms. The change_timestamp_ms is the timestamp of the first stable row. Finally, select session_id, change_timestamp_ms, the previous lane_id as from_lane, the new lane_id as to_lane, and the speed at change. Complexity is O(n) with a single scan and a few window operations. Example SQL (PostgreSQL):

WITH changes AS (

SELECT

session_id,
timestamp_ms,
lane_id,
speed,
LAG(lane_id) OVER (PARTITION BY session_id ORDER BY timestamp_ms) AS prev_lane,
LAG(timestamp_ms) OVER (PARTITION BY session_id ORDER BY timestamp_ms) AS prev_ts

FROM traffic

), stable AS (

SELECT

session_id,
timestamp_ms,
lane_id,
speed,
prev_lane,
prev_ts,
timestamp_ms - prev_ts AS delta

FROM changes

WHERE lane_id <> prev_lane

), final AS (

SELECT

session_id,
timestamp_ms AS change_timestamp_ms,
prev_lane AS from_lane,
lane_id AS to_lane,
speed AS speed_at_change

FROM stable

WHERE delta >= 1000

)

SELECT * FROM final;