Rank rows with window functions

Ranking a vehicle against others of the same make is a daily analyst question. Window functions rank rows without collapsing them into groups.

How it works

fact_vehicle_summary has one row per vehicle. A window function looks at the rows in the same partition, here the same make, and gives each row a number based on its place in the order you choose. GROUP BY would leave one row per make. A window function keeps every vehicle.

Common mistake

Ranking by a value that has ties, with no tie-breaker, gives several rows the same rank. A later condition such as rank <= 3 can then return more than three rows. Add a unique key to the ORDER BY when you need exactly n rows.

Worked example

SELECT make, vehicle_id, mileage_stats.last_recorded_mileage AS last_mileage,
  RANK() OVER (PARTITION BY make ORDER BY mileage_stats.last_recorded_mileage DESC) AS mileage_rank
FROM `learn.mot.fact_vehicle_summary`
WHERE make = 'SAAB' AND mileage_stats.last_recorded_mileage IS NOT NULL

Every SAAB vehicle with a recorded mileage stays in the result. The window only adds a column.

The vehicle with the highest last mileage gets mileage_rank 1. If two vehicles shared a mileage they would share a rank.

All BigQuery lessons