18 lines
693 B
SQL
18 lines
693 B
SQL
-- this is a heuristic—and not a very good one!
|
|
-- (also terrible sql)
|
|
-- but it does give some interesting results.
|
|
-- tldr "win" is "how often do i check/counter mon X times usage(X)"
|
|
-- and "fail" is that but backwards
|
|
-- there's a LOT of sampling bias
|
|
-- but this predicts the most used mons surprisingly well
|
|
SELECT name, win, -fail, (win - fail) AS margin
|
|
FROM (
|
|
SELECT
|
|
name, usage,
|
|
(SELECT sum(percentage*opp.usage) FROM cc JOIN mon opp ON opp.name = cc.mon WHERE cc.mon = mon.name) AS win,
|
|
(SELECT sum(percentage*opp.usage) FROM cc JOIN mon opp ON opp.name = cc.mon WHERE cc.opp = mon.name) AS fail
|
|
FROM mon
|
|
)
|
|
WHERE usage > 0.01
|
|
ORDER BY margin DESC;
|