Skip to content

Instantly share code, notes, and snippets.

@tswast

tswast/nyc-citibike-groups-destinations.sql

Last active September 13, 2017 15:26
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save tswast/6f64b09f5cd12fcb890bf30db06c6133 to your computer and use it in GitHub Desktop.
Save tswast/6f64b09f5cd12fcb890bf30db06c6133 to your computer and use it in GitHub Desktop.
Download ZIP
Most popular Citibike destinations for groups.
Raw
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment