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.
city_name, then month.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 |
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 |
| 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 |
| Column | Type | Description |
|---|---|---|
| trip_idPK | INT | Unique trip identifier |
| 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 such as completed or cancelled |
| fare_amount | DECIMAL(10,2) | Fare amount charged for the trip |
| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Unique user identifier |
| user_name | VARCHAR(100) | Rider name |
| signup_city_id | INT | City where the rider signed up |
| signup_date | DATE | Date the rider signed up |
| Column | Type | Description |
|---|---|---|
| city_idPK | INT | Unique city identifier |
| city_name | VARCHAR(100) | Market name |
| region | VARCHAR(50) | Region grouping for the city |
{"trips":[[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,"2024Output[["Austin","2024-03","1","1","1","1","0.00","0.00"],["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"]]