Rolling Telemetry Window SQL | Dataford Interview Questions

Write a SQL query using window functions to compute a 7-day rolling average of telemetry events by customer and rank the top customers each week. Use the supplied customer and telemetry event data, include customers with no events, and treat missing event days as zero. Return the top three rank positions per week, including ties.

Output

One row per included customer and calendar week.
Columns: customer_id, customer_name, week_start, weekly_event_count, rolling_7_day_avg, and weekly_rank.
Sort by week_start, weekly_rank, and customer_id.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer display name

telemetry_events

Column	Type	Description
event_idPK	INT	Unique telemetry event identifier
customer_id	INT	Customer associated with the event
occurred_at	TIMESTAMP	Timestamp when the telemetry event occurred
event_type	VARCHAR(50)	Telemetry event classification

Your question is Rolling Telemetry Window SQL. 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