Rolling Average with Window Functions | Dataford Interview Questions

How would you use a window function to calculate a rolling average of order values for Zomato users?

Use the completed orders only. Return one row per order, and ignore rows where the order timestamp is NULL.

Output

Columns: user_id, order_id, order_timestamp, order_value, rolling_avg_order_value
One row per completed order, ordered by user_id, then order_timestamp, then order_id
The rolling average should cover the current order and the two prior completed orders for the same user

zomato_orders

Column	Type	Description
user_id	INT	Zomato user identifier
order_idPK	INT	Unique order identifier
order_timestamp	TIMESTAMP	Timestamp when the order was placed
order_value	NUMERIC(10,2)	Final value of the order
order_status	VARCHAR(20)	Order status such as completed, cancelled, or pending

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

How would you use a window function to calculate a rolling average of order values for Zomato users?

Use the completed orders only. Return one row per order, and ignore rows where the order timestamp is NULL.

Columns: user_id, order_id, order_timestamp, order_value, rolling_avg_order_value
One row per completed order, ordered by user_id, then order_timestamp, then order_id
The rolling average should cover the current order and the two prior completed orders for the same user

zomato_orders

Column	Type	Description
user_id	INT	Zomato user identifier
order_idPK	INT	Unique order identifier
order_timestamp	TIMESTAMP	Timestamp when the order was placed
order_value	NUMERIC(10,2)	Final value of the order
order_status	VARCHAR(20)	Order status such as completed, cancelled, or pending

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