SQL data engineering interview problem. Difficulty: intermediate. Pattern: Aggregation. About 15 minutes. Part of the Pro drill bank.
matches has one 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.
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
Interview-style drill: Build a league table (played, won, lost, drawn, points) from one row per match.
`matches` has one 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`.