1.Understanding docker first run

image.png

2.db:5432

3.Trip Segmentation Count

with cte1 as (
select index ,case when trip_distance <=1 then '1m'
when trip_distance >1 and trip_distance<=3 then '3m'
when trip_distance >3 and trip_distance <=7 then '7m'
when trip_distance >7 and trip_distance<=10 then '10m'
else '10m+' end as trip_levels
from green_tripdata)

select count(index) as cnt,trip_levels from cte1
group by trip_levels

image.png

4.Longest trip for each day

select lpep_pickup_datetime::date,trip_distance from green_tripdata
order by trip_distance desc

image.png

5.. Three biggest pickup zones

with cte1 as (
select  g.total_amount,g."PULocationID",z."Zone" as zone from green_tripdata g join 
zone_lookup z on g."PULocationID" = z."LocationID"
	where lpep_pickup_datetime::date = '2019-10-18')

select sum(total_amount),zone from cte1
	group by zone
	order by sum(total_amount) desc

image.png