3cb-data

commit 28e27087d95c4c0e55e67cf1a71699866946faf8
parent 0491cf045cd6338687a463a39ffe4120839d349a
Author: alex wennerberg <alex@alexwennerberg.com>
Date:   Fri, 24 Nov 2023 17:41:03 -0500

update sql etc

Diffstat:
M
getdata.py
 | 
2
++
M
sql/export.sql
 | 
1
+
A
sql/new.sql
 | 
18
++++++++++++++++++

3 files changed, 21 insertions(+), 0 deletions(-)
diff --git a/getdata.py b/getdata.py
@@ -2,6 +2,7 @@
 # all rounds available at https://sites.google.com/view/3cb-metashape/pairings-results/past-results?authuser=0
 # depends on requests and stdlib
 # writes to 3cmdata.csv
+# TODO upload the csv, print out the round msg
 
 import requests, os, csv, re, io, sqlite3, titlecase, string, openpyxl
 from Levenshtein import distance
@@ -139,6 +140,7 @@ def best_guess(card):
 
 def replacement(card):
     m = {
+            "Gavel of the Righteous, for I Am Stubborn and Naively Hopeful": "Gavel of the Righteous",
             "Annex Chan": "Chancellor of the Annex",
             "Annex-Chan": "Chancellor of the Annex",
             "Forge-Chan": "Chancellor of the Forge",
diff --git a/sql/export.sql b/sql/export.sql
@@ -1,3 +1,4 @@
+.headers on
 select match.round,group_name,match.player,deck,sum(score) as score
 from match
 join (select round, player, group_concat(name, ';') as deck
diff --git a/sql/new.sql b/sql/new.sql
@@ -0,0 +1,18 @@
+/* awkward and repetitive bc my sql is bad */
+
+select "New cards this round:";
+with new as (
+select min(card.round) as rnd, group_name = 'final' as isfinal, name
+from match join card on match.round = card.round and match.player = card.player
+group by 2,3 order by 1,2)
+
+select name from new where rnd = (select max(rnd) from new) and not isfinal;
+select "";
+select "Cards that made finals for the first time:";
+with new as (
+select min(card.round) as rnd, group_name = 'final' as isfinal, name
+from match join card on match.round = card.round and match.player = card.player
+group by 2,3 order by 1,2)
+select name from new where rnd = (select max(rnd) from new) and isfinal;
+
+