7-Day Rolling Average Visits | Dataford Interview Questions

Write a SQL query using window functions to calculate a 7-day rolling average of daily website visits.

Use the website_visits table. Multiple rows may exist for the same date, and dates without records should be treated as zero visits. Include every calendar date from the minimum through maximum recorded date.

Output

One row per calendar date
Columns: visit_date, daily_visits, and rolling_average
The rolling average includes the current date and up to the six preceding calendar dates
Order by visit_date ascending

Schema

website_visits

Column	Type	Description
visit_idPK	INT	Unique source record identifier
visit_date	DATE	Date associated with the website visit count
visits	INT	Number of visits recorded for the date

Your question is 7-Day Rolling Average Visits. 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 SQL query using window functions to calculate a 7-day rolling average of daily website visits.

Output

One row per calendar date
Columns: visit_date, daily_visits, and rolling_average
The rolling average includes the current date and up to the six preceding calendar dates
Order by visit_date ascending

Schema

website_visits

Column	Type	Description
visit_idPK	INT	Unique source record identifier
visit_date	DATE	Date associated with the website visit count
visits	INT	Number of visits recorded for the date

Your question is 7-Day Rolling Average Visits. 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.