Description | SQL |
---|---|
Which day has the most flights from 2014–2019 ordered by number of flights descending | SELECT description AS day_of_week, Count(*) AS num_flights FROM sample.ontime a, sample.l_weekdays b WHERE a.year BETWEEN 2014 AND 2019 AND a.dayofweek = b.code GROUP BY 1 ORDER BY 2 DESC |
Which day of the week has the most delayed flights from 2014–2019, ordered by number of flights descending | SELECT description AS day_of_week, Count(*) AS no_flights FROM sample.ontime a, sample.l_weekdays b WHERE depdelay > 10 AND "year" BETWEEN 2014 AND 2019 AND a.dayofweek = b.code GROUP BY 1 ORDER BY no_flights DESC |
Total number of flights where year > 2014 | SELECT year, Count(*) AS numberofflights FROM sample.ontime WHERE year > 2014 GROUP BY year |
Percentage of delays for each carrier for 2018. | SELECT t3.description AS airline, total, delayed, Varchar(delayed*100/total) + '%' AS percentage FROM (SELECT reporting_airline, Count(*) AS delayed FROM sample.ontime WHERE depdelay > 10 AND year = 2018 GROUP BY reporting_airline) t JOIN (SELECT reporting_airline, Count(*) AS total FROM sample.ontime WHERE year = 2018 GROUP BY reporting_airline) t2 ON ( t.reporting_airline = t2.reporting_airline ) JOIN sample.l_unique_carriers t3 ON Char(t3.code) = t.reporting_airline ORDER BY percentage DESC |
Count all the rows in the ontime table | SELECT Count(*) AS count FROM sample.ontime |
Which cities are served the most by other cities | SELECT TOP 10 destcityname AS Destination, Count(DISTINCT origincityname) AS OriginCityCount FROM sample.ontime WHERE year = 2018 GROUP BY 1 ORDER BY 2 DESC |
Top 10 Markets with most destinations | SELECT m.description AS Market, Listagg(DISTINCT c.origincityname, '; ') within GROUP (ORDER BY c.origincityname) AS originarea, count(*) AS num_of_destinations FROM ( SELECT origincitymarketid, destcitymarketid FROM sample.ontime WHERE year = 2018 GROUP BY 1, 2) ct JOIN ( SELECT origincitymarketid, origincityname FROM sample.ontime GROUP BY 1, 2) c ON c.origincitymarketid = ct.origincitymarketid JOIN sample.l_city_market_id m on m.code= ct.origincitymarketid GROUP BY 1 ORDER BY 3 DESC FETCH first 10 rows only |
Reasons for aircraft delays for 2018 | SELECT 'Carrier Delay' AS Type, Count(*) AS Total FROM sample.ontime WHERE ( carrierdelay != 0 AND carrierdelay IS NOT NULL ) AND year = 2018 UNION SELECT 'Weather Delay' AS Type, Count(*) AS Total FROM sample.ontime WHERE ( weatherdelay != 0 AND weatherdelay IS NOT NULL ) AND year = 2018 UNION SELECT 'Nas Delay' AS Type, Count(*) AS Total FROM sample.ontime WHERE ( nasdelay != 0 AND nasdelay IS NOT NULL ) AND year = 2018 UNION SELECT 'Security Delay' AS Type, Count(*) AS Total FROM sample.ontime WHERE ( securitydelay != 0 AND securitydelay IS NOT NULL ) AND year = 2018 UNION SELECT 'Late Aircraft' AS Type, Count(*) AS Total FROM sample.ontime WHERE ( lateaircraftdelay != 0 AND lateaircraftdelay IS NOT NULL ) AND year = 2018 ORDER BY 2 DESC |