Percentiles and buckets: NTILE, PERCENTILE_CONT, PERCENT_RANK

Is a vehicle in the top quarter for mileage, and is its defect count ordinary for its make? Three window functions place a row among its peers.

How it works

Line the TESLA vehicles up by last recorded mileage. Here NTILE(4) cuts the line into four groups of nearly equal size and numbers them 1 to 4. Next, PERCENT_RANK gives each vehicle its place in the line as a number from 0 to 1. Last, PERCENTILE_CONT works the other way round. You give it a place such as 0.5 and it returns the value found there. A place of 0.5 is the median, the middle value. All three are window functions, so every vehicle stays in the result.

Common mistake

A bucket number only says where a row falls in the order. Without a unique key at the end of the ORDER BY, tied rows can change buckets from one run to the next. Also, PERCENTILE_CONT with OVER (PARTITION BY make) returns one row per vehicle. Without DISTINCT the result has a row per vehicle where you wanted one per make.

Worked example

SELECT vehicle_id, mileage_stats.last_recorded_mileage AS last_mileage,
  NTILE(4) OVER (ORDER BY mileage_stats.last_recorded_mileage, vehicle_id) AS quartile,
  PERCENTILE_CONT(mileage_stats.last_recorded_mileage, 0.5) OVER () AS median_mileage
FROM `learn.mot.fact_vehicle_summary`
WHERE make = 'SAAB'
ORDER BY last_mileage

SAAB has 8 vehicles, so each quartile holds 2 of them. The two lowest mileages are in quartile 1.

median_mileage is the same on every row, because OVER () makes all 8 vehicles one partition. With 8 vehicles it is the average of the 4th and 5th mileage.

All BigQuery lessons