/* Day 3: Aggregate functions
Tim Pierson, Dartmouth CS61, Spring 20
*/

use nyc_data;

-- PRACTICE slide 7 practice with aggregate functions min, max, avg, count
-- find min and max restaurant names
SELECT MIN(DBA), MAX(DBA) FROM restaurant_inspections; -- 'CESCA and ZZ's Pizza

-- use aggregate operations on inspection score (does not include nulls)
-- find average score over all inspections, count how many inspections (two questions on slide)
SELECT AVG(score) as AvgScore, COUNT(score) AS Count 
FROM restaurant_inspections; -- avg 20.41, count 380,680 (does not include nulls)


-- PRACTICE slide 8 practice with NULLS
-- see how NULL changes things
-- double check avg value (this will include nulls and not the same as above!)
SELECT SUM(score)/COUNT(*) AS AvgScore FROM restaurant_inspections; -- avg 19.53, above avg is 20.41

-- same avg score as first query by exluding nulls
SELECT SUM(score)/COUNT(*) AS AvgScore FROM restaurant_inspections WHERE score IS NOT NULL; -- 20.41
SELECT SUM(score)/COUNT(score) AS AvgScore FROM restaurant_inspections; -- 20.41


-- PRACTICE slide 13 practice with GROUP BY and HAVING
-- find average score by boro
SELECT boro, AVG(score) AS AvgScore, count(*) AS Count
FROM restaurant_inspections
GROUP BY boro
ORDER BY AvgScore desc;

-- which is better a low or high score? (looks like low is better)
SELECT AVG(score) FROM restaurant_inspections WHERE `critical flag` = 'N'; -- 17.69
SELECT AVG(score) FROM restaurant_inspections WHERE `critical flag` = 'Y'; -- 22.59

-- find average score and count by boro and cuisine
SELECT Boro, `cuisine description` AS Cuisine, AVG(score) AS AvgScore, count(Score) AS Count
FROM restaurant_inspections
GROUP BY Boro, Cuisine
ORDER BY Boro, Cuisine;
-- ORDER BY AvgScore;

-- find number of times each restaurant in Queens inspected, avg score, where 
-- number of inspection scores greater than 5
SELECT camis AS RestaurantID, dba AS RestaurantName, 
	COUNT(Score) AS NumberInspections, AVG(score) AS AvgScore
from restaurant_inspections
WHERE boro = 'QUEENS'
GROUP BY RestaurantID, dba -- note can use renamed attributes (RestaurantID not camis) here or not (dba not RestaurantName)
HAVING NumberInspections >= 5
ORDER BY AvgScore
LIMIT 2000;





-- nested queries
-- first find all inspection scores for Morris Park Bake Shop (camis = 30075445)
SELECT camis AS RestaurantID, dba as RestaurantName, Score, boro
FROM restaurant_inspections WHERE camis = 30075445;  -- returns 15 scores

-- use nested query in select clause
-- return  dba, max(score) and score 
-- but score is max (164) for *all* restaurant inspections, not just this store
SELECT camis AS RestaurantID, dba as RestaurantName, Score, 
	(SELECT MAX(Score) FROM restaurant_inspections) AS MaxScore
FROM restaurant_inspections WHERE camis = 30075445;

-- use nested correlated query find dba and score with max score for *this* restaurant
-- max score for Morris Park Bake Shop is 17
SELECT camis AS RestaurantID, dba AS RestaurantName, Score, 
	(SELECT MAX(Score) FROM restaurant_inspections r2 
    WHERE r2.camis = r1.camis) AS MaxScore
FROM restaurant_inspections r1
WHERE r1.camis = 30075445;

-- add more restuarnts than just Morris Park Bake Shop (now includes Wendy's)
-- Morris Park max still 17, Wendy's is 48
SELECT camis AS RestaurantID, dba AS RestaurantName, Score, 
	(SELECT MAX(Score) FROM restaurant_inspections r2 
    WHERE r2.camis = r1.camis) AS MaxScore
FROM restaurant_inspections r1
WHERE r1.camis < 30180000 -- increased camis numbers allowed
ORDER BY r1.camis;

-- can we do the same with a GROUP BY?
-- no, score must be in GROUP BY, groups some scores together!
-- MAX(Score) will be the max score for each group
SELECT camis AS RestaurantID, dba AS RestaurantID, Score, MAX(Score) 
FROM restaurant_inspections
WHERE camis < 30180000
GROUP BY camis,dba,score
ORDER BY camis;



-- get average score by boro
SELECT Boro, AVG(Score) FROM restaurant_inspections GROUP BY boro;

-- compare this restaurant vs. average inspection for it's boro
SELECT dba AS RestaurantName, Score, boro,
	(SELECT AVG(Score) FROM restaurant_inspections r2 
    WHERE r2.boro = r1.boro) AS AvgBoroScore
FROM restaurant_inspections r1
WHERE r1.camis < 30080000;
-- runs slowly, even for one restaurant, computes avg score a lot!

-- use WITH to first compute a temporary table of boro scores
-- then use that temp score in the main query
-- runs much faster, even with more restaurants, computes avg once
WITH AvgScoreTable AS 
	(SELECT Boro, AVG(Score) AS AvgScore FROM restaurant_inspections GROUP BY boro) 
SELECT camis AS RestaurantID, dba AS RestaurantName, Score, boro,
	(SELECT AvgScore FROM AvgScoreTable r2 
    WHERE r2.boro = r1.boro) AS AvgBoroScore
FROM restaurant_inspections r1
WHERE r1.camis < 30180000
ORDER BY r1.camis;

-- use subquery in where clause
-- find scores for restaurant with min camis id
SELECT camis as RestaurantID, dba AS RestaurantName, Score
FROM restaurant_inspections
WHERE camis = (SELECT MIN(CAMIS) FROM restaurant_inspections);

-- find restaurant with max score
SELECT * FROM restaurant_inspections
WHERE score = (SELECT MAX(Score) FROM restaurant_inspections);




-- PRACTICE slide 19
-- 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); 




