Interview Guides

Top City-Pair Revenue by Weekday | Dataford Interview Questions - Dataford - Ace your Interview

Top City-Pair Revenue by Weekday

Medium

SQL & Data Manipulation

Asked at 1 company1AggregationsWindow FunctionsRanking

Also asked at

Problem

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

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

Sample Data

Example 1

Input

{"trips":[["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-01","1","2","40.00","10.00","completed"],["105","2024-04-01","5","7","80.00","5.00","cancelled"],["106","2024-04-02","4","6","140.00","10.00","completed"],["107","2024-04-02","5","7","110.00","15.00","completed"],["108","2024-04-02","2","3","70.00",null,"completed"],["109","2024-04-02","4","8","95.00","

Output

[["Monday","1","New York -> Chicago","220.00","1"],["Monday","1","New York -> Boston","200.00","2"],["Monday","1","Boston -> New York","90.00","3"],["Tuesday","2","Seattle -> Denver","150.00","1"],["Tuesday","2","Austin -> Miami","125.00","2"],["Tuesday","2","Boston -> Chicago","70.00","3"],["Wednesday","3","Chicago -> New York","150.00","1"],["Wednesday","3","Denver -> Seattle","125.00","2"],["Friday","5","Seattle -> Boston","184.00","1"],["Saturday","6","Austin -> Miami","127.00","1"]]

Problem

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

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

Sample Data

Example 1

Input

{"trips":[["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-01","1","2","40.00","10.00","completed"],["105","2024-04-01","5","7","80.00","5.00","cancelled"],["106","2024-04-02","4","6","140.00","10.00","completed"],["107","2024-04-02","5","7","110.00","15.00","completed"],["108","2024-04-02","2","3","70.00",null,"completed"],["109","2024-04-02","4","8","95.00","

Output

[["Monday","1","New York -> Chicago","220.00","1"],["Monday","1","New York -> Boston","200.00","2"],["Monday","1","Boston -> New York","90.00","3"],["Tuesday","2","Seattle -> Denver","150.00","1"],["Tuesday","2","Austin -> Miami","125.00","2"],["Tuesday","2","Boston -> Chicago","70.00","3"],["Wednesday","3","Chicago -> New York","150.00","1"],["Wednesday","3","Denver -> Seattle","125.00","2"],["Friday","5","Seattle -> Boston","184.00","1"],["Saturday","6","Austin -> Miami","127.00","1"]]

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Monthly Trip Growth by MarketMedium

Top Driver Earnings Per HourMedium

Top Cities by Ride GrowthMedium

Next question

Top City-Pair Revenue by Weekday

Medium

SQL & Data Manipulation

Asked at 1 company1AggregationsWindow FunctionsRanking

Also asked at

Problem

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

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

Sample Data

Example 1

Input

{"trips":[["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-01","1","2","40.00","10.00","completed"],["105","2024-04-01","5","7","80.00","5.00","cancelled"],["106","2024-04-02","4","6","140.00","10.00","completed"],["107","2024-04-02","5","7","110.00","15.00","completed"],["108","2024-04-02","2","3","70.00",null,"completed"],["109","2024-04-02","4","8","95.00","

Output

[["Monday","1","New York -> Chicago","220.00","1"],["Monday","1","New York -> Boston","200.00","2"],["Monday","1","Boston -> New York","90.00","3"],["Tuesday","2","Seattle -> Denver","150.00","1"],["Tuesday","2","Austin -> Miami","125.00","2"],["Tuesday","2","Boston -> Chicago","70.00","3"],["Wednesday","3","Chicago -> New York","150.00","1"],["Wednesday","3","Denver -> Seattle","125.00","2"],["Friday","5","Seattle -> Boston","184.00","1"],["Saturday","6","Austin -> Miami","127.00","1"]]

Problem

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

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

Sample Data

Example 1

Input

{"trips":[["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-01","1","2","40.00","10.00","completed"],["105","2024-04-01","5","7","80.00","5.00","cancelled"],["106","2024-04-02","4","6","140.00","10.00","completed"],["107","2024-04-02","5","7","110.00","15.00","completed"],["108","2024-04-02","2","3","70.00",null,"completed"],["109","2024-04-02","4","8","95.00","

Output

[["Monday","1","New York -> Chicago","220.00","1"],["Monday","1","New York -> Boston","200.00","2"],["Monday","1","Boston -> New York","90.00","3"],["Tuesday","2","Seattle -> Denver","150.00","1"],["Tuesday","2","Austin -> Miami","125.00","2"],["Tuesday","2","Boston -> Chicago","70.00","3"],["Wednesday","3","Chicago -> New York","150.00","1"],["Wednesday","3","Denver -> Seattle","125.00","2"],["Friday","5","Seattle -> Boston","184.00","1"],["Saturday","6","Austin -> Miami","127.00","1"]]

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Monthly Trip Growth by MarketMedium

Top Driver Earnings Per HourMedium

Top Cities by Ride GrowthMedium

Next question