Create SQL view using min/max of column

Question

I have a table that stores a list of Game IDs, the User IDs of users that played that game, and the Score that each user achieved in the game, like below:

GameID     UserID     Score
1          Bob        12
1          Sally      14
2          Bob        17
2          Jane       17
3          Sally      16
3          Jane       10
3          Trish      10

I'm trying to write a SQL query to create a view of this data that would tell me how many games each user has won, drawn, and lost based on the score. So the view would look like below:

UserId     NumWins     NumLosses     NumDraws
Bob        0           1             1
Sally      2           0             0
Jane       0           1             1
Trish      0           1             0

I'm trying to come up with the syntax to create a view that transforms the data this way, but am having trouble.

Answer 1

You can use RANK() window function and conditional aggregation:

with
  ranks as (
    select *, rank() over (partition by gameid order by score desc) rnk
  from tablename
  ),
  cte as (
    select *, count(*) over (partition by gameid, rnk) counter
    from ranks
  )
select userid,
  sum(case when rnk = 1 and counter = 1 then 1 else 0 end) NumWins,          
  sum(case when rnk > 1 then 1 else 0 end) NumLosses,
  sum(case when rnk = 1 and counter > 1 then 1 else 0 end) NumDraws
from cte  
group by userid

See the demo.
Results:

> userid | NumWins | NumLosses | NumDraws
> :----- | ------: | --------: | -------:
> Bob    |       0 |         1 |        1
> Jane   |       0 |         1 |        1
> Sally  |       2 |         0 |        0
> Trish  |       0 |         1 |        0