Monthly Trip Growth by Market | Dataford Interview Questions

Your question is Monthly Trip Growth by Market. Start with the requirements and the three 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 market growth by city. Write a SQL query that joins trips, users, and cities to calculate monthly growth by market for completed trips in 2024.

Requirements

Return one row per city and month for completed trips only.
For each city-month, calculate:
- total completed trips
- distinct active riders
Use the previous month within each city to calculate:
- trip growth rate
- active rider growth rate
Exclude months where the previous month does not exist for that city.
Order the final output by city_name, then month.

Schema

trips

Column	Type	Description
trip_idPK	INT	Unique trip identifier
user_id	INT	Rider who took the trip
city_id	INT	City where the trip occurred
trip_date	DATE	Date of the trip
status	VARCHAR(20)	Trip status such as completed or cancelled
fare_amount	DECIMAL(10,2)	Fare amount charged for the trip

users

Column	Type	Description
user_idPK	INT	Unique user identifier
user_name	VARCHAR(100)	Rider name
signup_city_id	INT	City where the rider signed up
signup_date	DATE	Date the rider signed up

cities

Column	Type	Description
city_idPK	INT	Unique city identifier
city_name	VARCHAR(100)	Market name
region	VARCHAR(50)	Region grouping for the city