Skip to content

Instantly share code, notes, and snippets.

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