'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
Solution 1:[1]
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
Solution 2:[2]
Tried this with MySQL using just a bunch of case statements.
select team_id, team_name,
coalesce(sum(case when team_id = host_team then
(
case when host_goals > guest_goals then 3
when host_goals = guest_goals then 1
when host_goals < guest_goals then 0
end
)
when team_id = guest_team then
(
case when guest_goals > host_goals then 3
when guest_goals = host_goals then 1
when guest_goals < host_goals then 0
end
)
end), 0) as num_points
from Teams
left join Matches
on
Teams.team_id = Matches.host_team
or Teams.team_id = Matches.guest_team
group by team_id, team_name
order by num_points desc, team_id;
Solution 3:[3]
Here goes my suggestion
WITH points_per_game AS(
SELECT
t.team_id as team_id,
t.team_name as team_name,
CASE
WHEN t.team_id = m.host_team
AND host_goals > guest_goals
THEN 3
WHEN t.team_id = m.host_team
AND host_goals < guest_goals
THEN 0
WHEN t.team_id = guest_team
AND guest_goals > host_goals
THEN 3
WHEN t.team_id = guest_team
AND guest_goals < host_goals
THEN 0
WHEN guest_goals IS NULL
THEN 0
ELSE 1
END as game_points
FROM teams t
LEFT JOIN matches m ON t.team_id = m.host_team
OR t.team_id = m.guest_team
)
SELECT team_id, team_name, SUM(game_points)
FROM points_per_game
GROUP BY 1,2
ORDER BY SUM(game_points) DESC, team_id ASC
Solution 4:[4]
MySql Query.
select a.team_id, b.team_name, a.num_points
from (
select a.team_id
, sum(a.wins_point) as sum_wins_point
, sum(a.draws_point) as sum_draws_point
, sum(a.wins_point + a.draws_point) as num_points
from (
select b.team_type
, a.match_id
, (
case when b.team_type = 'host' then a.host_team else a.guest_team end
) as team_id
, (
case when b.team_type = 'host' then
case when a.host_goals > a.guest_goals then 3 else 0 end
else
case when a.host_goals < a.guest_goals then 3 else 0 end
end
) as wins_point
, (
case when b.team_type = 'host' then
case when a.host_goals = a.guest_goals then 1 else 0 end
else
case when a.host_goals = a.guest_goals then 1 else 0 end
end
) as draws_point
from matches a
cross join (
select 'host' as team_type
union all
select 'guest' as team_type
) b
) a
group by a.team_id
) a
inner join teams b on a.team_id = b.team_id
Solution 5:[5]
You've been Rick-Roll'd
create table #teams
(team_id integer not null,
team_name varchar(30) not null,
unique(team_id)
);
insert #teams (
team_id
, team_name
)
values
( 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)
);
insert #matches (
match_id
, host_team
, guest_team
, host_goals
, guest_goals
)
values
( 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 );
with
cte_1 as (
select match_id
, host_team as team_id
, host_goals as score
, case
when host_goals > guest_goals then 3
when host_goals = guest_goals then 1
else 0
end as points
from #matches
union all
select match_id
, guest_team as team_id
, guest_goals as score
, case
when guest_goals > host_goals then 3
when host_goals = guest_goals then 1
else 0
end as points
from #matches)
, cte_2 as (
select
team_id,
sum(points) as points
from cte_1
group by cte_1.team_id
)
select *
from #teams t
left outer join cte_2 p on t.team_id = p.team_id
order by p.points desc
drop table #matches
drop table #teams```
Solution 6:[6]
- Normalize the
matchesdata from "1 row has 2 columns" to "2 rows have 1 column".- More precisely: "1 composite attribute" instead of "1 column", as we're treating the
( Host_team, Host_goals )tuple as a single composite attribute. - This is done using a
UNION. This could also be done by anUNPIVOTif your RDBMS supports it.
- More precisely: "1 composite attribute" instead of "1 column", as we're treating the
- Compute scores based on who won the match.
- Aggregate that data by team.
- Finally,
JOINwithteamsdata at the very end, usingCOALESCEto provide a score of zero for teams that haven't played any games.
Here's step 1:
SELECT
match_id,
'host' AS side,
host_team AS team_id,
host_goals AS goals,
CASE WHEN host_goals > guest_goals THEN 1 WHEN host_goals = guest_goals THEN 0 ELSE -1 END AS victory
FROM
matches
UNION ALL
SELECT
match_id,
'guest' AS side,
guest_team AS team_id,
guest_goals AS goals,
CASE WHEN guest_goals > host_goals THEN 1 WHEN guest_goals = host_goals THEN 0 ELSE -1 END AS victory
FROM
matches
Here's step 2:
SELECT
match_id,
side,
sq.team_id,
CASE victory WHEN 1 THEN ( 3 ) WHEN 0 THEN ( 1 ) ELSE ( 0 ) END AS points
FROM
(
SELECT
match_id,
'host' AS side,
host_team AS team_id,
host_goals AS goals,
CASE WHEN host_goals > guest_goals THEN 1 WHEN host_goals = guest_goals THEN 0 ELSE -1 END AS victory
FROM
matches
UNION ALL
SELECT
match_id,
'guest' AS side,
guest_team AS team_id,
guest_goals AS goals,
CASE WHEN guest_goals > host_goals THEN 1 WHEN guest_goals = host_goals THEN 0 ELSE -1 END AS victory
FROM
matches
) AS sq
Here's step 3:
SELECT
sq.team_id,
SUM( CASE victory WHEN 1 THEN ( 3 ) WHEN 0 THEN ( 1 ) ELSE ( 0 ) END ) AS sumPoints
FROM
(
SELECT
match_id,
'host' AS side,
host_team AS team_id,
host_goals AS goals,
CASE WHEN host_goals > guest_goals THEN 1 WHEN host_goals = guest_goals THEN 0 ELSE -1 END AS victory
FROM
matches
UNION ALL
SELECT
match_id,
'guest' AS side,
guest_team AS team_id,
guest_goals AS goals,
CASE WHEN guest_goals > host_goals THEN 1 WHEN guest_goals = host_goals THEN 0 ELSE -1 END AS victory
FROM
matches
) AS sq
GROUP BY
sq.team_id
And step 4:
SELECT
teams.team_id,
team_name,
COALESCE( num_points, 0 ) AS num_points
FROM
teams
LEFT OUTER JOIN
(
SELECT
sq.team_id,
SUM( CASE victory WHEN 1 THEN ( 3 ) WHEN 0 THEN ( 1 ) ELSE ( 0 ) END ) AS num_points
FROM
(
SELECT
match_id,
'host' AS side,
host_team AS team_id,
host_goals AS goals,
CASE WHEN host_goals > guest_goals THEN 1 WHEN host_goals = guest_goals THEN 0 ELSE -1 END AS victory
FROM
matches
UNION ALL
SELECT
match_id,
'guest' AS side,
guest_team AS team_id,
guest_goals AS goals,
CASE WHEN guest_goals > host_goals THEN 1 WHEN guest_goals = host_goals THEN 0 ELSE -1 END AS victory
FROM
matches
) AS sq
GROUP BY
sq.team_id
) AS agg ON teams.team_id = agg.team_id
ORDER BY
num_points DESC
Step 5:
With the full query structure, we can simplify it by moving common expressions like victory to outer queries to reduce syntactical repetition:
SELECT
teams.team_id,
team_name,
COALESCE( num_points, 0 ) AS num_points
FROM
teams
LEFT OUTER JOIN
(
SELECT
sq.team_id,
SUM(
CASE
WHEN goals > rival_goals THEN 3
WHEN goals = rival_goals THEN 1 ELSE 0
END
) AS num_points
FROM
(
SELECT
match_id,
'host' AS side,
host_team AS team_id,
host_goals AS goals,
guest_goals AS rival_goals
FROM
matches
UNION ALL
SELECT
match_id,
'guest' AS side,
guest_team AS team_id,
guest_goals AS goals,
host_goals AS rival_goals
FROM
matches
) AS sq
GROUP BY
sq.team_id
) AS agg ON teams.team_id = agg.team_id
ORDER BY
num_points DESC
The outer query can be combiend with the agg inner-query - though it does make the query harder to follow - and I'm not a fan of adding additional GROUP BY columns just to avoid an outer-query. Also we can remove the columns added to make it easier to understand the intermediate data (like side and match_id):
SELECT
sq.team_id,
teams.team_name,
COALESCE(
SUM(
CASE
WHEN goals > rival_goals THEN 3
WHEN goals = rival_goals THEN 1 ELSE 0
END
),
0
) AS num_points
FROM
teams
LEFT OUTER JOIN
(
SELECT
host_team AS team_id,
host_goals AS goals,
guest_goals AS rival_goals
FROM
matches
UNION ALL
SELECT
guest_team AS team_id,
guest_goals AS goals,
host_goals AS rival_goals
FROM
matches
) AS sq ON teams.team_id = sq.team_id
GROUP BY
sq.team_id,
teams.team_name -- This additional GROUP BY column is necessary to avoid having to use an outer-query
ORDER BY
num_points DESC
Solution 7:[7]
You can try the query below:
SELECT
T.team_id,
T.Team_name,
IFNULL( h_points, 0 ) + IFNULL( g_points, 0 ) AS 'Total Points'
FROM
teams T
LEFT JOIN
--- first sub-query calculating host_team points
(
SELECT
host_team,
SUM(
CASE
WHEN Host_goals > Guest_goals THEN 3
WHEN Host_goals = Guest_goals THEN 1
WHEN Host_goals < Guest_goals THEN 0
END
) AS h_points
FROM
matches
GROUP BY
host_team
) H ON T.Team_id=H.host_team
LEFT JOIN
--- second sub-query calculating guest_team points
(
SELECT
guest_team,
SUM(
CASE
WHEN Host_goals < Guest_goals THEN 3
WHEN Host_goals = Guest_goals THEN 1
WHEN Host_goals > Guest_goals THEN 0
END
) AS g_points
FROM
matches
GROUP BY
guest_team
) G ON T.Team_id=G.guest_team
ORDER BY
IFNULL( h_points, 0 ) + IFNULL( g_points, 0 ) DESC, t.team_id ASC;;
I took the approach of calculating host and guest points separately then being in a LEFT JOIN from teams table. On the outer query, I perform this formula IFNULL( h_points, 0 ) + IFNULL( g_points, 0 ) to add up the host+guest teams points. The IFNULL is to replace any NULL value with 0 and the same formula I use for the ORDER BY ... DESCENDING
Solution 8:[8]
Here is a relatively simple solution using common table expression.
Step 1 - Create a CTE that identifies a winner and calculate winning team score, in second part it identifies losing team and losing score. Only reason to do this as we have to give each team 1 point in case of draw. If you run the table within the CTE it gives a temp result set like following:
match_id, host_team, guest_team, winning team, winner_score, losing_team, losing score
+---+----+----+----+---+----+---+
| 1 | 30 | 20 | 30 | 3 | 20 | 0 |
| 2 | 10 | 20 | 20 | 3 | 10 | 0 |
| 3 | 20 | 50 | 20 | 1 | 50 | 1 |
| 4 | 10 | 30 | 10 | 3 | 30 | 0 |
| 5 | 30 | 50 | 50 | 3 | 30 | 0 |
+---+----+----+----+---+----+---+
Step 2: CTE helps to organize this data into in different rows.. first query the winning team id, and winner score, and then union with losing team id and losing score.
+----+---+
| 50 | 3 |
| 20 | 3 |
| 50 | 1 |
| 30 | 3 |
| 10 | 3 |
| 20 | 1 |
+----+---+
Step 3: Fetch the outcome from second CTE, join with team table and group by on Team_id, and Team_name. Since there is one team that didn't play any game it score was coming up as null and to avoid sorting issue (and perfectly match with the expected results) used COALESCE function. Results looks like following:
+----+-------+---+
| 20 | Never | 4 |
| 50 | Gonna | 4 |
| 10 | Give | 3 |
| 30 | You | 3 |
| 40 | Up | 0 |
+----+-------+---+
with score as
(
SELECT match_id, host_team, guest_team,
cast (
case
when host_goals > guest_goals then host_team
when host_goals = guest_goals then host_team
else guest_team
end as integer
) as winner,
cast (
case
when host_goals > guest_goals then 3
when host_goals = guest_goals then 1
else 3
end as integer
) as winner_score,
cast (
case
when host_goals < guest_goals then host_team
when host_goals = guest_goals then guest_team
else guest_team
end as integer
) as loser,
cast (
case
when host_goals < guest_goals then 0
when host_goals = guest_goals then 1
else 0
end as integer
) as loser_score
from matches
),
flat_table as(
select winner as team_id, winner_score as final_score from score
union
select loser as team_id, loser_score as final_score from score where loser_score > 0
)
select t.team_id, t.team_name,
sum(COALESCE(f.final_score, 0)) as total_score
from teams t
left join flat_table f on t.team_id = f.team_id
group by t.team_id, t.team_name
order by total_score desc, team_id asc
Solution 9:[9]
Here is my solution in sqlite:
step 1: Get the points for any given team (i.e. team_id = 40).
select ifnull(sum(CASE
WHEN
(host_team = 40 and host_goals > guest_goals) or (guest_team = 40 and guest_goals > host_goals)
THEN 3
WHEN host_goals = guest_goals
THEN 1
ELSE 0
END), 0) as points
from matches
where host_team = 40 or guest_team = 40;
step 2: use the previous query to calculate the scores of all teams
select team_id, team_name, (
select ifnull(sum(CASE
WHEN
(host_team = team.team_id and host_goals > guest_goals) or (guest_team = team.team_id and guest_goals > host_goals)
THEN 3
WHEN host_goals = guest_goals
THEN 1
ELSE 0
END), 0) as points
from matches
where host_team = team.team_id or guest_team = team.team_id
) as points
from teams team
order by points desc, team_id asc;
Solution 10:[10]
My SQL query is as follows:
WITH points AS (
SELECT host_name, SUM(num_points) AS num_points
FROM(
SELECT host_name,
SUM(CASE
WHEN host_goals>guest_goals THEN 3
WHEN host_goals=guest_goals THEN 1
ELSE 0
END) AS num_points
FROM matches
GROUP BY host_name
UNION
SELECT guest_name,
SUM(CASE
WHEN guest_goals>host_goals THEN 3
WHEN host_goals=guest_goals THEN 1
ELSE 0
END) AS num_points
FROM matches
GROUP BY guest_name)
GROUP BY host_name
)
SELECT t.team_id, t.team_name,
CASE
WHEN p.num_points IS NOT null THEN p.num_points
ELSE 0
END AS num_points
FROM teams t FULL JOIN points p
ON t.team_id=p.host_name
ORDER BY p.num_points DESC
Solution 11:[11]
IN SQL SERVER
SELECT team_id,team_name,CASE WHEN host_goals>guest_goals
THEN 3
WHEN host_goals=guest_goals
THEN 1
ELSE 0 END as Points INTO #X
FROM teams
LEFT join matches ON host_team = team_id
UNION ALL
SELECT team_id,team_name,CASE WHEN guest_goals>host_goals
THEN 3
WHEN guest_goals=host_goals
THEN 1
WHEN host_goals>guest_goals
THEN 0
ELSE 0 END as Points from teams
LEFT join matches on guest_team = team_id;
SELECT team_id,team_name,SUM(Points) AS num_points FROM #X
GROUP BY team_id,team_name
ORDER BY SUM(Points) DESC,team_id ASC
Solution 12:[12]
MySQL:
select teams.team_id,
teams.team_name,
ifnull(sum(team_points.points), 0) num_points
from teams
left join (
select host_matches.host_team team_id,
case
when host_matches.host_goals>host_matches.guest_goals then 3
when host_matches.host_goals = host_matches.guest_goals then 1
else 0 end points
from matches host_matches
union all
select guest_matches.guest_team team_id,
case
when guest_matches.host_goals<guest_matches.guest_goals then 3
when guest_matches.host_goals = guest_matches.guest_goals then 1
else 0 end
from matches guest_matches
) team_points on team_points.team_id = teams.team_id
group by teams.team_id, teams.team_name
order by num_points desc, team_id asc;
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow