Your question is Top Seattle Sitters by Repeat Rate. 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.
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 |