git.alexw.nyc home about git garden
    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
   35
   36
   37
   38
   39
/* best-per-round */
/* .headers on */
/* .mode list */
/* .separator "\t" */

with card as (
select round,player,card1 as card from deck
union select round,player,card2 as card from deck
union select round,player,card3 as card from deck),

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;