commit 443e32850ac2a51a0e8f22558970df011a739eba
parent c4d0d0e62277de23b36edf51553d66e8c253322e
Author: alex wennerberg <alex@alexwennerberg.com>
Date: Sun, 23 Jun 2024 17:19:51 -0400
cleanup
Diffstat:
3 files changed, 11 insertions(+), 15 deletions(-)
diff --git a/getdata.py b/getdata.py
@@ -32,7 +32,6 @@ def get_round_fileids():
sheetre = re.compile("spreadsheets/d/(.*?)/")
curr_round = requests.get("https://sites.google.com/view/3cb-metashape/pairings-results")
ids = sheetre.findall(allrounds.text + curr_round.text)
- ids.append("1aG0aDWAvV0DRPywsbFl_agOZ5-Zv4ULk0oXxH5E2Gr4") # hack
return ids
def file_to_wb(fileid):
diff --git a/sql/topdecks.sql b/sql/topdecks.sql
@@ -1,17 +1,13 @@
-with deck as (select round, player, group_concat(name, ';') as deck
-from (
- select round,player,card.name from card
- left join ban on card.name = ban.name
- where ban.name is null
- order by round,player,card.name
-)
-group by 1,2
-having count(name) == 3
-)
-select deck.deck, cast(avg(score) * 100 as int) / 100.0 as avgscore, count(1) from
+select deck.card1, deck.card2, deck.card3, cast(avg(score) * 100 as int) / 100.0 as avgscore, count(1) from
deck
join match on deck.round = match.round and deck.player = match.player
-join deck d2 on deck.round = d2.round and match.opp_player = d2.player where d2.player is not null
+join deck d2 on deck.round = d2.round and match.opp_player = d2.player
+left join ban on deck.card1 = ban.name
+left join ban b2 on deck.card2 = b2.name
+left join ban b3 on deck.card3 = b3.name
+where d2.player is not null
+and (ban.name is null and b2.name is null and b3.name is null)
group by 1
-having count(1) > 10
-order by 2 desc;
+having count(1) > 30
+order by avgscore desc
+limit 5;
diff --git a/templates/index.html b/templates/index.html
@@ -16,6 +16,7 @@ tr:hover {background-color: #ddd;}
<body>
<h1>3 Card Blind Data Analysis</h1>
Full, cleaned data for <a href="//3cardmagic.org">3 card blind metashape</a>. Source code can be found <a href="//git.alexw.nyc/3cardblind">here</a>. <a href="export.tsv">Download</a> an export as tsv-formatted data.<br>
+ Maintained by aw on the 3cb discord. Tag him for questions/comments/feature requests.<br>
<b>Bold</b> represents that that round is the first time this card was played.
<p>Click on a header to sort by that value.</p>
<table class="sortable">
