7-Day Rolling Network Traffic

Juniper Networks uses daily telemetry from Juniper Mist and Junos devices to monitor network demand. Write a PostgreSQL query that calculates a seven-day rolling average of daily traffic and compares it with the immediately preceding seven-day period.

Requirements

1. Aggregate traffic by calendar date, treating missing dates and NULL traffic values as zero.
2. Generate a contiguous daily time series so each rolling window represents seven calendar days.
3. Calculate the current seven-day average, the previous seven-day average, the absolute change, and the percentage change.
4. Return only dates with two complete, non-overlapping seven-day periods, ordered chronologically.

Output details

• Return columns in this order: traffic_date, daily_traffic_gb, current_7_day_avg_gb, previous_7_day_avg_gb, change_gb, and change_percent.
• Round all numeric output columns to exactly two decimal places.
• Report change_gb as the signed current average minus the previous average, not as an absolute value.
• Return change_percent as NULL when the previous seven-day total is zero.