1
2
3
4
5
6
7
8
9
10
11
12
13
| select deck.card1, deck.card2, deck.card3, 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
left join ban on deck.card1 = ban.name
left join ban b2 on deck.card2 = b2.name
left join ban b3 on deck.card3 = b3.name
where d2.player is not null
and (ban.name is null and b2.name is null and b3.name is null)
group by 1
having count(1) > 30
order by avgscore desc
limit 5;
|