coolSQLQueries.txt

SELECT games.id, r.identifier, games.start_datetime, wt.location as WinningTeam, lt.location as LosingTeam, wtp.position as WinningTeamPosition, ltp.position as LosingTeamPosition, CAST(ltp.position AS SIGNED) - CAST(wtp.position AS SIGNED) AS UpsetMargin FROM games INNER JOIN rounds r ON games.round = r.id INNER JOIN seasons s ON s.id = r.season INNER JOIN teams wt ON games.winning_team = wt.id INNER JOIN teams lt ON games.losing_team = lt.id INNER JOIN ladder_entries wtp ON wtp.team = games.winning_team INNER JOIN rounds wr ON wr.id = wtp.round INNER JOIN seasons ws ON ws.id = wr.season INNER JOIN ladder_entries ltp ON ltp.team = games.losing_team INNER JOIN rounds lr ON lr.id = ltp.round INNER JOIN seasons ls ON ls.id = lr.season WHERE s.id = ws.id AND ws.id = ls.id AND s.year = 2020 AND wtp.position > ltp.position ORDER BY games.start_datetime ASC;