Interview Guides

Top Cities by Ride Growth | Dataford Interview Questions - Dataford - Ace your Interview

Top Cities by Ride Growth

Medium

SQL & Data Manipulation

Asked at 1 company1AggregationsRankingDate Functions

Also asked at

Problem

Task

SwiftRide wants to identify which cities are growing fastest in completed rides month over month. Write a SQL query to return the top cities by ride growth rate for each month.

Requirements

Count completed rides per city per month.
Compare each city-month to the previous month for the same city.
Calculate growth rate as (current_month_rides - previous_month_rides) / previous_month_rides * 100.
Return only months where the previous month exists and previous month rides are greater than 0.
For each month, rank cities by growth rate descending and return the top 2 cities.

Table Definitions

`cities`

column	type	description
city_id	INT	Unique city identifier
city_name	VARCHAR(100)	City name
region	VARCHAR(50)	Operating region

`drivers`

column	type	description
driver_id	INT	Unique driver identifier
city_id	INT	Driver's home city
driver_name	VARCHAR(100)	Driver name
status	VARCHAR(20)	Driver status

`rides`

column	type	description
ride_id	INT	Unique ride identifier
driver_id	INT	Driver who completed or attempted the ride
rider_id	INT	Rider identifier
ride_date	DATE	Ride date
ride_status	VARCHAR(20)	Ride outcome
fare_amount	NUMERIC(10,2)	Fare amount

Sample Data

Representative rows are included below. Assume multiple rides can occur in the same city and month, and some rides are cancelled or have NULL fares.

Expected Output

month_start	city_name	current_month_rides	previous_month_rides	growth_rate_pct	growth_rank
2024-02-01	Austin	3	1	200.00	1
2024-02-01	Seattle	2	1	100.00	2
2024-03-01	Seattle	4	2	100.00	1
2024-03-01	Austin	4	3	33.33	2

Schema

cities

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

drivers

Column	Type	Description
driver_idPK	INT	Unique driver identifier
city_id	INT	Driver's assigned city
driver_name	VARCHAR(100)	Driver full name
status	VARCHAR(20)	Driver status such as active or inactive

rides

Column	Type	Description
ride_idPK	INT	Unique ride identifier
driver_id	INT	Driver associated with the ride
rider_id	INT	Rider identifier
ride_date	DATE	Date the ride occurred
ride_status	VARCHAR(20)	Ride status such as completed or cancelled
fare_amount	NUMERIC(10,2)	Fare charged for the ride

Sample Data

Example 1

Input

{"rides":[[1001,101,501,"2024-01-05","completed","18.50"],[1002,102,502,"2024-01-07","completed","22.00"],[1003,103,503,"2024-01-08","completed","15.75"],[1004,104,504,"2024-01-09","completed","19.25"],[1005,101,505,"2024-02-03","completed","21.10"],[1006,106,506,"2024-02-10","completed","17.40"],[1007,106,507,"2024-02-15","completed",null],[1008,102,508,"2024-02-05","completed","23.00"],[1009,103,509,"2024-02-06","completed","16.20"],[1010,110,510,"2024-02-20","completed","18.90"],[1011,104,511

Output

[["2024-02-01","Austin","3","1","200.00","1"],["2024-02-01","Seattle","2","1","100.00","2"],["2024-03-01","Seattle","3","2","50.00","1"],["2024-03-01","Austin","4","3","33.33","2"]]

Problem

Task

SwiftRide wants to identify which cities are growing fastest in completed rides month over month. Write a SQL query to return the top cities by ride growth rate for each month.

Requirements

Count completed rides per city per month.
Compare each city-month to the previous month for the same city.
Calculate growth rate as (current_month_rides - previous_month_rides) / previous_month_rides * 100.
Return only months where the previous month exists and previous month rides are greater than 0.
For each month, rank cities by growth rate descending and return the top 2 cities.

Table Definitions

`cities`

column	type	description
city_id	INT	Unique city identifier
city_name	VARCHAR(100)	City name
region	VARCHAR(50)	Operating region

`drivers`

column	type	description
driver_id	INT	Unique driver identifier
city_id	INT	Driver's home city
driver_name	VARCHAR(100)	Driver name
status	VARCHAR(20)	Driver status

`rides`

column	type	description
ride_id	INT	Unique ride identifier
driver_id	INT	Driver who completed or attempted the ride
rider_id	INT	Rider identifier
ride_date	DATE	Ride date
ride_status	VARCHAR(20)	Ride outcome
fare_amount	NUMERIC(10,2)	Fare amount

Sample Data

Representative rows are included below. Assume multiple rides can occur in the same city and month, and some rides are cancelled or have NULL fares.

Expected Output

month_start	city_name	current_month_rides	previous_month_rides	growth_rate_pct	growth_rank
2024-02-01	Austin	3	1	200.00	1
2024-02-01	Seattle	2	1	100.00	2
2024-03-01	Seattle	4	2	100.00	1
2024-03-01	Austin	4	3	33.33	2

Schema

cities

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

drivers

Column	Type	Description
driver_idPK	INT	Unique driver identifier
city_id	INT	Driver's assigned city
driver_name	VARCHAR(100)	Driver full name
status	VARCHAR(20)	Driver status such as active or inactive

rides

Column	Type	Description
ride_idPK	INT	Unique ride identifier
driver_id	INT	Driver associated with the ride
rider_id	INT	Rider identifier
ride_date	DATE	Date the ride occurred
ride_status	VARCHAR(20)	Ride status such as completed or cancelled
fare_amount	NUMERIC(10,2)	Fare charged for the ride

Sample Data

Example 1

Input

{"rides":[[1001,101,501,"2024-01-05","completed","18.50"],[1002,102,502,"2024-01-07","completed","22.00"],[1003,103,503,"2024-01-08","completed","15.75"],[1004,104,504,"2024-01-09","completed","19.25"],[1005,101,505,"2024-02-03","completed","21.10"],[1006,106,506,"2024-02-10","completed","17.40"],[1007,106,507,"2024-02-15","completed",null],[1008,102,508,"2024-02-05","completed","23.00"],[1009,103,509,"2024-02-06","completed","16.20"],[1010,110,510,"2024-02-20","completed","18.90"],[1011,104,511

Output

[["2024-02-01","Austin","3","1","200.00","1"],["2024-02-01","Seattle","2","1","100.00","2"],["2024-03-01","Seattle","3","2","50.00","1"],["2024-03-01","Austin","4","3","33.33","2"]]

Practice SQL

PostgreSQL

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

Up next

Monthly Trip Growth by MarketMedium

User Ride Trends with WindowsMedium

Ride Frequency by Signup CohortMedium

Next question

Top Cities by Ride Growth

Medium

SQL & Data Manipulation

Asked at 1 company1AggregationsRankingDate Functions

Also asked at

Problem

Task

SwiftRide wants to identify which cities are growing fastest in completed rides month over month. Write a SQL query to return the top cities by ride growth rate for each month.

Requirements

Count completed rides per city per month.
Compare each city-month to the previous month for the same city.
Calculate growth rate as (current_month_rides - previous_month_rides) / previous_month_rides * 100.
Return only months where the previous month exists and previous month rides are greater than 0.
For each month, rank cities by growth rate descending and return the top 2 cities.

Table Definitions

`cities`

column	type	description
city_id	INT	Unique city identifier
city_name	VARCHAR(100)	City name
region	VARCHAR(50)	Operating region

`drivers`

column	type	description
driver_id	INT	Unique driver identifier
city_id	INT	Driver's home city
driver_name	VARCHAR(100)	Driver name
status	VARCHAR(20)	Driver status

`rides`

column	type	description
ride_id	INT	Unique ride identifier
driver_id	INT	Driver who completed or attempted the ride
rider_id	INT	Rider identifier
ride_date	DATE	Ride date
ride_status	VARCHAR(20)	Ride outcome
fare_amount	NUMERIC(10,2)	Fare amount

Sample Data

Representative rows are included below. Assume multiple rides can occur in the same city and month, and some rides are cancelled or have NULL fares.

Expected Output

month_start	city_name	current_month_rides	previous_month_rides	growth_rate_pct	growth_rank
2024-02-01	Austin	3	1	200.00	1
2024-02-01	Seattle	2	1	100.00	2
2024-03-01	Seattle	4	2	100.00	1
2024-03-01	Austin	4	3	33.33	2

Schema

cities

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

drivers

Column	Type	Description
driver_idPK	INT	Unique driver identifier
city_id	INT	Driver's assigned city
driver_name	VARCHAR(100)	Driver full name
status	VARCHAR(20)	Driver status such as active or inactive

rides

Column	Type	Description
ride_idPK	INT	Unique ride identifier
driver_id	INT	Driver associated with the ride
rider_id	INT	Rider identifier
ride_date	DATE	Date the ride occurred
ride_status	VARCHAR(20)	Ride status such as completed or cancelled
fare_amount	NUMERIC(10,2)	Fare charged for the ride

Sample Data

Example 1

Input

{"rides":[[1001,101,501,"2024-01-05","completed","18.50"],[1002,102,502,"2024-01-07","completed","22.00"],[1003,103,503,"2024-01-08","completed","15.75"],[1004,104,504,"2024-01-09","completed","19.25"],[1005,101,505,"2024-02-03","completed","21.10"],[1006,106,506,"2024-02-10","completed","17.40"],[1007,106,507,"2024-02-15","completed",null],[1008,102,508,"2024-02-05","completed","23.00"],[1009,103,509,"2024-02-06","completed","16.20"],[1010,110,510,"2024-02-20","completed","18.90"],[1011,104,511

Output

[["2024-02-01","Austin","3","1","200.00","1"],["2024-02-01","Seattle","2","1","100.00","2"],["2024-03-01","Seattle","3","2","50.00","1"],["2024-03-01","Austin","4","3","33.33","2"]]

Problem

Task

SwiftRide wants to identify which cities are growing fastest in completed rides month over month. Write a SQL query to return the top cities by ride growth rate for each month.

Requirements

Count completed rides per city per month.
Compare each city-month to the previous month for the same city.
Calculate growth rate as (current_month_rides - previous_month_rides) / previous_month_rides * 100.
Return only months where the previous month exists and previous month rides are greater than 0.
For each month, rank cities by growth rate descending and return the top 2 cities.

Table Definitions

`cities`

column	type	description
city_id	INT	Unique city identifier
city_name	VARCHAR(100)	City name
region	VARCHAR(50)	Operating region

`drivers`

column	type	description
driver_id	INT	Unique driver identifier
city_id	INT	Driver's home city
driver_name	VARCHAR(100)	Driver name
status	VARCHAR(20)	Driver status

`rides`

column	type	description
ride_id	INT	Unique ride identifier
driver_id	INT	Driver who completed or attempted the ride
rider_id	INT	Rider identifier
ride_date	DATE	Ride date
ride_status	VARCHAR(20)	Ride outcome
fare_amount	NUMERIC(10,2)	Fare amount

Sample Data

Representative rows are included below. Assume multiple rides can occur in the same city and month, and some rides are cancelled or have NULL fares.

Expected Output

month_start	city_name	current_month_rides	previous_month_rides	growth_rate_pct	growth_rank
2024-02-01	Austin	3	1	200.00	1
2024-02-01	Seattle	2	1	100.00	2
2024-03-01	Seattle	4	2	100.00	1
2024-03-01	Austin	4	3	33.33	2

Schema

cities

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

drivers

Column	Type	Description
driver_idPK	INT	Unique driver identifier
city_id	INT	Driver's assigned city
driver_name	VARCHAR(100)	Driver full name
status	VARCHAR(20)	Driver status such as active or inactive

rides

Column	Type	Description
ride_idPK	INT	Unique ride identifier
driver_id	INT	Driver associated with the ride
rider_id	INT	Rider identifier
ride_date	DATE	Date the ride occurred
ride_status	VARCHAR(20)	Ride status such as completed or cancelled
fare_amount	NUMERIC(10,2)	Fare charged for the ride

Sample Data

Example 1

Input

{"rides":[[1001,101,501,"2024-01-05","completed","18.50"],[1002,102,502,"2024-01-07","completed","22.00"],[1003,103,503,"2024-01-08","completed","15.75"],[1004,104,504,"2024-01-09","completed","19.25"],[1005,101,505,"2024-02-03","completed","21.10"],[1006,106,506,"2024-02-10","completed","17.40"],[1007,106,507,"2024-02-15","completed",null],[1008,102,508,"2024-02-05","completed","23.00"],[1009,103,509,"2024-02-06","completed","16.20"],[1010,110,510,"2024-02-20","completed","18.90"],[1011,104,511

Output

[["2024-02-01","Austin","3","1","200.00","1"],["2024-02-01","Seattle","2","1","100.00","2"],["2024-03-01","Seattle","3","2","50.00","1"],["2024-03-01","Austin","4","3","33.33","2"]]

Practice SQL

PostgreSQL

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

Up next

Monthly Trip Growth by MarketMedium

User Ride Trends with WindowsMedium

Ride Frequency by Signup CohortMedium

Next question