Uber-style Company ChallengeMediumVerified answerSQLite live

Driver Performance by City

For each city, what are the driver count, average rating, total trips, and total earnings?

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

Challenge brief

Understand the request

Operations Leadership needs a city-level driver performance dashboard showing quality, volume, and earnings in one view.

Build city-level driver metrics while preserving the natural grain of driver attributes and earnings.

Return

  • city_name
  • driver_count
  • avg_rating (rounded 2)
  • total_trips
  • total_earnings (rounded 2)

Constraints

  • Count each driver and their lifetime trip total once, regardless of earnings-row count
  • Aggregate earnings per driver before combining them with driver attributes
  • Treat missing earnings as zero
  • Order by total earnings descending, then city ID

Data you will use

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

cities

  • city_idINTEGER
  • city_nameVARCHAR(100)

drivers

  • driver_idINTEGER
  • city_idINTEGER
  • ratingREAL
  • total_tripsINTEGER

driver_earnings

  • earning_idINTEGER
  • driver_idINTEGER
  • total_earningREAL

Hints, when you need them

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

Hint 1

Cities are in cities. Driver stats are in drivers. Earnings are in driver_earnings — LEFT JOIN because LA driver has no earnings yet. Chain: cities INNER JOIN drivers INNER JOIN (LEFT JOIN) driver_earnings.

Hint 2

INNER JOIN cities to drivers on city_id. LEFT JOIN driver_earnings on driver_id. GROUP BY city. COUNT(DISTINCT driver_id), AVG(rating), SUM(total_trips), COALESCE(SUM(total_earning), 0).

Hint 3

First reduce the earnings fact to one row per driver; only then combine it with driver-level ratings and lifetime totals for city aggregation.

Verified SQL answer

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

Reveal solution and explanation
WITH earnings_by_driver AS (SELECT driver_id, SUM(total_earning) AS total_earning FROM driver_earnings GROUP BY driver_id) SELECT c.city_name, COUNT(d.driver_id) AS driver_count, ROUND(AVG(d.rating), 2) AS avg_rating, SUM(d.total_trips) AS total_trips, ROUND(COALESCE(SUM(ebd.total_earning), 0), 2) AS total_earnings FROM cities c INNER JOIN drivers d ON c.city_id = d.city_id LEFT JOIN earnings_by_driver ebd ON d.driver_id = ebd.driver_id GROUP BY c.city_id, c.city_name ORDER BY total_earnings DESC, c.city_id

Why this works

Earnings are reduced to one row per driver before the city join. That keeps driver ratings and lifetime trip totals from being multiplied when a driver has several earning records.

Success check

3 cities; San Francisco leads with 110.50 in earnings, and Los Angeles remains visible with zero earnings

Expected result

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

city_namedriver_countavg_ratingtotal_tripstotal_earnings
San Francisco24.852230110.5
New York24.651950106
Los Angeles14.57200

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.