Task description
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-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
combined.team_id AS team_id, teams.team_name, SUM(points) AS num_points
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
UNION ALL
SELECT team_id, 0 FROM teams
) AS combined
LEFT JOIN teams ON combined.team_id = teams.team_id
GROUP BY combined.team_id, teams.team_name
ORDER BY num_points DESC, combined.team_id
The solution obtained perfect score.
function result: +----+-------+---+ | 20 | Never | 4 | | 50 | Gonna | 4 | | 10 | Give | 3 | | 30 | You | 3 | | 40 | Up | 0 | +----+-------+---+
all teams have different score at the end, also no need to reorder anything
-- Implement your solution here
SELECT match_id, host_team, guest_team, host_goals, guest_goals, CASE WHEN host_goals > 1 THEN 69
FROM matches
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);
syntax error at or near "FROM" LINE 3: FROM matches ^
syntax error at or near "FROM" LINE 3: FROM matches ^
-- Implement your solution here
SELECT match_id, host_team, guest_team, host_goals, guest_goals, CASE WHEN host_goals > 1 THEN 69 END AS host_points
FROM matches
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);
example test
row 1: got ('1', '30', '20', '1', '0', 'NULL'), expected ('20', 'Never', '4')
function result: +---+----+----+---+---+------+ | 1 | 30 | 20 | 1 | 0 | NULL | | 2 | 10 | 20 | 1 | 2 | NULL | | 3 | 20 | 50 | 2 | 2 | 69 | | 4 | 10 | 30 | 1 | 0 | NULL | | 5 | 30 | 50 | 0 | 1 | NULL | +---+----+----+---+---+------+
function result: +---+----+----+---+---+------+ | 1 | 30 | 20 | 1 | 0 | NULL | | 2 | 10 | 20 | 1 | 2 | NULL | | 3 | 20 | 50 | 2 | 2 | 69 | | 4 | 10 | 30 | 1 | 0 | NULL | | 5 | 30 | 50 | 0 | 1 | NULL | +---+----+----+---+---+------+
-- Implement your solution here
SELECT match_id, host_team, guest_team, host_goals, guest_goals, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS host_points
FROM matches
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);
example test
row 1: got ('1', '30', '20', '1', '0', '3'), expected ('20', 'Never', '4')
function result: +---+----+----+---+---+---+ | 1 | 30 | 20 | 1 | 0 | 3 | | 2 | 10 | 20 | 1 | 2 | 0 | | 3 | 20 | 50 | 2 | 2 | 1 | | 4 | 10 | 30 | 1 | 0 | 3 | | 5 | 30 | 50 | 0 | 1 | 0 | +---+----+----+---+---+---+
function result: +---+----+----+---+---+---+ | 1 | 30 | 20 | 1 | 0 | 3 | | 2 | 10 | 20 | 1 | 2 | 0 | | 3 | 20 | 50 | 2 | 2 | 1 | | 4 | 10 | 30 | 1 | 0 | 3 | | 5 | 30 | 50 | 0 | 1 | 0 | +---+----+----+---+---+---+
-- Implement your solution here
SELECT match_id, host_team, guest_team, host_goals, guest_goals, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS host_points, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS guest_points
FROM matches
-- Implement your solution here
SELECT match_id, host_team, guest_team, host_goals, guest_goals, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS host_points, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS guest_points
FROM matches
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);
example test
row 1: got ('1', '30', '20', '1', '0', '3', '0'), expected ('20', 'Never', '4')
function result: +---+----+----+---+---+---+---+ | 1 | 30 | 20 | 1 | 0 | 3 | 0 | | 2 | 10 | 20 | 1 | 2 | 0 | 3 | | 3 | 20 | 50 | 2 | 2 | 1 | 1 | | 4 | 10 | 30 | 1 | 0 | 3 | 0 | | 5 | 30 | 50 | 0 | 1 | 0 | 3 | +---+----+----+---+---+---+---+
function result: +---+----+----+---+---+---+---+ | 1 | 30 | 20 | 1 | 0 | 3 | 0 | | 2 | 10 | 20 | 1 | 2 | 0 | 3 | | 3 | 20 | 50 | 2 | 2 | 1 | 1 | | 4 | 10 | 30 | 1 | 0 | 3 | 0 | | 5 | 30 | 50 | 0 | 1 | 0 | 3 | +---+----+----+---+---+---+---+
-- Implement your solution here
SELECT match_id, host_team, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS host_points, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS guest_points
FROM matches
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);
example test
row 1: got ('1', '30', '3', '0'), expected ('20', 'Never', '4')
function result: +---+----+---+---+ | 1 | 30 | 3 | 0 | | 2 | 10 | 0 | 3 | | 3 | 20 | 1 | 1 | | 4 | 10 | 3 | 0 | | 5 | 30 | 0 | 3 | +---+----+---+---+
function result: +---+----+---+---+ | 1 | 30 | 3 | 0 | | 2 | 10 | 0 | 3 | | 3 | 20 | 1 | 1 | | 4 | 10 | 3 | 0 | | 5 | 30 | 0 | 3 | +---+----+---+---+
-- Implement your solution here
SELECT host_team, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches
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);
function result: +----+---+ | 30 | 3 | | 10 | 0 | | 20 | 1 | | 10 | 3 | | 30 | 0 | +----+---+
function result: +----+---+ | 30 | 3 | | 10 | 0 | | 20 | 1 | | 10 | 3 | | 30 | 0 | +----+---+
-- Implement your solution here
SELECT host_team, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches
SELECT guest_team, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches
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);
syntax error at or near "SELECT" LINE 3: SELECT guest_team, CASE WHEN host_goals < guest_goals THEN 3... ^
syntax error at or near "SELECT" LINE 3: SELECT guest_team, CASE WHEN host_goals < guest_goals THEN 3... ^
-- Implement your solution here
WITH host_points
SELECT host_team, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches
SELECT guest_team, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches
-- Implement your solution here
WITH host_points AS (SELECT host_team, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches)
WITH guest_points AS (SELECT guest_team, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
-- Implement your solution here
WITH host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches)
WITH guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
-- Implement your solution here
WITH host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches)
WITH guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
-- Implement your solution here
WITH host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches)
WITH guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
MERGE host_points
-- Implement your solution here
WITH host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches)
WITH guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
MERGE host_points
USING guest_points
ON team_id
-- Implement your solution here
WITH host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches)
WITH guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
MERGE host_points
USING guest_points
ON team_id
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);
syntax error at or near "WITH guest_points" LINE 3: WITH guest_points AS (SELECT guest_team AS team_id, CASE WHE... ^
syntax error at or near "WITH guest_points" LINE 3: WITH guest_points AS (SELECT guest_team AS team_id, CASE WHE... ^
-- Implement your solution here
WITH host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches);
WITH guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches);
MERGE host_points
USING guest_points
ON team_id
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);
syntax error at or near ";" LINE 2: ...als < guest_goals THEN 0 ELSE 1 END AS points FROM matches); ^
syntax error at or near ";" LINE 2: ...als < guest_goals THEN 0 ELSE 1 END AS points FROM matches); ^
-- Implement your solution here
WITH host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches);
WITH guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches);
-- Implement your solution here
WITH host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches);
WITH guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches);
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);
syntax error at or near ";" LINE 2: ...als < guest_goals THEN 0 ELSE 1 END AS points FROM matches); ^
syntax error at or near ";" LINE 2: ...als < guest_goals THEN 0 ELSE 1 END AS points FROM matches); ^
-- Implement your solution here
WITH host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches);
WITH guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points);
-- Implement your solution here
WITH host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches);
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);
syntax error at or near ";" LINE 2: ...als < guest_goals THEN 0 ELSE 1 END AS points FROM matches); ^
syntax error at or near ";" LINE 2: ...als < guest_goals THEN 0 ELSE 1 END AS points FROM matches); ^
-- Implement your solution here
WITH host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches)
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);
syntax error at end of input LINE 4: ^
syntax error at end of input LINE 4: ^
-- Implement your solution here
WITH host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT * host_points
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);
syntax error at or near "host_points" LINE 3: SELECT * host_points ^
syntax error at or near "host_points" LINE 3: SELECT * host_points ^
-- Implement your solution here
WITH host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches)
WITH guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches);
SELECT * host_points
-- Implement your solution here
WITH host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches)
WITH guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches);
SELECT * FROM host_points
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);
syntax error at or near "WITH guest_points" LINE 3: WITH guest_points AS (SELECT guest_team AS team_id, CASE WHE... ^
syntax error at or near "WITH guest_points" LINE 3: WITH guest_points AS (SELECT guest_team AS team_id, CASE WHE... ^
-- Implement your solution here
WITH host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches)
WITH guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT * FROM host_points
-- Implement your solution here
WITH host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches)
WITH guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT * FROM host_points
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);
syntax error at or near "WITH guest_points" LINE 3: WITH guest_points AS (SELECT guest_team AS team_id, CASE WHE... ^
syntax error at or near "WITH guest_points" LINE 3: WITH guest_points AS (SELECT guest_team AS team_id, CASE WHE... ^
-- Implement your solution here
WITH host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches)
WITH guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
-- Implement your solution here
WITH host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches);
WITH guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches);
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points;
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);
syntax error at or near ";" LINE 3: ...als < guest_goals THEN 0 ELSE 1 END AS points FROM matches); ^
syntax error at or near ";" LINE 3: ...als < guest_goals THEN 0 ELSE 1 END AS points FROM matches); ^
-- Implement your solution here
WITH host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches);
WITH guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches);
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
-- Implement your solution here
WITH host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches)
WITH guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT * FROM host_points
-- Implement your solution here
WITH host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches), guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT * FROM host_points
-- Implement your solution here
WITH host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches), guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
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);
function result: +----+---+ | 30 | 3 | | 10 | 0 | | 20 | 1 | | 10 | 3 | | 30 | 0 | | 20 | 0 | | 20 | 3 | | 50 | 1 | | 30 | 0 | | 50 | 3 | +----+---+
function result: +----+---+ | 30 | 3 | | 10 | 0 | | 20 | 1 | | 10 | 3 | | 30 | 0 | | 20 | 0 | | 20 | 3 | | 50 | 1 | | 30 | 0 | | 50 | 3 | +----+---+
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches), guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches),
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches),
union_points AS (SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points)
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);
syntax error at end of input LINE 15: ^
syntax error at end of input LINE 15: ^
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
(
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
)
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
)
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
team_id, SUM(points)
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
)
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);
subquery in FROM must have an alias LINE 11: FROM ( ^ HINT: For example, FROM (SELECT ...) [AS] foo.
subquery in FROM must have an alias LINE 11: FROM ( ^ HINT: For example, FROM (SELECT ...) [AS] foo.
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
team_id, SUM(points)
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
) A
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
team_id, SUM(points)
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
) AS union
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);
syntax error at or near "union" LINE 15: ) AS union ^
syntax error at or near "union" LINE 15: ) AS union ^
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
team_id, SUM(points)
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
) AS combined
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);
column "combined.team_id" must appear in the GROUP BY clause or be used in an aggregate function LINE 10: team_id, SUM(points) ^
column "combined.team_id" must appear in the GROUP BY clause or be used in an aggregate function LINE 10: team_id, SUM(points) ^
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
team_id, SUM(points)
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
) AS combined
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
team_id, SUM(points)
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
) AS combined
GROUP_BY combined.team_id
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);
syntax error at or near "GROUP_BY" LINE 16: GROUP_BY combined.team_id ^
syntax error at or near "GROUP_BY" LINE 16: GROUP_BY combined.team_id ^
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
team_id, SUM(points)
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
) AS combined
GROUP BY combined.team_id
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);
function result: +----+---+ | 30 | 3 | | 20 | 4 | | 50 | 4 | | 10 | 3 | +----+---+
function result: +----+---+ | 30 | 3 | | 20 | 4 | | 50 | 4 | | 10 | 3 | +----+---+
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
team_id, SUM(points)
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
) AS combined
GROUP BY combined.team_id
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
team_id, SUM(points)
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
) AS combined
GROUP BY combined.team_id
JOIN
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
team_id, SUM(points)
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
) AS combined
GROUP BY combined.team_id
JOIN matches ON team_id
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);
syntax error at or near "JOIN" LINE 17: JOIN matches ON team_id ^
syntax error at or near "JOIN" LINE 17: JOIN matches ON team_id ^
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
team_id, SUM(points)
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
) AS combined
GROUP BY combined.team_id
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
team_id, SUM(points)
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
) AS combined
JOIN matches ON team_id
GROUP BY combined.team_id
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);
argument of JOIN/ON must be type boolean, not type integer LINE 16: JOIN matches ON team_id ^
argument of JOIN/ON must be type boolean, not type integer LINE 16: JOIN matches ON team_id ^
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
team_id, SUM(points)
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
) AS combined
JOIN matches ON team_id
GROUP BY combined.team_id
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
team_id, SUM(points)
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
) AS combined
JOIN matches ON combined.team_id = matches.team_id
GROUP BY combined.team_id
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);
column matches.team_id does not exist LINE 16: JOIN matches ON combined.team_id = matches.team_id ^
column matches.team_id does not exist LINE 16: JOIN matches ON combined.team_id = matches.team_id ^
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
team_id, SUM(points)
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
) AS combined
JOIN teams ON combined.team_id = matches.team_id
GROUP BY combined.team_id
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
team_id, SUM(points)
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
) AS combined
JOIN teams ON combined.team_id = teams.team_id
GROUP BY combined.team_id
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);
column reference "team_id" is ambiguous LINE 10: team_id, SUM(points) ^
column reference "team_id" is ambiguous LINE 10: team_id, SUM(points) ^
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
combined.team_id, SUM(points)
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
) AS combined
JOIN teams ON combined.team_id = teams.team_id
GROUP BY combined.team_id
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);
function result: +----+---+ | 30 | 3 | | 20 | 4 | | 50 | 4 | | 10 | 3 | +----+---+
function result: +----+---+ | 30 | 3 | | 20 | 4 | | 50 | 4 | | 10 | 3 | +----+---+
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
combined.team_id, SUM(points)
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
) AS combined
JOIN teams ON combined.team_id = teams.team_id
ORDE
GROUP BY combined.team_id
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
combined.team_id, SUM(points)
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
) AS combined
JOIN teams ON combined.team_id = teams.team_id
ORDER BY points, team_id
GROUP BY combined.team_id
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);
syntax error at or near "GROUP" LINE 18: GROUP BY combined.team_id ^
syntax error at or near "GROUP" LINE 18: GROUP BY combined.team_id ^
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
combined.team_id, SUM(points)
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
) AS combined
JOIN teams ON combined.team_id = teams.team_id
ORDER BY points, combined.team_id
GROUP BY combined.team_id
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);
syntax error at or near "GROUP" LINE 18: GROUP BY combined.team_id ^
syntax error at or near "GROUP" LINE 18: GROUP BY combined.team_id ^
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
combined.team_id, SUM(points)
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
) AS combined
JOIN teams ON combined.team_id = teams.team_id
GROUP BY combined.team_id
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
combined.team_id, SUM(points)
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
) AS combined
JOIN teams ON combined.team_id = teams.team_id
GROUP BY combined.team_id
ORDER BY points, combined.team_id
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);
column "combined.points" must appear in the GROUP BY clause or be used in an aggregate function LINE 18: ORDER BY points, combined.team_id ^
column "combined.points" must appear in the GROUP BY clause or be used in an aggregate function LINE 18: ORDER BY points, combined.team_id ^
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
combined.team_id, SUM(points) AS num_points
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
) AS combined
JOIN teams ON combined.team_id = teams.team_id
GROUP BY combined.team_id
ORDER BY points, combined.team_id
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
combined.team_id AS team_id, SUM(points) AS num_points
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
) AS combined
JOIN teams ON combined.team_id = teams.team_id
ORDER BY points, combined.team_id
GROUP BY combined.team_id
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
combined.team_id AS team_id, SUM(points) AS num_points
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
) AS combined
JOIN teams ON combined.team_id = teams.team_id
ORDER BY num_points, combined.team_id
GROUP BY combined.team_id
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);
syntax error at or near "GROUP" LINE 18: GROUP BY combined.team_id ^
syntax error at or near "GROUP" LINE 18: GROUP BY combined.team_id ^
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
combined.team_id AS team_id, SUM(points) AS num_points
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
) AS combined
JOIN teams ON combined.team_id = teams.team_id
ORDER BY num_points, combined.team_id
GROUP BY combined.team_id, nu
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
combined.team_id AS team_id, SUM(points) AS num_points
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
) AS combined
JOIN teams ON combined.team_id = teams.team_id
GROUP BY combined.team_id
ORDER BY num_points, combined.team_id
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);
function result: +----+---+ | 10 | 3 | | 30 | 3 | | 20 | 4 | | 50 | 4 | +----+---+
function result: +----+---+ | 10 | 3 | | 30 | 3 | | 20 | 4 | | 50 | 4 | +----+---+
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
combined.team_id AS team_id, SUM(points) AS num_points
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
) AS combined
JOIN teams ON combined.team_id = teams.team_id
GROUP BY combined.team_id
ORDER BY num_points, combined.team_id DESC
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);
function result: +----+---+ | 30 | 3 | | 10 | 3 | | 50 | 4 | | 20 | 4 | +----+---+
function result: +----+---+ | 30 | 3 | | 10 | 3 | | 50 | 4 | | 20 | 4 | +----+---+
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
combined.team_id AS team_id, SUM(points) AS num_points
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
) AS combined
JOIN teams ON combined.team_id = teams.team_id
GROUP BY combined.team_id
ORDER BY num_points, combined.team_id
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
combined.team_id AS team_id, SUM(points) AS num_points
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
) AS combined
JOIN teams ON combined.team_id = teams.team_id
GROUP BY combined.team_id
ORDER BY num_points DESC, combined.team_id
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);
function result: +----+---+ | 20 | 4 | | 50 | 4 | | 10 | 3 | | 30 | 3 | +----+---+
function result: +----+---+ | 20 | 4 | | 50 | 4 | | 10 | 3 | | 30 | 3 | +----+---+
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
combined.team_id AS team_id, SUM(points) AS num_points
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
) AS combined
LEFT JOIN teams ON combined.team_id = teams.team_id
GROUP BY combined.team_id
ORDER BY num_points DESC, combined.team_id
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);
function result: +----+---+ | 20 | 4 | | 50 | 4 | | 10 | 3 | | 30 | 3 | +----+---+
function result: +----+---+ | 20 | 4 | | 50 | 4 | | 10 | 3 | | 30 | 3 | +----+---+
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
combined.team_id AS team_id, SUM(points) AS num_points
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
) AS combined
JOIN teams ON combined.team_id = teams.team_id
GROUP BY combined.team_id
ORDER BY num_points DESC, combined.team_id
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
combined.team_id AS team_id, teams.team SUM(points) AS num_points
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
) AS combined
JOIN teams ON combined.team_id = teams.team_id
GROUP BY combined.team_id
ORDER BY num_points DESC, combined.team_id
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
combined.team_id AS team_id, teams.team_name, SUM(points) AS num_points
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
) AS combined
JOIN teams ON combined.team_id = teams.team_id
GROUP BY combined.team_id, teams.team_name
ORDER BY num_points DESC, combined.team_id
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);
function result: +----+-------+---+ | 20 | Never | 4 | | 50 | Gonna | 4 | | 10 | Give | 3 | | 30 | You | 3 | +----+-------+---+
function result: +----+-------+---+ | 20 | Never | 4 | | 50 | Gonna | 4 | | 10 | Give | 3 | | 30 | You | 3 | +----+-------+---+
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
combined.team_id AS team_id, teams.team_name, SUM(points) AS num_points
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
) AS combined
LEFTJOIN teams ON combined.team_id = teams.team_id
GROUP BY combined.team_id, teams.team_name
ORDER BY num_points DESC, combined.team_id
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
combined.team_id AS team_id, teams.team_name, SUM(points) AS num_points
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
) AS combined
LEFT JOIN teams ON combined.team_id = teams.team_id
GROUP BY combined.team_id, teams.team_name
ORDER BY num_points DESC, combined.team_id
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);
function result: +----+-------+---+ | 20 | Never | 4 | | 50 | Gonna | 4 | | 10 | Give | 3 | | 30 | You | 3 | +----+-------+---+
function result: +----+-------+---+ | 20 | Never | 4 | | 50 | Gonna | 4 | | 10 | Give | 3 | | 30 | You | 3 | +----+-------+---+
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
combined.team_id AS team_id, teams.team_name, COALESCE(l.hoursSpent, 0) as hoursSpent
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
) AS combined
LEFT JOIN teams ON combined.team_id = teams.team_id
GROUP BY combined.team_id, teams.team_name
ORDER BY num_points DESC, combined.team_id
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
combined.team_id AS team_id, teams.team_name, COALESCE(SUM(points), 0) AS num_points
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
) AS combined
LEFT JOIN teams ON combined.team_id = teams.team_id
GROUP BY combined.team_id, teams.team_name
ORDER BY num_points DESC, combined.team_id
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);
function result: +----+-------+---+ | 20 | Never | 4 | | 50 | Gonna | 4 | | 10 | Give | 3 | | 30 | You | 3 | +----+-------+---+
function result: +----+-------+---+ | 20 | Never | 4 | | 50 | Gonna | 4 | | 10 | Give | 3 | | 30 | You | 3 | +----+-------+---+
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
combined.team_id AS team_id, teams.team_name, SUM(points) AS num_points
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
) AS combined
LEFT JOIN teams ON combined.team_id = teams.team_id
GROUP BY combined.team_id, teams.team_name
ORDER BY num_points DESC, combined.team_id
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
combined.team_id AS team_id, teams.team_name, SUM(points) AS num_points
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
UNION ALL
SELECT team_id, 0 FROM teams
) AS combined
LEFT JOIN teams ON combined.team_id = teams.team_id
GROUP BY combined.team_id, teams.team_name
ORDER BY num_points DESC, combined.team_id
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);
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 | +----+-------+---+
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
combined.team_id AS team_id, teams.team_name, SUM(points) AS num_points
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
UNION ALL
SELECT team_id, 0 FROM
) AS combined
LEFT JOIN teams ON combined.team_id = teams.team_id
GROUP BY combined.team_id, teams.team_name
ORDER BY num_points DESC, combined.team_id
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
combined.team_id AS team_id, teams.team_name, SUM(points) AS num_points
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
UNION ALL
SELECT team_id, 0 FROM teams
) AS combined
LEFT JOIN teams ON combined.team_id = teams.team_id
GROUP BY combined.team_id, teams.team_name
ORDER BY num_points DESC, combined.team_id
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);
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 | +----+-------+---+
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
combined.team_id AS team_id, teams.team_name, SUM(points) AS num_points
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
UNION ALL
SELECT team_id, 0 FROM teams
) AS combined
LEFT JOIN teams ON combined.team_id = teams.team_id
GROUP BY combined.team_id, teams.team_name
ORDER BY num_points DESC, combined.team_id
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);
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 | +----+-------+---+
-- Implement your solution here
WITH
host_points AS (SELECT host_team AS team_id, CASE WHEN host_goals > guest_goals THEN 3 WHEN host_goals < guest_goals THEN 0 ELSE 1 END AS points FROM matches),
guest_points AS (SELECT guest_team AS team_id, CASE WHEN host_goals < guest_goals THEN 3 WHEN host_goals > guest_goals THEN 0 ELSE 1 END AS points FROM matches)
SELECT
combined.team_id AS team_id, teams.team_name, SUM(points) AS num_points
FROM (
SELECT * FROM host_points
UNION ALL
SELECT * FROM guest_points
UNION ALL
SELECT team_id, 0 FROM teams
) AS combined
LEFT JOIN teams ON combined.team_id = teams.team_id
GROUP BY combined.team_id, teams.team_name
ORDER BY num_points DESC, combined.team_id
The solution obtained perfect score.
function result: +----+-------+---+ | 20 | Never | 4 | | 50 | Gonna | 4 | | 10 | Give | 3 | | 30 | You | 3 | | 40 | Up | 0 | +----+-------+---+
all teams have different score at the end, also no need to reorder anything