final_queries_gtfs.sql

-- USE GTFS;

-- CREATE INDEX trip_updates__trip_id
-- ON trip_updates(trip_id);

-- CREATE INDEX trip_updates__stop_id
-- ON trip_updates(stop_id);

-- CREATE INDEX trip_updates__arrival_delay
-- ON trip_updates(arrival_delay);

-- CREATE INDEX trip_updates__departure_delay
-- ON trip_updates(departure_delay);

-- CREATE INDEX trip_updates__start_date
-- ON trip_updates(start_date);

-- CREATE INDEX trip_updates__route_id
-- ON trip_updates(route_id);

-- CREATE INDEX trip_updates__fetch_time
-- ON trip_updates(fetch_time);

-- ALTER TABLE GTFS.TRIP_UPDATES DROP INDEX trip_updates__route_id;

SELECT MIN(FETCH_TIME) FROM GTFS.TRIP_UPDATES WHERE FETCH_TIME >= '2019-02-11 09:30:00';

SELECT MIN(FETCH_TIME) FROM GTFS.TRIP_UPDATES WHERE FETCH_TIME >= '2019-02-15 14:30:00';

SELECT COUNT(1) FROM GTFS.TRIP_UPDATES;

SELECT DATE(FETCH_TIME), COUNT(1) FROM GTFS.TRIP_UPDATES GROUP BY DATE(FETCH_TIME);

SELECT ROUTE_ID, COUNT(DISTINCT TRIP_ID) FROM GTFS.TRIP_UPDATES GROUP BY ROUTE_ID;

SELECT ROUTE_ID, COUNT(DISTINCT STOP_ID) FROM GTFS.TRIP_UPDATES WHERE FETCH_TIME >= '2019-02-11' AND START_DATE <> '20190211' GROUP BY ROUTE_ID;

SELECT START_DATE, COUNT(DISTINCT TRIP_ID) FROM GTFS.TRIP_UPDATES 
GROUP BY START_DATE;

SELECT * FROM GTFS.TRIP_UPDATES WHERE FETCH_TIME > '2019-02-11' ORDER BY FETCH_TIME;

--

SELECT * FROM GTFS.ROUTES WHERE AGENCY_ID = 'GVB' AND ROUTE_SHORT_NAME IN ('907', '901', '22', '281', '4', '24', '54', '51');

SELECT DISTINCT(START_DATE) FROM GTFS.TRIP_UPDATES WHERE ROUTE_ID = '61617' AND FETCH_TIME >= '2019-02-10' AND START_DATE <> '20190210';

SELECT * FROM GTFS.TRIP_UPDATES WHERE ROUTE_ID = '61617' AND FETCH_TIME >= '2019-02-11' AND START_DATE <> '20190211' ORDER BY FETCH_TIME;

SELECT DISTINCT STOP_ID FROM GTFS.TRIP_UPDATES WHERE ROUTE_ID = '61617' AND FETCH_TIME >= '2019-02-11' AND START_DATE <> '20190211';

SELECT * FROM GTFS.TRIP_UPDATES WHERE ROUTE_ID = '60668' AND FETCH_TIME >= '2019-02-11' AND START_DATE <> '20190211' ORDER BY FETCH_TIME;

SELECT * FROM GTFS.TRIP_UPDATES WHERE TRIP_ID = '86197074' ORDER BY FETCH_TIME, STOP_SEQUENCE;

-- NIGHT BUS AVG DELAY ACROSS STOPS
SELECT STOP_ID, AVG(ARRIVAL_DELAY), AVG(DEPARTURE_DELAY) FROM GTFS.TRIP_UPDATES 
WHERE ROUTE_ID = '60668' AND FETCH_TIME >= '2019-02-11' AND START_DATE <> '20190211' GROUP BY STOP_ID;

-- AVG DELAY ACROSS STOPS FOR ALL OTHER LINES
SELECT ROUTE_ID, STOP_ID, AVG(ARRIVAL_DELAY), AVG(DEPARTURE_DELAY) FROM GTFS.TRIP_UPDATES 
WHERE ROUTE_ID <> '60668' AND FETCH_TIME >= '2019-02-11' AND START_DATE <> '20190211' AND START_DATE <> '20190219'
GROUP BY ROUTE_ID, STOP_ID ORDER BY ROUTE_ID;

SELECT ROUTE_ID, STOP_ID, MAX(ARRIVAL_DELAY), MAX(DEPARTURE_DELAY) FROM GTFS.TRIP_UPDATES 
WHERE ROUTE_ID = '61617' AND STOP_ID = '15340' AND FETCH_TIME >= '2019-02-11' AND START_DATE <> '20190211' AND START_DATE <> '20190219';

SELECT ROUTE_ID, STOP_ID, MIN(ARRIVAL_DELAY), MIN(DEPARTURE_DELAY) FROM GTFS.TRIP_UPDATES
WHERE ROUTE_ID = '61617' AND STOP_ID = '15340' AND FETCH_TIME >= '2019-02-11' AND START_DATE <> '20190211' AND START_DATE <> '20190219';

SELECT * FROM GTFS.TRIP_UPDATES WHERE ROUTE_ID = '61617' AND STOP_ID = '15340' AND ARRIVAL_DELAY = -3775
AND FETCH_TIME >= '2019-02-11' AND START_DATE <> '20190211' AND START_DATE <> '20190219' ORDER BY FETCH_TIME;

SELECT * FROM GTFS.TRIP_UPDATES WHERE TRIP_ID = '85235593' AND START_DATE = '20190218' ORDER BY FETCH_TIME, STOP_SEQUENCE;

SELECT * FROM GTFS.TRIP_UPDATES WHERE TRIP_ID = '85235593' AND STOP_ID = '15587' AND START_DATE = '20190218' ORDER BY FETCH_TIME, STOP_SEQUENCE;

SELECT ROUTE_ID, DIRECTION_ID, AVG(ARRIVAL_DELAY), AVG(DEPARTURE_DELAY),
MAX(ARRIVAL_DELAY), MAX(DEPARTURE_DELAY), MIN(ARRIVAL_DELAY), MIN(DEPARTURE_DELAY) FROM GTFS.TRIP_UPDATES 
WHERE ROUTE_ID <> '60668' AND FETCH_TIME >= '2019-02-11' AND START_DATE <> '20190211' AND START_DATE <> '20190219'
GROUP BY ROUTE_ID, DIRECTION_ID;

SELECT ROUTE_ID, DIRECTION_ID, AVG(ARRIVAL_DELAY), AVG(DEPARTURE_DELAY), MAX(ARRIVAL_DELAY), MAX(DEPARTURE_DELAY), 
MIN(ARRIVAL_DELAY), MIN(DEPARTURE_DELAY) FROM GTFS.TRIP_UPDATES 
WHERE ROUTE_ID = '60668' AND FETCH_TIME >= '2019-02-11' AND START_DATE <> '20190211'
GROUP BY ROUTE_ID, DIRECTION_ID;

-- STOP CLEANUP

SELECT * FROM GTFS.STOPS WHERE STOP_ID IN (SELECT DISTINCT STOP_ID FROM GTFS.TRIP_UPDATES
WHERE ROUTE_ID = '61617' AND FETCH_TIME >= '2019-02-11' AND START_DATE <> '20190211' AND START_DATE <> '20190219');

SELECT DIRECTION_ID, STOP_ID, COUNT(DISTINCT STOP_SEQUENCE) FROM GTFS.TRIP_UPDATES
WHERE ROUTE_ID = '61617' AND FETCH_TIME >= '2019-02-11' AND START_DATE <> '20190211' AND START_DATE <> '20190219' 
GROUP BY DIRECTION_ID, STOP_ID ORDER BY DIRECTION_ID, STOP_ID;

---

SELECT DISTINCT TRIP_ID FROM GTFS.TRIP_UPDATES 
WHERE ROUTE_ID = '49928' AND FETCH_TIME >= '2019-02-11' AND START_DATE <> '20190211' AND START_DATE <> '20190219'
AND ARRIVAL_DELAY > 450
UNION
SELECT DISTINCT TRIP_ID FROM GTFS.TRIP_UPDATES 
WHERE ROUTE_ID = '49928' AND FETCH_TIME >= '2019-02-11' AND START_DATE <> '20190211' AND START_DATE <> '20190219'
AND ARRIVAL_DELAY < -400;

SELECT ROUTE_ID, DIRECTION_ID, STDDEV_SAMP(ARRIVAL_DELAY), STDDEV_SAMP(DEPARTURE_DELAY), AVG(ARRIVAL_DELAY), AVG(DEPARTURE_DELAY), 
MAX(ARRIVAL_DELAY), MAX(DEPARTURE_DELAY), MIN(ARRIVAL_DELAY), MIN(DEPARTURE_DELAY) FROM GTFS.TRIP_UPDATES 
WHERE ROUTE_ID <> '60668' AND FETCH_TIME >= '2019-02-11' AND START_DATE <> '20190211' AND START_DATE <> '20190219'
GROUP BY ROUTE_ID, DIRECTION_ID;

SELECT ROUTE_ID, STDDEV_SAMP(ARRIVAL_DELAY), STDDEV_SAMP(DEPARTURE_DELAY), AVG(ARRIVAL_DELAY), AVG(DEPARTURE_DELAY), 
MAX(ARRIVAL_DELAY), MAX(DEPARTURE_DELAY), MIN(ARRIVAL_DELAY), MIN(DEPARTURE_DELAY) FROM GTFS.TRIP_UPDATES 
WHERE ROUTE_ID <> '60668' AND FETCH_TIME >= '2019-02-11' AND START_DATE <> '20190211' AND START_DATE <> '20190219'
GROUP BY ROUTE_ID;

SELECT ROUTE_ID, STDDEV_SAMP(ARRIVAL_DELAY), AVG(ARRIVAL_DELAY), MAX(ARRIVAL_DELAY), MIN(ARRIVAL_DELAY) FROM GTFS.TRIP_UPDATES 
WHERE ROUTE_ID <> '60668' AND FETCH_TIME >= '2019-02-11' AND START_DATE <> '20190211' AND START_DATE <> '20190219'
AND ARRIVAL_TIME != 0
GROUP BY ROUTE_ID;

SELECT ROUTE_ID, STDDEV_SAMP(DEPARTURE_DELAY), AVG(DEPARTURE_DELAY), MAX(DEPARTURE_DELAY), MIN(DEPARTURE_DELAY) FROM GTFS.TRIP_UPDATES 
WHERE ROUTE_ID <> '60668' AND FETCH_TIME >= '2019-02-11' AND START_DATE <> '20190211' AND START_DATE <> '20190219'
AND DEPARTURE_TIME != 0
GROUP BY ROUTE_ID;

---

SELECT TRIP_ID, MIN(ARRIVAL_DELAY), MIN(DEPARTURE_DELAY), AVG(ARRIVAL_DELAY), AVG(DEPARTURE_DELAY), MAX(ARRIVAL_DELAY), MAX(DEPARTURE_DELAY)
FROM GTFS.TRIP_UPDATES 
WHERE ROUTE_ID = '481' AND FETCH_TIME >= '2019-02-11' AND START_DATE <> '20190211' AND START_DATE <> '20190219'
AND ARRIVAL_DELAY > 600
GROUP BY TRIP_ID;

SELECT * FROM GTFS.TRIP_UPDATES WHERE TRIP_ID IN (SELECT DISTINCT TRIP_ID FROM GTFS.TRIP_UPDATES 
WHERE ROUTE_ID = '49928' AND FETCH_TIME >= '2019-02-11' AND START_DATE <> '20190211' AND START_DATE <> '20190219'
AND ARRIVAL_DELAY > 400) AND FETCH_TIME >= '2019-02-11' AND START_DATE <> '20190211' AND START_DATE <> '20190219'
ORDER BY FETCH_TIME, STOP_SEQUENCE;

SELECT FROM_UNIXTIME(TRIP_UPDATES.ARRIVAL_TIME), TRIP_UPDATES.* FROM GTFS.TRIP_UPDATES 
WHERE TRIP_ID IN ('85235593') AND FETCH_TIME >= '2019-02-11' AND START_DATE <> '20190211' AND START_DATE <> '20190219'
AND STOP_ID = '1537584'
ORDER BY FETCH_TIME, STOP_SEQUENCE;

SELECT COUNT(1) FROM GTFS.TRIP_UPDATES
WHERE FETCH_TIME >= '2019-02-11' AND START_DATE <> '20190211' AND START_DATE <> '20190219'
AND ARRIVAL_TIME = 0
AND DEPARTURE_TIME = 0;

SELECT COUNT(1) FROM GTFS.TRIP_UPDATES
WHERE FETCH_TIME >= '2019-02-11' AND START_DATE <> '20190211' AND START_DATE <> '20190219'
AND ARRIVAL_TIME = 0
AND DEPARTURE_TIME = 0
AND ARRIVAL_DELAY = 0
AND DEPARTURE_DELAY = 0;

SELECT COUNT(1) FROM GTFS.TRIP_UPDATES
WHERE FETCH_TIME >= '2019-02-11' AND START_DATE <> '20190211' AND START_DATE <> '20190219';

-- DELETE FROM;

SELECT ROUTE_ID, STDDEV_SAMP(ARRIVAL_DELAY), STDDEV_SAMP(DEPARTURE_DELAY), AVG(ARRIVAL_DELAY), AVG(DEPARTURE_DELAY), 
MAX(ARRIVAL_DELAY), MAX(DEPARTURE_DELAY), MIN(ARRIVAL_DELAY), MIN(DEPARTURE_DELAY) FROM GTFS.TRIP_UPDATES 
WHERE ROUTE_ID = '61617' AND FETCH_TIME >= '2019-02-11' AND START_DATE <> '20190211' AND START_DATE <> '20190219'
-- AND TRIP_ID NOT IN ('85262552', '85262891', '85262892', '85262939', '85262996', '85263083', '85263475', '85263561', '85263838', '85263841', '86633426')
-- AND TRIP_ID NOT IN ('85242847','85225340','85252301','85243862','85252857','85223857','85227210','85233952','85238565','85228592','85236613','85255825','85247172','85233764','85233766','85219783','85252305','85248007','85252858','85232474','85247176','85232376','85245893')
AND TRIP_ID NOT IN ('85235593','85221247','85235456','85234369','85239963','85245862','85235109','85226686','85255230','85234656','85231390','85233868','85229966','85244137','85231676','85256979','85245495','85237303','85246650','85232419','85234579','85245856','85221417','85229938','85224831','85231726','85254162','85224834','85236711','85245858','85243311','85223151','85244097','85245497','85244067','85239860','85232458','85244099','85220593','85223149','85250999','85221756','85236706','85249842','85254160','85249886','85258037','85251187','85225656','85220630','85226759','85253382','85252417','85249075','85245857','85248611','85234655','85244209','85234657','85246514','85222574','85235465')
GROUP BY ROUTE_ID;

-- ARRIVAL AND DEPARTURE DELAY DISTRIBUTION

SELECT '61617' AS ROUTE, ARRIVAL_DELAY, COUNT(DISTINCT TRIP_ID) FROM GTFS.TRIP_UPDATES 
WHERE ROUTE_ID = '61617' AND FETCH_TIME >= '2019-02-11' AND START_DATE <> '20190211' AND START_DATE <> '20190219'
-- AND TRIP_ID NOT IN ('85262552', '85262891', '85262892', '85262939', '85262996', '85263083', '85263475', '85263561', '85263838', '85263841', '86633426')
-- AND TRIP_ID NOT IN ('85242847','85225340','85252301','85243862','85252857','85223857','85227210','85233952','85238565','85228592','85236613','85255825','85247172','85233764','85233766','85219783','85252305','85248007','85252858','85232474','85247176','85232376','85245893')
AND TRIP_ID NOT IN ('85235593','85221247','85235456','85234369','85239963','85245862','85235109','85226686','85255230','85234656','85231390','85233868','85229966','85244137','85231676','85256979','85245495','85237303','85246650','85232419','85234579','85245856','85221417','85229938','85224831','85231726','85254162','85224834','85236711','85245858','85243311','85223151','85244097','85245497','85244067','85239860','85232458','85244099','85220593','85223149','85250999','85221756','85236706','85249842','85254160','85249886','85258037','85251187','85225656','85220630','85226759','85253382','85252417','85249075','85245857','85248611','85234655','85244209','85234657','85246514','85222574','85235465')
GROUP BY ARRIVAL_DELAY;

SELECT '61617' AS ROUTE, TRIP_ID, COUNT(DISTINCT ARRIVAL_DELAY) FROM GTFS.TRIP_UPDATES 
WHERE ROUTE_ID = '61617' AND FETCH_TIME >= '2019-02-11' AND START_DATE <> '20190211' AND START_DATE <> '20190219'
-- AND TRIP_ID NOT IN ('85262552', '85262891', '85262892', '85262939', '85262996', '85263083', '85263475', '85263561', '85263838', '85263841', '86633426')
-- AND TRIP_ID NOT IN ('85242847','85225340','85252301','85243862','85252857','85223857','85227210','85233952','85238565','85228592','85236613','85255825','85247172','85233764','85233766','85219783','85252305','85248007','85252858','85232474','85247176','85232376','85245893')
AND TRIP_ID NOT IN ('85235593','85221247','85235456','85234369','85239963','85245862','85235109','85226686','85255230','85234656','85231390','85233868','85229966','85244137','85231676','85256979','85245495','85237303','85246650','85232419','85234579','85245856','85221417','85229938','85224831','85231726','85254162','85224834','85236711','85245858','85243311','85223151','85244097','85245497','85244067','85239860','85232458','85244099','85220593','85223149','85250999','85221756','85236706','85249842','85254160','85249886','85258037','85251187','85225656','85220630','85226759','85253382','85252417','85249075','85245857','85248611','85234655','85244209','85234657','85246514','85222574','85235465')
GROUP BY TRIP_ID;

SELECT FLOOR(ARRIVAL_DELAY/60)*60 AS BIN_FLOOR, COUNT(DISTINCT TRIP_ID) FROM GTFS.TRIP_UPDATES 
WHERE ROUTE_ID = '61617' AND FETCH_TIME >= '2019-02-11' AND START_DATE <> '20190211' AND START_DATE <> '20190219'
AND TRIP_ID NOT IN ('85235593','85221247','85235456','85234369','85239963','85245862','85235109','85226686','85255230','85234656','85231390','85233868','85229966','85244137','85231676','85256979','85245495','85237303','85246650','85232419','85234579','85245856','85221417','85229938','85224831','85231726','85254162','85224834','85236711','85245858','85243311','85223151','85244097','85245497','85244067','85239860','85232458','85244099','85220593','85223149','85250999','85221756','85236706','85249842','85254160','85249886','85258037','85251187','85225656','85220630','85226759','85253382','85252417','85249075','85245857','85248611','85234655','85244209','85234657','85246514','85222574','85235465')
GROUP BY 1 ORDER BY 1;

SELECT FLOOR(DT.AAD/60)*60 AS BIN_FLOOR, COUNT(DISTINCT DT.TRIP_ID) FROM(
SELECT TRIP_ID, AVG(ARRIVAL_DELAY) AS AAD FROM GTFS.TRIP_UPDATES 
WHERE ROUTE_ID = '61617' AND FETCH_TIME >= '2019-02-11' AND START_DATE NOT IN ('20190210', '20190211', '20190219')
AND TRIP_ID NOT IN ('85235593','85221247','85235456','85234369','85239963','85245862','85235109','85226686','85255230','85234656','85231390','85233868','85229966','85244137','85231676','85256979','85245495','85237303','85246650','85232419','85234579','85245856','85221417','85229938','85224831','85231726','85254162','85224834','85236711','85245858','85243311','85223151','85244097','85245497','85244067','85239860','85232458','85244099','85220593','85223149','85250999','85221756','85236706','85249842','85254160','85249886','85258037','85251187','85225656','85220630','85226759','85253382','85252417','85249075','85245857','85248611','85234655','85244209','85234657','85246514','85222574','85235465')
GROUP BY TRIP_ID) DT
GROUP BY 1 ORDER BY 1;

---

-- 468
SELECT FLOOR(DT.AAD/30)*30 AS BIN_FLOOR, COUNT(DISTINCT DT.START_DATE, DT.TRIP_ID) FROM(
SELECT START_DATE, TRIP_ID, AVG(ARRIVAL_DELAY) AS AAD FROM GTFS.TRIP_UPDATES 
WHERE ROUTE_ID = '468' AND FETCH_TIME >= '2019-02-11' AND START_DATE NOT IN ('20190210', '20190211', '20190219')
AND TRIP_ID NOT IN ('85253011','85256908','85231464')
GROUP BY START_DATE, TRIP_ID) DT
GROUP BY 1 ORDER BY 1;

-- 481
SELECT FLOOR(DT.AAD/30)*30 AS BIN_FLOOR, COUNT(DISTINCT DT.START_DATE, DT.TRIP_ID) FROM(
SELECT START_DATE, TRIP_ID, AVG(ARRIVAL_DELAY) AS AAD FROM GTFS.TRIP_UPDATES 
WHERE ROUTE_ID = '481' AND FETCH_TIME >= '2019-02-11' AND START_DATE NOT IN ('20190210', '20190211', '20190219')
AND TRIP_ID NOT IN ('84862012','84860405','84861635','84862280','84862375','84860476','84860626','84862351','84860733','84860737','84860491')
GROUP BY START_DATE, TRIP_ID) DT
GROUP BY 1 ORDER BY 1;

SELECT DISTINCT TU.DIRECTION_ID, S.* FROM GTFS.STOPS S, GTFS.TRIP_UPDATES TU 
WHERE S.STOP_ID = TU.STOP_ID AND TU.ROUTE_ID = '468' AND TU.FETCH_TIME >= '2019-02-11' AND TU.START_DATE NOT IN ('20190210', '20190211', '20190219')
ORDER BY S.STOP_NAME;

SELECT TU.STOP_ID, COUNT(DISTINCT TRIP_ID) FROM GTFS.TRIP_UPDATES TU WHERE TU.STOP_ID IN ('96467', '96458')
AND TU.ROUTE_ID = '468' AND TU.FETCH_TIME >= '2019-02-11' AND TU.START_DATE NOT IN ('20190210', '20190211', '20190219') GROUP BY TU.STOP_ID;

SELECT DISTINCT TRIP_ID FROM GTFS.TRIP_UPDATES WHERE ROUTE_ID = '468' 
AND FETCH_TIME >= '2019-02-11' AND START_DATE NOT IN ('20190210', '20190211', '20190219') AND STOP_ID = '96458';

SELECT * FROM GTFS.TRIP_UPDATES WHERE ROUTE_ID = '468' AND TRIP_ID IN ('85231462', '85221347')
AND FETCH_TIME >= '2019-02-11' AND START_DATE NOT IN ('20190210', '20190211', '20190219') ORDER BY FETCH_TIME, STOP_SEQUENCE;

SELECT START_DATE, COUNT(DISTINCT TRIP_ID) FROM GTFS.TRIP_UPDATES WHERE ROUTE_ID = '481'
AND FETCH_TIME >= '2019-02-11' AND START_DATE NOT IN ('20190210', '20190211', '20190219')
GROUP BY START_DATE;