Jerry Jerry - 6 months ago 19
SQL Question

sql - get team's win rate against all other teams

I want to be able to calculate the win percentage of each of my players against a single enemy player (and return the win percentages). So say the enemy player is '104'. I want to find out the win percentage of my player '23' vs '104', '48' vs '104', etc. and how many games they are played in total (to make the win percentage)
I have been trying to figure out how to make a query in PostgreSQL to do the following:

input:

myTeamID = 26691960, enemyPlayerID = 104


table:

Matchups
winner_team_id | winner_player_id | loser_team_id | loser_player_id
----------------+------------------+---------------+-----------------
26691960 | 24 | 45631137 | 104
26691960 | 23 | 45334612 | 104
26691960 | 48 | 22191174 | 104
26691960 | 23 | 31191882 | 104
26691960 | 14 | 20731636 | 104
26691960 | 14 | 23648001 | 104
26691960 | 14 | 35009401 | 104
26691960 | 23 | 28954626 | 104
28809466 | 104 | 26691960 | 23
70012915 | 104 | 26691960 | 24
...


(this table only shows games where enemy player ids are 104 and all the players on team id = 26691960)

output:

player_id | win_rate | games_played
----------------------------------
23 75% 4
14 100% 3
48 100% 1
24 50% 4

Answer

A few simple aggregate functions paired with a FULL JOIN should do the trick. Note that there could be performance issues with the COALESCEs. you could wrap the COALESCE(win_count, 0) + COALESCE(loss_count, 0) in a subquery to get games_played and then use that in the win_rate equation.

Anyway, without further ado:

SELECT COALESCE(wins.loser_player_id, losses.winner_player_id) as player_id,
(COALESCE(loss_count, 0) / (COALESCE(win_count, 0) + COALESCE(loss_count, 0)))::numeric(3, 2) as win_rate,
(COALESCE(win_count, 0) + COALESCE(loss_count, 0))::bigint as games_played

FROM (
    SELECT winner_player_id, loser_player_id, count(*) * 1::numeric(3,2) as win_count --this is a hack to get a decimal
    FROM a.scores win
    WHERE winner_player_id = 104
    GROUP BY winner_player_id, loser_player_id
)
as wins

FULL JOIN
(
    SELECT winner_player_id, loser_player_id, count(*) * 1::numeric(3,2) as loss_count
    FROM a.scores win
    WHERE loser_player_id = 104
    GROUP BY winner_player_id, loser_player_id
) as losses ON row(wins.winner_player_id, wins.loser_player_id) = row(losses.loser_player_id, losses.winner_player_id)