Brad Davis Brad Davis - 1 month ago 7
SQL Question

Perform conditional calculations using data distributed over multiple records

I am trying to calculate win rates for players using different 'nations'. The raw data I get is on a per player per game basis, so in a 1v1 game I will get two entries in the database. One will show the 'win' for one team, and the other entry will record the 'loss' for the opposing team. The issue is that there are multiple different 'nations' and I want to be able to calculate the nation vs nation win rate, as opposed to the overall generalized win rate per team, if that makes sense. e.g.

Looking at the example below, I want to be able to calculate the rate at which dogs beat cats, cats beat mice, and mice beat dogs.

Here is a simplified toy model of the data I'm working with

date match sessionid team nation result
1/1/2016 1 143138354 0 cats loss
1/1/2016 1 143146203 1 dogs win
1/1/2016 2 143134711 0 mice win
1/1/2016 2 143165199 1 dogs loss
1/1/2016 3 143183402 0 cats win
1/1/2016 3 143127251 1 mice loss
1/1/2016 4 143192433 0 cats win
1/1/2016 4 143129777 1 dogs loss
1/1/2016 5 143197254 0 mice win
1/1/2016 5 143147178 1 dogs loss
1/1/2016 6 143220297 0 cats loss
1/1/2016 6 143168454 1 mice win
1/1/2016 7 143169544 0 cats win
1/1/2016 7 143188824 0 cats win
1/1/2016 7 143178786 1 mice loss
1/1/2016 7 143212127 1 dogs loss


I've considered something like

SELECT
match,
CASE WHEN nation='cats' AND result='loss' AND nation='dogs' AND result='win' THEN 'dogs_over_cats' END as result
FROM
table
GROUP BY
match


But of course that doesn't work because nation can't be simultaneously 'cats' and 'dogs' at the same time.

What I want is something like this

date, match, winning_nation, losing_nation

or alternatively

date, match, result

where result would be a string indicating who beat who ('dogs_over_cats') or something.

I have no idea how to do this. It seems like it should be pretty simple but I can't figure out how to do it. How do I get a CASE statement to consider the field values over multiple records at the same time.. Is that possible? Do I just have to use lag/lead functions?

Thanks
Brad

Answer

You can trasform it like this:

select A1.match, A1.team as winner, A2.team as loser
from tableA A1
inner join tableA A2
on A1.match = A2.match

where A1.result = 'win'
and A2.result = 'loss'