smogon stats

turn https://smogon.com/stats/ chaos json files into SQLite databases

you can find pre-generated dbs at https://pyrope.net/mon/stats (currently only 2025-02), and you can fiddle with them in your browser or on your own computer.

examples

smogon-stats gen9ou-1500.json -o gen9ou-1500.sqlite
sqlite3 gen9ou-1500.sqlite -markdown "SELECT name, format('%.2f%%', usage * 100) as usage FROM mon WHERE mon.usage > 0.04 ORDER BY mon.usage DESC LIMIT 10"

output:

you can also use SQLite's .expert to find indexes that can dramatically speed up queries, but the numbers are small enough that it likely won't matter except for exploratory stuff—even 100^3 is only 1_000_000.

you can use SQLite's .excel to open the result of the next query in a spreadsheet application, so here are some fun graphs with the commands that generated the data. the smogon data is from wget https://smogon.com/stats/2025-02/chaos/gen9ou-{0,1500,1695}.json:

(apologies for the surely terrible SQL—the point of this is that you can do your own queries :P)

usage by elo

attach 'gen9ou-0.sqlite' as ou0; attach 'gen9ou-1500.sqlite' as ou1500; attach 'gen9ou-1695.sqlite' as ou1695;

SELECT ou0.mon.name, ou0.mon.usage as usage0, ou1500.mon.usage as usage1500, ou1695.mon.usage as usage1695
FROM ou0.mon
JOIN ou1500.mon on ou0.mon.name = ou1500.mon.name
JOIN ou1695.mon on ou0.mon.name = ou1695.mon.name
WHERE ou0.mon.usage > 0.03
ORDER BY ou0.mon.usage DESC;

top 50 moves (1695)

SELECT m.name, mon.usage * m.usage AS usage_adj
FROM move m
JOIN mon ON mon.name = m.mon
GROUP BY usage_adj
ORDER BY usage_adj
DESC limit 50;

"win" and "fail" heuristics (1695)

see examples/win-fail.sql for source and explanation (i could see this heuristic being improved in the future!).