Tasks Details
medium
1.
SqlWorldCup
Given a list of matches in a group stage of the soccer World Cup, compute the number of points each team currently has.
Task Score
100%
Correctness
100%
Performance
Not assessed
You are given two tables, teams and matches, with the following structures:
create table teams ( team_id integer not null, team_name varchar(30) not null, unique(team_id) ); 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) );Each record in the table teams represents a single soccer team. Each record in the table matches represents a finished match between two teams. Teams (host_team, guest_team) are represented by their IDs in the teams table (team_id). No team plays a match against itself. You know the result of each match (that is, the number of goals scored by each team).
You would like to compute the total number of points each team has scored after all the matches described in the table. The scoring rules are as follows:
- If a team wins a match (scores strictly more goals than the other team), it receives three points.
- If a team draws a match (scores exactly the same number of goals as the opponent), it receives one point.
- If a team loses a match (scores fewer goals than the opponent), it receives no points.
Write an SQL query that returns a ranking of all teams (team_id) described in the table teams. For each team you should provide its name and the number of points it received after all described matches (num_points). The table should be ordered by num_points (in decreasing order). In case of a tie, order the rows by team_id (in increasing order).
For example, for:
teams: team_id | team_name ---------+--------------- 10 | Give 20 | Never 30 | You 40 | Up 50 | Gonna matches: 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 | 1your query should return:
team_id | team_name | num_points ---------+-----------+------------ 20 | Never | 4 50 | Gonna | 4 10 | Give | 3 30 | You | 3 40 | Up | 0
Copyright 2009–2024 by Codility Limited. All Rights Reserved. Unauthorized copying, publication or disclosure prohibited.
Solution
Programming language used SQL (PostgreSQL)
Time spent on task 19 minutes
Notes
not defined yet
Task timeline
Code: 13:47:26 UTC,
sql-postgres,
autosave
Code: 13:49:10 UTC,
sql-postgres,
autosave
with tie1 as (select F.host_team, sum(F.cnt) as num
from (select A.host_team, 1 as cnt
from (SELECT match_id, host_team, guest_team, host_goals, guest_goals,case
when host_goals = guest_goals then 1
else 3
end as score,
case
when host_goals > guest_goals then host_team
when host_goals < guest_goals then guest_team
else NULL
end as winner
from matches ) A
where winner is null) F -- 경기결과가 동점인 경우, host_team에 더 해야할 점수
group by F.host_team),
tie2 as (select F.guest_team, sum(F.cnt) as num
from (select A.guest_team, 1 as cnt
from (SELECT match_id, host_team, guest_team, host_goals, guest_goals,case
when host_goals = guest_goals then 1
else 3
end as score,
case
when host_goals > guest_goals then host_team
when host_goals < guest_goals then guest_team
else NULL
end as winner
from matches ) A
where winner is null) F
group by F.guest_team), -- 경기결과가 동점인 경우, guest_team에 더 해야할 점수
win as (select A.winner, sum(A.score) as num
from (SELECT match_id, host_team, guest_team, host_goals, guest_goals,case
when host_goals = guest_goals then 1
else 3
end as score,
case
when host_goals > guest_goals then host_team
when host_goals < guest_goals then guest_team
else NULL
end as winner
from matches) A
group by A.winner) -- 이긴 팀이 있는 경우, 각 팀에 더해야할 점수
-- teams, win, tie1, tie2 join
select R.team_id, R.team_name, R.sumv1 + R.sumv2 + R.sumv3 as num_points
from (select team_id, team_name,
case when win.num is null then 0 else win.num end as sumv1,
case when tie1.num is null then 0 else tie1.num end as sumv2,
case when tie2.num is null then 0 else tie2.num end as sumv3
from teams
left outer join win on teams.team_id=win.winner
left outer join tie1 on teams.team_id=tie1.host_team
left outer join tie2 on teams.team_id=tie2.guest_team) R
order by num_points desc, R.team_id
Code: 13:49:29 UTC,
sql-postgres,
autosave
Code: 13:49:50 UTC,
sql-postgres,
autosave
SELECT t.team_id, t.team_name, COALESCE(sum(
CASE
WHEN t.team_id = m.host_team THEN m.home_Score
WHEN t.team_id = m.guest_team THEN m.away_Score
END
), 0) AS num_points FROM teams t
LEFT JOIN (
SELECT
host_team AS host_team,
(CASE
WHEN host_goals > guest_goals THEN 3
WHEN host_goals = guest_goals THEN 1
ELSE 0
END) AS home_Score,
guest_team AS guest_team,
(CASE
WHEN host_goals > guest_goals THEN 0
WHEN host_goals = guest_goals THEN 1
ELSE 3
END) AS away_Score
FROM matches
) m
ON t.team_id = m.host_team OR t.team_id = m.guest_team
GROUP BY t.team_id, t.team_name
ORDER BY num_points DESC, t.team_id
Code: 13:53:29 UTC,
sql-postgres,
autosave
SELECT
t.team_id as team_id,
t.team_name as team_name,
COALESCE(sum(
CASE
WHEN
t.team_id = m.host_team THEN m.home_score
WHEN
t.team_id = m.guest_team THEN m.away_score
END
), 0) AS num_points
FROM teams t
LEFT JOIN (
SELECT
host_team AS host_team,
(CASE
WHEN
host_goals > guest_goals THEN 3
WHEN
host_goals = guest_goals THEN 1
ELSE 0
END) AS home_score,
guest_team AS guest_team,
(CASE
WHEN
host_goals > guest_goals THEN 0
WHEN
host_goals = guest_goals THEN 1
ELSE 3
END) AS away_score
FROM matches
) m
ON
t.team_id = m.host_team
OR
t.team_id = m.guest_team
GROUP BY
t.team_id, t.team_name
ORDER BY
num_points DESC, t.team_id
Code: 13:53:29 UTC,
sql-postgres,
verify,
result: Passed
SELECT
t.team_id as team_id,
t.team_name as team_name,
COALESCE(sum(
CASE
WHEN
t.team_id = m.host_team THEN m.home_score
WHEN
t.team_id = m.guest_team THEN m.away_score
END
), 0) AS num_points
FROM teams t
LEFT JOIN (
SELECT
host_team AS host_team,
(CASE
WHEN
host_goals > guest_goals THEN 3
WHEN
host_goals = guest_goals THEN 1
ELSE 0
END) AS home_score,
guest_team AS guest_team,
(CASE
WHEN
host_goals > guest_goals THEN 0
WHEN
host_goals = guest_goals THEN 1
ELSE 3
END) AS away_score
FROM matches
) m
ON
t.team_id = m.host_team
OR
t.team_id = m.guest_team
GROUP BY
t.team_id, t.team_name
ORDER BY
num_points DESC, t.team_id
User test case 1:
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);
Analysis
expand all
Example tests
1.
0.064 s
OK
function result: +----+-------+---+ | 20 | Never | 4 | | 50 | Gonna | 4 | | 10 | Give | 3 | | 30 | You | 3 | | 40 | Up | 0 | +----+-------+---+
function result: +----+-------+---+ | 20 | Never | 4 | | 50 | Gonna | 4 | | 10 | Give | 3 | | 30 | You | 3 | | 40 | Up | 0 | +----+-------+---+
expand all
User tests
1.
0.064 s
OK
function result: +----+-------+---+ | 20 | Never | 4 | | 50 | Gonna | 4 | | 10 | Give | 3 | | 30 | You | 3 | | 40 | Up | 0 | +----+-------+---+
function result: +----+-------+---+ | 20 | Never | 4 | | 50 | Gonna | 4 | | 10 | Give | 3 | | 30 | You | 3 | | 40 | Up | 0 | +----+-------+---+
Code: 14:04:41 UTC,
sql-postgres,
autosave
SELECT
팀.team_id as team_id,
팀.team_name as team_name,
COALESCE(sum(
CASE
WHEN
팀.team_id = 경기.host_team THEN 경기.home_score
WHEN
팀.team_id = 경기.guest_team THEN 경기.away_score
END
), 0) AS num_points -- null 대신 0 쓰기
FROM teams AS 팀
LEFT JOIN (
SELECT
host_team,
(CASE
WHEN
host_goals > guest_goals THEN 3 -- 한 팀이 경기에서 이기면(상대 팀보다 훨씬 더 많은 골을 넣음) 3점을 받습니다.
WHEN
host_goals = guest_goals THEN 1 -- 한 팀이 경기에서 무승부를 기록하면(상대 팀과 정확히 같은 수의 골을 넣은 경우) 1점을 받습니다.
ELSE 0 -- 팀이 경기에서 패배하면(상대 팀보다 득점이 적음) 점수를 받지 못합니다.
END) AS home_score,
guest_team,
(CASE
WHEN
host_goals > guest_goals THEN 0
WHEN
host_goals = guest_goals THEN 1
ELSE 3
END) AS away_score
FROM matches
) AS 경기
ON
팀.team_id = 경기.host_team
OR
팀.team_id = 경기.guest_team
GROUP BY
팀.team_id, 경기.team_name
ORDER BY
num_points DESC, 경기.team_id
Code: 14:04:42 UTC,
sql-postgres,
verify,
result: Failed
SELECT
팀.team_id as team_id,
팀.team_name as team_name,
COALESCE(sum(
CASE
WHEN
팀.team_id = 경기.host_team THEN 경기.home_score
WHEN
팀.team_id = 경기.guest_team THEN 경기.away_score
END
), 0) AS num_points -- null 대신 0 쓰기
FROM teams AS 팀
LEFT JOIN (
SELECT
host_team,
(CASE
WHEN
host_goals > guest_goals THEN 3 -- 한 팀이 경기에서 이기면(상대 팀보다 훨씬 더 많은 골을 넣음) 3점을 받습니다.
WHEN
host_goals = guest_goals THEN 1 -- 한 팀이 경기에서 무승부를 기록하면(상대 팀과 정확히 같은 수의 골을 넣은 경우) 1점을 받습니다.
ELSE 0 -- 팀이 경기에서 패배하면(상대 팀보다 득점이 적음) 점수를 받지 못합니다.
END) AS home_score,
guest_team,
(CASE
WHEN
host_goals > guest_goals THEN 0
WHEN
host_goals = guest_goals THEN 1
ELSE 3
END) AS away_score
FROM matches
) AS 경기
ON
팀.team_id = 경기.host_team
OR
팀.team_id = 경기.guest_team
GROUP BY
팀.team_id, 경기.team_name
ORDER BY
num_points DESC, 경기.team_id
User test case 1:
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);
Analysis
expand all
Example tests
1.
0.064 s
RUNTIME ERROR,
tested program terminated with exit code 1
stderr:
column ??.team_id does not exist
LINE 41: num_points DESC, ??.team_id
^
expand all
User tests
1.
0.060 s
RUNTIME ERROR,
tested program terminated with exit code 1
stderr:
column ??.team_id does not exist
LINE 41: num_points DESC, ??.team_id
^
Code: 14:05:36 UTC,
sql-postgres,
autosave
SELECT
t.team_id as team_id,
t.team_name as team_name,
COALESCE(sum(
CASE
WHEN
t.team_id = m.host_team THEN m.home_score
WHEN
t.team_id = m.guest_team THEN m.away_score
END
), 0) AS num_points -- null 대신 0 쓰기
FROM teams AS t
LEFT JOIN (
SELECT
host_team,
(CASE
WHEN
host_goals > guest_goals THEN 3 -- 한 팀이 경기에서 이기면(상대 팀보다 훨씬 더 많은 골을 넣음) 3점을 받습니다.
WHEN
host_goals = guest_goals THEN 1 -- 한 팀이 경기에서 무승부를 기록하면(상대 팀과 정확히 같은 수의 골을 넣은 경우) 1점을 받습니다.
ELSE 0 -- 팀이 경기에서 패배하면(상대 팀보다 득점이 적음) 점수를 받지 못합니다.
END) AS home_score,
guest_team,
(CASE
WHEN
host_goals > guest_goals THEN 0
WHEN
host_goals = guest_goals THEN 1
ELSE 3
END) AS away_score
FROM matches
) m
ON
t.team_id = m.host_team
OR
t.team_id = m.guest_team
GROUP BY
t.team_id, t.team_name
ORDER BY
num_points DESC, t.team_id
Code: 14:05:37 UTC,
sql-postgres,
verify,
result: Passed
SELECT
t.team_id as team_id,
t.team_name as team_name,
COALESCE(sum(
CASE
WHEN
t.team_id = m.host_team THEN m.home_score
WHEN
t.team_id = m.guest_team THEN m.away_score
END
), 0) AS num_points -- null 대신 0 쓰기
FROM teams AS t
LEFT JOIN (
SELECT
host_team,
(CASE
WHEN
host_goals > guest_goals THEN 3 -- 한 팀이 경기에서 이기면(상대 팀보다 훨씬 더 많은 골을 넣음) 3점을 받습니다.
WHEN
host_goals = guest_goals THEN 1 -- 한 팀이 경기에서 무승부를 기록하면(상대 팀과 정확히 같은 수의 골을 넣은 경우) 1점을 받습니다.
ELSE 0 -- 팀이 경기에서 패배하면(상대 팀보다 득점이 적음) 점수를 받지 못합니다.
END) AS home_score,
guest_team,
(CASE
WHEN
host_goals > guest_goals THEN 0
WHEN
host_goals = guest_goals THEN 1
ELSE 3
END) AS away_score
FROM matches
) m
ON
t.team_id = m.host_team
OR
t.team_id = m.guest_team
GROUP BY
t.team_id, t.team_name
ORDER BY
num_points DESC, t.team_id
User test case 1:
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);
Analysis
expand all
Example tests
1.
0.060 s
OK
function result: +----+-------+---+ | 20 | Never | 4 | | 50 | Gonna | 4 | | 10 | Give | 3 | | 30 | You | 3 | | 40 | Up | 0 | +----+-------+---+
function result: +----+-------+---+ | 20 | Never | 4 | | 50 | Gonna | 4 | | 10 | Give | 3 | | 30 | You | 3 | | 40 | Up | 0 | +----+-------+---+
expand all
User tests
1.
0.060 s
OK
function result: +----+-------+---+ | 20 | Never | 4 | | 50 | Gonna | 4 | | 10 | Give | 3 | | 30 | You | 3 | | 40 | Up | 0 | +----+-------+---+
function result: +----+-------+---+ | 20 | Never | 4 | | 50 | Gonna | 4 | | 10 | Give | 3 | | 30 | You | 3 | | 40 | Up | 0 | +----+-------+---+
Code: 14:05:52 UTC,
sql-postgres,
final,
score:
100
SELECT
t.team_id as team_id,
t.team_name as team_name,
COALESCE(sum(
CASE
WHEN
t.team_id = m.host_team THEN m.home_score
WHEN
t.team_id = m.guest_team THEN m.away_score
END
), 0) AS num_points -- null 대신 0 쓰기
FROM teams AS t
LEFT JOIN (
SELECT
host_team,
(CASE
WHEN
host_goals > guest_goals THEN 3 -- 한 팀이 경기에서 이기면(상대 팀보다 훨씬 더 많은 골을 넣음) 3점을 받습니다.
WHEN
host_goals = guest_goals THEN 1 -- 한 팀이 경기에서 무승부를 기록하면(상대 팀과 정확히 같은 수의 골을 넣은 경우) 1점을 받습니다.
ELSE 0 -- 팀이 경기에서 패배하면(상대 팀보다 득점이 적음) 점수를 받지 못합니다.
END) AS home_score,
guest_team,
(CASE
WHEN
host_goals > guest_goals THEN 0
WHEN
host_goals = guest_goals THEN 1
ELSE 3
END) AS away_score
FROM matches
) m
ON
t.team_id = m.host_team
OR
t.team_id = m.guest_team
GROUP BY
t.team_id, t.team_name
ORDER BY
num_points DESC, t.team_idAnalysis summary
The solution obtained perfect score.
Analysis
expand all
Example tests
1.
0.060 s
OK
function result: +----+-------+---+ | 20 | Never | 4 | | 50 | Gonna | 4 | | 10 | Give | 3 | | 30 | You | 3 | | 40 | Up | 0 | +----+-------+---+
function result: +----+-------+---+ | 20 | Never | 4 | | 50 | Gonna | 4 | | 10 | Give | 3 | | 30 | You | 3 | | 40 | Up | 0 | +----+-------+---+
expand all
Correctness tests
1.
0.064 s
OK
2.
0.064 s
OK
1.
0.064 s
OK
2.
0.060 s
OK
1.
0.064 s
OK
1.
0.064 s
OK
1.
0.064 s
OK
simple_distinct_scores
all teams have different score at the end, also no need to reorder anything
all teams have different score at the end, also no need to reorder anything
✔
OK
1.
0.060 s
OK
1.
0.064 s
OK
1.
0.060 s
OK
1.
0.060 s
OK
1.
0.060 s
OK
1.
0.064 s
OK