https://www.youtube.com/watch?v=TRiWeg3M3oQ

DROP TABLE IF EXISTS teams;
CREATE TABLE teams 
(
       team_id       INT PRIMARY KEY,
       team_name     VARCHAR(50) NOT NULL
);


DROP TABLE IF EXISTS matches;
CREATE TABLE matches 
(
       match_id 	INT PRIMARY KEY,
       host_team 	INT,
       guest_team 	INT,
       host_goals 	INT,
       guest_goals 	INT
);

INSERT INTO teams VALUES(10, 'Give');
INSERT INTO teams VALUES(20, 'Never');
INSERT INTO teams VALUES(30, 'You');
INSERT INTO teams VALUES(40, 'Up');
INSERT INTO teams VALUES(50, 'Gonna');

INSERT INTO matches VALUES(1, 30, 20, 1, 0);
INSERT INTO matches VALUES(2, 10, 20, 1, 2);
INSERT INTO matches VALUES(3, 20, 50, 2, 2);
INSERT INTO matches VALUES(4, 10, 30, 1, 0);
INSERT INTO matches VALUES(5, 30, 50, 0, 1);


SELECT * FROM teams;
SELECT * FROM matches;

with cte as (
SELECT match_id,host_team,guest_team,host_goals,guest_goals FROM matches 
UNION 
Select match_id,guest_team,host_team,guest_goals,host_goals FROM matches m2
) 

SELECT 
	team_id,team_name,
    SUM(case when host_goals > guest_goals then 3
    when host_goals = guest_goals then 1
    else 0 end) as num_points
FROM cte c right JOIN teams t on c.host_team = t.team_id
group by team_name
order by num_points desc,team_id asc