WCA Statistics Website

[macky]

Since the official WCA Statistics page is necessarily limited in scope, I'm setting up a WCA Statistics Website (thanks Lucas!). For now, I've put an All-Time World Championship Medal Table.

Goals:
* (unofficial) updated statistics on one website
* user-submitted statistics code (php and MySQL)

Regarding the first point: Although popular statistics may be incorporated into the official WCA Statistics page, some statistics are inherently unofficial, e.g. different proposed global ranking systems. The hope, moreover, is to encourage cubers, especially those without server access, to learn and experiment with php and MySQL.

To do:
* think about how best to set up user submission

If you'd like to contribute statistics code or help with the project, please contact me.

macky

[Schmidt]

It is nothing much, but could you put numbers in front of the country names??

[kinch2002]

I'd like to know what program(s) I can get hold of to run SQL queries on the data base myself. I do this sort of thing at work, but have no software at home...

[collinbxyz]

Great idea. I will try to contribute, but I don't know how much I could actually do.

Just a little error, at the bottom of the medal table, I see this:

MySQL Query

SELECT personCountryId,
SUM(IF(pos = 1, 1, 0)) AS gold,
SUM(IF(pos = 2, 1, 0)) AS silver,
SUM(IF(pos = 3, 1, 0)) AS bronze,
SUM(IF(pos <= 3, 1, 0)) AS total
FROM Results
JOIN Competitions
WHERE Results.competitionId = Competitions.id AND
(competitionId = 'WC2011' OR
competitionId = 'WC2009' OR
competitionId = 'WC2007' OR
competitionId = 'WC2005' OR
competitionId = 'WC2003' OR
competitionId = 'WC1982')
AND best >0 AND (roundId = 'f' OR roundId = 'c')
GROUP BY personCountryId
ORDER BY gold DESC, silver DESC, bronze DESC, personCountryId

[PatrickJameson]

Great idea. I will try to contribute, but I don't know how much I could actually do.

Just a little error, at the bottom of the medal table, I see this:

That is supposed to be there. This is the "code"(MySQL script) that was run against the database in order to get the table. It is there for reference.

I'd like to know what program(s) I can get hold of to run SQL queries on the data base myself. I do this sort of thing at work, but have no software at home...

You can download the WCA db here.

You'll have to do research to be able to run queries against the database on your platform.

[Lucas Garron]

Here's my long list of crazy statistics ideas from a 3 years ago. I don't plan to spend time on this, so y'all should feel free to implement any of them that you find interesting.

[oranjules]

13.37 single is impossible to obtain, so do you want the averages ?
I wanted to do that but the database is too big, i can't import it -_- (even in zip format)
EDIT : ok, found a way to do it, here is the query :

SELECT competitionId, eventId, personName, personId, personCountryId
FROM `results`
WHERE average =1337

I don't know how to make the results pretty, so here is a non-pretty version

competitionId eventId personName personId personCountryId

BelgianOpen2008 333 Milán Baticz 2005BATI01 Hungary
PoznanOpen2008 222 Szczepan Łągiewka 2007LAGI01 Poland
BarcelonaOpen2009 333 Jimmy Coll 2006COLL01 Belgium
SanDiegoOpen2009 333 Phillip Espinoza 2007ESPI01 USA
TaiwanSpringOpen2009 333 Han-Cyun Chen (陳翰群) 2008CHEN06 Taiwan
MadridOpen2009 333 Alejandro Aguado Barahona 2007AGUA01 Spain
DutchOpen2009 pyram Olivier Polspoel 2007POLS01 Belgium
Thankscubing2009 333 Chester Lian 2009LIAN03 Malaysia
SpanishChampionship2009 pyram Pau Vela García 2009GARC04 Spain
SpanishChampionship2009 pyram Jordi Piqueras Gómez 2008PIQU01 Spain
ChicagoOpen2010 333 Justin Jaffray 2008JAFF01 Canada
TaiwanWinterOpen2010 333 Hsuan Chang (張璿) 2008CHAN09 Taiwan
NorwegianOpen2010 333 Hong Tan Lam 2008LAMH01 Norway
PolishOpen2010 333 Piotr Kózka 2005KOZK01 Poland
MadridOpen2010 333 Rodrigo Septién Rodríguez 2008SEPT01 Spain
BerkeleySpring2010 333 Justin Adsuara 2007ADSU01 USA
DiscoveryScience2010 333 Deven Nadudvari 2008NADU01 USA
BerkeleySummer2010 333 Dan Dzoan 2006DZOA03 USA
BerkeleySummer2010 333 David Gomes 2006GOME01 USA
USNationals2010 333 Joey Gouly 2007GOUL01 United Kingdom
USNationals2010 333 Emily Wang 2009WANG15 Canada
PolishNationals2010 333 Michał Robaczyk 2006ROBA01 Poland
PhilippinesOpen2010 333 Richard Jay S. Apagar 2010APAG01 Philippines
Euro2010 333 Moritz Karl 2008KARL02 Germany
MunichOpen2010 222 Hendrik Hammer 2010HAMM02 Germany
NaonedOpen2010 333 Antoine Piau 2008PIAU01 France
MITFall2010 333 Patricia Li 2009LIPA01 USA
SwedishCubedays2010 333 Harald Stiff 2009STIF01 Sweden
IshikawaGokokuShintoOpen2010 333 Nobuaki Suga (菅 信昭) 2007SUGA01 Japan
SpanishChampionship2010 333 Jorge Morata Carrasco 2008MORA02 Spain

The most impressive thing is that a lot of them are well-known ! (Emily Wang, Milan Baticz, Jimmy Coll, Chester Lian, Joey Gouly...)

[Kirjava]

(Emily Wang, Milan Baticz, Jimmy Coll, Chester Lian, Joey Gouly...)

Who the hell is Joey Gouly?

[Stefan]

13.37 single is impossible to obtain

Not true... 21 people have managed that (four this year), Phillip Espinoza even twice! Edit: And I just saw him in your average list. Also, Pau Vela García got 13.37 average twice but never single. Besides those two, 61 people got one single or average 13.37.

[oranjules]

these must be 11.37 +2, i saw in an other topic (maybe the "odd wca stats") that the stackmat timer can't do this time...