Created
February 3, 2017 15:37
-
-
Save mmmayo13/3c3cfdbce8caf54a2c06cd17a5af15d6 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
CASE WHEN TipPercentage < 0 THEN 'No Tip' | |
WHEN TipPercentage BETWEEN 0 AND 5 THEN 'Less but still a Tip' | |
WHEN TipPercentage BETWEEN 5 AND 10 THEN 'Decent Tip' | |
WHEN TipPercentage > 10 THEN 'Good Tip' | |
ELSE 'Something different' | |
END AS TipRange, | |
Hr, | |
Wk, | |
TripMonth, | |
Trips, | |
Tips, | |
AverageSpeed, | |
AverageDistance, | |
TipPercentage, | |
Tipbin | |
FROM | |
(SELECT | |
EXTRACT(HOUR from pickup_datetime) As Hr, | |
EXTRACT(DAYOFWEEK from pickup_datetime) As Wk, | |
Extract (MONTH from pickup_datetime) As TripMonth, | |
case when tip_amount=0 then 'No Tip' | |
when (tip_amount > 0 and tip_amount <=5) then '0-5' | |
when (tip_amount > 5 and tip_amount <=10) then '5-10' | |
when (tip_amount > 10 and tip_amount <=20) then '10-20' | |
when tip_amount > 20 then '> 20' | |
else 'other' | |
end as Tipbin, | |
COUNT(*) Trips, | |
SUM(tip_amount) as Tips, | |
ROUND(AVG(trip_distance / | |
TIMESTAMP_DIFF(dropoff_datetime,pickup_datetime,second))*3600,1) as AverageSpeed, | |
ROUND(AVG(trip_distance),1) as AverageDistance, | |
ROUND(avg((tip_amount)/(total_amount-tip_amount))*100,3) as TipPercentage | |
FROM `bigquery-public-data.new_york.tlc_yellow_trips_2015` | |
WHERE trip_distance >0 | |
AND fare_amount/trip_distance BETWEEN 2 AND 10 | |
AND dropoff_datetime > pickup_datetime | |
group by 1,2,3,tip_amount,total_amount,tipbin) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment