CODEWITHSUNDEEP
sqlamazon-redshift
Brad Davis
Brad Davis

Reputation: 1170

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

Upvotes: 0

Views: 21

Answers (1)

JohnHC
JohnHC

Reputation: 11195

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'

Upvotes: 2

Related Questions