Most popular Citibike destinations for groups.

nyc-citibike-groups-destinations.sql

#standardSql
SELECT
  group_trips / (single_trips + group_trips) AS percent_groups,
  single_trips + group_trips AS total_trips,
  q.end_station_id AS end_station_id,
  stations.name AS name,
  stations.latitude AS latitude,
  stations.longitude AS longitude
FROM (
  SELECT
    COUNTIF(group_size = 1) AS single_trips,
    COUNTIF(group_size != 1) AS group_trips,
    end_station_id
  FROM (
    SELECT
      ROUND(UNIX_SECONDS(starttime) / 120) AS start,
      -- round to nearest 2 minutes
      ROUND(UNIX_SECONDS(stoptime) / 120) AS stop,
      -- round to nearest 2 minutes
      start_station_id,
      end_station_id,
      COUNT(*) AS group_size
    FROM
      `bigquery-public-data.new_york.citibike_trips`
    GROUP BY
      start,
      stop,
      start_station_id,
      end_station_id )
  GROUP BY
    end_station_id ) q
LEFT JOIN
  `bigquery-public-data.new_york.citibike_stations` AS stations
ON
  q.end_station_id = stations.station_id
ORDER BY
  percent_groups DESC