Top three drivers per city

Each city manager wants a podium: their three highest-earning drivers, and how much of the city’s money each one brought in.

Use only trips with status = 'completed'. A driver’s earnings in a city are the sum of fare over their completed trips in that city (use trips.city). The city total is the sum of fare over every completed trip in that city.

Rank drivers inside each city by earnings, highest first, with ties sharing a rank and the next rank skipping (1, 2, 2, 4). Keep every driver whose rank is 3 or better. A tie for third place keeps both drivers, so a city can return more than three rows. A city with fewer than three drivers returns all of them.

Return:

column	type	meaning
city	text	the city
driver_rank	integer	rank inside the city, 1 = top earner
driver_id	integer	the driver
earnings	decimal	the driver’s completed fares in that city, rounded to 2 decimals
city_share_pct	decimal	earnings / city total * 100, rounded to 1 decimal

Order matters: by city, then driver_rank, then driver_id.

Tables: trips.

Approach

Aggregate to (city, driver), add a per-city rank and a per-city total as window columns, then keep ranks 1 to 3.

WITH earnings AS (
  SELECT city, driver_id, SUM(fare) AS earnings
  FROM trips
  WHERE status = 'completed'
  GROUP BY city, driver_id
),
ranked AS (
  SELECT city,
         driver_id,
         earnings,
         RANK() OVER (PARTITION BY city ORDER BY earnings DESC) AS driver_rank,
         100.0 * earnings / SUM(earnings) OVER (PARTITION BY city) AS share
  FROM earnings
)
SELECT city,
       driver_rank,
       driver_id,
       ROUND(earnings, 2) AS earnings,
       ROUND(share, 1) AS city_share_pct
FROM ranked
WHERE driver_rank <= 3
ORDER BY city, driver_rank, driver_id;

Why it works

Order of operations is the whole problem. The share needs the total of every driver in the city, and the top-three filter throws most drivers away. So the share is computed in ranked, where all drivers are still present, and the filter runs one level up.

SUM(earnings) OVER (PARTITION BY city) has no ORDER BY, so its frame is the whole partition: every row gets the full city total without collapsing rows the way GROUP BY would.

RANK() <= 3 keeps ties at the cutoff. Ranks can skip (1, 1, 3), so “rank 3 or better” may be three rows or more, and that’s intended. The final ORDER BY uses driver_id as a tie-breaker so tied drivers always print in the same order.

Under the hood

Both window columns use the same PARTITION BY city, so DuckDB partitions the rows once. Inside each partition it sorts by earnings DESC for the rank. The partition-wide SUM needs no sort at all: it’s computed once per partition and copied onto each row. The outer ORDER BY is a final, small sort over the surviving 15 or so rows.