Recently I came across a SQL interview question, the “SQL Soccer Matches” question, have you seen this already?
This is the kind of question you can find in an interview exam. Anyway, in order to answer that I developed many approaches to solve it: using union, using many ctes and union, using case sum, and using unpivot crossjoin.
Wanna see all those solving ideas? Check it on my DataCamp workbook: https://app.datacamp.com/workspace/w/21c61079-0787-42ae-b21d-ae7ff127d556
This is the question:
The question:
Compute the total number of points each team has scored after all the matches. The rules are as follows:
a. if a team wins a match (scores more goals than the other team) it gets 3 points.
b. if a team draws a match (scores exactly same number of goals as other team) it gets one point.
c. if a team loses a match (scores fewer goals than other team) it gets no points.
Write an SQL query that selects the team_id, team_name and num_points of each team in the tournament.
Return the result table ordered by num_points in decreasing order.
In case of a tie, order the records by team_id.Solutions:
https://app.datacamp.com/workspace/w/21c61079-0787-42ae-b21d-ae7ff127d556
Pedro Carvalho 