These are some interesting queries you can run on this BigQuery table: https://bigquery.cloud.google.com/table/nyctaximap:dataflow.nyc_output_join_fare_distinct . The data has been aggregated using Google Cloud Dataflow. For more background information on how the data has been processed see: https://googlecloudplatform.blogspot.com

avg_fare_amount.sql

SELECT
  pickup_polyId,
  SUM(fare_amount)/COUNT(*) AS average_fare,
  COUNT(*) AS no_of_trips
FROM
  [nyctaximap:dataflow.nyc_output_join_fare_distinct]
WHERE
  fare_amount!=0
GROUP BY
  pickup_polyId
HAVING
  no_of_trips > 100000
ORDER BY
  average_fare DESC;

avg_tip_amount.sql

SELECT
  pickup_polyId,
  SUM(tip_amount)/COUNT(*) AS average_tip,
  COUNT(*) AS no_of_trips
FROM
  [nyctaximap:dataflow.nyc_output_join_fare_distinct]
WHERE
  tip_amount!=0
GROUP BY
  pickup_polyId
HAVING
  no_of_trips > 50000
ORDER BY
  average_tip DESC;

no_of_trips.sql

SELECT
  pickup_polyId,
  COUNT(*) AS no_of_trips
FROM
  [nyctaximap:dataflow.nyc_output_join_fare_distinct]
GROUP BY
  pickup_polyId
ORDER BY
  no_of_trips DESC;