WITH
  base AS (
  SELECT
    station_id AS store_id,
    region_id,
    CAPACITY,
    ST_GEOGPOINT(longitude,
      latitude) AS geogpoint,
    # order the stores by capacity per region
    ROW_NUMBER() OVER (PARTITION BY region_id ORDER BY CAPACITY DESC) AS rnk
  FROM
    `bigquery-public-data.new_york_citibike.citibike_stations`),
  top_leads AS (
  SELECT
    * EXCEPT(rnk)
  FROM
    base
  WHERE
    rnk = 1 # select only store with highest capacity in the region
    )
SELECT
  base.region_id,
  base.store_id,
  base.CAPACITY,
  base.geogpoint,
  # calculate distance between top lead in the region with current lead
  ST_DISTANCE(top_leads.geogpoint,
    base.geogpoint) AS distance_from_top_lead,
FROM
  base
INNER JOIN
  top_leads
USING
  (region_id)
ORDER BY
  base.region_id,
  distance_from_top_lead