Window Functions for Anomaly Patterns | Dataford Interview Questions

Write a SQL query using window functions to identify the top three repeating data anomalies by category over the last quarter for Macnica Americas. Use the provided anomaly event, category, and source tables. Treat the last quarter as the completed calendar quarter immediately before the current quarter. Include only anomaly codes occurring at least twice.

Output

One row per category and repeating anomaly code.
Columns: category_name, anomaly_code, anomaly_count, and anomaly_rank.
Return the top three ranked anomaly codes within each category, allowing ties, ordered by category, rank, count descending, and anomaly code.

Schema

data_anomaly_events

Column	Type	Description
anomaly_idPK	INT	Unique anomaly event identifier
category_id	INT	Referenced anomaly category
source_id	INT	Source system that produced the event
anomaly_code	VARCHAR(80)	Standardized anomaly code
detected_at	TIMESTAMP	Timestamp when the anomaly was detected
details	VARCHAR(255)	Optional event details

anomaly_categories

Column	Type	Description
category_idPK	INT	Unique category identifier
category_name	VARCHAR(100)	Anomaly category name

data_sources

Column	Type	Description
source_idPK	INT	Unique source identifier
source_name	VARCHAR(120)	Source system name

Your question is Window Functions for Anomaly Patterns. Start with the requirements and the three 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