7-Day Rolling Average with Window Functions

MediumSQL · PostgreSQL

Problem

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.

Output details

Use inclusive earliest and latest order dates as the calendar range bounds.
Exclude orders whose user_id is NULL from volume counts.
Treat rolling_7_day_avg as the average of the current date and preceding 6 calendar days, including zero-volume days.
Round rolling_7_day_avg to 2 decimal places.
Return order_volume as an integer.

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

Tablesusersorders

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.

Output details

Use inclusive earliest and latest order dates as the calendar range bounds.
Exclude orders whose user_id is NULL from volume counts.
Treat rolling_7_day_avg as the average of the current date and preceding 6 calendar days, including zero-volume days.
Round rolling_7_day_avg to 2 decimal places.
Return order_volume as an integer.