Rank Plants by Anomaly Rate | Dataford Interview Questions

Write a SQL query using window functions to compute a 7-day rolling average and rank plants by their weekly anomaly rate at Siemens.

Use the provided plants and plant_readings tables. Treat a reading with a true anomaly_flag as anomalous. Calculate rates using all readings in each plant-week, including readings whose sensor value is NULL.

Output

One row per plant and reading date with available readings.
Return week_start, plant_name, reading_date, daily_average, rolling_7_day_average, weekly_anomaly_rate, and weekly_anomaly_rank.
Sort by week_start, weekly_anomaly_rank, plant_name, and reading_date.

Schema

plants

Column	Type	Description
plant_idPK	INT	Unique plant identifier
plant_name	VARCHAR(100)	Plant name
region	VARCHAR(50)	Geographic region of the plant

plant_readings

Column	Type	Description
reading_idPK	INT	Unique reading identifier
plant_id	INT	Referenced plant identifier
reading_date	DATE	Date when the reading was recorded
sensor_value	NUMERIC(10,2)	Measured sensor value
anomaly_flag	BOOLEAN	Whether the reading was flagged as anomalous

Your question is Rank Plants by Anomaly Rate. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem