Uber-style Company ChallengeHardVerified answerSQLite live

Top-Earning Driver per City

In each city, which driver has earned the most in net pay, and how much have they earned?

  • CTEs
  • Window functions
  • Joins
  • Subqueries
  • Aggregation

Challenge brief

Understand the request

Driver Incentives is awarding city-level driver bonuses and needs to identify the top earner in each city.

Find the top-earning driver per city using a CTE and ROW_NUMBER() partitioned by city.

Return

  • city_name
  • driver_name (full name)
  • net_earnings (rounded 2)

Constraints

  • Consider only drivers with at least one earnings record
  • Return one top earner per city
  • Break equal earnings by smaller driver ID
  • Order final rows by net earnings descending, then city ID

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)
  • city_idINTEGER

cities

  • city_idINTEGER
  • city_nameVARCHAR(100)

driver_earnings

  • driver_idINTEGER
  • net_earningREAL

Hints, when you need them

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

Hint 1

Two-step pattern: CTE aggregates net earnings per driver per city and assigns ROW_NUMBER() OVER (PARTITION BY city_id ORDER BY SUM(net_earning) DESC). Outer query: WHERE rn = 1 picks the top earner in each city.

Hint 2

CTE: INNER JOIN drivers to cities and to driver_earnings. GROUP BY driver and city. SUM(net_earning) AS net_earnings. ROW_NUMBER() OVER (PARTITION BY city_id ORDER BY SUM(net_earning) DESC) AS rn. Outer: WHERE rn = 1.

Hint 3

Aggregate pay at driver and city grain, rank drivers within each city with a unique tie-break, then retain the city winner.

Verified SQL answer

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

Reveal solution and explanation
WITH city_earnings AS (SELECT d.driver_id, d.first_name || ' ' || d.last_name AS driver_name, c.city_id, c.city_name, ROUND(SUM(de.net_earning), 2) AS net_earnings, ROW_NUMBER() OVER (PARTITION BY c.city_id ORDER BY SUM(de.net_earning) DESC, d.driver_id) AS rn FROM drivers d INNER JOIN cities c ON d.city_id = c.city_id INNER JOIN driver_earnings de ON d.driver_id = de.driver_id GROUP BY d.driver_id, d.first_name, d.last_name, c.city_id, c.city_name) SELECT city_name, driver_name, net_earnings FROM city_earnings WHERE rn = 1 ORDER BY net_earnings DESC, city_id

Why this works

ROW_NUMBER() OVER (PARTITION BY city_id ...) resets to 1 for each city's top earner. INNER JOIN driver_earnings excludes David Brown (LA) who has no earnings — that's why only 2 cities appear.

Success check

2 cities — New York: Sarah Williams ($55.50), San Francisco: Jane Smith ($48.75). LA driver has no earnings.

Expected result

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

city_namedriver_namenet_earnings
New YorkSarah Williams55.5
San FranciscoJane Smith48.75

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.