Rolling 7-Day SQL Window | Dataford Interview Questions

Write a SQL query using window functions to calculate a rolling 7-day average for each product or user.

Use entities and daily_metrics. Include only products and users with metric observations. The rolling period includes the current date and the preceding six calendar days; missing dates do not contribute values.

Output

One row per product or user metric observation
Columns: entity_name, entity_type, metric_date, metric_value, rolling_7_day_avg
Order by entity_type, entity_id, and metric_date ascending

Schema

entities

Column	Type	Description
entity_idPK	INT	Unique identifier for a product or user
entity_type	VARCHAR(20)	Entity classification, such as Product or User
entity_name	VARCHAR(100)	Display name of the entity

daily_metrics

Column	Type	Description
metric_idPK	INT	Unique identifier for a daily metric observation
entity_id	INT	Referenced product or user identifier
metric_date	DATE	Date of the metric observation
metric_value	DECIMAL(10,2)	Metric value recorded for the entity and date

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