Description | SQL |
Count of all the rows | SELECT count(*) FROM ontime\g |
Number of flights per year | SELECT year,count(*) as c1 from ontime group by YEAR;\g |
Percentage of delays for each carrier for 2016 year | SELECT t.carrier, c, c2, c*1000/c2 as c3 FROM (SELECT carrier, count(*) AS c FROM ontime WHERE DepDelay>10 AND Year=2016 GROUP BY carrier) t JOIN (SELECT carrier, count(*) AS c2 FROM ontime WHERE Year=2016 GROUP BY carrier) t2 ON (t.Carrier=t2.Carrier) ORDER BY c3 DESC;\g |
Percent of flights delayed more than 10 minutes per year | SELECT t.year, c1/c2 FROM (select year,count(*)*1000 as c1 from ontime WHERE DepDelay>10 GROUP BY Year) t JOIN (select year,count(*) as c2 from ontime GROUP BY year) t2 ON (t.year=t2.year);\g |
Count of delays per airport for years 2010–2016 | SELECT Origin, count(*) AS c FROM ontime WHERE DepDelay>10 AND Year BETWEEN 2010 AND 2016 GROUP BY Origin ORDER BY c DESC fetch first 10 rows only;\g |
Cities with the most flights in 2016 | SELECT top 10 origincityname as City, count(*) as Num_of_flights FROM ontime WHERE Year = 2016 GROUP BY origincityname ORDER BY Num_of_flights DESC;\g |
Top 10 flights with biggest average delays (a flight is defined by the carrier, departure airport, and destination) | SELECT c.carrier, origin + ' (' + origincityname + ')' AS origin, dest + ' (' + destcityname + ')' AS dest, avg(arrdelayminutes) AS TotalArrivalDelay, avg(carrierdelay) as carrierdelay, avg(weatherdelay) AS weatherdelay, avg(nasdelay) AS nasdelay, avg(SecurityDelay) SecurityDelay, avg(LateAircraftDelay) LateAircraftDelay FROM ontime o LEFT JOIN carriers c ON o.carrier=c.ccode WHERE o.ArrDelay > 10 AND o.Cancelled <> 1 AND o.year = 2016 GROUP BY 1,2,3 ORDER BY 4 DESC FETCH FIRST 50 ROWS ONLY;\g |
Percentage of a flight time when the airplane left from the gate but is not in flight vs. total duration, per airline in 2016 | SELECT o.carrier, c.carrier, 1- avg(airtime)/ avg (ActualElapsedTime) AS pct_time_not_in_flight FROM ontime o LEFT JOIN carriers c ON o.carrier=c.ccode where year=2016 group by 1,2 order by 3 DESC;\g |
What cities are served by the most other cities | SELECT top 10 destcityname, count(distinct origincityname) from ontime where year = 2016 group by 1 order by 2 desc;\g |
Top 10 markets with most destinations | SELECT ct.origincitymarketid , LISTAGG(DISTINCT c.origincityname, '; ') WITHIN GROUP (ORDER BY c.origincityname) as originarea,count(*) AS num_of_destinations FROM ( SELECT origincitymarketid, destcitymarketid FROM ontime WHERE year = 2016 GROUP BY 1,2) ct JOIN ( SELECT origincitymarketid, origincityname FROM ontime GROUP BY 1,2) c ON c.origincitymarketid = ct.origincitymarketid GROUP BY 1 ORDER BY 3 DESC FETCH FIRST 10 ROWS ONLY;\g |
Days of week with most total flights over several years | SELECT DayOfWeek, count(*) AS c FROM ontime WHERE Year BETWEEN 2000 AND 2008 GROUP BY DayOfWeek ORDER BY c DESC;\g |
Days of week with most delays over several years | SELECT DayOfWeek, count(*) AS c FROM ontime WHERE DepDelay>10 AND Year BETWEEN 2000 AND 2008 GROUP BY DayOfWeek ORDER BY c DESC;\g |
Carrier with most delays in one year | SELECT carrier, count(*) FROM ontime WHERE DepDelay>10 AND Year=2007 GROUP BY carrier ORDER BY 2 DESC;\g |
Carrier with the most percentage delays in one year | WITH t AS (SELECT carrier, count(*) AS c FROM ontime WHERE DepDelay>10 AND Year=2007 GROUP BY carrier), t2 AS (SELECT carrier, count(*) AS c2 FROM ontime WHERE Year=2007 GROUP BY carrier) SELECT t.carrier, c, c2, c*1000/c2 as c3 FROM t JOIN t2 ON (t.Carrier=t2.Carrier) ORDER BY c3 DESC;\g |
Carrier with the most percentage delays in multiple years | WITH t AS (SELECT carrier, count(*) AS c FROM ontime WHERE DepDelay>10 AND Year between 2000 and 2008 GROUP BY carrier), t2 AS (SELECT carrier, count(*) AS c2 FROM ontime WHERE Year between 2000 and 2008 GROUP BY carrier) SELECT t.carrier, c, c2, c*1000/c2 as c3 FROM t JOIN t2 ON (t.Carrier=t2.Carrier) ORDER BY c3 DESC;\g |
Running total of flights over the years by carrier | WITH t AS (SELECT carrier, year,count(*) cnt FROM ontime GROUP BY carrier, year) SELECT carrier, year, cnt, sum(cnt) OVER (PARTITION BY carrier ORDER BY carrier, year ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "Running Total" FROM t FETCH FIRST 10 ROWS ONLY;\g |