Task
SwiftRide wants to identify which cities are growing fastest in completed rides month over month. Write a SQL query to return the top cities by ride growth rate for each month.
Requirements
- Count completed rides per city per month.
- Compare each city-month to the previous month for the same city.
- Calculate growth rate as
(current_month_rides - previous_month_rides) / previous_month_rides * 100.
- Return only months where the previous month exists and previous month rides are greater than 0.
- For each month, rank cities by growth rate descending and return the top 2 cities.
Table Definitions
cities
| column | type | description |
|---|
| city_id | INT | Unique city identifier |
| city_name | VARCHAR(100) | City name |
| region | VARCHAR(50) | Operating region |
drivers
| column | type | description |
|---|
| driver_id | INT | Unique driver identifier |
| city_id | INT | Driver's home city |
| driver_name | VARCHAR(100) | Driver name |
| status | VARCHAR(20) | Driver status |
rides
| column | type | description |
|---|
| ride_id | INT | Unique ride identifier |
| driver_id | INT | Driver who completed or attempted the ride |
| rider_id | INT | Rider identifier |
| ride_date | DATE | Ride date |
| ride_status | VARCHAR(20) | Ride outcome |
| fare_amount | NUMERIC(10,2) | Fare amount |
Sample Data
Representative rows are included below. Assume multiple rides can occur in the same city and month, and some rides are cancelled or have NULL fares.
Expected Output
| month_start | city_name | current_month_rides | previous_month_rides | growth_rate_pct | growth_rank |
|---|
| 2024-02-01 | Austin | 3 | 1 | 200.00 | 1 |
| 2024-02-01 | Seattle | 2 | 1 | 100.00 | 2 |
| 2024-03-01 | Seattle | 4 | 2 | 100.00 | 1 |
| 2024-03-01 | Austin | 4 | 3 | 33.33 | 2 |
