Driver earnings rank

Ops wants a leaderboard of drivers by money earned.

A driver’s earnings are the sum of fare over their trips with status = 'completed'. Leave out drivers with no completed trips.

Rank drivers by earnings, highest first, starting at 1. Drivers with equal earnings share a rank, and the next rank skips ahead: two drivers tied at 1 are followed by rank 3. (This is “competition” ranking, like a race with a shared podium place.)

Return one row per driver:

column	type	meaning
driver_id	integer	the driver
name	text	the driver’s name
earnings	decimal	total completed fare, rounded to 2 decimals
earnings_rank	integer	1 for the top earner; ties share a rank

Row order doesn’t matter.

Tables: drivers, trips.

Approach

Group to one row per driver, then rank the groups by their sum in the same SELECT.

SELECT d.driver_id,
       d.name,
       ROUND(SUM(t.fare), 2) AS earnings,
       RANK() OVER (ORDER BY SUM(t.fare) DESC) AS earnings_rank
FROM drivers d
JOIN trips t ON t.driver_id = d.driver_id
WHERE t.status = 'completed'
GROUP BY d.driver_id, d.name;

Why it works

SQL evaluates WHERE, then GROUP BY, then window functions, then the final SELECT list. So the window sees one row per driver and can order by an aggregate. You don’t need a subquery.

RANK() gives each row 1 + the number of rows that sort strictly ahead of it. Two drivers tied for first both have zero rows ahead, so both get 1, and the next driver has two ahead, so gets 3. DENSE_RANK() would give 2, and ROW_NUMBER() would split the tie arbitrarily.

The inner join plus WHERE status = 'completed' drops drivers with no completed trips, as asked.

Under the hood

The hash aggregate produces 50 rows. The window operator then sorts them by SUM(t.fare) DESC and walks the sorted run once. For RANK it only remembers the previous sort key and the current row number: same key, reuse the rank; new key, rank = row number. No partition means one sort over the whole result.