Substation Feeder Peak Load Window Aggregation Generator

Role: Lead Grid Capacity Planning Analyst specializing in distribution system load profiling and time-series SQL analytics.

Context

• Grid balancing authority: {{grid_operator_name}}
• Source substation load table: {{substation_load_table}}
• Feeder circuit identifier: {{feeder_circuit_column}}
• Event timestamp column: {{reading_time_column}}
• Active load metric: {{megawatt_load_column}}
• Historical evaluation span: {{aggregation_window_days}}

Task

Develop an analytical SQL script that scans {{substation_load_table}} over {{aggregation_window_days}} days to isolate peak coincident loading events, identify top constrained circuits per feeder, and export capacity utilization metrics for {{grid_operator_name}}.

Method

1. Filter telemetry records from {{substation_load_table}} across the trailing {{aggregation_window_days}} days relative to {{reading_time_column}}.
2. Deduplicate simultaneous sensor events using an initial staging CTE with deterministic tie-breaking.
3. Compute rolling 1-hour and 4-hour moving averages for {{megawatt_load_column}} using window frames partitioned by {{feeder_circuit_column}}.
4. Apply DENSE_RANK() partitioned by feeder circuit to isolate top 5 load peaks across the observation window.
5. Calculate circuit headroom percentage against dynamic peak thresholds per feeder group.
6. Construct a final consolidated dataset pairing peak timestamps, maximum MW readings, and feeder utilization percentages.

Constraints

• MUST avoid non-deterministic functions in window frame ordering clauses.
• MUST filter out negative or null load measurements prior to aggregation.
• Output SQL must be modularized using CTEs rather than nested subqueries.
• Query execution must minimize full table scans via explicit temporal filtering on {{reading_time_column}}.

Output format

Provide the solution as an executable SQL script:

1. Header documentation comment detailing parameters and intended engine dialect.
2. Staging CTEs for data cleaning and moving average computation.
3. Peak ranking CTE with rank partitions.
4. Final output query returning feeder ID, peak timestamp, peak MW, and moving average metrics.

Self-review

• Ensure all variables ({{grid_operator_name}}, {{substation_load_table}}, {{feeder_circuit_column}}, {{reading_time_column}}, {{megawatt_load_column}}, {{aggregation_window_days}}) are accurately represented.
• Verify that moving average calculations do not cause window frame boundary overflow.
• Check that ranking filters correctly isolate only the top capacity events per feeder circuit.