queries.sql

ALTER TABLE flights_on_time_2007
RENAME TO flights_on_time_2007;

ALTER TABLE flights_on_time_2007
RENAME COLUMN `Year` TO `year`,
RENAME COLUMN `Month` TO `month`,
RENAME COLUMN `DayOfMonth` TO `day_of_month`,
RENAME COLUMN `DayOfWeek` TO `day_of_week`,
RENAME COLUMN `DepTime` TO `dep_time`,
RENAME COLUMN `CRSDepTime` TO `CRS_dep_time`,
RENAME COLUMN `ArrTime` TO `arr_time`,
RENAME COLUMN `CRSArrTime` TO `CRS_arr_time`,
RENAME COLUMN `UniqueCarrier` TO `unique_carrier`,
RENAME COLUMN `FlightNum` TO `flight_num`,
RENAME COLUMN `TailNum` TO `tail_num`,
RENAME COLUMN `ActualElapsedTime` TO `actual_elapsed_time`,
RENAME COLUMN `CRSElapsedTime` TO `CRS_elapsed_time`,
RENAME COLUMN `AirTime` TO `air_time`,
RENAME COLUMN `ArrDelay` TO `arr_delay`,
RENAME COLUMN `DepDelay` TO `dep_delay`,
RENAME COLUMN `Origin` TO `origin`,
RENAME COLUMN `Dest` TO `dest`,
RENAME COLUMN `Distance` TO `distance`,
RENAME COLUMN `TaxiIn` TO `taxi_in`,
RENAME COLUMN `TaxiOut` TO `taxi_out`,
RENAME COLUMN `Cancelled` TO `cancelled`,
RENAME COLUMN `CancellationCode` TO `cancellation_code`,
RENAME COLUMN `Diverted` TO `diverted`,
RENAME COLUMN `CarrierDelay` TO `carrier_delay`,
RENAME COLUMN `WeatherDelay` TO `weather_delay`,
RENAME COLUMN `NASDelay` TO `NAS_delay`,
RENAME COLUMN `SecurityDelay` TO `security_delay`,
RENAME COLUMN `LateAircraftDelay` TO `late_aircraft_delay`
;

ALTER TABLE flights_on_time_2007
RENAME COLUMN CRS_dep_time TO crs_dep_time,
RENAME COLUMN CRS_arr_time TO crs_arr_time,
RENAME COLUMN CRS_elapsed_time TO crs_elapsed_time,
RENAME COLUMN NAS_delay TO nas_delay;

UPDATE flights_on_time_2007
SET arr_delay = null
WHERE arr_delay = 'NA';

UPDATE flights_on_time_2007
SET dep_delay = null
WHERE dep_delay = 'NA';

ALTER TABLE flights_on_time_2007
MODIFY arr_delay INT,
MODIFY dep_delay INT;

DESCRIBE flights_on_time_2007;

ALTER TABLE carriers
RENAME COLUMN `Code` TO `code`,
RENAME COLUMN `Description` TO `description`;

SELECT *
FROM flights_on_time_2007;

WITH total_records(cnt) AS (
SELECT COUNT(*) FROM flights_on_time_2007
UNION ALL
SELECT COUNT(*) FROM carriers
UNION ALL
SELECT COUNT(*) FROM airports
)
SELECT SUM(cnt)
FROM total_records;

-- Count number of flights in US in 2007: 7453215 records
SELECT COUNT(*)
FROM flights_on_time_2007;

-- Check that all flights have carrier listed in the carrier table: returns 0 records
SELECT unique_carrier, `code`, `description`
FROM flights_on_time_2007
LEFT JOIN carriers
ON unique_carrier = `code`
WHERE `code` IS NULL;

-- Look at list of all carriers associated with the flights
SELECT DISTINCT unique_carrier, `code`, `description`
FROM flights_on_time_2007
LEFT JOIN carriers
ON unique_carrier = `code`
ORDER BY unique_carrier;

-- Number of flights per month
SELECT `month`, COUNT(*) AS num_flights
FROM flights_on_time_2007
GROUP BY `month`;

-- Number of flights per day
SELECT `month`, `day_of_month`, COUNT(*) AS num_flights
FROM flights_on_time_2007
GROUP BY `month`, `day_of_month`;

-- Note that US Airways (US) and America West (HP) merged into US Airways (US) start Jan. 2006

-- Total number of cancellations: 160748
SELECT COUNT(*)
FROM flights_on_time_2007
WHERE cancelled = 1;

-- Look at number of cancellations per month
SELECT `month`, COUNT(*) AS num_cancellations
FROM flights_on_time_2007
WHERE cancelled = 1
GROUP BY `month`;

-- Number of cancellations per month grouped by cancellation type
SELECT `month`, cancellation_code, COUNT(*) AS num_cancellations
FROM flights_on_time_2007
WHERE cancelled = 1
GROUP BY `month`, cancellation_code;

-- Number of cancellations per day
SELECT `month`, `day_of_month`, COUNT(*) AS num_cancellations
FROM flights_on_time_2007
WHERE cancelled = 1
GROUP BY `month`, `day_of_month`;

-- Number of cancellations made by each airline
SELECT `description`, COUNT(*) AS num_cancellations
FROM flights_on_time_2007
LEFT JOIN carriers
ON unique_carrier = `code`
WHERE cancelled = 1
GROUP BY `description`;

-- Cancellation rate for each airline
SELECT `description`, COUNT(*) AS num_flights, SUM(cancelled) AS num_cancellations
FROM flights_on_time_2007
LEFT JOIN carriers
ON unique_carrier = `code`
GROUP BY `description`;

SELECT `description`, SUM(cancelled)/Count(*) AS cancellation_rate
FROM flights_on_time_2007
LEFT JOIN carriers
ON unique_carrier = `code`
GROUP BY `description`;

-- Cancellation cause breakdown for each airline
WITH airline_cancellations AS (
	SELECT `description`, cancellation_code, COUNT(*) OVER(PARTITION BY `description`) AS total_cancellations
    FROM flights_on_time_2007
    LEFT JOIN carriers
    ON unique_carrier = `code`
    WHERE cancelled = 1
)
SELECT `description`, cancellation_code, ROUND(COUNT(*)/total_cancellations, 2) AS cancellation_percentage
FROM airline_cancellations
GROUP BY `description`, cancellation_code
ORDER BY `description`, cancellation_code;

-- Rank airlines by number of cancellations
SELECT `description`, COUNT(*) AS num_cancellations, RANK() OVER(ORDER BY COUNT(*) DESC) AS `Rank`
FROM flights_on_time_2007
INNER JOIN carriers
ON unique_carrier = `code`
WHERE cancelled = 1
GROUP BY `description`;
 
 -- Rank airlines by cancellation rate
SELECT `description`, SUM(cancelled)/COUNT(*), SUM(cancelled), COUNT(*), RANK() OVER(ORDER BY SUM(cancelled)/COUNT(*) DESC) AS `Rank`
FROM flights_on_time_2007
INNER JOIN carriers
ON unique_carrier = `code`
GROUP BY `description`;

-- Here, we consider only the flight's arrival performance. A flight is 'late' if it arrives 15 minutes or more later than the scheduled time
SELECT arr_delay, dep_delay, carrier_delay, weather_delay, nas_delay, security_delay, late_aircraft_delay
FROM flights_on_time_2007;

-- Add new column indicating if flight is 'delayed' or not
ALTER TABLE flights_on_time_2007
ADD COLUMN `delayed` BOOL;

UPDATE flights_on_time_2007
SET `delayed` = 0;

UPDATE flights_on_time_2007
SET `delayed` = 1
WHERE arr_delay >= 15;

SELECT `delayed`, arr_delay, carrier_delay, weather_delay, nas_delay, security_delay, late_aircraft_delay
FROM flights_on_time_2007
WHERE carrier_delay + weather_delay + nas_delay + security_delay + late_aircraft_delay < 15 AND arr_delay >= 15;

SELECT `delayed`, arr_delay, carrier_delay, weather_delay, nas_delay, security_delay, late_aircraft_delay
FROM flights_on_time_2007;

-- Number of delayed flights: 1803320
SELECT SUM(`delayed`)
FROM flights_on_time_2007;

SELECT COUNT(*)
FROM flights_on_time_2007
WHERE `delayed` = 1;

-- Look at number of delays per month
SELECT `month`, COUNT(*) AS num_delays
FROM flights_on_time_2007
WHERE `delayed` = 1
GROUP BY `month`;

-- Number of delays per day
SELECT `month`, `day_of_month`, COUNT(*) AS num_delays
FROM flights_on_time_2007
WHERE `delayed` = 1
GROUP BY `month`, `day_of_month`;

-- Number of delays made by each airline
SELECT `description`, COUNT(*) AS num_delays
FROM flights_on_time_2007
LEFT JOIN carriers
ON unique_carrier = `code`
WHERE `delayed` = 1
GROUP BY `description`;

-- Overall delay rate
SELECT SUM(`delayed`)/COUNT(*) FROM flights_on_time_2007;

-- Overall average delay time
SELECT AVG(arr_delay) FROM flights_on_time_2007;

-- Delay rate for each airline
SELECT `description`, COUNT(*) AS num_flights, SUM(`delayed`) AS num_delays
FROM flights_on_time_2007
LEFT JOIN carriers
ON unique_carrier = `code`
GROUP BY `description`;

SELECT `description`, SUM(`delayed`)/COUNT(*) AS delay_rate
FROM flights_on_time_2007
LEFT JOIN carriers
ON unique_carrier = `code`
GROUP BY `description`;

-- Delay cause breakdown for each airline
WITH delay_min_sums AS (
	SELECT
		`description`,
		SUM(arr_delay) AS total_delay_mins,
		SUM(carrier_delay) AS cdm,
		SUM(weather_delay) AS wdm,
		SUM(nas_delay) AS ndm,
		SUM(security_delay) AS sdm,
		SUM(late_aircraft_delay) AS ladm
	FROM flights_on_time_2007
	LEFT JOIN carriers
	ON unique_carrier = `code`
	WHERE `delayed` = 1
	GROUP BY `description`
)
SELECT
	`description`,
	ROUND(cdm/total_delay_mins, 2) AS carrier_delay_percent,
	ROUND(wdm/total_delay_mins, 2) AS weather_delay_percent,
    ROUND(ndm/total_delay_mins, 2) AS nas_delay_percent,
    ROUND(sdm/total_delay_mins, 2) AS security_delay_percent,
    ROUND(ladm/total_delay_mins, 2) AS late_aircraft_delay_percent
FROM delay_min_sums;

-- Rank airlines by number of delays
SELECT `description`, COUNT(*) AS num_delays, RANK() OVER(ORDER BY COUNT(*) DESC) AS `Rank`
FROM flights_on_time_2007
INNER JOIN carriers
ON unique_carrier = `code`
WHERE `delayed` = 1
GROUP BY `description`;
 
 -- Rank airlines by delay rate
SELECT `description`, SUM(`delayed`)/COUNT(*), COUNT(*), RANK() OVER(ORDER BY SUM(`delayed`)/COUNT(*) DESC) AS `Rank`
FROM flights_on_time_2007
INNER JOIN carriers
ON unique_carrier = `code`
GROUP BY `description`;

-- Rank airlines by taking average of cancellation and delay rate
SELECT
	`description`,
	SUM(cancelled)/COUNT(*),
    SUM(`delayed`)/COUNT(*),
    (SUM(cancelled)/COUNT(*) + SUM(`delayed`)/COUNT(*))/2,
    RANK() OVER(ORDER BY (SUM(cancelled)/COUNT(*) + SUM(`delayed`)/COUNT(*))/2 DESC) AS `Rank`
FROM flights_on_time_2007
INNER JOIN carriers
ON unique_carrier = `code`
GROUP BY `description`;

-- Find max delay time
SELECT MAX(arr_delay)
FROM flights_on_time_2007;

SELECT *
FROM flights_on_time_2007
WHERE arr_delay = 2598;

-- Histogram of delay times
SELECT
	FLOOR(arr_delay/15.00)*15 AS bucket_floor,
    COUNT(*) AS count
FROM flights_on_time_2007
WHERE arr_delay IS NOT NULL
GROUP BY bucket_floor
ORDER BY bucket_floor;

SELECT
	bucket_floor,
    CONCAT(bucket_floor, ' to ', bucket_ceiling) AS bucket_name,
    COUNT(*)/7453215 AS count
FROM (
		SELECT
			FLOOR(arr_delay/15.00)*15 AS bucket_floor,
            FLOOR(arr_delay/15.00)*15 + 15 AS bucket_ceiling
		FROM flights_on_time_2007
        INNER JOIN carriers
        ON unique_carrier = `code`
        WHERE arr_delay IS NOT NULL
) a
GROUP BY bucket_floor, bucket_name
ORDER BY bucket_floor;

-- Check each flight is matched to airport for both origin and dest: returns 0 rows
SELECT unique_carrier, flight_num, origin, o.airport, dest, d.airport
FROM flights_on_time_2007
LEFT JOIN airports o
ON origin = o.iata
LEFT JOIN airports d
ON dest = d.iata
WHERE o.airport IS NULL OR d.airport IS NULL;

-- Look at origin and dest airports for each flight
SELECT unique_carrier, flight_num, origin, o.airport, dest, d.airport
FROM flights_on_time_2007
LEFT JOIN airports o
ON origin = o.iata
LEFT JOIN airports d
ON dest = d.iata;

-- Number of outgoing flights for each airport
SELECT airport, COUNT(*) AS num_flights
FROM flights_on_time_2007
INNER JOIN airports
ON origin = iata
GROUP BY airport;

-- Number of incoming flights for each airport
SELECT airport, COUNT(*) AS num_flights
FROM flights_on_time_2007
INNER JOIN airports
ON origin = iata
GROUP BY airport;

-- Number of outgoing flights for each airport, grouped by carrier
SELECT airport, `description`, COUNT(*) AS num_flights
FROM flights_on_time_2007
INNER JOIN carriers
ON unique_carrier = `code`
INNER JOIN airports
ON origin = iata
GROUP BY airport, `description`
ORDER BY airport, `description`;

-- Number of cancelled outgoing flights for each airport
SELECT airport, COUNT(*) AS num_cancellations
FROM flights_on_time_2007
INNER JOIN airports
ON origin = iata
WHERE cancelled = 1
GROUP BY airport;

-- Number of cancelled incoming flights for each airport
SELECT airport, COUNT(*) AS num_cancellations
FROM flights_on_time_2007
INNER JOIN airports
ON dest = iata
WHERE cancelled = 1
GROUP BY airport;

-- Number of cancelled outgoing flights for each airport grouped by cancellation cause
SELECT airport, cancellation_code, COUNT(*) AS num_cancellations
FROM flights_on_time_2007
INNER JOIN airports
ON origin = iata
WHERE cancelled = 1
GROUP BY airport, cancellation_code
ORDER BY airport, cancellation_code;

-- Rank airports by number of outgoing flight cancellations
SELECT airport, COUNT(*) AS num_cancellations, RANK() OVER(ORDER BY COUNT(*) DESC) AS `rank`
FROM flights_on_time_2007
INNER JOIN airports
ON origin = iata
WHERE cancelled = 1
GROUP BY airport;

-- Rank airports by outgoing flight cancellation rate
SELECT airport, SUM(cancelled)/COUNT(*) AS cancellation_rate, RANK() OVER(ORDER BY SUM(cancelled)/COUNT(*) DESC) AS `rank`
FROM flights_on_time_2007
INNER JOIN airports
ON origin = iata
GROUP BY airport;

-- Rank airports by number of incoming flight cancellations
SELECT airport, COUNT(*) AS num_cancellations, RANK() OVER(ORDER BY COUNT(*) DESC) AS `rank`
FROM flights_on_time_2007
INNER JOIN airports
ON dest = iata
WHERE cancelled = 1
GROUP BY airport;

-- Rank airports by incoming flight cancellation rate
SELECT airport, SUM(cancelled)/COUNT(*) AS cancellation_rate, RANK() OVER(ORDER BY SUM(cancelled)/COUNT(*) DESC) AS `rank`
FROM flights_on_time_2007
INNER JOIN airports
ON dest = iata
GROUP BY airport;

-- Number of delayed outgoing flights for each airport
SELECT airport, COUNT(*) AS num_delays
FROM flights_on_time_2007
INNER JOIN airports
ON origin = iata
WHERE `delayed` = 1
GROUP BY airport;

-- Number of delayed incoming flights for each airport
SELECT airport, COUNT(*) AS num_delays
FROM flights_on_time_2007
INNER JOIN airports
ON dest = iata
WHERE `delayed` = 1
GROUP BY airport;

-- Rank airports by number of outgoing flight delays
SELECT airport, COUNT(*) AS num_delays, RANK() OVER(ORDER BY COUNT(*) DESC) AS `rank`
FROM flights_on_time_2007
INNER JOIN airports
ON origin = iata
WHERE `delayed` = 1
GROUP BY airport;

-- Rank airports by outgoing flight delay rate
SELECT airport, SUM(`delayed`)/COUNT(*) AS delay_rate, RANK() OVER(ORDER BY SUM(`delayed`)/COUNT(*) DESC) AS `rank`
FROM flights_on_time_2007
INNER JOIN airports
ON origin = iata
GROUP BY airport;

SELECT * FROM flights_on_time_2007 WHERE origin = 'MKC' OR dest = 'MKC';

-- Rank airports by number of incoming flight delays
SELECT airport, COUNT(*) AS num_delays, RANK() OVER(ORDER BY COUNT(*) DESC) AS `rank`
FROM flights_on_time_2007
INNER JOIN airports
ON dest = iata
WHERE `delayed` = 1
GROUP BY airport;

-- Rank airports by incoming flight delay rate
SELECT airport, SUM(`delayed`)/COUNT(*) AS delay_rate, RANK() OVER(ORDER BY SUM(`delayed`)/COUNT(*) DESC) AS `rank`
FROM flights_on_time_2007
INNER JOIN airports
ON dest = iata
GROUP BY airport;

SELECT SUM(late_aircraft_delay)/SUM(`delayed`) FROM flights_on_time_2007;

SELECT * FROM flights_on_time_2007
WHERE crs_elapsed_time <= 0;

SELECT COUNT(*) FROM flights_on_time_2007 WHERE distance between 11 and 69;

SELECT `year`, `month`, `day_of_month`, CONCAT_WS('-', `year`, LPAD(`month`, 2, '0'), LPAD(`day_of_month`, 2, '0'))
FROM flights_on_time_2007;

-- Add new date column combining information in year, month, dayofmonth cols
ALTER TABLE flights_on_time_2007
ADD COLUMN `date` DATE;

UPDATE flights_on_time_2007
SET `date` = CONCAT_WS('-', `year`, LPAD(`month`, 2, '0'), LPAD(`day_of_month`, 2, '0'));

SELECT DISTINCT `year`, `month`, `day_of_month`, `date`
FROM flights_on_time_2007;


-- Get rid of records from 2008 because it only includes records up to April
SELECT *
FROM flights_test_2004_to_2008
WHERE `year` = 2008;

RENAME TABLE flights_test_2004_to_2008 TO flights_test_2004_to_2007;

SELECT COUNT(*) FROM flights_2004_to_2008;

SELECT SUM(arr_delay), SUM(carrier_delay), SUM(late_aircraft_delay), SUM(nas_delay), SUM(security_delay), SUM(weather_delay) FROM flights_on_time_2007
WHERE arr_delay > 0 ;
SELECT arr_delay, carrier_delay, late_aircraft_delay, nas_delay, security_delay, weather_delay FROM flights_on_time_2007;

WITH test AS (
SELECT f.arr_delay FROM flights_on_time_2007 f
)
SELECT arr_delay FROM test;

SELECT COUNT(*)
FROM flights_on_time_2007
WHERE arr_delay >= 120;