Skip to content

Instantly share code, notes, and snippets.

@BohuTANG

BohuTANG/ontime-query.sql

Created January 21, 2022 04:38
Show Gist options
  • Save BohuTANG/63484db525d77270fb2f1594ba96bf8e to your computer and use it in GitHub Desktop.
Save BohuTANG/63484db525d77270fb2f1594ba96bf8e to your computer and use it in GitHub Desktop.
Download ZIP
ontime-query.sql
Raw
ontime-query.sql
Q1:
SELECT DayOfWeek, count(*) AS c FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;
Q2:
SELECT DayOfWeek, count(*) AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY DayOfWeek ORDER BY c DESC;
Q3:
SELECT Origin, count(*) AS c FROM ontime WHERE DepDelay>10 AND Year >= 2000 AND Year <= 2008 GROUP BY Origin ORDER BY c DESC LIMIT 10;
Q4:
SELECT IATA_CODE_Reporting_Airline AS Carrier, count() FROM ontime WHERE DepDelay>10 AND Year = 2007 GROUP BY Carrier ORDER BY count() DESC;
Q5:
SELECT IATA_CODE_Reporting_Airline AS Carrier, avg(cast(DepDelay>10 as Int8))*1000 AS c3 FROM ontime WHERE Year=2007 GROUP BY Carrier ORDER BY c3 DESC;
Q6:
SELECT IATA_CODE_Reporting_Airline AS Carrier, avg(cast(DepDelay>10 as Int8))*1000 AS c3 FROM ontime WHERE Year>=2000 AND Year <=2008 GROUP BY Carrier ORDER BY c3 DESC;
Q7:
SELECT IATA_CODE_Reporting_Airline AS Carrier, avg(DepDelay) * 1000 AS c3 FROM ontime WHERE Year >= 2000 AND Year <= 2008 GROUP BY Carrier
Q8:
SELECT Year, avg(DepDelay) FROM ontime GROUP BY Year;
Q9:
select Year, count(*) as c1 from ontime group by Year;
Q10:
SELECT avg(cnt) FROM (SELECT Year,Month,count(*) AS cnt FROM ontime WHERE DepDel15=1 GROUP BY Year,Month) a;
Q11:
select avg(c1) from (select Year,Month,count(*) as c1 from ontime group by Year,Month) a;
Q12:
SELECT OriginCityName, DestCityName, count(*) AS c FROM ontime GROUP BY OriginCityName, DestCityName ORDER BY c DESC LIMIT 10;
Q13:
SELECT OriginCityName, count(*) AS c FROM ontime GROUP BY OriginCityName ORDER BY c DESC LIMIT 10;
Q14:
select count(*) from ontime
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment