new.sql (1058B)
1 .headers off 2 .mode list 3 4 /* awkward and repetitive bc my sql is bad */ 5 select 'New cards this round:'; 6 7 with card as ( 8 select round,player,card1 as card from deck 9 union select round,player,card2 as card from deck 10 union select round,player,card3 as card from deck), 11 12 new as ( 13 select min(card.round) as rnd, group_name = 'final' as isfinal, card 14 from match join card on match.round = card.round and match.player = card.player 15 group by 2,3 order by 1,2) 16 17 select card from new where rnd = (select max(rnd) from new) and not isfinal; 18 select ''; 19 select 'Cards that made finals for the first time:'; 20 with card as ( 21 select round,player,card1 as card from deck 22 union select round,player,card2 as card from deck 23 union select round,player,card3 as card from deck), 24 25 new as ( 26 select min(card.round) as rnd, group_name = 'final' as isfinal, card 27 from match join card on match.round = card.round and match.player = card.player 28 group by 2,3 order by 1,2) 29 select card from new where rnd = (select max(rnd) from new) and isfinal; 30 select 'Data at https://data.3cardmagic.org'