SQL: 7-Day Rolling Orders Per Shopper | Dataford Interview Questions

Your question is SQL: 7-Day Rolling Orders Per Shopper. Start with the requirements and the three tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Instacart analyzes shopper workload across fulfillment zones such as North and Central. Write a PostgreSQL query that calculates the seven-day rolling average of completed orders for each shopper and fulfillment zone.

Requirements

Join completed orders to the shopper and fulfillment zone dimensions.
Aggregate completed orders by shopper, zone, and calendar date.
Use a window function partitioned by shopper and zone, with a date-based seven-day frame covering the current date and the prior six days.
Return one row for each shopper-zone-date that has at least one completed order, ordered by shopper, zone, and date.

Schema

shoppers

Column	Type	Description
shopper_idPK	INTEGER	Unique shopper identifier
shopper_name	VARCHAR(100)	Shopper display name
home_zone_id	INTEGER	Shopper's assigned home fulfillment zone

fulfillment_zones

Column	Type	Description
zone_idPK	INTEGER	Unique fulfillment zone identifier
zone_name	VARCHAR(100)	Fulfillment zone name

orders

Column	Type	Description
order_idPK	INTEGER	Unique order identifier
shopper_id	INTEGER	Assigned shopper identifier
zone_id	INTEGER	Fulfillment zone identifier
order_date	DATE	Calendar date of the order
status	VARCHAR(20)	Order processing status