Top Riders by City | Dataford Interview Questions

Lyft city operations wants to identify the highest-spending riders in each market. Write a PostgreSQL query using Lyft ride data to return the top five riders by completed-trip spending during the past quarter, represented here as 2025-04-01 through 2025-06-30.

Requirements

Include only completed rides requested during the quarter.
Calculate total spending for each rider within their city, treating NULL fares as zero.
Rank riders independently within each city and return no more than five riders per city.
Order results by city and spending rank.

Schema

cities

Column	Type	Description
city_idPK	INT	Unique city identifier
city_name	VARCHAR(100)	Lyft market name

riders

Column	Type	Description
rider_idPK	INT	Unique rider identifier
rider_name	VARCHAR(100)	Rider's display name
city_id	INT	Rider's associated city

rides

Column	Type	Description
ride_idPK	INT	Unique ride identifier
rider_id	INT	Rider who requested the ride
fare_amount	DECIMAL(10,2)	Fare charged for the ride
requested_at	TIMESTAMP	Ride request timestamp
status	VARCHAR(20)	Ride lifecycle status

Your question is Top Riders by City. Start with the requirements and the three tables in the Question tab.

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

Problem