/* Day 17: Data analysis
Tim Pierson, Dartmouth CS61, Spring 2020 
*/

use nyc_inspections;

-- PRACTICE on slide 24
-- ROLLUP and UNION
-- first return counts of inspections grouped by boro and cuisine
SELECT r.Boro, c.CuisineDescription, count(*) AS Count
FROM Inspections i JOIN Restaurants r ON i.RestaurantID = r.RestaurantID
	JOIN Cuisine c ON r.CuisineID = c.CuisineID
GROUP BY r.Boro, c.CuisineDescription
ORDER BY r.Boro, c.CuisineDescription;
-- returns 2103 American cuisine inspections in the Bronx
-- test we get the same count with this query 
SELECT count(*) 
FROM Inspections i JOIN Restaurants r ON i.RestaurantID = r.RestaurantID 
WHERE r.CuisineID = 2 AND r.Boro = 'Bronx'; -- checks, returns 2103
 
 -- add ROLLUP for cuisine type (but not yet boro just to test)
SELECT c.CuisineDescription, count(*) AS Count 
FROM Inspections i JOIN Restaurants r ON i.RestaurantID = r.RestaurantID
	JOIN Cuisine c ON r.CuisineID = c.CuisineID
GROUP BY c.CuisineDescription WITH ROLLUP
ORDER BY Count desc ;
 
 -- add roll up for boro and cuisine type
 SELECT r.Boro, c.CuisineDescription, count(*) AS Count
FROM Inspections i JOIN Restaurants r ON i.RestaurantID = r.RestaurantID
	JOIN Cuisine c ON r.CuisineID = c.CuisineID
GROUP BY Boro, CuisineDescription WITH ROLLUP
ORDER BY Boro, CuisineDescription ;
-- problem here!  the aggregate rows come first, but we want them to come last!
-- solve by using grouping
-- grouping returns 0 if not rollup, else returns 1, can use to sort so totals go last
-- solve by ordering by GROUPING first (ascending), then by boro
-- also fill in nulls with "All Boros or All Cuisines" or both for Grand Total!
SELECT IF(GROUPING(r.Boro),'All Boros',r.Boro) as Boro,
IF(GROUPING(c.CuisineDescription),'All Cuisines',c.CuisineDescription) as Cuisine,
FORMAT(count(*),0) AS Inspections  -- format adds commas and zero decimal digits
FROM Inspections i JOIN Restaurants r ON i.RestaurantID = r.RestaurantID
	JOIN Cuisine c ON r.CuisineID = c.CuisineID
GROUP BY Boro, CuisineDescription WITH ROLLUP
ORDER BY GROUPING(Boro), Boro, GROUPING(CuisineDescription), CuisineDescription ;

-- test grand total of 145,752 should be the same as the number of inspections
SELECT count(*) FROM Inspections; -- it is!


-- RANK
-- for demonstration, erase inspections for Tim's and insert one with score of 10
DELETE FROM Inspections WHERE RestaurantID = 1111;
INSERT INTO Inspections (RestaurantID, Score) VALUES (1111,10);


-- update inspection stats
UPDATE Restaurants r 
	SET InspectionCount = (SELECT count(*) FROM Inspections i WHERE i.RestaurantID = r.RestaurantID),
    InspectionAvgScore = (SELECT AVG(Score) FROM Inspections i WHERE i.RestaurantID = r.RestaurantID);
-- show results
SELECT * FROM Restaurants WHERE RestaurantName like 'Tim''s%'; -- count=2, avgscore=8 as expected


-- insert an inspection for Tim's Tasty Treats with score of 6 
-- previously had 1 inspection with a score of 10, so avg score now should be 8
-- triggers from before update stats
INSERT INTO Inspections (RestaurantID, Score) VALUES (1111,6);
-- show results to confirm two inspections with avg score of 8
SELECT RestaurantName, InspectionCount,InspectionAvgScore 
FROM Restaurants WHERE RestaurantName LIKE 'Tim''s%'; -- 8 checks (note: Untasty treats is null)

-- we are going to rank fruit/vegtable restauants
-- first find CuisineID for fruits/vegetable restaurants
SET @CuisineID = (SELECT CuisineID from Cuisine WHERE CuisineDescription like 'fruit%');
SELECT @CuisineID; -- 83

-- lets look at all the fruit/vegetable restaurants
SELECT * FROM Restaurants WHERE CuisineID = @CuisineID ORDER BY InspectionAvgScore; -- 7 of them in total
-- now rank them by boro in order of InspectionAvgScore 
SELECT 
	RANK() OVER (PARTITION BY Boro ORDER BY InspectionAvgScore) AS `Rank`,
    RestaurantID, RestaurantName, Boro, InspectionAvgScore
FROM Restaurants 
WHERE CuisineID = @CuisineID;
-- NOTE: Tim's Tasty Treats and Ono Bowls are both tied for 2nd in Manhattan, 
-- so both get a rank of 2, whereas Juke Box Juice gets rank 4 (not rank of 3)
-- ties have the same rank

-- now rank them without considering the boro (partition by is optional)
SELECT 
	RANK() OVER (ORDER BY InspectionAvgScore asc) AS `Rank`,
    RestaurantID, RestaurantName, Boro, InspectionAvgScore
FROM Restaurants 
WHERE CuisineID = @CuisineID;

-- use ROW_NUMBER instead of RANK to give ties a unique number (based on primary key order)
SELECT 
	ROW_NUMBER() OVER (ORDER BY InspectionAvgScore asc) AS `Rank`,
    RestaurantID, RestaurantName, Boro, InspectionAvgScore
FROM Restaurants 
WHERE CuisineID = @CuisineID;
-- Tim's and Ono bowls now have unique rank (not tied as before)

-- use WITH and LIMIT to get top k results
SET @k = 2; -- using variable k for convenience, not because we need to
WITH RestaurantRanks AS (
	SELECT RANK() OVER (ORDER BY InspectionAvgScore ) AS `Rank`,
		RestaurantID, RestaurantName, Boro, InspectionAvgScore
	FROM Restaurants WHERE CuisineID = @CuisineID)
SELECT * FROM RestaurantRanks WHERE `Rank`<= @k;

-- get top 2 restaurants by Cuisine type in Manhattan boro (do not count zero or null in avg score)
-- use partition by to break ordering by cuisine id
-- also note, use of IS NULL to sort nulls last (see https://www.designcise.com/web/tutorial/how-to-order-null-values-first-or-last-in-mysql)
-- and use of IF to set 0 to null so we don't count them either
-- also no ties (only return 2 restaurants per category)
WITH RestaurantRanks AS (
SELECT ROW_NUMBER() OVER (PARTITION BY r.CuisineID ORDER BY IF(InspectionAvgScore=0,NULL,InspectionAvgScore) IS NULL, InspectionAvgScore ) AS `Rank`,
	CuisineDescription, RestaurantID, RestaurantName, Boro, InspectionAvgScore
FROM Restaurants r JOIN Cuisine c ON r.CuisineID = c.CuisineID 
WHERE Boro = 'Manhattan')
SELECT * FROM RestaurantRanks WHERE `Rank` <=2;

-- alternative using coalesce
WITH RestaurantRanks AS (
SELECT RANK() OVER (PARTITION BY r.CuisineID ORDER BY COALESCE(IF(InspectionAvgScore=0,NULL,InspectionAvgScore),1000) asc) AS `Rank`, 
	CuisineDescription, RestaurantID, RestaurantName, Boro, InspectionAvgScore
FROM Restaurants r JOIN Cuisine c ON r.CuisineID = c.CuisineID 
WHERE Boro = 'Manhattan')
SELECT * FROM RestaurantRanks WHERE `Rank` <=2;


-- create a stored procedure of this data with parmeters for top k, and boro
-- return as json (https://stackoverflow.com/questions/41758870/how-to-convert-result-table-to-json-array-in-mysql)
DROP PROCEDURE IF EXISTS TopByBoro;
DELIMITER $$
CREATE PROCEDURE TopByBoro (
    IN borough varchar(20),
    IN topK int
) 
BEGIN
	WITH RestaurantRanks AS (
	SELECT RANK() OVER (Partition By r.CuisineID Order by COALESCE(IF(InspectionAvgScore=0,NULL,InspectionAvgScore),1000) asc) as `Rank`, 
		CuisineDescription, RestaurantID, RestaurantName, Boro, InspectionAvgScore
	FROM Restaurants r INNER JOIN Cuisine c on r.CuisineID = c.CuisineID WHERE Boro = borough)
    -- to return as table, comment out SELECT after 'return as JSON' comment, uncomment next line
	 SELECT * FROM RestaurantRanks WHERE `Rank` <= topK;
    -- to return as JSON, comment prior line and uncomment next line
    -- SELECT json_arrayagg(json_object('Rank',`Rank`,'CuisineDescription',CuisineDescription,'RestaurantID',RestaurantID, 'RestaurantName', RestaurantName, 'Boro',Boro,'InspectionAvgScore',InspectionAvgScore)) 
	-- FROM RestaurantRanks WHERE `Rank` <= topK;
END $$
DELIMITER ;

Call TopByBoro('Manhattan',3);
-- NOTE: must make stored procedure create a temp table if other queries to select from results
-- temp table ends when connection ends (https://www.mysqltutorial.org/mysql-temporary-table/) can drop temp table when done


-- output as JSON
WITH RestaurantRanks AS (
SELECT RANK() OVER (Partition By r.CuisineID Order by COALESCE(IF(InspectionAvgScore=0,NULL,InspectionAvgScore),1000) asc) as `Rank`, CuisineDescription, RestaurantID, RestaurantName, Boro, InspectionAvgScore
	FROM Restaurants r INNER JOIN Cuisine c on r.CuisineID = c.CuisineID WHERE Boro = 'Manhattan')
SELECT json_arrayagg(json_object('Rank',`Rank`,'CuisineDescription',CuisineDescription,'RestaurantID',RestaurantID, 'RestaurantName', RestaurantName, 'Boro',Boro,'InspectionAvgScore',InspectionAvgScore )) 
FROM RestaurantRanks WHERE `Rank` <=2;


-- window
-- return the last 3 inspections for a given restaurant
-- first get all inspections for this restaurant
SET @RestaurantID = 30075445;
SELECT RestaurantName, i.* FROM Restaurants r JOIN Inspections i on r.RestaurantID = i.RestaurantID
WHERE r.RestaurantID = @RestaurantID
ORDER BY InspectionDate desc;

-- get last 3 inspections for a restaurant
SELECT RestaurantName, i.* FROM Restaurants r JOIN Inspections i on r.RestaurantID = i.RestaurantID
WHERE r.RestaurantID = @RestaurantID
ORDER BY InspectionDate desc
LIMIT 3;


-- pivot table data
SELECT * 
FROM Restaurants r JOIN Inspections i ON r.RestaurantID = i.RestaurantID
	JOIN Cuisine c ON c.CuisineID = r.CuisineID
WHERE r.CuisineID = @CuisineID
ORDER BY InspectionDate desc;




 