Brad Davis - 1 year ago 89
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

``````select A1.match, A1.team as winner, A2.team as loser