Instacart's Shopper Operations team wants to identify top-performing shoppers based on how quickly they fulfill customer orders. Using June 30, 2024 as the reporting date, write a PostgreSQL query that evaluates completed orders from the rolling 30-day window ending on that date.
Requirements
- Join shoppers, orders, and fulfillment records.
- Include only delivered orders placed from June 1 through June 30, 2024, with non-null pickup and delivery timestamps.
- Return shoppers with at least three completed orders, their completed order count, and average fulfillment time in minutes.
- Rank shoppers from fastest to slowest average fulfillment time, returning the top three ranks. Include ties.
Fulfillment time is measured from picked_at to delivered_at. Use RANK() so shoppers with equal averages receive the same rank.
