ZOOM-CAMP 1

HW1.sql

3
SELECT COUNT(*)
FROM yellowtripdata
WHERE tpep_pickup_datetime::date = '2021-01-15';

4
SELECT MAX(tip_amount), tpep_pickup_datetime::date
FROM public.yellowtripdata
WHERE EXTRACT(MONTH FROM tpep_pickup_datetime) = 1
GROUP BY tpep_pickup_datetime::date
ORDER BY MAX(tip_amount) DESC;


5
SELECT *
FROM taxizone a
         JOIN

     (SELECT COUNT(dolocationid), dolocationid
      FROM taxizone
               JOIN yellowtripdata on taxizone."LocationID" = yellowtripdata.pulocationid
      WHERE "Zone" = 'Central Park'
        AND EXTRACT(MONTH FROM tpep_pickup_datetime) = 1
        AND EXTRACT(DAY FROM tpep_pickup_datetime) = 14
      GROUP BY dolocationid
      ORDER BY COUNT(dolocationid) DESC
      LIMIT 1) b ON a."LocationID" = b.dolocationid;



6
SELECT a."Zone", c."Zone"
FROM public.taxizone a
         INNER JOIN
     (SELECT pulocationid, dolocationid
      FROM yellowtripdata
      GROUP BY pulocationid, dolocationid
      ORDER BY AVG(total_amount) DESC
      LIMIT 1) b ON a."LocationID" = b.pulocationid
         INNER JOIN
     public.taxizone c ON b.dolocationid = c."LocationID";