Programming smogon-stats: Turn smogon.com/stats JSON files into SQLite databases

[mehbark]

https://smogon.com/stats already has useful aggregate reports, and sites like Pikalytics provide a snazzy interface for most basic queries. But what about queries that aren't basic? Well, the JSON files (listed under stats/*/chaos) have all the data you need, but JSON isn't a great format to work with—this project was motivated by four second JSON load times (now under half a second thanks to a library switch, but still).

All this tool does is convert those JSON files into SQLite databases that can be queried locally (or on the browser) with the SQLite REPL or your favorite programming language's SQLite bindings.

Here's my treat, and what actually motivated me to polish this up and publish it: I've pre-generated databases for 2025-02:
https://pyrope.net/mon/stats/2025-02
Why? Because I suck at Pokémon! (The fact that I've been too busy with this project to play the past few days doesn't help :P)
I know that other people can use these databases much more effectively to generate cool graphs and stuff, and they can identify what omitted stats are worth adding (Did you know some monster ran a Scizor with 0 happiness once in February 2025? These databases can't tell you that :[).

More info in the READMEs here:
Rust crate: https://crates.io/crates/smogon-stats
Git repository: https://g.pyrope.net/smogon-stats

Example graphs:

SVOU usage for various glicko levels (it's actually a weighting or something like that). Meowscarada.

Move usage:

Overly-simplistic "win" and "fail" heuristics, explained in the repo.

PS: I did some light internet searching to avoid duplicating effort, but I wouldn't be surprised if this, or something very similar to this, had been done before. I apologize if so, and I hope this can still be of some use.

 

[mehbark]

Update: pre-generated SQLite files for the last decade of stats are now available at https://pyrope.net/mon/stats

 

[mehbark]

Stats for 2025-03 are not up at https://pyrope.net/mon/stats/2025-03. April fools'!
(I won't be announcing each month in the future; I just wanted to let y'all know that I'll do my best to keep things up to date)

edit: Here's the glicko-weighted usage graph for 2025-03:

 

[mehbark]

This is too useless to post elsewhere but too fun not to post!

I made a script to generate the most "typical" team for a metagame based on the sqlite file this generates.
That is, it

1. Starts with a provided mon (or the most used one)
2. Adds the mon that is most often paired with the Pokémon in the current team until there are six
3. Gives each mon its most common item, ability, tera type (in metas with tera), spread, and moves

Does this make complete, complementary teams? Not really
Are the individual sets good? I mean sometimes they're okay

This is not a tractable alternative to normal teambuilding practice or the strategy dex, but it is, again, fun. A potentially good challenge methinks!

Example outputs:
SVOU: https://psim.us/t/1200546
SV NDOU: https://psim.us/t/1200508
SVOU (starting with Gliscor): https://psim.us/t/1200540
SVOU (starting with Blissey): https://psim.us/t/1200550

This uses an updated version of the program that gathers spread data (pre-generated files have been updated!); compiling the program and running the script isn't too hard, but feel free to PM me if you want me to do it real quick.

 

[mehbark]

I'm here with another, perhaps bordering on actually useful, script that takes advantage of the interesting checks and counters data (henceforth abbreviated as cc) that github:smogon/usage-stats gathers. These are fun to make and play with, sue me!

You give it a database and a set of Pokémon and it'll list Pokémon that check and counter them most reliably, generally biased towards higher usage. It's easiest to demonstrate with an example. Let's say you're a big water-type fan, so you run $ ruby cc.rb --bbcode gen9ou-1695.sqlite Ogerpon-Wellspring Alomomola Araquanid Dondozo Primarina Samurott-Hisui and get the following result:

ENEMY >:[	USAGE	ALL	Ogerpon-Wellspring	Alomomola	Araquanid	Dondozo	Primarina	Samurott-Hisui
Raging Bolt	13.9%	12.9%	69.1%	70.0%	84.9%	69.5%	60.4%	74.7%
Kyurem	17.0%	8.4%	66.4%	74.6%	81.8%	55.0%	52.5%	71.8%
Iron Valiant	19.2%	6.4%	65.6%	76.3%	65.2%	65.4%	37.8%	79.2%
Dragonite	19.8%	6.4%	77.4%	71.2%	81.9%	26.6%	67.3%	78.9%
Garganacl	8.8%	4.1%	33.9%	82.4%	73.2%	65.1%	53.6%	57.9%
Hydrapple	2.9%	3.7%	67.5%	85.7%	79.1%	63.8%	15.7%	80.4%
Darkrai	13.0%	2.9%	68.1%	78.8%	51.0%	63.2%	33.3%	51.0%
Ogerpon-Wellspring	21.0%	2.6%	37.8%	77.0%	54.9%	39.1%	75.7%	55.8%
Iron Moth	11.6%	2.6%	47.6%	73.7%	50.1%	56.7%	53.2%	48.2%
Kingambit	23.4%	2.1%	66.2%	76.1%	81.7%	15.8%	65.0%	49.0%
Zamazenta	22.2%	2.0%	75.7%	65.6%	59.0%	22.5%	37.9%	79.2%
Pecharunt	11.9%	1.7%	56.3%	69.7%	54.4%	42.1%	46.6%	40.2%
Gholdengo	23.2%	1.4%	38.8%	69.8%	51.1%	67.1%	58.9%	26.2%
Dragapult	16.2%	1.0%	53.2%	63.1%	75.0%	51.9%	28.5%	28.0%
Zapdos	7.0%	1.0%	40.9%	46.9%	56.5%	41.0%	43.7%	50.1%
Hatterene	14.0%	0.9%	27.8%	72.3%	39.3%	56.0%	37.1%	54.9%
Great Tusk	33.0%	0.3%	21.8%	39.2%	38.2%	24.5%	53.4%	69.0%
Samurott-Hisui	11.5%	0.3%	34.3%	64.5%	55.8%	19.0%	23.6%	49.3%
Gliscor	12.5%	0.1%	20.4%	73.0%	35.6%	9.2%	34.6%	38.3%
Corviknight	11.8%	0.0%	27.0%	27.8%	52.4%	12.9%	19.7%	45.2%
Cinderace	12.9%	0.0%	37.3%	41.3%	26.9%	12.3%	32.5%	23.8%
Iron Treads	12.3%	0.0%	9.6%	41.2%	32.2%	18.7%	37.6%	36.4%
Landorus-Therian	15.8%	0.0%	7.2%	42.7%	28.7%	28.7%	34.3%	27.6%
Slowking-Galar	15.1%	0.0%	24.1%	60.9%	13.7%	42.1%	36.4%	6.8%
Ting-Lu	16.7%	0.0%	6.3%	56.6%	10.2%	48.1%	27.3%	24.5%
Alomomola	8.3%	0.0%	8.4%	24.4%	22.1%	20.0%	15.2%	23.3%

CC data isn't a silver bullet, but this gives a decent picture of your most common and consistent threats. You can then use the same program to find what checks and counters your checks and counters! $ ruby cc.rb --bbcode gen9ou-1695.sqlite 'Raging Bolt' Kyurem 'Iron Valiant' Dragonite

ENEMY >:[	USAGE	ALL	Raging Bolt	Kyurem	Iron Valiant	Dragonite
Excadrill	1.9%	11.3%	74.9%	50.7%	61.7%	48.0%
Weavile	4.0%	7.7%	66.5%	69.1%	33.9%	49.7%
Iron Valiant	19.2%	7.1%	45.3%	71.0%	48.5%	45.5%
Dragonite	19.8%	7.0%	58.6%	47.4%	52.3%	48.5%
Clefable	6.1%	5.8%	37.5%	50.8%	59.2%	51.8%
Iron Hands	1.8%	5.8%	52.5%	50.9%	40.8%	53.2%
Zamazenta	22.2%	5.6%	35.7%	72.4%	40.2%	54.2%
Ceruledge	5.7%	5.6%	46.6%	53.9%	56.2%	39.5%
Latios	4.5%	4.8%	54.6%	65.3%	31.6%	42.6%
Gholdengo	23.2%	4.6%	35.1%	56.7%	60.6%	38.2%
Kingambit	23.4%	4.6%	40.1%	63.3%	39.7%	45.6%
Ursaluna	2.3%	4.4%	59.4%	34.0%	43.4%	50.3%
Hatterene	14.0%	4.3%	35.9%	49.7%	52.6%	46.0%
Enamorus	5.5%	4.1%	39.4%	51.0%	57.3%	36.1%
Iron Crown	5.6%	4.0%	37.8%	64.6%	46.2%	35.2%
Manaphy	2.0%	3.8%	50.4%	43.3%	39.1%	44.7%
Hoopa-Unbound	2.9%	3.8%	60.2%	66.5%	37.3%	25.4%
Ninetales-Alola	3.8%	3.7%	51.2%	50.4%	33.0%	43.6%
Raging Bolt	13.9%	3.6%	49.0%	37.5%	49.7%	39.3%
Tinkaton	2.4%	3.6%	36.8%	52.6%	45.2%	40.6%
Iron Treads	12.3%	3.5%	72.2%	39.5%	27.3%	44.8%
Kyurem	17.0%	3.5%	61.1%	46.8%	24.3%	49.9%
Iron Moth	11.6%	3.2%	48.0%	53.6%	59.7%	21.0%
Dragapult	16.2%	3.2%	43.9%	46.4%	33.8%	46.4%
Garganacl	8.8%	2.9%	38.6%	49.7%	29.8%	50.6%
Deoxys-Speed	5.3%	2.6%	40.6%	47.2%	41.1%	32.6%
Darkrai	13.0%	2.3%	31.3%	50.9%	38.0%	38.0%
Primarina	5.2%	2.1%	33.3%	41.3%	53.2%	28.9%
Scizor	4.2%	2.0%	18.0%	49.6%	60.2%	36.8%
Walking Wake	4.3%	1.8%	36.1%	47.0%	33.5%	32.1%

Is Excadrill SVOU's ultimate answer??? Probably not? That's why I show the usage as well.