/* Day 3 - Aggregation
Tim Pierson, Dartmouth CS61, Spring 2026
*/

use nyc_data;

-- SLIDE 2
SELECT CAMIS, dba, `inspection date`, `violation code`, Score, Grade
	FROM restaurant_inspections
	WHERE boro='Manhattan'
	ORDER BY Score desc, `inspection date`;


-- SLIDE 6
-- calculate average score
SELECT AVG(score) AS AvgScore 
	FROM restaurant_inspections; -- 25.5945

-- count(*) will count NULLs for score because some attribute in the row is not NULL
SELECT SUM(score)/COUNT(*) AS AvgScore 
	FROM restaurant_inspections; -- 23.62.08, doesn't match the command above because this query counts NULL scores too!
    
-- instead of counting *, you can fix the previous query by counting scores, because NULL doesn't count in COUNT
SELECT SUM(score)/COUNT(score) AS AvgScore 
	FROM restaurant_inspections; -- 25.5945
    
-- another way to deal with NULL is to filter NULL scores out in the WHERE clause
SELECT SUM(score)/COUNT(*) AS AvgScore 
	FROM restaurant_inspections
    WHERE Score IS NOT NULL; -- 25.5945
    
-- SLIDE 7
-- Check to see if restaurant in 1 of 5 boros using OR
SELECT * 
	FROM restaurant_inspections
	WHERE boro = 'Manhattan' 
		OR boro = 'Bronx'
        OR boro = 'Brooklyn'
        OR boro = 'Queens'
        OR boro = 'Staten Island';
        
-- Equivalent boro check using IN
SELECT * 
	FROM restaurant_inspections
	WHERE boro IN ('Manhattan', 'Bronx', 'Brooklyn', 'Queens', 'Staten Island');

-- ********************************************************************************
-- PRACTICE 1 slide 8
-- ********************************************************************************
-- where any restaurants not in one of the five boros?
SELECT * 
	FROM restaurant_inspections
	WHERE boro NOT IN ('Manhattan', 'Bronx', 'Brooklyn', 'Queens', 'Staten Island');
    -- yes, 16 restaurants had 0 for boro!
    
-- another approach to find restaurants not in 1 or 5 boros, use DISTINCT to find unique boro values
SELECT DISTINCT(boro)
	FROM restaurant_inspections; -- we see there is a zero for boro sometimes
SELECT * 
	FROM restaurant_inspections
    WHERE boro = '0'; -- see the 16 restaurants with a zero for boro

-- find min and max restaurant name
SELECT MIN(DBA) FROM restaurant_inspections; -- :3 COFFEE
SELECT MAX(DBA) FROM restaurant_inspections; -- ZZ's PIZZA AND GRILL

-- What is the average score of all inspection scores?
SELECT AVG(SCORE) FROM restaurant_inspections; -- 25.59

-- How many restaurant inspections were recorded
SELECT COUNT(*) 
	FROM restaurant_inspections; -- 291,534

-- How many restaurant inspections were recorded where score not null
SELECT COUNT(*) 
	FROM restaurant_inspections
    WHERE Score IS NOT NULL; -- 269,053

-- Try to answer the last two questions with one SELECT query
SELECT AVG(score) as AvgScore, COUNT(*) as Count 
	FROM restaurant_inspections
    WHERE Score IS NOT NULL; -- 25.59 and 269,053


-- SLIDE 10
use college;
SELECT ID, name, dept_name,
	FORMAT(salary,0) AS Salary
	FROM instructor 
	ORDER BY dept_name;
    
SELECT dept_name, 
	FORMAT(AVG(salary),0) AS AvgSalary
	FROM instructor
	GROUP BY dept_name
	ORDER BY AvgSalary DESC;

-- SLIDE 11
SELECT dept_name, 
	FORMAT(AVG(salary),0) AS AvgSalary
	FROM instructor
	GROUP BY dept_name
	ORDER BY AvgSalary DESC;
    
SELECT dept_name, 
	FORMAT(AVG(salary),0) AS AvgSalary
	FROM instructor
	GROUP BY dept_name
	HAVING AVG(salary) > 65000
	ORDER BY AvgSalary DESC;
    
    
    
-- ********************************************************************************
-- PRACTICE 2 slide 16
-- ********************************************************************************
-- which is better high or low score?
use nyc_data;
SELECT `critical flag` as Critical, AVG(SCORE) 
	FROM restaurant_inspections
	GROUP BY Critical; 
    -- Critical 28.0644, Not Critical 22.4387
    -- looks like a lower score is better
    
-- confirm suspicion about which is better by averaging when no violations found
-- know you data!
SELECT `action`, AVG(Score)
	FROM restaurant_inspections
    WHERE `action` IS NOT NULL AND `action` <> ''
    GROUP BY `action`;
    -- When restaurant is closed after inspection avg is 68.64, Violations cited (but not closed) avg is 24.0348
    -- When no violations, score is always NULL
    -- Higher score is worse!
    
-- battle of the boros: find average score and number of inspections by boro and cuisine descritpion
SELECT boro, AVG(score) as AvgScore, count(score) as InspectionCount
	FROM restaurant_inspections 
    WHERE boro in ('Manhattan', 'Bronx', 'Brooklyn', 'Queens', 'Staten Island')
        -- AND score >= 0
    GROUP BY boro
    ORDER BY AVG(score);

-- grouping by cuisine too
SELECT boro, `cuisine description` AS Cuisine, AVG(score) as AvgScore, count(score) as InspectionCount
	FROM restaurant_inspections 
    WHERE boro in ('Manhattan', 'Bronx', 'Brooklyn', 'Queens', 'Staten Island')
        AND score >= 0
    GROUP BY boro, Cuisine
    -- HAVING COUNT(*) > 5 -- if we want to make sure each cuisine in a boro was inpsected at least 5 times
    ORDER BY AVG(score);
	-- ORDER BY boro, AVG(score); -- could also sort by boro first, then by avg score
    
    -- hotdog stands do pretty well!

    
-- for restaurants in Queens, find avg score and number of inspections where restaurant has at least 5 inspections
-- sort by avg score, best first
SELECT CAMIS, dba, AVG(score) as AvgScore, COUNT(score) as InspectionCount
	FROM restaurant_inspections 
    WHERE boro = 'Queens'
    GROUP BY CAMIS, dba
    HAVING COUNT(score) > 5
    ORDER BY AVG(score) asc;
    
-- SLIDE 19    
SELECT dba AS RestaurantName, Score, 
	(SELECT MAX(Score) 
		FROM restaurant_inspections r2 
		WHERE r2.camis = r1.camis) AS MaxScore
	FROM restaurant_inspections r1
	WHERE dba LIKE 'Morris Park Bake%';
    
-- SLIDE 20
SELECT dba AS RestaurantName, Score
	FROM (SELECT * FROM restaurant_inspections WHERE boro = 'Manhattan') AS ManhattanRestaurants
    WHERE Score IS NOT NULL;



-- SLIDE 21
SELECT camis AS RestaurantID, dba AS RestaurantName, Score
	FROM restaurant_inspections
	WHERE camis = (SELECT MIN(CAMIS) 
					FROM restaurant_inspections);
                    
SELECT * FROM restaurant_inspections
	WHERE score = (SELECT MAX(Score) 
					FROM restaurant_inspections);
                    
-- SLIDE 22
-- First create table with Max inspection, query that table to find restaurants that have the max score
WITH MaxScoreTable AS 
	(SELECT MAX(Score) AS MaxScore FROM restaurant_inspections)
SELECT * FROM restaurant_inspections
	WHERE score = (SELECT MaxScore FROM MaxScoreTable);

-- ********************************************************************************
-- PRACTICE 3 slide 23
-- ********************************************************************************
-- find distinct restaurants not in manhattan or queens and their avg score
SELECT camis as RestaurantID, dba as RestaurantName, boro, AVG(Score) as AvgScore
	FROM restaurant_inspections
	WHERE boro NOT IN ('Manhattan','Queens') 
	GROUP BY camis, dba, boro
	ORDER BY AvgScore desc
	LIMIT 2000;

-- find all restaurants with score greater than average
-- use WITH
WITH AvgScoreTable AS (SELECT AVG(Score) AS AvgScore FROM restaurant_inspections)
SELECT * FROM restaurant_inspections, AvgScoreTable 
	WHERE score > AvgScore;

-- same thing, but without WITH
SELECT * FROM restaurant_inspections
	WHERE score > (SELECT AVG(Score) FROM restaurant_inspections); 



-- SLIDE 25
use nyc_data;
SELECT camis AS RestaurantID, dba AS RestaurantName, `inspection date`, Score,
	IF(Grade = '','N/A',Grade) AS Grade, `grade date`
	FROM restaurant_inspections 
	WHERE camis = 30075445;

-- SLIDE 27
SELECT camis, dba, count(*) AS `Total Inspections`,
CASE 
	WHEN count(*) < 10 THEN 'Infrequent inspections '
	WHEN count(*) BETWEEN 10 AND 15 THEN 'Moderate inspections'
    	ELSE 'Frequent inspections'
END AS Frequency
FROM restaurant_inspections
GROUP BY CAMIS, dba
HAVING count(*) > 1;

-- ********************************************************************************
-- PRACTICE 4 slide 28
-- ********************************************************************************
-- see distinct grades
SELECT DISTINCT grade 
	FROM restaurant_inspections
    ORDER BY grade;
	-- A, B, C, G, N, P, Z, ''

-- SLIDE 29
SELECT CAMIS, dba AS RestaurantName, 
    SUM(CASE Grade WHEN 'A' THEN 1 ELSE 0 END) AS 'A', 
    SUM(CASE Grade WHEN 'B' THEN 1 ELSE 0 END) AS 'B',
    SUM(CASE Grade WHEN 'C' THEN 1 ELSE 0 END) AS 'C',
    SUM(CASE Grade WHEN 'G' THEN 1 ELSE 0 END) AS 'G',
    SUM(CASE Grade WHEN 'N' THEN 1 ELSE 0 END) AS 'N',
    SUM(CASE Grade WHEN 'P' THEN 1 ELSE 0 END) AS 'P',
    SUM(CASE Grade WHEN 'Z' THEN 1 ELSE 0 END) AS 'Z', 
    -- SUM(CASE WHEN Grade = '' THEN 1 ELSE 0 END) AS 'Empty',  -- uncomment if want empty grade
    COUNT(*) AS Total
FROM restaurant_inspections 
WHERE grade <> '' -- comment out if want empty grades
GROUP BY CAMIS, dba;

-- SLIDE 30
-- another solution, here we use COUNT and set value to NULL if not Grade (A, B, C, ...)
SELECT CAMIS, dba AS RestaurantName, 
	COUNT(IF (Grade = 'A', Grade, NULL)) AS 'A', 
	COUNT(IF (Grade = 'B', Grade, NULL)) AS 'B', 
	COUNT(IF (Grade = 'C', Grade, NULL)) AS 'C', 
	COUNT(IF (Grade = 'G', Grade, NULL)) AS 'G', 
	COUNT(IF (Grade = 'N', Grade, NULL)) AS 'N', 
	COUNT(IF (Grade = 'P', Grade, NULL)) AS 'P', 
	COUNT(IF (Grade = 'Z', Grade, NULL)) AS 'Z', 
	-- COUNT(IF (Grade = '', 1, NULL)) AS 'Empty', -- uncomment if want empty grades
	COUNT(*) as Total
FROM restaurant_inspections 
WHERE Grade <> '' -- comment out if want empty grades
GROUP BY CAMIS, dba;





-- SLIDE 32
use classicmodels;
SELECT *
	FROM Sales;
    
SELECT productLine, FORMAT(SUM(orderValue),2) AS totalOrders
	FROM sales
    GROUP BY productLine;
    
-- SLIDE 33
SELECT productLine, FORMAT(SUM(orderValue),2) AS totalOrders
	FROM sales
    GROUP BY productLine
UNION ALL
SELECT
	NULL,
    SUM(orderValue) totalOrderValue
FROM sales;

-- SLIDE 34
SELECT productLine, FORMAT(SUM(orderValue),2) AS totalOrders
	FROM sales
    GROUP BY productLine WITH ROLLUP;

-- SLIDE 35
SELECT productLine, orderYear, FORMAT(SUM(orderValue),2) AS totalOrders
	FROM sales
	GROUP BY productline, orderYear WITH ROLLUP;

-- SLIDE 36 (reverse group order of previous query)
SELECT orderYear, productLine, FORMAT(SUM(orderValue),2) AS totalOrders
	FROM sales
	GROUP BY orderYear, productline WITH ROLLUP;

-- SLIDE 37
SELECT IFNULL(orderYear,'GRAND TOTAL') AS orderYear, 
	IFNULL(productLine,'Line Total') AS productLine, 
    FORMAT(SUM(orderValue),2) AS totalOrders
	FROM sales
	GROUP BY orderYear, productline WITH ROLLUP;
    
    
-- SLIDE 38
SELECT IF(GROUPING(orderYear),'All Years',orderYear)AS orderYear,
	IF(GROUPING(productLine),'All Products',productLine)AS productLine,
	SUM(orderValue)AS totalOrderValue
	FROM sales
	GROUP BY orderYear, productline WITH ROLLUP;
    
-- SLIDE 39
use nyc_data;
SELECT IF(GROUPING(boro),'TOTAL',boro) AS Boro, 	       	-- final super aggregate row
	IF(GROUPING(zipcode),'Boro Avg',zipcode) AS ZipCode, 	-- super aggregate row per zip code
    AVG(Score) AS AvgScore
	FROM restaurant_inspections
	WHERE boro IN ('Manhattan', 'Bronx') 
		AND zipcode <> ''
		AND score IS NOT NULL
    GROUP BY boro, zipcode WITH ROLLUP;


-- SLIDE 41
-- RANK
SELECT RANK() OVER (ORDER BY AVG(score)) AS `Rank`, boro,  AVG(score) AS Avg
	FROM restaurant_inspections 
	WHERE `cuisine description` LIKE 'American%'
	GROUP BY boro;
    
-- SLIDE 42
SET @k = 2; -- return top k=2
WITH RestaurantRanks AS (
	SELECT RANK() OVER (ORDER BY AVG(Score)) AS `Rank`, boro, AVG(score) AS Avg
	FROM restaurant_inspections 
	WHERE `cuisine description` LIKE 'American%'
	GROUP BY boro) 
SELECT * FROM RestaurantRanks WHERE `Rank`<= @k; -- top 2


    
-- ********************************************************************************
-- END DAY 3
-- ********************************************************************************
