7-Day Rolling Average Visits

MediumSQL · PostgreSQL00:00

Practice interviewer

In session

5 left

00:00

Your question is 7-Day Rolling Average Visits. Start with the requirements and the one table on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

You need to log in / sign up to run or submit.

Problem

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

Tableswebsite_visits

Your solutionPostgreSQL

You need to log in / sign up to run or submit.

Run a query to see results