Task
RideNow wants to understand market growth by city. Write a SQL query that joins trips, users, and cities to calculate monthly growth by market for completed trips in 2024.
Requirements
- Return one row per city and month for completed trips only.
- For each city-month, calculate:
- total completed trips
- distinct active riders
- Use the previous month within each city to calculate:
- trip growth rate
- active rider growth rate
- Exclude months where the previous month does not exist for that city.
- Order the final output by
city_name, then month.
Table Definitions
trips
| column | type | description |
|---|
| trip_id | INT | Primary key for each trip |
| user_id | INT | Rider who took the trip |
| city_id | INT | City where the trip occurred |
| trip_date | DATE | Date of the trip |
| status | VARCHAR(20) | Trip status |
| fare_amount | DECIMAL(10,2) | Fare paid for the trip |
| | |
users
| column | type | description |
|---|
| user_id | INT | Primary key for each user |
| user_name | VARCHAR(100) | Rider name |
| signup_city_id | INT | City where the user signed up |
| signup_date | DATE | Signup date |
| | |
cities
| column | type | description |
|---|
| city_id | INT | Primary key for each city |
| city_name | VARCHAR(100) | Market name |
| region | VARCHAR(50) | Region grouping |
| | |
Sample Data
cities
| city_id | city_name | region |
|---|
| 3 | Chicago | Midwest |
| 1 | New York | East |
| 5 | Miami | South |
| 2 | San Francisco | West |
| 4 | Seattle | West |
| 6 | Austin | South |
| 7 | Denver | West |
| 8 | Boston | East |
| | |
users
| user_id | user_name | signup_city_id | signup_date |
|---|
| 104 | Priya Shah | 3 | 2024-01-20 |
| 101 | Alice Chen | 1 | 2023-12-15 |
| 108 | Noah Kim | 8 | 2024-02-18 |
| 106 | Sofia Martinez | 5 | 2024-02-10 |
| 103 | Carla Gomez | 2 | 2024-01-10 |
| 110 | | 2 | 2024-03-01 |
| 102 | Ben Lee | 1 | 2024-01-05 |
| 109 | Liam Patel | NULL | 2024-02-25 |
| 105 | Omar Hassan | 4 | 2024-02-01 |
| 107 | Maya Johnson | 6 | 2024-02-15 |
| | | |
trips
| trip_id | user_id | city_id | trip_date | status | fare_amount |
|---|
| 11 | 106 | 5 | 2024-03-10 | completed | 17.00 |
| 2 | 102 | 1 | 2024-01-12 | completed | 18.00 |
| 7 | 104 | 3 | 2024-02-07 | completed | 14.00 |
| 14 | 108 | 8 | 2024-03-05 | completed | 13.00 |
| 4 | 101 | 1 | 2024-02-03 | completed | 25.00 |
| 1 | 101 | 1 | 2024-01-08 | completed | 22.50 |
| 9 | 105 | 4 | 2024-02-11 | cancelled | 0.00 |
| 5 | 103 | 2 | 2024-02-05 | completed | 30.00 |
| 13 | 107 | 6 | 2024-03-02 | completed | 16.00 |
| 3 | 101 | 1 | 2024-01-20 | cancelled | 0.00 |
| 12 | 107 | 6 | 2024-02-20 | completed | 15.00 |
| 10 | 106 | 5 | 2024-02-14 | completed | 19.00 |
| 8 | 104 | 3 | 2024-03-08 | completed | 15.00 |
| 15 | 108 | 8 | 2024-03-22 | completed | 11.00 |
| 6 | 103 | 2 | 2024-02-18 | completed | 28.00 |
| 16 | 999 | 7 | 2024-03-12 | completed | 20.00 |
| 17 | 101 | NULL | 2024-03-15 | completed | 12.00 |
| 18 | 102 | 1 | 2023-12-28 | completed | 21.00 |
| | | | | |
Expected Output
| city_name | month | completed_trips | active_riders | prev_month_trips | prev_month_active_riders | trip_growth_rate_pct | rider_growth_rate_pct |
|---|
| Austin | 2024-03 | 1 | 1 | 1 | 1 | 0.00 | 0.00 |
| Boston | 2024-03 | 2 | 1 | 0 | 0 | NULL | NULL |
| Chicago | 2024-03 | 1 | 1 | 1 | 1 | 0.00 | 0.00 |
| Miami | 2024-03 | 1 | 1 | 1 | 1 | 0.00 | 0.00 |
| New York | 2024-02 | 1 | 1 | 2 | 2 | -50.00 | -50.00 |
| San Francisco | 2024-03 | 0 | 0 | 2 | 1 | -100.00 | -100.00 |
| Seattle | 2024-03 | 0 | 0 | 0 | 0 | NULL | NULL |
