Task
RideFlow 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.
Requirements
- Return one row per user per month for completed rides only.
- For each user-month, calculate:
- total completed rides
- total completed fare
- previous month's completed ride count
- month-over-month change in completed rides
- a trend label:
growing, declining, or flat
- Include users even if they have no matching city record.
- Order the final output by
user_id and month.
Table Definitions
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 |
| | |
Sample Data
Representative data is shown below.
- Users belong to different cities, and one user has no matching city.
- Rides include multiple months, duplicate monthly activity, cancelled rides, and a NULL fare.
Expected Output
| 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 |
