Drill 6 of 12 in Gaming questions
0

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…