Code snippets for Homework of RisingWave workshop

risingwave_homework.sql

-- The materialized view for questions 1 and 2

CREATE MATERIALIZED VIEW trip_stats AS
SELECT 
    tz1.zone AS pickup_zone,
    tz2.zone AS dropoff_zone,
    AVG(tpep_dropoff_datetime - tpep_pickup_datetime) AS avg_trip_time,
    MIN(tpep_dropoff_datetime - tpep_pickup_datetime) AS min_trip_time,
    MAX(tpep_dropoff_datetime - tpep_pickup_datetime) AS max_trip_time,
    COUNT(tpep_dropoff_datetime - tpep_pickup_datetime) AS no_of_trips
FROM trip_data td 
JOIN taxi_zone AS tz1 ON td.PULocationid = tz1.location_id
JOIN taxi_zone AS tz2 ON td.DOLocationid = tz2.location_id
GROUP BY 1,2 ; 

-- Question 1: highest average trip time between 2 zones

SELECT
    pickup_zone, 
    dropoff_zone,
    avg_trip_time
FROM trip_stats
ORDER BY avg_trip_time desc  
limit 1 ; 


-- Question 2: no. of trips between zones of highest average trip time 

SELECT
    pickup_zone, 
    dropoff_zone,
    no_of_trips
FROM trip_stats
ORDER BY avg_trip_time desc  
limit 1 ;  



-- Question 3: busiest 3 hubs 

CREATE MATERIALIZED VIEW latest_pickups AS
SELECT 
    MAX(tpep_pickup_datetime) AS max_pickup_time,
    MAX(tpep_pickup_datetime) - INTERVAL '17 hours' AS max_pickup_time_minus_17_hrs
FROM trip_data ; 

SELECT 
    tz.zone AS pickup_zone,
    COUNT(*) AS num_pickups
FROM trip_data td
JOIN taxi_zone tz ON td.pulocationid = tz.location_id
JOIN ( SELECT max_pickup_time_minus_17_hrs, max_pickup_time FROM latest_pickups) 
    AS T1
    ON td.tpep_pickup_datetime BETWEEN T1.max_pickup_time_minus_17_hrs 
        AND T1.max_pickup_time
GROUP BY tz.zone
ORDER BY num_pickups DESC
LIMIT 3 ;