SQL 3 way standings tie logic

Question

I am writing a program using postgresql to show standings for a video game tournament that play in round robin pools of 5. So there is a possibility that there is a 3 way tie somewhere. I found logic to determine head to head winners: Here and this was super helpful but it does not work for a 3 way tie. I modified the schema slightly so here is what im using:

[poolteam]
-team 
-wins
 [versus]
 -team1
 -team2
 -win 

The versus table just tells us who won the match between the two teams. Then I get the rank for the teams by passing it into a new table

select team, wins, RANK() OVER (ORDER BY wins desc) AS rank 
    INTO rankTable 
from poolteam;

Then using the answer from the link I can get the ranks and the head to head tie breakers. I wrote this to find if there are more than 2 teams with the same rank

select rank 
from finalStandings 
group BY rank having count(*) >2

The logic first looks at most wins, then head to head, then if more than 2 tied teams the head to head of all the teams tied, heres an example:

Team A 3-1
Team B 2-2
Team C 2-2
Team D 2-2
Team E 1-3

Where Team A beats B,C,E, Team B beats C&D, Team C beats E&D, Team D beats E&A, team E beats B.

In this case the three tied teams (B,C,D) have 2 wins but since B beat both tied teams, they would take 2nd, C would take 3rd cause they win the head to head win D.

In the case all three teams beat each other the tiebreaker looks at the games won verses the tied teams which is easy to calculate, its just this one scenario I need help with.

Answer 1

In the case of three tied teams with the same rank, A B and C, either:

1. One team (say A) beat both B and C, one team (say B) beat one team (C), one team (C) beat no teams OR
2. All teams beat exactly one other team and there is a cycle (e.g. A beat B, B beat C, and C beat A)

You can see this because if three teams have the same rank and played each other once then there were three games played and three wins must be given out, but no team played more than two games, so it's either 1, 1, 1 or 2, 1, 0. This means that with a table of wins and a table of teams with rank you can get the results you want by simply counting in-rank victories and ordering by those.

Assuming you've got this schema: table name named teamrank with name, wins, and losses columns and a table named matches with winner and loser columns, you can use:

Schema:

CREATE TABLE matches (winner CHAR(1), loser CHAR(1));
insert into matches (winner, loser) values('A','B');
insert into matches (winner, loser) values('A','C');
insert into matches (winner, loser) values('D','A');
insert into matches (winner, loser) values('A','E');
insert into matches (winner, loser) values('B','C');
insert into matches (winner, loser) values('B','D');
insert into matches (winner, loser) values('E','B');
insert into matches (winner, loser) values('C','D');
insert into matches (winner, loser) values('C','E');
insert into matches (winner, loser) values('D','E');
create table team (name CHAR(1));
insert into team (name)  VALUEs('A');
insert into team (name)  VALUEs('B');
insert into team (name)  VALUEs('C');
insert into team (name)  VALUEs('D');
insert into team (name)  VALUEs('E');
CREATE TABLE TEAMSTAT (NAME CHAR(1), WINS SMALLINT, LOSSES SMALLINT);
insert into teamstat SELECT T.NAME,
       coalesce(m1.wins,0) as wins,
       coalesce(m2.losses,0) as losses
from team t
LEFT JOIN(select winner, count(winner) as wins 
          from matches
          group by winner
         ) m1 ON m1.winner = t.name 
LEFT JOIN(select loser, count(loser) as losses
          from matches
          group by loser
         ) m2 ON m2.loser = t.name

Query:

select t.name, t.wins, t.losses
, sum(case when t.name = m.winner and t.wins = t2.wins then 1 else 0 end) as wins_in_rank
, sum(case when t.name = m.winner and t.wins <= t2.wins then 1 else 0 end) as wins_in_or_above_rank
from teamstat t
 left join teamstat t2 on t2.name <> t.name and t.wins <= t2.wins
 left join matches m on m.winner in (t2.name, t.name) and m.loser in(t2.name, t.name)
group by t.name, t.wins, t.losses
order by t.wins desc, "wins_in_rank" desc, "wins_in_or_above_rank" desc

Updated fiddle: http://sqlfiddle.com/#!17/585c8d/13/0