Window Functions for Rolling Metrics

Explain window functions in SQL and provide a practical use case for calculating rolling averages over time-series data.

Use the table below as the input source. Write a SQL query that returns a rolling 3-day average for each device's metric value, ordered by device and timestamp.

Output

1. One row per device reading
2. Columns: device_id, reading_ts, metric_value, rolling_3_day_avg
3. Include all rows from the input table
4. Sort by device_id, then reading_ts

Output details

• Use the current reading and the preceding two readings for each device, regardless of calendar-day gaps.
• Sort device_id and reading_ts in ascending order.
• Ignore NULL metric values when calculating averages and return NULL when no values exist in the frame.
• Return rolling_3_day_avg without rounding, preserving the average's full numeric precision.