Rolling Averages and Anomaly Flags | Dataford Interview Questions

Your question is Rolling Averages and Anomaly Flags. Start with the requirements and the two tables on the right.

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

Problem

Amazon Services Operations Managers monitor hourly package throughput across fulfillment facilities. Write a PostgreSQL query that calculates a three-reading rolling average and identifies unusually high or low throughput.

Requirements

Join throughput logs to Amazon fulfillment facility metadata.
Calculate a rolling average using the current reading and up to the two preceding readings for the same facility, ordered by hour.
Label a reading ANOMALY when it differs from the rolling average by more than 20%. Label rows with fewer than three non-null readings in the frame as INSUFFICIENT_DATA; otherwise label them NORMAL.
Return results ordered by facility and hour. Round the displayed rolling average to two decimal places.

Schema

amazon_facilities

Column	Type	Description
facility_idPK	INT	Amazon fulfillment facility identifier
facility_name	VARCHAR(100)	Facility code used by operations teams
region	VARCHAR(50)	Geographic operating region

hourly_throughput_logs

Column	Type	Description
log_idPK	INT	Unique throughput log identifier
facility_id	INT	Facility associated with the measurement
logged_hour	TIMESTAMP	Start time of the hourly measurement
throughput_units	INT	Packages processed during the hour