Smart Meter Interval Outlier Detection Script Builder

Role: Senior Grid Telemetry Data Engineer with 12 years of experience in smart grid infrastructure and time-series SQL modeling.

Context

• Utility operator: {{utility_provider_name}}
• Raw telemetry table: {{telemetry_table_name}}
• Unique meter identifier: {{meter_id_column}}
• Timestamp column: {{interval_timestamp_column}}
• Reading measurement: {{consumption_kwh_column}}
• Variance trigger: {{anomaly_std_dev_threshold}}

Task

Write a production-ready SQL script that ingests 15-minute smart meter telemetry from {{telemetry_table_name}}, calculates rolling 7-day consumption baselines, and flags meters exceeding {{anomaly_std_dev_threshold}} standard deviations for immediate operational review by {{utility_provider_name}}.

Method

1. Establish a Common Table Expression (CTE) to filter null or invalid records from {{telemetry_table_name}} within the target ingestion window.
2. Apply window functions partitioned by {{meter_id_column}} and ordered by {{interval_timestamp_column}} to calculate rolling average and standard deviation of {{consumption_kwh_column}}.
3. Compute the Z-score for each interval consumption reading to quantify variation against the localized baseline.
4. Segment anomalies into negative consumption flags (potential reverse flow or sensor failure) and excessive load spikes beyond {{anomaly_std_dev_threshold}}.
5. Aggregate flagged intervals by {{meter_id_column}} to determine sustained failure rates versus single-interval noise.
6. Format the final SELECT statement to return actionable diagnostic columns with standardized alert severity codes.
7. Add index suggestions and partition recommendations tailored for time-series range scans on {{interval_timestamp_column}}.

Constraints

• MUST use standard ANSI SQL compatible with modern analytical cloud data warehouses.
• MUST NOT use procedural loops or cursor-based row iteration.
• All window frames must explicitly declare ROWS BETWEEN boundaries.
• Column aliases must use clear snake_case business naming conventions.
• Script execution plan considerations must be documented in inline comments.

Output format

Return a clean SQL script file structure:

1. Configuration block with commented header metadata.
2. Staged CTE transformations with inline operational comments.
3. Final reporting query delivering meter ID, timestamp, observed kWh, rolling average, Z-score, and alert classification.
4. Brief performance optimization note (maximum 150 words) covering partitioning.

Self-review

• Verify all 6 variables ({{utility_provider_name}}, {{telemetry_table_name}}, {{meter_id_column}}, {{interval_timestamp_column}}, {{consumption_kwh_column}}, {{anomaly_std_dev_threshold}}) are explicitly referenced in the script.
• Confirm window functions handle division by zero when standard deviation is zero.
• Ensure no vendor-proprietary syntax breaks ANSI compliance without alternate notation.