CREATE TABLE staffStats AS
    SELECT "Kavi" AS name, 19 AS age, 65 AS height, "SoCal" AS region UNION
    SELECT "Justin",       19,        72,           "NorCal"          UNION
    SELECT "Kevin",        21,        "lol Annie",  "NorCal"          UNION
    SELECT "Joyce",        21,        60,           "NorCal"          UNION
    SELECT "Joyce",        21,        60,           "NorCal"          UNION
    SELECT "Kelly",        19,        63,           "NorCal"          UNION
    SELECT "Elaine",       19,        66,           "SoCal"           UNION
    SELECT "Karthik",      19,        65,           "NorCal"          UNION
    SELECT "Samantha",     22,        63,           "NorCal"          UNION
    SELECT "Sequoia",      17,        70,           "Bolivia"         UNION
    SELECT "Alex",         19,        74,           "NorCal"          UNION
    SELECT "Gibbes",       20,        70,           "East Coast"      UNION
    SELECT "Matt",         19,        69,           "East Coast"      UNION
    SELECT "Angela",       19,        70,           "SoCal"           UNION
    SELECT "Yichen",       19,        65,           "NorCal"          UNION
    SELECT "Michelle",     19,        62,           "SoCal"           UNION
    SELECT "Shreya",       18,        64,           "NorCal"          UNION
    SELECT "Annie",        19,        62,           "NorCal";

CREATE TABLE staffFacts AS
    SELECT "Kavi" AS name, 42 as favNumber, "boba" as favDrink,         "SciFi" as favMovieGenre, "burrito" as favFood                               UNION
    SELECT "Justin",       1,               "water",                    "Action",                 "anything from Kimchi Garden"                      UNION
    SELECT "Kevin",        1,               "water",                    "SciFi",                  "soylent"                                          UNION
    SELECT "Joyce",        0,               "chia seed lemonade",       "SciFi",                  "sushi"                                            UNION
    SELECT "Joyce",        0,               "the blood of my students", "Action",                 "blood pudding made from the blood of my students" UNION
    SELECT "Kelly",        5,               "boba",                     "RomComs",                "N/a"                                              UNION
    SELECT "Elaine",       18,              "smoothies",                "Family",                 "kalbichim"                                        UNION
    SELECT "Karthik",      42,              "boba",                     "Comedy",                 "pizza"                                            UNION
    SELECT "Samantha",     7,               "tea",                      "Comedy",                 "cheese!"                                          UNION
    SELECT "Sequoia",      42,              "milk",                     "SciFi",                  "javascript cookies, especially in small bytes"    UNION
    SELECT "Alex",         10213223,        "water",                    "SciFi",                  "ramen"                                            UNION
    SELECT "Gibbes",       7,               "smoothies",                "Comedy",                 "cookies"                                          UNION
    SELECT "Matt",         ":(",            "boba",                     "SciFi",                  "SUSHI"                                            UNION
    SELECT "Angela",       "dance number",  "water",                    "RomComs",                "mom's food"                                       UNION
    SELECT "Yichen",       7,               "coffee",                   "Action",                 "sushi!"                                           UNION
    SELECT "Michelle",     11,              "tea",                      "Comedy",                 "chili cheese fries"                               UNION
    SELECT "Shreya",       6,               "smoothies",                "RomComs",                "avocado toast"                                    UNION
    SELECT "Annie",        7,               "water",                    "Family",                 "bread";

-- JOINS DEMO: fullStaff TABLE
CREATE TABLE fullStaff AS SELECT * FROM staffStats, staffFacts
    WHERE staffStats.name == staffFacts.name;

-- JOINS DEMO PRACTICE 1:
select staffStats.name, favDrink, favMovieGenre
    from staffStats, staffFacts
    where staffStats.name = staffFacts.name order by height

-- JOINS DEMO PRACTICE 2:
select a.name, b.name, a.height
    from staffStats as a, staffStats as b
    where a.name < b.name and a.height = b.height;

-- AGGREGATION DEMO:
select name, max(age) from staffStats;

select max(age) + min(height) from staffStats;

select name, min(height) + 5 from staffStats;

select avg(height) from staffStats;

select min(favNumber - height) from staffStats, staffFacts where staffStats.name = staffFacts.name;

select count(*) from staffStats;

select count(*) from staffFacts;

select count(distinct favNumber) from staffFacts;

select count(distinct age) from staffFacts;

select avg(distinct height) from staffStats;

select max(age) from staffStats, staffFacts where staffStats.name = staffFacts.name and favDrink = "boba";

select max(age) from staffStats, staffFacts where staffStats.name = staffFacts.name and favDrink = "tea";



-- INTRO GROUPING DEMO (different group by possibilities)
select name, min(age), region from staffStats group by region;
select min(name), age, region from staffStats group by region;
select age, favDrink, count(*) from fullStaff group by age, favDrink;
select name, height/age, count(*) from fullStaff group by height/age;


-- DISCUSSION QUESTION #1
-- (1) Shortest from each region
SELECT name, region, min(height)
    FROM staffStats
    GROUP BY region
    ORDER BY name;
-- (2) Distribution of course staff's ages
SELECT age, count(*) AS count
    FROM staffStats
    GROUP BY age
    ORDER BY count;

-- Filtering with Aggregation: can we use WHERE?
SELECT name, min(age)
    FROM fullStaff
    WHERE avg(height) < 70
    GROUP BY region;
SELECT name, min(age), region, height
    FROM fullStaff
    WHERE height < 70
    GROUP BY region;

-- WHERE vs HAVING
SELECT name, min(age)
    FROM fullStaff
    WHERE age > 17
    GROUP BY age;
SELECT name, min(age)
    FROM fullStaff
    GROUP BY age
    HAVING age > 17;

-- FILTERING WITH AGGREGATION: solve using HAVING
SELECT name, min(age)
    FROM fullStaff
    GROUP BY region
    HAVING avg(height) < 70;
SELECT name, min(age), region, avg(height)
    FROM fullStaff
    GROUP BY region
    HAVING avg(height) < 70;
-- (BONUS DEMOS): let's see how many people are in this region:
SELECT name, min(age), region, avg(height), count(*)
    FROM fullStaff
    GROUP BY region;
SELECT name, min(age), region, avg(height), count(*)
    FROM fullStaff
    GROUP BY region
    HAVING avg(height) < 70;
SELECT region
    FROM fullStaff
    GROUP BY region
    HAVING count(age) > 1;

-- CREATING LOCAL TABLES (favdrink)
WITH rating as (
    SELECT "boba" as drink, 5 as stars UNION
    SELECT "tea"          , 3          UNION
    SELECT "water"           , 5
)
SELECT name, favDrink FROM rating, staffFacts
       WHERE favDrink == drink and stars > 3;

-- DISCUSSION QUESTION #2: max height for staff
WITH diffs AS (
    SELECT age as a, max(height) - min(height) as h, count(*) as count
    FROM staffStats GROUP BY age
)
SELECT max(h) FROM diffs;
-- DISC QUESTION BONUS:
WITH diffs AS (
    SELECT age as a, max(height) - min(height) as h, count(*) as count
    FROM staffStats GROUP BY age
) SELECT * FROM diffs;
