Rolling Failure Rate With SQL

MediumSQL · PostgreSQL

Problem

Business Context

Atlas Copco SMARTLINK provides operational data for monitoring industrial equipment. The service team wants to identify machines with elevated failure rates by tracking daily rates and their rolling 30-day averages.

Task

Write a PostgreSQL query that calculates the daily failure rate and rolling average failure rate for active SMARTLINK-connected machines between 2025-01-01 and 2025-02-28.

Requirements

Join machine metadata with daily operational metrics and include only active machines.
Calculate the daily failure rate as failures divided by inspections, expressed as a percentage. If inspections are zero or the failure count is unavailable, return NULL.
Use a window function to calculate the average daily failure rate over the current date and the preceding 29 calendar days for each machine.
Return results ordered by machine and metric date. A missing failure count must remain unavailable rather than being treated as zero.

Schema

smartlink_machines

Column	Type	Description
machine_idPK	INTEGER	Unique machine identifier
machine_name	VARCHAR(100)	Machine name shown in SMARTLINK
is_active	BOOLEAN	Whether the machine is currently active

machine_daily_metrics

Column	Type	Description
metric_idPK	INTEGER	Unique daily metric record
machine_id	INTEGER	Referenced machine identifier
metric_date	DATE	Date of the measurements
inspection_count	INTEGER	Number of inspections
failure_count	INTEGER	Number of failures, possibly unavailable

Tablessmartlink_machinesmachine_daily_metrics