Uber mid JOINGROUP BYSUMORDER BY

Find total earnings of each driver and show top 3 earning drivers per city.

Uber · Driver Earnings — practice this real-world SQL scenario live in your browser.

📖 Story Uber · Driver Earnings

Uber HQ, Friday evening. Product manager Sneha walks in: "We need top earning drivers per city for bonus distribution." Time to combine trips and drivers data.

🎯 Your Mission

📋 Table Structure

🗂 drivers

driver_id TEXT D001

driver_name TEXT Ravi Kumar

city TEXT Chennai

🗂 trips

trip_id TEXT T101

driver_id TEXT D001

fare REAL 250.50

⚡ Step-by-Step Walkthrough

Join drivers and trips

query.sql

SELECT d.driver_id, d.driver_name, d.city, t.fare
FROM drivers d
JOIN trips t ON d.driver_id = t.driver_id;

💡 Explanation

The drivers table has name and city — but no fare. The trips table has fare amounts — but no city or driver name. The only link between them is driver_id.
JOIN finds matching rows in both tables using the condition d.driver_id = t.driver_id. Every trip row gets paired with the correct driver row.
We use aliases: d for drivers, t for trips. This shortens the query and avoids repeating full table names every time.
Without JOIN, you'd have to run two separate queries and manually match rows by driver_id — JOIN does this automatically in one shot.
The result of this step: each row now has driver_id, driver_name, city, and fare together in one place — ready for the next aggregation step.

Calculate total earnings per driver

query.sql

SELECT d.driver_id,
       d.driver_name,
       d.city,
       SUM(t.fare) AS total_earnings
FROM drivers d
JOIN trips t ON d.driver_id = t.driver_id
GROUP BY d.driver_id, d.driver_name, d.city
ORDER BY total_earnings DESC;

💡 Explanation

SUM(t.fare) adds up all fare values for each driver across all their trips. For example, driver D001 has 3 trips: 250.50 + 180.00 + 320.75 = 751.25 total.
GROUP BY d.driver_id, d.driver_name, d.city tells SQL: group all rows with the same driver_id together, then calculate SUM separately for each group.
Why include driver_name and city in GROUP BY? SQL rule: every column in SELECT that is NOT inside an aggregate function (like SUM) must also appear in GROUP BY.
AS total_earnings gives a clean, readable name to the calculated column. Without it, the output column header would literally say SUM(t.fare) — confusing.
ORDER BY total_earnings DESC sorts results from highest earner to lowest. This makes it easy to spot the top drivers at a glance.
At this point you have one row per driver with their total earnings — but you still cannot filter top 3 per city. For that, you need ranking logic in the next step.

Get top 3 drivers per city using ROW_NUMBER

query.sql

SELECT driver_id, driver_name, city, total_earnings
FROM (
  SELECT d.driver_id,
         d.driver_name,
         d.city,
         SUM(t.fare) AS total_earnings,
         ROW_NUMBER() OVER (
           PARTITION BY d.city
           ORDER BY SUM(t.fare) DESC
         ) AS rn
  FROM drivers d
  JOIN trips t ON d.driver_id = t.driver_id
  GROUP BY d.driver_id, d.driver_name, d.city
) ranked
WHERE rn <= 3;

💡 Explanation

The previous step gave total earnings per driver — but we need top 3 per city, not overall top 3. For that, we must rank drivers within each city separately.
ROW_NUMBER() is a window function. Unlike GROUP BY which collapses multiple rows into one, window functions add a new column to existing rows without removing any rows.
OVER (...) defines the 'window' — the rules for how ROW_NUMBER should group and sort. Think of OVER as: 'apply this ranking logic inside this specific window of rows'.
PARTITION BY d.city splits drivers into groups by city before ranking. Chennai drivers get ranked among themselves. Delhi drivers get ranked among themselves. Each city is independent.
ORDER BY SUM(t.fare) DESC inside OVER means: within each city group, assign rank 1 to the highest earner, rank 2 to the second highest, rank 3 to third, and so on.
The inner query is wrapped in a subquery (aliased as 'ranked') because SQL evaluates WHERE before window functions. You cannot write WHERE rn <= 3 in the same query — it must be in an outer query.
WHERE rn <= 3 in the outer query keeps only rows where the rank is 1, 2, or 3 — meaning the top 3 earners from each city partition.
Why not just use LIMIT 3? LIMIT 3 would give the top 3 drivers globally across all cities. ROW_NUMBER with PARTITION BY is the correct approach when you need top-N within each group.

▶ Practice this live ← Browse all questions