Creating data sets as per the business criteria

Run queries to create data sets that help answer burning business questions.

Run the following query to get the number of passengers on an airline with long layovers:

-- Number of passengers on the airline that have long, planned layovers for an international flight
SELECT 
   a.leg1uniquecarrier as carrier, 
   count(a.leg1uniquecarrier) as passengers
FROM 
   `airline_ontime_orc`.unique_tickets a
where 
   a.leg2deptime - a.leg1arrtime>90
group by 
   a.leg1uniquecarrier
;

Run the following query to get the number of passengers on an airline with delayed international leg:

-- Number of passengers on airlines that have elongated layovers for an international flight caused by delayed connection
SELECT 
   a.leg1uniquecarrier as carrier, 
   count(a.leg1uniquecarrier) as passengers 
FROM 
   `airline_ontime_orc`.unique_tickets a, 
   `airline_ontime_orc`.flights o, 
   `airline_ontime_orc`.flights d
where 
       a.leg1flightnum = o.flightnum
   AND a.leg1uniquecarrier = o.uniquecarrier 
   AND a.leg1origin = o.origin 
   AND a.leg1dest = o.dest 
   AND a.leg1month = o.month 
   AND a.leg1dayofmonth = o.dayofmonth
   AND a.leg1dayofweek = o.`dayofweek` 
   AND a.leg2flightnum = d.flightnum
   AND a.leg2uniquecarrier = d.uniquecarrier 
   AND a.leg2origin = d.origin 
   AND a.leg2dest = d.dest 
   AND a.leg2month = d.month 
   AND a.leg2dayofmonth = d.dayofmonth
   AND a.leg2dayofweek = d.`dayofweek` 
   AND o.depdelay > 60
group by 
   a.leg1uniquecarrier
;

Run the following query to get the number of passengers on an airline with missed international connections:

-- Number of passengers on airlines that have elongated layovers for an international flight caused by missed connection
SELECT 
   a.leg1uniquecarrier as carrier, 
   count(a.leg1uniquecarrier) as passengers
--   o.arrdelay as delay
FROM 
   `airline_ontime_orc`.unique_tickets a, 
   `airline_ontime_orc`.flights o, 
   `airline_ontime_orc`.flights d
where 
       a.leg1flightnum = o.flightnum
   AND a.leg1uniquecarrier = o.uniquecarrier 
   AND a.leg1origin = o.origin 
   AND a.leg1dest = o.dest 
   AND a.leg1month = o.month 
   AND a.leg1dayofmonth = o.dayofmonth
   AND a.leg1dayofweek = o.`dayofweek` 
   AND a.leg2flightnum = d.flightnum
   AND a.leg2uniquecarrier = d.uniquecarrier 
   AND a.leg2origin = d.origin 
   AND a.leg2dest = d.dest 
   AND a.leg2month = d.month 
   AND a.leg2dayofmonth = d.dayofmonth
   AND a.leg2dayofweek = d.`dayofweek` 
   AND d.deptime-o.arrtime < o.arrdelay-45
group by 
   a.leg1uniquecarrier
;