---------------------------------------------------------------------------
-- Lecture 10-3: Advanced Query Techniques
-- DATA 503: Fundamentals of Data Engineering
-- Student Worksheet
--
-- Instructions:
-- 1. Create the database:    advanced_queries;
-- 2. Run the SETUP section below to create and load all tables.
-- 4. Work through each question.
---------------------------------------------------------------------------


---------------------------------------------------------------------------
-- SETUP: Ensure you have the Run this entire section first!
---------------------------------------------------------------------------

-- County population estimates (3,142 rows)
-- NOTE: You need us_counties_pop_est_2019 from Chapter 5.
-- If you already have it in another database, you can export/import it.
-- Otherwise, create and load it here:
CREATE TABLE us_counties_pop_est_2019 (
    state_fips text,
    county_fips text,
    region smallint,
    state_name text,
    county_name text,
    area_land bigint,
    area_water bigint,
    internal_point_lat numeric(10,7),
    internal_point_lon numeric(10,7),
    pop_est_2018 integer,
    pop_est_2019 integer,
    births_2019 integer,
    deaths_2019 integer,
    international_migr_2019 integer,
    domestic_migr_2019 integer,
    residual_2019 integer,
    CONSTRAINT counties_2019_key PRIMARY KEY (state_fips, county_fips)
);

-- Update path to your CSV location!
COPY us_counties_pop_est_2019 FROM '/tmp/us_counties_pop_est_2019.csv' WITH (FORMAT CSV, HEADER);

-- County business patterns (2,764 rows)
CREATE TABLE cbp_naics_72_establishments (
    state_fips text,
    county_fips text,
    county text NOT NULL,
    st text NOT NULL,
    naics_2017 text NOT NULL,
    naics_2017_label text NOT NULL,
    year smallint NOT NULL,
    establishments integer NOT NULL,
    CONSTRAINT cbp_fips_key PRIMARY KEY (state_fips, county_fips)
);

COPY cbp_naics_72_establishments FROM '/tmp/cbp_naics_72_establishments.csv' WITH (FORMAT CSV, HEADER);

-- Employees table (from Chapter 7)
CREATE TABLE employees (
    emp_id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    first_name text,
    last_name text,
    salary numeric(10,2),
    dept text
);

INSERT INTO employees (first_name, last_name, salary, dept)
VALUES ('Julia', 'Reyes', 115300, 'IT'),
       ('Janet', 'King', 98000, 'IT'),
       ('Arthur', 'Pappas', 72700, 'Sales'),
       ('Michael', 'Taylor', 89100, 'Sales'),
       ('Samit', 'Gupta', 112000, 'Sales'),
       ('Li', 'Yu', 105700, 'Marketing');

-- Teachers table (from Chapter 7)
CREATE TABLE teachers (
    id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    first_name text,
    last_name text,
    school text,
    hire_date date,
    salary numeric
);

INSERT INTO teachers (first_name, last_name, school, hire_date, salary)
VALUES ('Janet', 'Smith', 'F.D. Roosevelt HS', '2011-10-30', 36200),
       ('Lee', 'Reynolds', 'F.D. Roosevelt HS', '1993-05-22', 65000),
       ('Samuel', 'Cole', 'Myers Middle School', '2005-08-01', 43500),
       ('Samantha', 'Bush', 'Myers Middle School', '2011-10-30', 36200),
       ('Betty', 'Diaz', 'Myers Middle School', '2005-08-30', 43500),
       ('Kathleen', 'Roush', 'F.D. Roosevelt HS', '2010-10-22', 38500);

-- Ice cream survey (200 rows)
CREATE TABLE ice_cream_survey (
    response_id integer PRIMARY KEY,
    office text,
    flavor text
);

COPY ice_cream_survey FROM '/tmp/ice_cream_survey.csv' WITH (FORMAT CSV, HEADER);

-- Temperature readings (730 rows)
CREATE TABLE temperature_readings (
    station_name text,
    observation_date date,
    max_temp integer,
    min_temp integer,
    CONSTRAINT temp_key PRIMARY KEY (station_name, observation_date)
);

COPY temperature_readings FROM '/tmp/temperature_readings.csv' WITH (FORMAT CSV, HEADER);


SELECT count(*) FROM us_counties_pop_est_2019;    -- 3,142
SELECT count(*) FROM cbp_naics_72_establishments;  -- 3,074
SELECT count(*) FROM employees;                    -- 6
SELECT count(*) FROM ice_cream_survey;             -- 200
SELECT count(*) FROM temperature_readings;         -- 1,077

-- Enable crosstab
CREATE EXTENSION tablefunc;


---------------------------------------------------------------------------
-- QUESTION 1: Scalar Subquery in WHERE
--
-- Return all counties where pop_est_2019 is at or above the 90th
-- percentile. Show county_name, state_name, and pop_est_2019.
-- Order by population descending.
---------------------------------------------------------------------------

-- Your query here:



---------------------------------------------------------------------------
-- QUESTION 2: Above-Average Counties
--
-- Return all counties where pop_est_2019 is above the national
-- average. Show county name, state, and population.
-- How many counties are above average? Why is it less than half?
---------------------------------------------------------------------------

-- Your query here:



---------------------------------------------------------------------------
-- QUESTION 3: Derived Table Join
--
-- Using derived tables (subqueries in FROM), compute the number
-- of food/accommodation establishments per 1,000 people at the
-- STATE level. Join a state-level sum of establishments to a
-- state-level sum of population. Order by rate descending.
---------------------------------------------------------------------------

-- Your query here:



---------------------------------------------------------------------------
-- QUESTION 4: Column Subquery - Difference from Median
--
-- Show county_name, state_name, pop_est_2019, the national median,
-- and the difference from median. Filter to counties within
-- 1,000 of the median.
---------------------------------------------------------------------------

-- Your query here:



---------------------------------------------------------------------------
-- QUESTION 5: Correlated Subquery
--
-- For each county, show its population alongside its STATE average
-- population (computed via a correlated subquery in SELECT).
-- Show county_name, state_name, pop_est_2019, and state_avg.
-- Order by state, then population descending. Limit to 20 rows.
---------------------------------------------------------------------------

-- Your query here:



---------------------------------------------------------------------------
-- QUESTION 6: EXISTS / NOT EXISTS
--
-- First create and populate the retirees table:
--   CREATE TABLE retirees (id int, first_name text, last_name text);
--   INSERT INTO retirees VALUES (2, 'Janet', 'King'), (4, 'Michael', 'Taylor');
--
-- Then write TWO queries:
-- a) Find employees who ARE retirees (using EXISTS)
-- b) Find employees who are NOT retirees (using NOT EXISTS)
---------------------------------------------------------------------------

-- Your queries here:



---------------------------------------------------------------------------
-- QUESTION 7: LATERAL Join - Last 2 Lab Accesses per Teacher
--
-- First create and populate the teachers_lab_access table:
--   (see setup in lecture slides)
--
-- Then use a LEFT JOIN LATERAL to get each teacher's 2 most recent
-- lab accesses. Teachers with no lab visits should still appear.
---------------------------------------------------------------------------

-- Your query here:



---------------------------------------------------------------------------
-- QUESTION 8: CTE - State Population Summary
--
-- Using two CTEs:
-- CTE 1: total population per state
-- CTE 2: number of counties per state
-- Join them and compute average population per county.
-- Order by average descending.
---------------------------------------------------------------------------

-- Your query here:



---------------------------------------------------------------------------
-- QUESTION 9: Crosstab - Ice Cream Survey
--
-- Generate a crosstab showing offices as rows and ice cream
-- flavors as columns, with counts as values.
-- (Requires CREATE EXTENSION tablefunc;)
---------------------------------------------------------------------------

-- Your query here:



---------------------------------------------------------------------------
-- QUESTION 10: CASE in a CTE - Population Tiers
--
-- Classify counties into tiers:
--   Metro (>= 500,000), Urban (100k-499k),
--   Suburban (50k-99k), Rural (< 50k)
-- Then count counties and sum population per tier.
-- Which tier has the most counties? Most total population?
---------------------------------------------------------------------------

-- Your query here:



---------------------------------------------------------------------------
-- QUESTION 11: CASE - Temperature Classification
--
-- Using a CTE with CASE, classify each temperature reading as
-- Hot/Warm/Pleasant/Cold/Frigid/Inhumane (see lecture thresholds).
-- Count the number of days in each category per station.
-- Order by station, then count descending.
---------------------------------------------------------------------------

-- Your query here:



---------------------------------------------------------------------------
-- QUESTION 12: Temperature Crosstab
--
-- Generate a crosstab of median max temperature by station and
-- month. Stations as rows, months (Jan-Dec) as columns.
---------------------------------------------------------------------------

-- Your query here:



---------------------------------------------------------------------------
-- BONUS: CTE vs Derived Table
--
-- Rewrite the derived table query from Question 3 using CTEs.
-- Which version do you find more readable? Why?
---------------------------------------------------------------------------

-- Your query here: