-- 1. Count total number of origins and destinations
SELECT COUNT(DISTINCT origin_city) as origins, COUNT(DISTINCT dest_city) as dest FROM flights;
/* Actual Result
origins   dest 
327  326
(1 row)
*/

-- 2. What origin city is not a destination city
SELECT origin_city
FROM Flights
WHERE origin_city NOT IN (SELECT dest_city FROM Flights);
/* Actual Result
origin_city

Victoria TX
(1 row)
*/

-- 3. Find the top 10 most popular 2-hop flights flying with their counts
SELECT f1.origin_city as origin, f2.dest_city as dest, COUNT(*) as cnt
FROM Flights f1, Flights f2
WHERE f1.dest_city = f2.origin_city
GROUP BY f1.origin_city, f2.dest_city
ORDER BY COUNT(*) DESC
LIMIT 10;
/* Actual Result
origin           dest           cnt
Midland/Odessa TX           Richmond VA           103230
Fairbanks AK           Williston ND           10509
San Francisco CA           Abilene TX           315981
Anchorage AK           Birmingham AL           163177
Albany NY           Lafayette LA           57607
Richmond VA Myrtle           Beach SC           532378
Columbia MO Fort           Lauderdale FL           67640
Dayton OH Fort           Lauderdale FL           1316264
Yuma AZ San           Diego CA           362066
Greensboro/High Point NC Corpus           Christi TX           170181
(1 row)
*/

-- 4. Find all the origin and dest city pairs where you can only get to that destination from that origin. Output the distinct origin and dest city pairs.
SELECT DISTINCT origin_city, dest_city
FROM Flights
WHERE dest_city IN (
    SELECT dest_city
    FROM Flights
    GROUP BY dest_city
    HAVING COUNT(DISTINCT origin_city) = 1);

-- 4. Find all the destination cities whose only origin city is Atlanta GA.
--TRY 1: WRONG! (where clause happens before aggregate)
SELECT dest_city, COUNT(Distinct origin_city)
FROM Flights
WHERE origin_city = 'Atlanta GA'
GROUP BY dest_city
HAVING COUNT(DISTINCT origin_city) = 1;

--TRY 2: RIGHT
SELECT DISTINCT dest_city
FROM Flights F1
WHERE origin_city = 'Atlanta GA' AND NOT EXISTS (
    SELECT *
    FROM Flights F2
    WHERE F2.dest_city = F1.dest_city AND F2.origin_city != 'Atlanta GA')
ORDER BY dest_city;

--TRY 3: RIGHT WITH TRICK
SELECT dest_city
FROM Flights
GROUP BY dest_city
HAVING COUNT(DISTINCT origin_city) = 1 AND MAX(origin_city) = 'Atlanta GA'
ORDER BY dest_city;

/* Actual Result
Albany GA
Augusta GA
Brunswick GA
Columbus GA
Columbus MS
Dothan AL
Fayetteville NC
Florence SC
Hickory NC
Jacksonville/Camp Lejeune NC
Kinston NC
Lewisburg WV
Lynchburg VA
Macon GA
Naples FL
New Bern/Morehead/Beaufort NC
Tupelo MS
Valdosta GA
*/


