Rolling Average Over Last 30 Days | Dataford Interview Questions

Write a query using SQL window functions to calculate the rolling average of customer engagement over the last 30 days.

Use the engagement_daily table. Include each recorded engagement row, and treat the 30-day period as inclusive of the current date. Rows with a NULL engagement score remain in the output but do not contribute to the average.

Output

Return customer_id, engagement_date, engagement_score, rolling_average, and engagement_id.
Return one row per engagement record, ordered by customer_id, engagement_date, and engagement_id.

Schema

engagement_daily

Column	Type	Description
engagement_idPK	INTEGER	Unique engagement record identifier
customer_id	VARCHAR(30)	Customer associated with the engagement
engagement_date	DATE	Date on which the engagement was recorded
engagement_score	NUMERIC(10,2)	Measured customer engagement score

Your question is Rolling Average Over Last 30 Days. Start with the requirements and the one table 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

Write a query using SQL window functions to calculate the rolling average of customer engagement over the last 30 days.

Output

Return customer_id, engagement_date, engagement_score, rolling_average, and engagement_id.
Return one row per engagement record, ordered by customer_id, engagement_date, and engagement_id.

Schema

engagement_daily

Column	Type	Description
engagement_idPK	INTEGER	Unique engagement record identifier
customer_id	VARCHAR(30)	Customer associated with the engagement
engagement_date	DATE	Date on which the engagement was recorded
engagement_score	NUMERIC(10,2)	Measured customer engagement score

Your question is Rolling Average Over Last 30 Days. Start with the requirements and the one table 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.