Task
RideNow wants to understand which origin-destination city pairs generate the most revenue on each day of the week. Write a SQL query to return the top 3 city pairs by total revenue for each weekday.
Requirements
- Join trip records to the cities table twice to get the origin and destination city names.
- Exclude trips that are not
completed and exclude rows where either city is missing.
- Aggregate total revenue by day of week and city pair.
- Rank city pairs within each weekday by revenue in descending order and return only the top 3 per weekday.
- Output the weekday name, weekday number, city pair, total revenue, and rank. Order results by weekday number, then rank, then city pair.
Table Definitions
trips
| column | type | description |
|---|
| trip_id | INT | Primary key for each trip |
| trip_date | DATE | Date the trip occurred |
| origin_city_id | INT | Origin city |
| destination_city_id | INT | Destination city |
| fare_amount | DECIMAL(10,2) | Base fare charged |
| surge_amount | DECIMAL(10,2) | Surge component; may be NULL |
| trip_status | VARCHAR(20) | Trip status such as completed or cancelled |
| | |
cities
| column | type | description |
|---|
| city_id | INT | Primary key for each city |
| city_name | VARCHAR(100) | City name |
| region | VARCHAR(50) | Operating region |
Sample Data
cities
| city_id | city_name | region |
|---|
| 1 | New York | East |
| 2 | Boston | East |
| 3 | Chicago | Central |
| 4 | Seattle | West |
| 5 | Austin | South |
| 6 | Denver | West |
| 7 | Miami | South |
| 8 | Portland | West |
trips
| trip_id | trip_date | origin_city_id | destination_city_id | fare_amount | surge_amount | trip_status |
|---|
| 101 | 2024-04-01 | 1 | 2 | 120.00 | 30.00 | completed |
| 102 | 2024-04-01 | 1 | 3 | 200.00 | 20.00 | completed |
| 103 | 2024-04-01 | 2 | 1 | 90.00 | NULL | completed |
| 104 | 2024-04-02 | 4 | 6 | 140.00 | 10.00 | completed |
| 105 | 2024-04-02 | 5 | 7 | 110.00 | 15.00 | completed |
| 106 | 2024-04-02 | 4 | 8 | 95.00 | 5.00 | cancelled |
Expected Output
| weekday_name | weekday_num | city_pair | total_revenue | revenue_rank |
|---|
| Monday | 1 | New York -> Chicago | 220.00 | 1 |
| Monday | 1 | New York -> Boston | 150.00 | 2 |
| Monday | 1 | Boston -> New York | 90.00 | 3 |
| Tuesday | 2 | Seattle -> Denver | 150.00 | 1 |
| Tuesday | 2 | Austin -> Miami | 125.00 | 2 |
