top of page
Search

Airline Performance Analysis

  • Writer: Rajwol Khadka
    Rajwol Khadka
  • Mar 21
  • 3 min read

Updated: Apr 4

📌Project Overview


This report provides a data-driven analysis of airline performance, leveraging a Power BI dashboard and SQL queries to evaluate key flight metrics such as on-time performance, delays, and cancellations. The dataset, provided for this analysis, contains detailed flight records from multiple airlines. Stakeholders from the FAA requested this assessment to identify patterns in flight disruptions, determine the primary causes of cancellations and delays, and offer data-driven recommendations for improving airline efficiency.


By integrating SQL for data extraction and Power BI for visualization, this analysis highlights airline punctuality trends, major delay contributors, and leading cancellation reasons. The findings will help stakeholders make strategic operational improvements, enhance scheduling practices, and implement better risk management strategies to minimize disruptions in the aviation industry.


 

🎯 Project Objectives

  • Identify which airlines experience the most delays and cancellations.

  • Determine the leading causes of flight cancellations.

  • Compare on-time performance across airlines.

  • Provide data-driven recommendations to improve airline performance.


 

Data Sources

The analysis was conducted using:

  • SQL Queries for extracting insights from raw datasets.

  • A Power BI Dashboard for data visualization.


 

Flight Performance Summary

My first priority was to create a SQL query that analyzes airline performance based on key flight metrics (total flights, delayed flights, and canceled flights for each airline). To ensure a fair assessment, I incorporated conditional aggregations to calculate percentages, allowing performance evaluation without bias towards airlines with a higher total flight volume. The results are grouped by airlines and sorted by on-time performance in descending order to identify the airlines with the best and worst punctuality rates.


SQL Query:
























Results:













Key Observations

  • 🏆 On-Time Performance:

    • Best On-Time Airlines: Alaska Airlines (74.4%), Hawaiian Airlines (73.4%), and Delta Air Lines (67.3%)

    • Worst On-Time Airlines: United Airlines (49.0%), Spirit Airlines (53.9%), and Southwest Airlines (53.8%)


  • ⏳ Flight Delays:

    • Most Delayed Airlines: United Airlines (49.75%), Spirit Airlines (44.3%), and Southwest Airlines (44.9%)

    • Least Delayed Airlines: Hawaiian Airlines (26.4%) and Alaska Airlines (25.2%)


  • ❌ Flight Cancellations:

    • Highest Cancellation Rate: American Eagle Airlines (5.1%) and Atlantic Southeast Airlines (2.66%)

    • Lowest Cancellation Rate: Hawaiian Airlines (0.22%) and Delta Air Lines (0.44%)


 

Most Common Reason for Flight Cancellations

Next, I wanted to determine the primary reason for flight cancellations for each airline. I grouped cancellations by airline and cancellation type, then used DENSE_RANK() to rank cancellation reasons within each airline. By incorporating this logic into a Common Table Expression (CTE), I was able to efficiently retrieve the top cancellation reason for each carrier.


SQL Query:

















Results:



















Key Observations

  • 🌧️ Weather is the dominant cause of flight cancellations across most airlines.

  • 🏢 Alaska Airlines, Frontier Airlines, and Hawaiian Airlines experienced the most cancellations due to Airline/Carrier issues.

  • 🛫 Atlantic Southeast Airlines and Virgin America had a high cancellation rate due to National Air System disruptions.


Challenges & Limitations

  • 🌦️ Weather Dependency: External factors like storms and airport congestion impact flight schedules.

  • 🏢 Airline Management: Differences in airline policies for handling delays can skew comparisons.

  • 📊 Data Completeness: Data accuracy depends on consistent reporting from airlines.


 

Recommendations & Next Steps


🔹 Improving On-Time Performance

  • Airlines with low on-time performance (Spirit - 53.9%, United - 49.0%, Southwest - 53.8%) should implement better scheduling and operational efficiency strategies.


  • Goal: Improve on-time performance by at least 10%, bringing Spirit and United above 60% and Southwest to at least 65%.


🔹 Reducing Delays

  • Airlines with high delay rates (United - 49.75%, Spirit - 44.3%, Southwest - 44.9%, JetBlue - 38.2%) should focus on improving turnaround times and strengthening weather contingency planning.


  • Goal: Reduce delay percentages by at least 5-7%, ensuring no airline exceeds a 40% delay rate.


🔹 Minimizing Cancellations

  • American Eagle Airlines (5.1%) and Atlantic Southeast Airlines (2.66%) need enhanced risk management strategies, including better crew and equipment planning, improved passenger rebooking processes, and investment in predictive analytics to anticipate and mitigate cancellations.


  • Goal: Reduce cancellation rates by 30% within the next operational cycle (e.g., from 5.1% to ~3.5% for American Eagle and from 2.66% to ~1.8% for Atlantic Southeast Airlines).


📌 Additional Strategic Recommendations

✅ Implement predictive analytics to forecast delays and cancellations.

✅ Enhance ground crew efficiency to minimize turnaround delays.

✅ Improve weather adaptation measures by leveraging historical data trends.


 

🖼️ Visualizing the Insights


If you're interested in seeing how I visualized the insights generated from these SQL queries, click here!




 
 
 

Comentarios

bottom of page