Rolling 7-Day Member Engagement SQL | Dataford Interview Questions

Write an SQL query using window functions to calculate a rolling 7-day average of member engagement by date at Blue Cross Blue Shield Of Massachusetts.

Assume members identifies valid members and member_engagement stores engagement scores by member and date. Include only records with a matching member, and treat NULL scores as missing values.

Output

One row per date represented by valid engagement records.
Return engagement_date, daily_avg_engagement, and rolling_7_day_avg.
The rolling average must cover the current date and the six preceding calendar days, excluding NULL scores. Order by engagement_date ascending.

Schema

members

Column	Type	Description
member_idPK	INT	Unique member identifier
member_group	VARCHAR(50)	Member reporting group

member_engagement

Column	Type	Description
engagement_idPK	INT	Unique engagement record identifier
member_id	INT	Member associated with the engagement record
engagement_date	DATE	Date of the engagement measurement
engagement_score	NUMERIC(10,2)	Member engagement score for the date

Your question is Rolling 7-Day Member Engagement 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