How do i crack this SQL Soccer Matches assignment?

I came across this problem recently, i’m a sql newbie preparing for an interview and i need some help understanding how to connect the tables without using joins (since there is no common column)to get the desired result

create table teams
(team_id integer not null,
team_name varchar(30) not null,
unique(team_id)
);

Team_id .  Team_name
10 .       Give
20 .       Never
30 .       You
40 .       up
50 .       Gonna



create table matches 
(match_id integer not null,
host_team integer not null,
guest_team integer not null,
host_goals integer not null,
guest_goals integer not null,
unique(match_id)
);


Match_id   Host_team  Guest_team   Host_goals  Guest_goals
1 .        30 .       20 .         1 .         0 
2 .        10 .       20 .         1 .         2
3 .        20 .       50 .         2 .         2
4 .        10 .       30 .         1 .         0
5 .        30 .       50 .         0 .         1

Compute the total number of points each team has scored after all the matches the rules are as follows

if a team wins a match(scores more goals than the other team) it gets 3 points.

if a team draws a match(scores exactly same number of goals as other team) it gets one point

if a team loses a match(scores fewer goals than other team) it gets no points.

write a query that returns a ranking of all teams(team_id) described in the table teams. for each team provide its name and number of points it received after all the matches(num_points). The table should be ordered by num_points in desc order. in case of a tie order the rows by team_id

Saw a solution that uses union all but it doesn’t provide the answer, tried modifying it myself too.

select a.team_id,a.team_name,3*count(1) from teams a, matches b
where a.team_id = b.host_team
and b.host_goals > b.guest_goals
group by a.team_id,a.team_name

union all

select a.team_id,a.team_name,count(1) from teams a, matches b
where a.team_id = b.host_team
and b.host_goals = b.guest_goals
group by a.team_id,a.team_name

union all

select a.team_id,a.team_name,3*count(1) from teams a, matches b
where a.team_id = b.guest_team
and b.host_goals < b.guest_goals
group by a.team_id,a.team_name

union all

select a.team_id,a.team_name,count(1) from teams a, matches b
where a.team_id = b.guest_team
and b.host_goals = b.guest_goals
group by a.team_id,a.team_name;






Solution should look like
team_id    team_name    num_points
20         Never        4
50         Gonna        4
10         Give         3
30         You          3
40         Up           0

Answer

I suggest the following:

WITH cteHostPoints AS (SELECT HOST_TEAM AS TEAM,
                              CASE
                                WHEN HOST_GOALS > GUEST_GOALS THEN 3
                                WHEN HOST_GOALS = GUEST_GOALS THEN 1
                                ELSE 0
                              END AS POINTS
                         FROM MATCHES),
     cteGuestPoints AS (SELECT GUEST_TEAM AS TEAM,
                               CASE
                                 WHEN GUEST_GOALS > HOST_GOALS THEN 3
                                 WHEN GUEST_GOALS = HOST_GOALS THEN 1
                                 ELSE 0
                               END AS POINTS
                          FROM MATCHES),
     cteAllPoints AS (SELECT TEAM, POINTS FROM cteHostPoints
                      UNION ALL
                      SELECT TEAM, POINTS FROM cteGuestPoints)
SELECT t.TEAM_ID, t.TEAM_NAME, COALESCE(SUM(ap.POINTS), 0) AS TOTAL_POINTS
  FROM TEAMS t
  LEFT OUTER JOIN cteAllPoints ap
    ON ap.TEAM = t.TEAM_ID
  GROUP BY t.TEAM_ID, t.TEAM_NAME
  ORDER BY COALESCE(SUM(POINTS), 0) DESC, t.TEAM_ID

dbfiddle here