Airlines Analysis In My SQL

Q1 — Find the load Factor percentage on a yearly, Quarterly, and Monthly basis

select Year,
round(avg(Load_Fator),2) from highcloud_airlines.main_data
group by Year;

select Quarter,
round(avg(Load_Fator),2) from highcloud_airlines.main_data
group by Quarter;

select Month_Number,Month_FullName,
round(avg(Load_Fator),2) from highcloud_airlines.main_data
group by Month_FullName
order by Month_Number asc;

Q2 — Find the load Factor percentage on a Carrier Name basis

select Unique_Carrier,round(avg(Load_Fator),2) as Avg_Loadfactor
from highcloud_airlines.main_data
group by Unique_Carrier 
order by Avg_Loadfactor desc;

Q3 — Identify the Top 10 Carrier Names based on passenger preference

select Unique_Carrier,count(Departures_Performed) as Departure_Performed
from highcloud_airlines.main_data
group by Unique_Carrier
order by Departure_Performed desc
limit 10;

Q4 — Display top Routes (from to the City) based on the Number of Flights

select From_To_City,count(Carrier_Name) as Carrier_Name
from highcloud_airlines.main_data
group by From_To_City
order by Carrier_Name desc
limit 10;

Q5 — Identity how much load factor is occupied on Weekends vs Weekdays.

select Weekday_Weekend,
round(avg(Load_Fator),2) from highcloud_airlines.main_data
group by Weekday_Weekend;

select Weekday_Number,Weekday_Name,Weekday_Weekend,
round(avg(Load_Fator),2) from highcloud_airlines.main_data
group by Weekday_Weekend,Weekday_Name
order by Weekday_Number asc;

Q6 — Identify the number of flights based on Distance groups

select distance_groups.Distance_Interval,count(Unique_Carrier) as Unique_carrier from main_data
inner join highcloud_airlines.distance_groups on main_data.Distance_Group_ID = distance_groups.Distance_Group_ID
group by distance_groups.Distance_Interval
order by Unique_carrier desc;