✈️ Flight Delay & Cancellation Analysis: Uncovering Customer Dissatisfaction Drivers 

 1. Executive Summary

This project analyzes flight operational data using MySQL to identify the root causes of poor customer reviews regarding departure delays and flight cancellations. Raw transaction data was ingested into a staging table (flight_st) to verify data integrity and check for duplicates or missing records. Aggregation queries and Common Table Expressions (CTEs) were leveraged to evaluate airline performance, time-of-day delay patterns, and high-risk origin hubs like Boston Logan International Airport (BOS). Key findings reveal that Delta suffers from the highest cancellation rate (12.50%) and longest average delay (34.38 minutes), while Southwest experiences the highest overall delay frequency (83.33%). Furthermore, Evening flights and operations at BOS exhibit severe delay bottlenecks.  As a solution, I recommend management and the operational team to focus on evening schedule buffering, targeted operational audits at BOS, and establishing SLA thresholds for underperforming carriers.

2. Business Problem

• Background: The management team observed a spike in negative customer reviews, pointing toward poor flight reliability and schedule disruptions.

• Business Impact: Severe delays and last-minute cancellations damage brand reputation, erode Customer Lifetime Value (CLV), and increase customer compensation costs.

• Key Analytical Questions:

  1. Which airline has the highest cancellation rate (cancel_percent) and highest average delay duration (delay_time)?

  2. Does departure time (morning, afternoon, evening) affect delay severity?

  3. Which airport experiences the most issues, and what are the primary root causes?

3. Tech Stack & SQL Methodology

• Database Engine: MySQL / MySQL Workbench.

• SQL Concepts & Functions Used:

  • Data Staging & Integrity: Created flight_st staging table using CREATE TABLE ... LIKE and INSERT INTO to preserve raw data. Checked for duplicates using HAVING COUNT(*) > 1 and validated NULL records in actual_departure.

  • Feature Engineering: Classified departure schedules into time slots (morning, afternoon, evening) using EXTRACT(HOUR FROM ...) within a conditional CASE WHEN block.

  • Conditional Aggregation & Metrics: Calculated custom metrics such as cancel_percent and delay_percent using SUM(CASE WHEN ...) paired with total flight counts (COUNT(*)).

  • Common Table Expressions (CTE): Utilized WITH ... AS to isolate evening flight patterns specifically out of Boston (BOS).

 4. Key Insights

1. Airline Performance Summary:

• Delta recorded the highest cancellation rate at 12.50% (2 out of 16 flights) and the longest average delay duration at 34.38 minutes.

• Southwest had the lowest cancellation rate (5.56%), but the highest delay frequency, with 83.33% of its flights experiencing departure delays.

• American Airlines and JetBlue exhibited high overall delay rates at 74.19% and 70.00%, respectively.

2. Time-of-Day Dynamics:

• Departure delays progressively escalate throughout the day: Morning flights average 30.00 minutes of delay, increasing to 35.95 minutes in the Afternoon, and peaking in the Evening at 37.68 minutes.

3. Most Impacted Airport & Root Causes:

• BOS (Boston) emerged as the single worst origin airport for delays, averaging 61.82 minutes per delayed flight—nearly double that of JFK (37.78 minutes).

• Primary Root Causes: Issues at BOS are heavily concentrated during evening departures (Evening flights), driven primarily by accumulated delays from JetBlue (3 delayed flights) and United (3 delayed flights).

5. Business Recommendations

1. Buffer Evening Flight Schedules: Implement wider turnaround buffers for evening departures to prevent the compounding "domino effect" of delays accumulated earlier in the day.

2. Operational Audit at BOS Airport: Conduct a dedicated ground-handling and turnaround time audit at Boston Logan Airport (BOS) to tackle its 61.82-minute average delay bottleneck.

3. Airline Partner SLAs & Proactive Communication: Enforce stricter Service Level Agreements (SLAs) for carriers with high cancellation/delay rates (e.g., Delta and Southwest) and launch automated SMS/email alerts with compensation vouchers to mitigate negative customer reviews during disruptions.