Ride-sharing operations teams surface top performers to recognize drivers, investigate quality issues, and calibrate driver supply in each market. Rather than dropping tied drivers arbitrarily, the query must return all drivers who share the top rank in their city. Using the rides table, return city, driver_id, and ride_count for the driver(s) with the most completed rides in each city, ordered by city. Include ties.
rides
| column | type |
|---|---|
| id | INTEGER |
| driver_id | INTEGER |
| city | TEXT |
| status | TEXT |
| created_at | DATE |
rides
| id | driver_id | city | status | created_at |
|---|---|---|---|---|
| 1 | 1 | NYC | completed | 2024-01-01 |
| 2 | 1 | NYC | completed | 2024-01-02 |
| 3 | 1 | NYC | completed | 2024-01-03 |
| 4 | 2 | NYC | completed | 2024-01-01 |
| 5 | 2 | NYC | completed | 2024-01-04 |
| 6 | 2 | NYC | cancelled | 2024-01-05 |
| 7 | 3 | LA | completed | 2024-01-01 |
| 8 | 3 | LA | completed | 2024-01-02 |
| 9 | 4 | LA | completed | 2024-01-03 |
| 10 | 4 | LA | completed | 2024-01-04 |
| city | driver_id | ride_count |
|---|---|---|
| LA | 3 | 2 |
| LA | 4 | 2 |
| NYC | 1 | 3 |
In NYC, driver 1 has 3 completed rides and driver 2 has 2 — driver 1 wins outright. In LA, drivers 3 and 4 each have 2 completed rides — both are returned due to the tie. Driver 2's cancelled ride in NYC is not counted.