3cb-data

commit 50aeb140d9ecbe11114c7bf24e675ffe9a510467
parent 0d07de9ba73335ee4ce1726d7fbb4f68bad1d99d
Author: alex wennerberg <alex@alexwennerberg.com>
Date:   Sun,  4 Feb 2024 00:02:41 -0500

blahdeblah

Diffstat:
M
getdata.py
 | 
4
----
M
sql/export.sql
 | 
37
+++++++++++++++++++++++++++++--------

2 files changed, 29 insertions(+), 12 deletions(-)
diff --git a/getdata.py b/getdata.py
@@ -182,13 +182,9 @@ def clean_card(card_name):
         return best_guess(clean)
     return clean
 
-def export_round():
-    print("hi")
-
 if __name__ == "__main__":
     for table in schema:
         con.execute(table)
     update_bans()
     main()
-    export_round()
 
diff --git a/sql/export.sql b/sql/export.sql
@@ -1,10 +1,31 @@
+/* best-per-round */
 .headers on
-/* new card, best performance of this card */
-select match.round,group_name,match.player,
-  card1,
-  card2,
-  card3,
-  sum(score) as score
-from match join deck
+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),
+
+ranked_cards as (
+select 
+  card.card, 
+  card.round, 
+  card.player,
+  /* adjust by 100 for finals */
+  sum(case when match.group_name = 'final' then match.score * 100 else match.score end) as score
+  from card
+  join match on card.round = match.round and card.player = match.player
+group by 1),
+
+top_scores as (
+select card, round, player, max(score) from ranked_cards group by 1)
+
+select deck.round,deck.player,
+card1,
+card2,
+card3,
+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
-group by 1,2,3
+group by 1,2;
+