Top City-Pair Revenue by Weekday | Dataford Interview Questions

Your question is Top City-Pair Revenue by Weekday. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

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.

Schema

trips

Column	Type	Description
trip_idPK	INT	Unique trip identifier
trip_date	DATE	Date the trip occurred
origin_city_id	INT	Origin city ID referencing cities.city_id
destination_city_id	INT	Destination city ID referencing cities.city_id
fare_amount	DECIMAL(10,2)	Base fare amount
surge_amount	DECIMAL(10,2)	Surge amount added to the fare
trip_status	VARCHAR(20)	Trip lifecycle status

cities

Column	Type	Description
city_idPK	INT	Unique city identifier
city_name	VARCHAR(100)	City name
region	VARCHAR(50)	Operating region for the city