git.alexw.nyc home about git garden
    1
    2
    3
    4
    5
    6
    7
    8
    9
   10
   11
   12
   13
   14
   15
   16
   17
with deck as (select round, player, group_concat(name, ';') as deck
from (
  select round,player,card.name from card 
  left join ban on card.name = ban.name
  where ban.name is null
  order by round,player,card.name
)
group by 1,2
having count(name) == 3
) 
select deck.deck, cast(avg(score) * 100 as int) / 100.0 as avgscore, count(1) from
deck
join match on deck.round = match.round and deck.player = match.player
join deck d2 on deck.round = d2.round and match.opp_player = d2.player where d2.player is not null
group by 1
having count(1) > 10
order by 2 desc;