Rolling 7-Day Orders SQL | Dataford Interview Questions

How would you use SQL window functions to calculate a rolling 7-day average of orders per user for MealPal?

Use the provided MealPal users and orders data. Include calendar days with no orders as zero-order days so the average represents a complete seven-day period.

Output

One row per user and calendar day from January 1 through January 7, 2024.
Columns: user_id, user_name, order_date, daily_orders, and rolling_7_day_avg.
Sort by user_id, then order_date.

Schema

users

Column	Type	Description
user_idPK	INT	Unique MealPal user identifier
user_name	VARCHAR(100)	MealPal user's display name
email	VARCHAR(255)	User email address

orders

Column	Type	Description
order_idPK	INT	Unique MealPal order identifier
user_id	INT	User who placed the order
order_date	DATE	Calendar date when the order was placed
status	VARCHAR(20)	Order processing status

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