SQL 7-Day Rolling Impressions | Dataford Interview Questions

Given a table of daily user logins and ad impressions, calculate the 7-day rolling average of impressions per active user.

Treat NULL ad impressions as zero. A day with zero active users has a NULL daily ratio and is excluded from the average, while remaining in the output.

Output

One row per reporting date with activity_date, active_users, ad_impressions, impressions_per_active_user, and rolling_7_day_avg
Use the calendar-date range from the current date through the prior six days
Sort by activity_date ascending.

Schema

daily_user_metrics

Column	Type	Description
activity_datePK	DATE	Reporting date
active_users	INT	Number of users who logged in that day
ad_impressions	INT	Total ad impressions recorded that day

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