Rolling Users and Basket Size

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

1. Count distinct users with a completed order for the partner within the rolling seven-day calendar window.
2. 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.
3. Exclude cancelled orders and orders outside the reporting period when determining report dates or contributing metrics.
4. Sort the output by partner name and report date.