sql-final-project

Data Analysis and Insights

1. Average Ticket Price by Fare Class

Business: 51143

Comfort: 32740

Economy: 15959

Insight:

The way the airline prices its tickets is like a ladder. Business class tickets are really expensive they cost than three times what you would pay for an Economy ticket. Comfort class is somewhere in the middle of Business and Economy. This shows that the airline has levels of pricing and the fancy classes make a lot of money for each person who buys a ticket. The airline is counting on people who pay for Business and other premium classes to make most of its money not on having a lot of people, on its planes. The airline really needs these premium travelers to make money because they pay a lot for Business class tickets and other premium classes.

2. Airports With the Most Departures

Top departure airports in the dataset:

• DME (Moscow Domodedovo): 3217

• SVO (Moscow Sheremetyevo): 2981

• LED (St. Petersburg): 1900

• VKO (Moscow Vnukovo): 1719

• OVB (Novosibirsk): 1055

Insight:

Moscow is really important for airlines. The three big airports in Moscow have 8000 flights leaving every week. This shows that Moscow is the center for air travel in the country. St. Petersburg is the other city that has a lot of airline traffic. Most other airports in the country are smaller. People usually fly from these airports to Moscow. Moscow is, like the hub and all the other airports are connected to it.

3. Flight Status Distribution

• Arrived: 16707

• Scheduled: 15383

• On Time: 518

• Departed: 58

• Cancelled: 414

• Delayed: 41

Insight:

Most flights are. Arrived or Scheduled, which is what you would expect when you look at old records of how things actually happened. The number of flights that're On Time is pretty small but that probably has more to do with how the information was written down than how well the flights actually ran on time. There are not flights that were cancelled or delayed which means that the flights usually ran smoothly and things did not go wrong very often with flights.

4. Most Frequently Used Aircraft

• Cessna 208 Caravan: 9273

• Bombardier CRJ 200: 9048

• Sukhoi Superjet 100: 8504

• Airbus A321 200: 1952

• Boeing 737 300: 1274

• Airbus A319 100: 1239

• Boeing 767 300: 1221

• Boeing 777 300: 610

Insight:

The top three aircraft that this airline uses are all jets or turboprops. This makes sense because the airline flies to a lot of airports. You do not see planes like the A321, 737 and 777 as much. This means that the airline does not do many long haul flights or flights with a lot of people. The airlines fleet of aircraft shows that they really focus on flying people around their country and to remote areas. The airlines fleet composition and the aircraft they use like jets or turboprops are important for domestic and remote region connectivity. The aircraft that the airline uses such, as jets or turboprops are a big part of their plan to connect people in these areas.

5. Longest Scheduled Flight

• Flight ID: 1185

• Flight Number: PG0134

• Route: DME to BTK (Moscow to Bratsk)

Insight:

This route is the one that takes the time to complete. Moscow to Bratsk is a long flight that goes east across Siberia, which is what you would expect given how big Russia is. The Moscow to Bratsk flight also shows that the airline is trying to connect Moscow to places that're far away in the east. Moscow to Bratsk is an example of this because it is a flight that Moscow airline offers to get people, from Moscow to the remote parts of eastern Russia.

Overall Summary

When you look at all the questions you get an idea of what is going on with the queries. This is true, for all the queries. The queries show a picture.

• The airline has a place in Moscow where all the flights go to and come from it is like a big circle with Moscow, in the middle and all the other places are connected to it this is what they call a hub and spoke network and it is centered on Moscow.

• The airlines fleet is mostly made up of aircraft. This is because the airline focuses on flying to places and remote destinations. The airline really likes to use aircraft for these flights.

• The thing about premium fare classes is that they make a lot money for each ticket that is sold. Premium fare classes are really good at generating a lot of revenue per ticket. This is because people are willing to pay more for premium fare classes. So premium fare classes are very important, for making money from ticket sales.

• The buses and trains seem to run on time with not many trips being cancelled or late.

• The longest routes are the ones that connect Moscow to cities that're really far east or in Siberia. These routes are very long. They connect Moscow to far eastern cities or to cities, in Siberia.

These insights collectively show how SQL can be used to extract meaningful operational, financial, and structural insights from raw airline data, revealing patterns in pricing, fleet usage, network design, and reliability.

Note:

I cannot add the database because it exeeds GitHub's maximm file size. The link to the Kaggle site I got it from is: https://www.kaggle.com/datasets/saadharoon27/airlines-dataset