Active Rider Directory
Show all registered riders with their email, city, and lifetime trip count.
- Joins
- Sorting
Challenge brief
Understand the request
Customer Success needs a directory of all registered riders with their city assignment and trip history for a re-engagement campaign.
List all riders with full name, email, city name, and total_trips.
Return
- rider_name (first + last)
- city_name
- total_trips
Constraints
- Return every registered rider
- Order by city name, last name, then rider ID
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
riders
rider_idINTEGERfirst_nameVARCHAR(50)last_nameVARCHAR(50)emailVARCHAR(100)total_tripsINTEGERcity_idINTEGER
cities
city_idINTEGERcity_nameVARCHAR(100)
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Rider details are in riders. City names are in cities. JOIN on city_id. Concatenate first_name and last_name for full name.
Hint 2
INNER JOIN riders to cities on city_id. Concatenate first and last name using || ' ' || to build rider_name. ORDER BY c.city_name, r.last_name.
Hint 3
Join rider records to their city labels and use the rider key as the final stable ordering key.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT r.first_name || ' ' || r.last_name AS rider_name, r.email, c.city_name, r.total_trips FROM riders r INNER JOIN cities c ON r.city_id = c.city_id ORDER BY c.city_name, r.last_name, r.rider_idWhy this works
INNER JOIN connects each rider to their city name via city_id. total_trips is a denormalised counter in riders so no aggregation is needed. Daniel Davis has the most lifetime trips (51).
Success check
5 riders across Los Angeles, New York, and San Francisco
Expected result
Use this output to verify values, aliases, ordering, and row count.
| rider_name | city_name | total_trips | |
|---|---|---|---|
| Emma Evans | emma.e@email.com | Los Angeles | 38 |
| Carol Clark | carol.c@email.com | New York | 28 |
| Daniel Davis | daniel.d@email.com | New York | 51 |
| Alice Anderson | alice.a@email.com | San Francisco | 45 |
| Bob Baker | bob.b@email.com | San Francisco | 32 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Explore related company challenges
Airbnb
Independent Airbnb-style marketplace, booking, listing, payment, review, and guest analytics SQL practice.
Amazon
Independent Amazon-style e-commerce, warehouse, inventory, and customer analytics SQL practice.
Google
Independent Google-style search, advertising, user-engagement, and video-product SQL practice.
Return to the complete interview preparation experience.