DE_ZoomCamp Assignment Week1

--Question 3: Count records
select date(tpep_pickup_datetime), count(tpep_pickup_datetime) from public.yellow_taxi_trips
where date(tpep_pickup_datetime)='2021-01-15'
group by date(tpep_pickup_datetime)

--Question 4: Largest tip for each day
select date(tpep_pickup_datetime), max(tip_amount) from public.yellow_taxi_trips
where (select extract(month from(date(tpep_pickup_datetime))))='01'
group by date(tpep_pickup_datetime)
order by max(tip_amount) desc limit 1

--Question 5: Most popular destination
select "Zone", count("Zone") from public.yellow_taxi_trips Y
join taxi_zone Z on Z."LocationID" = Y."DOLocationID"
where "PULocationID"='43' and date(tpep_pickup_datetime)='"2021-01-14"'
group by "Zone"
order by count("Zone") desc limit 1


--Question 6: Most expensive route 
with cte as (
select Y."PULocationID", Y."DOLocationID",
CONCAT(D."Zone", '/', P."Zone") As PU_DO_Pair, Y."total_amount"
from public.yellow_taxi_trips Y
join taxi_zone D on D."LocationID" = Y."PULocationID"
join taxi_zone P on P."LocationID" = Y."DOLocationID"
)
select pu_do_pair, avg(total_amount) as Ave_Price from cte
group by pu_do_pair
order by Ave_Price  desc limit 1