Uber-style Company ChallengeMediumVerified answerSQLite live

Percentage of Drivers by Vehicle Type

What percentage of active drivers does each vehicle type represent?

  • Joins
  • Subqueries
  • Aggregation
  • Numeric functions
  • NULL handling

Challenge brief

Understand the request

Fleet Strategy is reviewing the service tier distribution to decide how many UberXL and UberBlack drivers to recruit.

Calculate the share of active drivers per vehicle type using a scalar subquery for the total.

Return

  • vehicle_type
  • driver_count
  • percentage (rounded 2)

Constraints

  • Use only drivers whose status is 'active'
  • Return a defined result without a division error when there are no active drivers
  • Order by percentage descending, then vehicle type

Data you will use

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

vehicles

  • vehicle_idINTEGER
  • vehicle_typeVARCHAR(30)

drivers

  • driver_idINTEGER
  • vehicle_idINTEGER
  • statusVARCHAR(20)

Hints, when you need them

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

Hint 1

Vehicle types are in vehicles. Driver status is in drivers. JOIN on vehicle_id. The percentage denominator is the total active driver count — compute it as a scalar subquery.

Hint 2

INNER JOIN vehicles to drivers on vehicle_id. WHERE d.status = 'active'. GROUP BY vehicle_type. COUNT(d.driver_id). Divide by (SELECT COUNT(*) FROM drivers WHERE status = 'active') * 100.0.

Hint 3

Compute grouped active-driver counts and divide by a separately counted active population, protecting the denominator when that population is empty.

Verified SQL answer

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

Reveal solution and explanation
SELECT v.vehicle_type, COUNT(d.driver_id) AS driver_count, ROUND(COUNT(d.driver_id) * 100.0 / NULLIF((SELECT COUNT(*) FROM drivers WHERE status = 'active'), 0), 2) AS percentage FROM vehicles v INNER JOIN drivers d ON v.vehicle_id = d.vehicle_id WHERE d.status = 'active' GROUP BY v.vehicle_type ORDER BY percentage DESC, v.vehicle_type

Why this works

The scalar subquery (SELECT COUNT(*) FROM drivers WHERE status = 'active') returns 5 and is the denominator for all rows. Multiplying by 100.0 ensures floating-point division. All 5 drivers are active.

Success check

3 types — UberX 60% (3 drivers), UberXL 20% (1), UberBlack 20% (1)

Expected result

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

vehicle_typedriver_countpercentage
UberX360
UberBlack120
UberXL120

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.