Points table from match results
SQL data engineering interview problem. Difficulty: intermediate. Pattern: Aggregation. About 15 minutes. Part of the Pro drill bank.
A league table shows, for every team, how many matches it played and how many it won, lost or drew, with points. In matches each row is one match with two teams (team1 and team2) and a winner. A NULL winner means the match was a draw. Return 1 row for every team with team, played, won, lost, drawn and points, where a win gives 3 points, a draw gives 1 and a loss gives 0. Sort by points from the highest, then by team.
Requirements
- Count every match for both teams that took part.
- A team wins when winner is its name.
- A match is a draw when winner is NULL, and it counts for both teams.
- A match is a loss for a team when someone else is the winner.
- Points are 3 for each win and 1 for each draw.
- Sort by points descending, then team.
Constraints
- The table is matches with the columns match_id, team1, team2 and winner.
- Every team appears in at least one match.
- Return only these 6 columns, in this order: team, played, won, lost, drawn, points.
Examples
Input: matches match_id | team1 | team2 | winner 1 | Ajax | Bears | Ajax 2 | Cobras | Dingo | NULL 3 | Ajax | Cobras | Cobras 4 | Bears | Dingo | Bears 5 | Ajax | Dingo | NULL 6 | Bears | Cobras | Bears 7 | Dingo | Ajax | Ajax Output: team | played | won | lost | drawn | points Ajax | 4 | 2 | 1 | 1 | 7 Bears | 3 | 2 | 1 | 0 | 6 Cobras | 3 | 1 | 1 | 1 | 4 Dingo | 4 | 0 | 2 | 2 | 2 Why this passes: Ajax and Bears each won two of their matches; Dingo and Cobras only collect draws and the odd win. Every match is counted for both of its teams.
Input: matches match_id | team1 | team2 | winner 1 | Red | Blue | Red Output: team | played | won | lost | drawn | points Red | 1 | 1 | 0 | 0 | 3 Blue | 1 | 0 | 1 | 0 | 0 Why this passes: Two teams, one match, one winner.
Topics: lakebench, sql, union all, conditional aggregation.
More SQL interview questions · All interview problems · Learn data engineering
Points table from match results
Interview-style drill: Build a league table (played, won, lost, drawn, points) from 1 row per match.
`matches` has 1 row per match with `team1`, `team2` and the `winner`. A NULL `winner` means the match was drawn. Return a standings row for every team with: - `played`, `won`, `lost`, `drawn` - `points`: 3 for a win, 1 for a draw, 0 for a loss Columns: `team`, `played`, `won`, `lost`, `drawn`, `points`. Order by `points` descending, then `team`.