You’re working on analytics for a pet-sitting marketplace (think Rover-scale) with millions of bookings per year across major US cities. The Seattle operations team is rolling out a loyalty program and wants to identify the sitters who drive the most repeat business—because repeat bookings correlate strongly with higher lifetime value and lower customer acquisition costs.
In this marketplace, a repeat booking is defined as a pet owner booking the same sitter more than once (across different bookings). The team wants a leaderboard of the best-performing sitters in Seattle by repeat booking rate, with ties broken deterministically.
Write a SQL query to find the top 10 sitters in Seattle based on repeat booking rate.
city = 'Seattle'.status = 'completed' (ignore cancelled/refunded).repeat_booking_rate = (number of completed bookings that are repeats) / (total completed bookings)(owner_id, sitter_id) pair.sitter_id, sitter_name, total_completed_bookings, repeat_completed_bookings, repeat_booking_raterepeat_booking_rate DESC,total_completed_bookings DESC,sitter_id ASC.| Column | Type | Description |
|---|---|---|
| sitter_idPK | INT | Unique identifier for the sitter |
| sitter_name | VARCHAR(100) | Sitter display name |
| city | VARCHAR(100) | Primary city where the sitter operates |
| created_at | TIMESTAMP | Timestamp when the sitter joined |
| Column | Type | Description |
|---|---|---|
| booking_idPK | BIGINT | Unique identifier for the booking |
| owner_id | BIGINT | Unique identifier for the pet owner |
| sitter_id | INT | Sitter who fulfilled the booking (FK to sitters) |
| start_date | DATE | Start date of the booking |
| end_date | DATE | End date of the booking |
| status | VARCHAR(20) | Booking status (completed, cancelled, refunded, etc.) |
| booked_at | TIMESTAMP | Timestamp when the booking was created |