Rolling Users and Basket Size | Dataford Interview Questions

Your question is Rolling Users and Basket Size. Start with the requirements and the two 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's retail partner teams want a daily view of customer activity and basket economics. Write a PostgreSQL query that calculates rolling 7-day active users and average basket size for each retail partner.

Use completed orders from January 1 through January 8, 2024. A rolling 7-day window includes the report date and the six preceding calendar days. Return one row for each partner and date with at least one completed order during the reporting period.

Requirements

Count distinct users with a completed order for the partner within the rolling seven-day calendar window.
Calculate the average basket_amount for completed orders in the same window, rounded to two decimal places. Ignore NULL basket amounts using PostgreSQL aggregate behavior.
Exclude cancelled orders and orders outside the reporting period when determining report dates or contributing metrics.
Sort the output by partner name and report date.

Schema

partners

Column	Type	Description
partner_idPK	INTEGER	Unique retail partner identifier
partner_name	VARCHAR(100)	Retail partner name

orders

Column	Type	Description
order_idPK	INTEGER	Unique order identifier
partner_id	INTEGER	References partners.partner_id
user_id	INTEGER	Instacart customer identifier
order_date	DATE	Order date
status	VARCHAR(20)	Order lifecycle status
basket_amount	NUMERIC(10,2)	Total value of the order basket