Jerry - 2 years ago 79
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
``````

A few simple aggregate functions paired with a `FULL JOIN` should do the trick. Note that there could be performance issues with the `COALESCE`s. 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.

``````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)
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download