LRideFlow wants to analyze how rider activity changes month over month. Write a PostgreSQL query to calculate user-level ride trends over time using window functions.
growing, declining, or flatuser_id and month.users| column | type | description |
|---|---|---|
| user_id | INT | Unique rider ID |
| user_name | VARCHAR(100) | Rider name |
| signup_date | DATE | Account creation date |
| city_id | INT | Rider's home city |
cities| column | type | description |
|---|---|---|
| city_id | INT | Unique city ID |
| city_name | VARCHAR(100) | City name |
rides| column | type | description |
|---|---|---|
| ride_id | INT | Unique ride ID |
| user_id | INT | Rider who took the trip |
| ride_date | DATE | Ride date |
| status | VARCHAR(20) | Ride status |
| fare_amount | NUMERIC(10,2) | Fare charged |
Representative data is shown below.
| user_id | user_name | city_name | ride_month | completed_rides | completed_fare | prev_month_rides | ride_change | trend |
|---|---|---|---|---|---|---|---|---|
| 1 | Ava Patel | New York | 2024-01 | 2 | 33.50 | NULL | NULL | flat |
| 1 | Ava Patel | New York | 2024-02 | 1 | 18.00 | 2 | -1 | declining |
| 1 | Ava Patel | New York | 2024-03 | 2 | 31.00 | 1 | 1 | growing |
| 2 | Ben Ortiz | San Francisco | 2024-01 | 1 | 30.00 | NULL | NULL | flat |
| 2 | Ben Ortiz | San Francisco | 2024-02 | 2 | 47.00 | 1 | 1 | growing |
| 3 | Chloe Kim | NULL | 2024-02 | 1 | 12.00 | NULL | NULL | flat |
| 3 | Chloe Kim | NULL | 2024-03 | 1 | 0.00 | 1 | 0 | flat |
| 4 | Daniel Reed | Chicago | 2024-01 | 1 | 9.00 | NULL | NULL | flat |
| 4 | Daniel Reed | Chicago | 2024-03 | 2 | 27.00 | 1 | 1 | growing |
| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Unique rider identifier |
| user_name | VARCHAR(100) | Rider full name |
| signup_date | DATE | Date the rider signed up |
| city_id | INT | Home city reference |
| Column | Type | Description |
|---|---|---|
| city_idPK | INT | Unique city identifier |
| city_name | VARCHAR(100) | City name |
| Column | Type | Description |
|---|---|---|
| ride_idPK | INT | Unique ride identifier |
| user_id | INT | Rider who took the trip |
| ride_date | DATE | Date of the ride |
| status | VARCHAR(20) | Ride status such as completed or cancelled |
| fare_amount | NUMERIC(10,2) | Fare charged for the ride |
{"rides":[["1005","2","2024-02-21","completed","25.00"],["1001","1","2024-01-05","completed","12.50"],["1012","4","2024-03-15","completed","13.00"],["1008","3","2024-02-05","completed","12.00"],["1003","1","2024-02-10","completed","18.00"],["1015","6","2024-03-10","cancelled","11.00"],["1002","1","2024-01-18","completed","21.00"],["1011","4","2024-01-07","completed","9.00"],["1006","2","2024-02-28","completed","22.00"],["1014","5","2024-02-20","cancelled","14.00"],["1007","2","2024-03-03","canceOutput[["1","Ava Patel","New York","2024-01","2","33.50","null","null","flat"],["1","Ava Patel","New York","2024-02","1","18.00","2","-1","declining"],["1","Ava Patel","New York","2024-03","2","31.00","1","1","growing"],["2","Ben Ortiz","San Francisco","2024-01","1","30.00","null","null","flat"],["2","Ben Ortiz","San Francisco","2024-02","2","47.00","1","1","growing"],["3","Chloe Kim","San Francisco","2024-02","1","12.00","null","null","flat"],["3","Chloe Kim","San Francisco","2024-03","1","0","1","0"