Flight Delay Analysis

The problem was to analyze the difference in total flights between weekdays and weekends to identify any variations in air travel demand patterns. To address this, I utilized SQL queries to extract the relevant flight data from the database. I calculated and aggregated the total number of flights for weekdays and weekends. I then transferred the data to Excel and created visualizations such as line charts or bar graphs to compare and analyze the variations in air travel demand between weekdays and weekends. Additionally, I leveraged Tableau or Power BI to create interactive dashboards and visualizations that provided a comprehensive view of the data and facilitated further exploration of the patterns.
The objective was to determine the total number of cancelled flights specifically for JetBlue Airways on the first date of every month, highlighting potential issues or challenges faced by the airline. Using SQL queries, I filtered the data to extract the flight records specifically for JetBlue Airways. I further narrowed down the data to include only the first date of each month. I calculated the total number of cancelled flights for those specific dates and presented the findings in Excel, potentially through tables or pivot tables. To enhance the presentation, I also used Tableau or Power BI to create interactive visualizations and dashboards that showcased the monthly cancellation trends for JetBlue Airways and provided insights into any challenges or issues faced by the airline.
The problem statement involved examining the delay statistics of flights on a weekly basis, categorized by states, cities, and respective airlines, to understand patterns and identify any areas with significant delays. To address this, I utilized SQL queries to extract the relevant flight data and categorized it based on states, cities, and airlines. I calculated metrics such as average delay time or percentage of delayed flights for each category. I presented the findings in Excel through tables or charts, highlighting any areas with significant delays. Additionally, I leveraged the visualization capabilities of Tableau or Power BI to create interactive visualizations and dashboards that provided a comprehensive view of the delay statistics, allowing for further exploration and analysis.
The goal was to identify the number of airlines that consistently have no departure or arrival delays, focusing on flights covering a distance between 2500 and 3000, providing insights into airlines with efficient operations. Using SQL queries, I filtered the flight data based on the specified distance range and extracted the relevant records. I then identified airlines with no departure or arrival delays consistently within that range. I presented the findings in Excel, potentially using tables or conditional formatting to highlight the airlines with efficient operations. Furthermore, I leveraged Tableau or Power BI to create visualizations and dashboards that showcased the performance of the airlines, providing a clear understanding of their efficiency in operations.

Throughout the process, I ensured that the data was accurately analyzed, and the insights derived were effectively communicated through Excel, SQL queries, and the visualization capabilities of Tableau or Power BI. The combination of these tools allowed for comprehensive data analysis, visualization, and the presentation of key findings, enabling a deeper understanding of the identified KPIs.

I successfully completed the analysis of key performance indicators (KPIs) related to air travel patterns and operational efficiency using Microsoft Excel. This explanation will outline the approach taken, data collection, analysis methods, and the resulting insights from each KPI.

Analysis of Total Flights on Weekdays vs. Weekends: To identify variations in air travel demand patterns, I collected flight data and categorized it by weekdays and weekends. Using Excel, I calculated the total number of flights for each category and created a bar chart to visualize the differences. The chart clearly illustrates the variations in air travel demand between weekdays and weekends, providing insights into the preferred travel days.

Excel Actions:

Imported the flight data into Excel.
Created a new column to categorize each flight as a weekday or weekend based on the flight date.
Used the COUNTIF function to calculate the total number of flights for weekdays and weekends.
Created a pie chart using the charting tools in Excel to represent the data visually.

2. Determining Total Cancelled Flights for JetBlue Airways on the First Date of Every Month: To highlight potential issues faced by JetBlue Airways, I focused on determining the total number of cancelled flights specifically on the first date of every month. Using Excel, I filtered

the data for JetBlue Airways flights, identified the first date of each month, and counted the cancelled flights.

Excel Actions:

Filtered the flight data to include only JetBlue Airways flights.
Extracted the day component from the flight date using the DAY function in Excel.
Created a pivot table to summarize the cancelled flights count by the first date of each month.
Utilized conditional formatting to highlight any months with a high number of cancelled flights.

3. Examining Delay Statistics of Flights on a Weekly Basis by States, Cities, and Airlines: To understand delay patterns and identify areas with significant delays, I analyzed delay statistics categorized by states, cities, and respective airlines on a weekly basis. With Excel, I grouped the data by these categories and calculated average delay times, enabling a comprehensive overview of the delay trends.

Excel Actions:

Grouped the flight data by week, state, city, and airline using the Grouping feature in Excel.
Calculated the average delay time for each category using the AVERAGE function in Excel.
Created a pivot table and pivot chart to display the delay statistics by states, cities, and airlines.

4. Identifying Airlines with No Departure/Arrival Delays within a Specified Distance Range: To gain insights into airlines with efficient operations,

I focused on identifying the number of airlines that consistently had no departure or arrival delays for flights covering a distance between

2500 and 3000. By filtering the data based on the distance range and examining the delay columns, I determined the number of airlines meeting the criteria.

Excel Actions:

Filtered the flight data to include only flights within the distance range of 2500 to 3000.
Utilized Excel's filtering capabilities to exclude flights with departure or arrival delays.
Counted the distinct airlines meeting the criteria using the COUNTIFS function in Excel.

Conclusion: Through the use of Excel, I successfully completed the analysis of the provided KPIs. Excel allowed me to efficiently process and analyze large datasets, enabling me to generate valuable insights into air travel patterns, cancellations, delays, and operational efficiency. The actions performed in Excel, such as data filtering, calculations, pivot tables, charts, and conditional formatting, facilitated the visualization and interpretation of the data, enabling informed decision-making and potential areas for improvement within

Excel Dashboard

SQL Queries

Creating Schema and Loading the Data

First KPI Weekday Vs Weekend total flights statistics

1. Analyzing the difference in total flights between weekdays and weekends:
- Start by having a table that includes flight data with a column representing the flight date and another column for the total number of flights on that day.
- Use the DAYOFWEEK function in MySQL to determine the day of the week for each flight date (1 for Sunday, 2 for Monday, etc.).
- Write a query that groups the flights by the day of the week and calculates the total number of flights for each group.
- Compare the total flights for weekdays (Monday to Friday) versus weekends (Saturday and Sunday) to identify any variations in air travel demand patterns.

Second KPI Total number of cancelled flights for JetBlue Airways on first date of every month

2. Determining the total number of cancelled flights for JetBlue Airways on the first date of every month:

- Create a table that includes flight data with columns for the flight date, airline, and cancellation status.

- Write a query that filters the data for JetBlue Airways and uses the MONTH and YEAR functions to extract the month and year from the flight date.

- Group the data by month and year and calculate the count of cancelled flights for each group.

- Add a condition to select only the records where the flight date is the first day of the month.

- This query will provide the total number of cancelled flights specifically for JetBlue Airways on the first date of every month.

Third KPI Week wise, State wise and City wise statistics of delay of flights with airline details

3. Examining the delay statistics of flights on a weekly basis, categorized by states, cities, and respective airlines:

- Ensure you have a table that contains flight data with columns for the flight date, state, city, airline, and delay duration.

- Write a query that groups the flights by week (using the WEEK function), state, city, and airline.

- Calculate the delay statistics for each group, such as the average delay duration, maximum delay duration, or any other relevant metrics.

- This query will provide insights into delay patterns on a weekly basis, categorized by states, cities, and respective airlines.

Third KPI Number of airlines with No departure/arrival delay with distance covered between 2500 and 3000

4. Identifying the number of airlines with no departure or arrival delays, focusing on flights covering a distance between 2500 and 3000:

- Create a table that includes flight data with columns for the airline, departure delay duration, arrival delay duration, and flight distance.

- Write a query that filters the data for flights with a distance between 2500 and 3000.

- Add conditions to select only the records where both departure and arrival delays are zero.

- Group the data by the airline and calculate the count of airlines that meet the criteria.

- This query will provide insights into the number of airlines that consistently have no departure or arrival delays for flights within the specified distance range.

Tableau Dashboard

ALL KPI's

1. Analyzing the difference in total flights between weekdays and weekends:
- Gather the necessary flight data, including dates, flight numbers, and passenger counts.
- Import the data into Tableau and ensure it is properly structured for analysis.
- Create a new worksheet in Tableau.
- Drag the "Date" field to the Columns shelf and select the appropriate date aggregation (e.g., Day of Week).
- Drag the "Flight Number" field to the Rows shelf and choose the count aggregation to represent the total flights.
- Apply a filter to distinguish weekdays from weekends.
- Create a visualization, such as a bar chart or line graph, to compare the total flights between weekdays and weekends.

2. Determining the total number of cancelled flights for JetBlue Airways on the first date of every month:
- Prepare the flight data with relevant information, including airline names, flight dates, and cancellation status.
- Import the data into Tableau and ensure it is structured correctly.
- Create a new worksheet in Tableau.
- Drag the "Airline" field to the Filters shelf and select JetBlue Airways.
- Drag the "Date" field to the Columns shelf and select the Month aggregation.
- Drag the "Flight Number" field to the Rows shelf and choose the count aggregation.
- Add a filter to include only cancelled flights.
- Apply a filter to include only the first date of each month.
- Create a visualization, such as a bar chart, to display the total number of cancelled flights for JetBlue Airways on the first date of every month.

3. Examining delay statistics of flights on a weekly basis, categorized by states, cities, and airlines:
- Collect the necessary flight data, including departure and arrival times, delay durations, states, cities, and airlines.
- Import the data into Tableau and ensure it is properly formatted.
- Create a new worksheet in Tableau.
- Drag the relevant fields (departure time, arrival time, delay duration, states, cities, and airlines) to the appropriate shelves.
- Use the appropriate aggregations, such as average or sum, to calculate delay statistics.
- Group the data by week to analyze delay patterns over time.
- Create visualizations, such as bar charts or heat maps, to display the delay statistics categorized by states, cities, and airlines.

4. Identifying the number of airlines with no departure or arrival delays for flights covering a distance between 2500 and 3000:
- Collect the flight data with relevant information, including airlines, departure delays, arrival delays, and flight distances.
- Import the data into Tableau and ensure it is structured correctly.
- Create a new worksheet in Tableau.
- Drag the relevant fields (airlines, departure delays, arrival delays, flight distances) to the appropriate shelves.
- Apply filters to include only flights covering a distance between 2500 and 3000.
- Create a calculated field to determine if there are no departure or arrival delays (e.g., IF [Departure Delay] = 0 AND [Arrival Delay] = 0 THEN 1 ELSE 0 END).
- Drag the calculated field to the Rows or Columns shelf and choose the count aggregation.
- Create a visualization, such as a bar chart, to display the number of airlines with no departure or arrival delays for flights covering a distance between 2500 and 3000.

PowerBI Dashboard

ALL KPI's

1. Analyzing the difference in total flights between weekdays and weekends:
a. Import the dataset containing flight data into Power BI.
b. Clean and prepare the data, ensuring it includes information such as flight dates, weekdays, and total flights.
c. Create a new visual in Power BI by selecting the appropriate visualization type, such as a bar chart or line chart.
d. Add the necessary fields to the visual, such as weekdays and total flights, and configure the visualization settings to display the data effectively.
e. Customize the visual by adding titles, axis labels, and formatting options to enhance readability.
f. Analyze the visual to identify any variations in total flights between weekdays and weekends, gaining insights into air travel demand patterns.

2. Determining the total number of cancelled flights for JetBlue Airways on the first date of every month:
a. Filter the dataset to include only flights operated by JetBlue Airways.
b. Create a calculated column or measure to extract the month and date from the flight dates.
c. Filter the dataset to include only the first date of each month.
d. Calculate the total number of cancelled flights using a measure or aggregation function.
e. Create a visual, such as a card or a table, to display the total number of cancelled flights.
f. Customize the visual by adding appropriate labels and formatting options to highlight the specific information for JetBlue Airways.

3. Examining delay statistics of flights on a weekly basis, categorized by states, cities, and respective airlines:
a. Prepare the dataset to include information about flight delays, states, cities, and airlines.
b. Create a new visual, such as a stacked column chart or a heat map, in Power BI.
c. Add the necessary fields to the visual, such as week numbers, states, cities, airlines, and delay statistics (e.g., average delay time, number of delayed flights).
d. Configure the visual settings to display the desired information clearly.
e. Analyze the visual to identify patterns and areas with significant delays, gaining insights into the delay statistics on a weekly basis.

4. Identifying the number of airlines with no departure or arrival delays, focusing on flights covering a distance between 2500 and 3000:
a. Filter the dataset to include only flights within the specified distance range.
b. Create a calculated column or measure to identify flights with no departure or arrival delays.
c. Group the data by airlines and calculate the count of flights with no delays using a measure or aggregation function.
d. Create a visual, such as a donut chart or a table, to display the number of airlines with no delays.
e. Customize the visual by adding appropriate labels and formatting options to showcase the insights into airlines with efficient operations.