1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
| /* best-per-round */
/* .headers on */
/* .mode list */
/* .separator "\t" */
with firsts as (
select card,min(round) as first_round from card
group by 1
),
summary as (select deck.round,deck.player,
card1,
card2,
card3,
f1.card is not null as card1new,
f2.card is not null as card2new,
f3.card is not null as card3new,
min(match.group_name) as prelim_group,
sum(case when match.group_name != 'final' then match.score else 0 end) as prelim_score,
sum(case when match.group_name = 'final' then match.score else null end) as final_score
from deck join match
on deck.round = match.round and deck.player = match.player
left join firsts f1
on f1.card = deck.card1
and f1.first_round = deck.round
left join firsts f2
on f2.card = deck.card2
and f2.first_round = deck.round
left join firsts f3
on f3.card = deck.card3
and f3.first_round = deck.round
group by 1,2 order by 1 desc, prelim_group,2)
select * from summary order by round desc,final_score desc,prelim_score desc,prelim_group,player;
|
