Business Context
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.
Task
Write a SQL query to find the top 10 sitters in Seattle based on repeat booking rate.
Requirements
- Consider only bookings where the sitter’s
city = 'Seattle'.
- Consider only bookings with
status = 'completed' (ignore cancelled/refunded).
- Define repeat booking rate per sitter as:
repeat_booking_rate = (number of completed bookings that are repeats) / (total completed bookings)- A booking is a repeat if it is not the first completed booking for that
(owner_id, sitter_id) pair.
- Return these columns:
sitter_id, sitter_name, total_completed_bookings, repeat_completed_bookings, repeat_booking_rate
- Order results by:
repeat_booking_rate DESC,- then
total_completed_bookings DESC,
- then
sitter_id ASC.
- Return only the top 10 rows.
Table Definitions
sitters
| column | type | description |
|---|
| sitter_id | INT | Primary key for sitter |
| sitter_name | VARCHAR(100) | Display name |
| city | VARCHAR(100) | Primary city where sitter operates |
| created_at | TIMESTAMP | When sitter joined the platform |
bookings
| column | type | description |
|---|
| booking_id | BIGINT | Primary key for booking |
| owner_id | BIGINT | Pet owner who placed the booking |
| sitter_id | INT | Sitter who fulfilled the booking (FK to sitters) |
| start_date | DATE | Booking start date |
| end_date | DATE | Booking end date |
| status | VARCHAR(20) | Booking status (e.g., completed, cancelled) |
| booked_at | TIMESTAMP | When the booking was created |
Sample Data
sitters
| sitter_id | sitter_name | city | created_at |
|---|
| 101 | Maya Chen | Seattle | 2023-02-10 09:15:00 |
| 102 | Jordan Patel | Seattle | 2023-05-01 12:00:00 |
| 103 | Sam Rivera | Seattle | 2024-01-20 08:30:00 |
| 201 | Taylor Brooks | Portland | 2023-03-11 10:00:00 |
bookings
| booking_id | owner_id | sitter_id | start_date | end_date | status | booked_at |
|---|
| 9001 | 501 | 101 | 2024-03-01 | 2024-03-03 | completed | 2024-02-20 14:01:00 |
| 9002 | 501 | 101 | 2024-04-10 | 2024-04-12 | completed | 2024-04-01 09:10:00 |
| 9003 | 502 | 101 | 2024-04-15 | 2024-04-16 | completed | 2024-04-10 11:00:00 |
| 9004 | 503 | 102 | 2024-04-01 | 2024-04-02 | completed | 2024-03-28 16:20:00 |
| 9005 | 503 | 102 | 2024-05-01 | 2024-05-03 | cancelled | 2024-04-25 10:00:00 |
Expected Output (for sample data)
| sitter_id | sitter_name | total_completed_bookings | repeat_completed_bookings | repeat_booking_rate |
|---|
| 101 | Maya Chen | 3 | 1 | 0.333333 |
| 102 | Jordan Patel | 1 | 0 | 0.000000 |
