7-Day Rolling Average with Window Functions | Dataford Interview Questions

Write a SQL query using window functions to calculate the 7-day rolling average of order volume per user.

Use the provided users and orders data. Include every user for every calendar date between the earliest and latest order dates, treating days without orders as zero volume.

Output

One row per user and calendar date.
Columns: user_id, order_date, order_volume, and rolling_7_day_avg.
Include users with no orders and dates with zero orders.
Order by user_id, then order_date ascending.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
user_name	VARCHAR(100)	User display name

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
user_id	INT	User who placed the order
order_date	DATE	Calendar date when the order was placed

Your question is 7-Day Rolling Average with Window Functions. 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