Uber-style Company ChallengeBeginnerVerified answerSQLite live

Active Drivers by City

Which drivers are currently active, which city are they in, and what vehicle type do they drive?

  • Joins
  • Filtering
  • Sorting

Challenge brief

Understand the request

Driver Operations needs a current roster of all active drivers showing their city assignment and vehicle category.

List all active drivers with name, email, city, and vehicle type.

Return

  • first_name
  • last_name
  • email
  • city_name
  • vehicle_type

Constraints

  • Return only drivers whose status is 'active'
  • Order by city name, then driver ID so ties are stable

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

drivers

  • driver_idINTEGER
  • first_nameVARCHAR(50)
  • last_nameVARCHAR(50)
  • emailVARCHAR(100)
  • statusVARCHAR(20)
  • city_idINTEGER
  • vehicle_idINTEGER

cities

  • city_idINTEGER
  • city_nameVARCHAR(100)

vehicles

  • vehicle_idINTEGER
  • vehicle_typeVARCHAR(30)

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

Driver names and status are in drivers. City names are in cities. Vehicle types are in vehicles. Two JOINs required: drivers to cities on city_id, and drivers to vehicles on vehicle_id.

Hint 2

INNER JOIN drivers to cities ON d.city_id = c.city_id. INNER JOIN vehicles ON d.vehicle_id = v.vehicle_id. WHERE d.status = 'active'. ORDER BY c.city_name.

Hint 3

Start from drivers, connect the city and vehicle dimensions by their IDs, then apply the active-status filter and stable sort.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
SELECT d.first_name, d.last_name, d.email, c.city_name, v.vehicle_type FROM drivers d INNER JOIN cities c ON d.city_id = c.city_id INNER JOIN vehicles v ON d.vehicle_id = v.vehicle_id WHERE d.status = 'active' ORDER BY c.city_name, d.driver_id

Why this works

Two INNER JOINs chain from the drivers fact table to two dimension tables. status = 'active' filters only working drivers. All 5 drivers are active so all appear.

Success check

5 active drivers across San Francisco, New York, and Los Angeles

Expected result

Use this output to verify values, aliases, ordering, and row count.

first_namelast_nameemailcity_namevehicle_type
DavidBrowndavid.b@uber.comLos AngelesUberX
MikeJohnsonmike.j@uber.comNew YorkUberBlack
SarahWilliamssarah.w@uber.comNew YorkUberXL
JohnDoejohn.doe@uber.comSan FranciscoUberX
JaneSmithjane.smith@uber.comSan FranciscoUberX

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Explore related company challenges

SQL Interview Practice →

Return to the complete interview preparation experience.