7-Day Rolling Average with Window Functions

MediumSQL · PostgreSQL

Problem

Siemens Healthineers operations analysts need to monitor downtime for the SOMATOM CT product line. Write a PostgreSQL query that calculates daily unplanned operational downtime and its seven-day rolling average.

Requirements

Restrict results to active equipment assigned to the SOMATOM CT product line.
Include every date from 2025-01-01 through 2025-01-10, including dates with no downtime.
Exclude planned downtime and ignore events from inactive equipment.
Aggregate downtime across equipment for each date.
Use a window function to calculate the average of the current day and six preceding calendar rows.
Return results in ascending date order, with downtime values rounded to two decimal places.

Schema

product_lines

Column	Type	Description
product_line_idPK	INT	Product line identifier
product_line_name	VARCHAR(100)	Product line name

equipment

Column	Type	Description
equipment_idPK	INT	Equipment identifier
product_line_id	INT	Assigned product line
equipment_code	VARCHAR(30)	Equipment code
status	VARCHAR(20)	Equipment lifecycle status

downtime_events

Column	Type	Description
event_idPK	INT	Downtime event identifier
equipment_id	INT	Equipment affected by the event
downtime_date	DATE	Date of downtime
downtime_minutes	NUMERIC(10,2)	Duration of downtime in minutes
downtime_type	VARCHAR(20)	Planned or unplanned downtime

calendar_dates

Column	Type	Description
calendar_datePK	DATE	Date in the reporting calendar

Tablesproduct_linesequipmentdowntime_eventscalendar_dates