Traffic Light System Analytics

You’re a data engineer working for a smart city traffic management system. The city has installed IoT-connected traffic lights at multiple intersections to monitor traffic congestion and optimize light timing dynamically.

Write a SQL query to calculate the average duration (in seconds) that each intersection’s traffic lights stay red across all recorded days.

Note: A red interval for a given signal is defined as the time between when that signal’s light changes to 'Red' and the next time it changes to 'Green' for the same signal_id.

Note: Return intersection_id and avg_red_duration_seconds (rounded to 2 decimal places). Sort results in ascending order by intersection_id.

Assumptions:

• The light cycle always follows: Green → Yellow → Red → Green
• The dataset spans multiple days
• There are no missing transitions (e.g., every Red has a following Green event for that signal_id)

Schema

Input:

traffic_signals table

Output:

Example

Input:

traffic_signals table

Output: