citi_bike.sql

SELECT
  start_station_id,
  start_station_name,
  start_station_latitude,
  start_station_longitude,
  
  # calculate the average distance for all trips from each start station
  
  AVG(st_distance(st_geogpoint(start_station_latitude,
        start_station_longitude),
      st_geogpoint(end_station_latitude,
        end_station_longitude)))
FROM
  `bigquery-public-data.new_york_citibike.citibike_trips`
WHERE
  starttime BETWEEN '2017-06-01T00:00:00'
  AND '2017-06-30T00:00:00'
GROUP BY
  start_station_id,
  start_station_name,
  start_station_longitude,
  start_station_latitude
ORDER BY
  AVG(st_distance(st_geogpoint(start_station_latitude,
        start_station_longitude),
      st_geogpoint(end_station_latitude,
        end_station_longitude))) DESC