7-Day Rolling Average Order Value | Dataford Interview Questions

Given a dataset of REVOLVE customer transactions, write a SQL query using window functions to compute a 7-day rolling average order value by customer segment.

Return one row for each customer segment and transaction date represented in the data. The rolling period includes the current date and the preceding six calendar days. Ignore NULL order values when calculating averages.

Output

Columns: customer_segment, order_date, daily_order_value, rolling_7_day_avg
One row per segment and transaction date
Order by customer_segment, then order_date ascending

Schema

revolve_transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
customer_segment	VARCHAR(50)	REVOLVE customer segment
order_date	DATE	Date on which the order was placed
order_value	NUMERIC(10,2)	Order value in US dollars

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

Given a dataset of REVOLVE customer transactions, write a SQL query using window functions to compute a 7-day rolling average order value by customer segment.

Output

Columns: customer_segment, order_date, daily_order_value, rolling_7_day_avg
One row per segment and transaction date
Order by customer_segment, then order_date ascending

Schema

revolve_transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
customer_segment	VARCHAR(50)	REVOLVE customer segment
order_date	DATE	Date on which the order was placed
order_value	NUMERIC(10,2)	Order value in US dollars

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