Given a transactional database table, write a query to identify top-performing personal shoppers based on delivery speed and customer ratings.
Use completed deliveries, require at least two completed deliveries and two non-null customer ratings per shopper, and return the top two shoppers. Delivery speed is measured in minutes from pickup to delivery.
Output
- One row per qualifying shopper with
shopper_id, shopper_name, avg_delivery_minutes, avg_rating, completed_deliveries, and performance_rank.
- Sort by rank ascending, then shopper ID ascending. Faster average delivery ranks higher, followed by higher rating and more completed deliveries.
Output details
- Round
avg_delivery_minutes and avg_rating to two decimal places.
- Exclude completed deliveries lacking either a pickup timestamp or delivery timestamp.
- Count each distinct
delivery_id once toward completed_deliveries.
- Assign equal ranks to shoppers tied on all ranking criteria, and retain every shopper with
performance_rank <= 2.
