-- Overall Leaderboard
-- This lists all the products that cost
-- more than a given price.

USE modena;
DROP TABLE if exists allteam;
DROP TABLE if exists fish;
DROP TABLE if exists chicken;
DROP TABLE if exists ribs;
DROP TABLE if exists porkshoulder;
DROP TABLE if exists brisket;
DROP TABLE if exists leaderboard;

Create table allteam(
teams varchar(30));

Insert into allteam(teams)
select team from results INNER JOIN dishes_to_team on results.unique_dish_id = dishes_to_team.unique_dish_id GROUP BY team;

CREATE TABLE fish
SELECT team,ROUND(SUM(total),2) as total_fish FROM results INNER JOIN dishes_to_team on results.unique_dish_id = dishes_to_team.unique_dish_id where category="Fish" group by team;

CREATE TABLE chicken
SELECT team,ROUND(SUM(total),2) as total_chicken FROM results INNER JOIN dishes_to_team on results.unique_dish_id = dishes_to_team.unique_dish_id where category="chicken" group by team;

CREATE TABLE ribs
SELECT team,ROUND(SUM(total),2) as total_ribs FROM results INNER JOIN dishes_to_team on results.unique_dish_id = dishes_to_team.unique_dish_id where category="Ribs" group by team;

CREATE TABLE porkshoulder
SELECT team,ROUND(SUM(total),2) as total_porkshoulder FROM results INNER JOIN dishes_to_team on results.unique_dish_id = dishes_to_team.unique_dish_id where category="PorkShoulder" group by team;

CREATE TABLE brisket
SELECT team,ROUND(SUM(total),2) as total_brisket FROM results INNER JOIN dishes_to_team on results.unique_dish_id = dishes_to_team.unique_dish_id where category="Brisket" group by team;


create table leaderboard
select teams,total_fish,total_chicken,total_ribs,total_porkshoulder,total_brisket,
ROUND(
coalesce(total_fish,"0.00")
+coalesce(total_chicken,"0.00")
+coalesce(total_ribs,"0.00")
+coalesce(total_porkshoulder,"0.00")
+coalesce(total_brisket,"0.00")
,2
) AS grand_total
from allteam 
left join fish on teams=fish.team
left join chicken on allteam.teams=chicken.team
left join ribs on allteam.teams=ribs.team  
left join porkshoulder on allteam.teams=porkshoulder.team 
left join brisket on allteam.teams=brisket.team 
group by teams,total_fish,total_chicken,total_ribs,total_porkshoulder, total_brisket order by teams;

select * from leaderboard order by grand_total desc, total_ribs desc, total_brisket desc, total_chicken desc, total_porkshoulder desc;