Skip to content

Instantly share code, notes, and snippets.

@thorwebdev
Last active August 29, 2015 14:11
Show Gist options
  • Save thorwebdev/b6206ee6ca95ef02daf0 to your computer and use it in GitHub Desktop.
Save thorwebdev/b6206ee6ca95ef02daf0 to your computer and use it in GitHub Desktop.
Calculate the average fare amount for each area. Exclude fares with value 0.
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment