Interview Question Bank · Self-join · strict comparison
6. Upsets: the lower-rated player won
MediumGamingQuestion #150
Concept: Self-join · strict comparisonshow
When each event is stored as one row per participant, a self-join puts two participants side by side: join the table to itself on the shared id, and give each copy a role with a condition (here, one copy is the winner and the other the loser). Spelling out both roles also stops each pair from appearing twice, once in each direction. Then compare the two copies' columns directly.
Example (different data):
SELECT a.game_id, a.team AS home, b.team AS away FROM game_teams a JOIN game_teams b ON b.game_id = a.game_id AND b.side = 'away' WHERE a.side = 'home' AND a.payroll < b.payroll;
Your task
Skyrift Arena's esports team wants a highlight reel of upsets: 1v1 matches won by the player with the lower skill rating going in.
Return match_id, winner, winner_rating, loser, loser_rating (each player's rating_before) and rating_gap (loser_rating - winner_rating) for every match won by the lower-rated player. Equal ratings aren't upsets, and drawn matches have no winner. Sort by rating_gap descending, then match_id.
Starting Postgres in your browser…
Checking your access…