# WCA Statistics Website



## macky (Dec 22, 2011)

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 (Dec 22, 2011)

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


----------



## kinch2002 (Dec 22, 2011)

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 (Dec 22, 2011)

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,
> ...


----------



## PatrickJameson (Dec 22, 2011)

collinbxyz said:


> 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.



kinch2002 said:


> 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 (Dec 23, 2011)

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 (Dec 23, 2011)

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
> ...



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


----------



## Kirjava (Dec 23, 2011)

oranjules said:


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


 
Who the hell is Joey Gouly?


----------



## Stefan (Dec 23, 2011)

oranjules said:


> 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 (Dec 23, 2011)

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...


----------



## Stefan (Dec 23, 2011)

oranjules said:


> 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...



Unlikely:
There have been 22 single 13.37s.
There have been 11 single 11.37s.
Why would a big majority of the 11.37s get +2s?

Continuing down:
There have been 13 single 9.37s.
There have been 8 single 7.37s.
There have been 6 single 5.37s.
There have been 25 single 3.37s.
There have been 18 single 1.37s.

And for comparison:
There have been 366 single 13.36s.
There have been 358 single 13.38s.


----------



## oranjules (Dec 23, 2011)

well, i assume i'm wrong


----------



## stricgoogle (Dec 23, 2011)

@Stefan
The big majority would be because it seems like the only explanation.

Though, there could be a mistake, like judge's handwriting being hard to read (I could definitely be the judge here ), judge writting the time incorectly, even judge intentionally writing wrong, or a QJ timer.

The 13.36, and 13.38 comparison actually favors the +2 theory,
since you can't get 13.37 on a Stackmat, it isn't as common as those two are.

EDIT:
Ok, it seems the non-memory timers are responsible for most of them.


----------



## Blake4512 (Dec 23, 2011)

@Stefan 13.37 might not be an impossible time but it is really rare to get. The people that got a 13.37 single might have used the timers before the memory timer because the memory timer skips times.


----------



## Henrik (Dec 23, 2011)

I don't know what timer version is used, but this proves that 13.37 should be possible.

(took like 10 sec to search on youtube, maybe because I knew there had been a video up sometime (and still is))


----------



## cmhardw (Dec 23, 2011)

There should be a picture from Worlds 2007 where two people practicing on stage got 13.37 singles on 3x3 at the same time, and at timers fairly close to each other. I remember both solvers pausing so that audience members could take pictures (this was during warm-ups, not during any round). Sadly I don't remember who the two solvers were, I only remember the incident.


----------



## Blake4512 (Dec 23, 2011)

idk I could be wrong. We could ask some sport stackers or something and see if they think it's possible..?


----------



## Gunnar (Dec 23, 2011)

The times that seemingly can't be gotten on stackmats nowadays are possible on older timers. My timer is from 2005 and I can get 13.37, 9.99, 0.99, 1.00, 1.01 etc. 

Maybe most (if not all) 13.37 was obtained with older timers?

By some weird reason it seems as they went from a perfectly fine time chip to a worse one a few years back. I hope the new 3-digit timers are more accurate.


----------



## Kian (Dec 23, 2011)

The DNFs, Sub X and WCA Growth tabs don't open for me. Is that the case for anyone else?


----------



## Hippolyte!!! (Dec 23, 2011)

That's the case for me for the two first.


----------



## Blake4512 (Dec 24, 2011)

Gunnar said:


> I hope the new 3-digit timers are more accurate.




They don't skip times, I got one a few days ago and as far as I know, it has more accurate than the previous timer.


----------



## kinch2002 (Dec 25, 2011)

I'm afraid that my laptop won't let me install MySQL, so I can't help much with query writing. I have 2 requests for statistics though.

1. Identical to the sum of single/average ranks lists on the WCA website, but covering more than the top ten. I guess if it doesn't take too long to run it for everybody that would be good, but otherwise I'd like to see a top 100 list.

2. Sum of single/average ranks for each country (i.e. add the world rankings of the NR holders for each event). Add 1 to the last place in the rankings as usual if the country doesn't have a result in an event.

Thanks in advance!


----------



## oranjules (Dec 25, 2011)

It would be hard, because there is no datas for the NRs... It is still possible though (i will try it a bit later)


----------



## Henrik (Jan 3, 2012)

Could someone explain me how to understand the "most records" statistics.

I see that is counts all WRs for the person in a specific event, or all events, but why does Feliks not have any NRs in 3x3? since his WRs are Australian NR why does that not sum up.

What about the persons with many NRs but few or no WR/CRs ? Why does that not show. 
I show once in the "most record" page under Magic results it gives me one avg CR and one single NR. 
Is that maybe how its counted? In that WR/CR was both single and avg WR/CR? If not, what was the other? "Only" NR?

I would also like to see who set most NRs, since that is also records, and "most records" would also contain NRs I think.

(I know I have 206 NRs one of them an ER)


----------



## macky (Jan 5, 2012)

e.g. 'CR' counts continental records that are not world records. Note that counting this way or by "summing up" doesn't change the ranking.



Henrik said:


> What about the persons with many NRs but few or no WR/CRs ? Why does that not show.


That was an error. Thanks for the catch. It should be fixed now.

(Hi Henrik!)

Names with highest consonant/vowel ratio

```
Rank	Person			Citizen of	 	Ratio		Consonant	Vowel
1	Ng Tsz Chun (吳子俊)	Hong Kong	 	8.0000		8		1	 
2	KT Trask		USA		 	6.0000		6		1	 
3	J.J. Tang		China		 	5.0000		5		1	 
 	Chen Lv (吕宸)		China		 	5.0000		5		1	 
 	JD Nutt			USA		 	5.0000		5		1
```


----------



## Cielo (Jan 6, 2012)

I wanted to know the most sub 30 in FM, and I found that I should fill the blank with "0.30".


----------



## squilliams (Jan 15, 2012)

My name doesn't appear on Best Magicers without Master Magic result. I have a 1.19 magic single and still haven't solve mastermagic on official competition.


----------



## Pro94 (Jan 15, 2012)

squilliams said:


> My name doesn't appear on Best Magicers without Master Magic result. I have a 1.19 magic single and still haven't solve mastermagic on official competition.


 
Because the page loads only 100 results, also if someone (like you in this case) has the same single.


----------



## woony (Jan 19, 2012)

that's interesting.
If you are looking for some queries I can easily write, whatever result you would like. I kind of do this for a living  or atleast part of my job


----------



## Zane_C (Jan 19, 2012)

woony said:


> that's interesting.
> If you are looking for some queries I can easily write, whatever result you would like. I kind of do this for a living  or atleast part of my job


Awesome, there's a thread devoted to odd WCA stats, you might like to keep an eye on it. I'm surprised it has remained dormant for a week, normally it's quite active. Stefan is usually the first to attain requested stats, he probably wouldn't mind some help.


----------



## woony (Jan 19, 2012)

Zane_C said:


> Awesome, there's a thread devoted to odd WCA stats, you might like to keep an eye on it. I'm surprised it has remained dormant for a week, normally it's quite active. Stefan is usually the first to attain requested stats, he probably wouldn't mind some help.



k cool, I'll keep an eye on it I'll install the db tonight.


----------



## macky (Jan 19, 2012)

woony said:


> k cool, I'll keep an eye on it I'll install the db tonight.



Lucas posted some ideas. Also, it's most helpful if you could also write the php. The current code is available on GitHub.


----------



## David Zemdegs (Jan 19, 2012)

In the "WCA Growth" under "Best 3x3x3" there is an issue with the year 2009


----------



## Stefan (Jan 19, 2012)

fazdad said:


> In the "WCA Growth" under "Best 3x3x3" there is an issue with the year 2009


 
These statistics are not cumulative. Each year is on its own. That's also why PersCountries and WRs sometimes decreased.


----------



## bamilan (Jan 24, 2012)

http://stats.cubing.net/best_medal_collection.php?eventId=&regionId=&years=&order=gold

Changed order by to Total, but still ranks by gold.


----------



## Anthony (Jan 24, 2012)

bamilan said:


> http://stats.cubing.net/best_medal_collection.php?eventId=&regionId=&years=&order=gold
> 
> Changed order by to Total, but still ranks by gold.


 
Also, my stats are divided by Brooks and Searle. I don't really care all that much, but it would be nice if this was fixed. I also seem to be showing up under the last name Searle instead of Brooks in most of the statistics.


----------



## Mike Hughey (Jan 24, 2012)

It would be kind of fun to be able to order by silver or bronze (even if it's somewhat nonsensical) - I think I'd probably rank pretty high ordered by silver. (I'm good at coming in second place at 3x3x3 BLD. )


----------



## macky (Jan 25, 2012)

For future reference, you can get a GitHub account and submit bug reports here.



bamilan said:


> http://stats.cubing.net/best_medal_collection.php?eventId=&regionId=&years=&order=gold
> 
> Changed order by to Total, but still ranks by gold.



Fixed, thanks.



Anthony said:


> Also, my stats are divided by Brooks and Searle. I don't really care all that much, but it would be nice if this was fixed. I also seem to be showing up under the last name Searle instead of Brooks in most of the statistics.


 
I'll try to figure this out.



Mike Hughey said:


> It would be kind of fun to be able to order by silver or bronze (even if it's somewhat nonsensical) - I think I'd probably rank pretty high ordered by silver. (I'm good at coming in second place at 3x3x3 BLD. )



Done. The choices are now


code said:


> function medal_str() {
> $order = $_GET['order'];
> switch($order) {
> case 'total':
> ...


Feel free to suggest finer options.



Sahid Velji said:


> http://stats.cubing.net/best_worst_result.php?eventId=333mbf&regionId=&years=&single=Single
> How does the ranking for this ^ work?



An explanation of the multi-BLD result is here. I'll work on getting this displayed like on the WCA results pages.


----------



## Mike Hughey (Jan 25, 2012)

macky said:


> Mike Hughey said:
> 
> 
> > It would be kind of fun to be able to order by silver or bronze (even if it's somewhat nonsensical) - I think I'd probably rank pretty high ordered by silver. (I'm good at coming in second place at 3x3x3 BLD. )
> ...


 
Thanks. That revealed an interesting result: Congratulations to Kian Berry, who appears to have the most medals (28) without a single gold!  Nick Pappas is close behind with 27. (And my daughter Rebecca is probably in the top 10, with 14!)


----------



## Kian (Jan 25, 2012)

Mike Hughey said:


> Thanks. That revealed an interesting result: Congratulations to Kian Berry, who appears to have the most medals (28) without a single gold!  Nick Pappas is close behind with 27. (And my daughter Rebecca is probably in the top 10, with 14!)


 
Yes, I figured I had that wonderful distinction. It's a running joke in the northeast. Thanks to Rowe and Dan for making sure I never win anything.

EDIT: Not sure if this is updated as of last weekend but if not I extended this lead by coming in 3rd at OH behind, guess who, Rowe and Dan.


----------



## Lucas Garron (Jan 25, 2012)

I have six 13.36s and four 13.38s, making me the only person in first place for each of those results.

But... no 13.37s.


----------



## Mr.Toad (Jan 25, 2012)

Anthony said:


> Also, my stats are divided by Brooks and Searle. I don't really care all that much, but it would be nice if this was fixed. I also seem to be showing up under the last name Searle instead of Brooks in most of the statistics.





macky said:


> I'll try to figure this out.


I just saw this, to solve it you just need to remove the bit in red from the query:


```
group by personId[U][COLOR="red"], personName[/COLOR][/U]
```


----------



## macky (Jan 25, 2012)

Mr.Toad said:


> I just saw this, to solve it you just need to remove the bit in red from the query:
> 
> 
> ```
> ...


 
Yes, I should remove that either way, but that shows him as "Anthony Searle" because that's the first name encountered with his WCA id. Either I find a way to have MySQL go through the data from the latest row, or I need to use subid=1 somewhere (maybe subquery, maybe in the php).


----------



## qqwref (Jan 25, 2012)

Can you make a page for the bottom of the "best worst result" list? (That is, worst result.)


----------



## Carson (Feb 7, 2012)

An interesting thought:

It would be neat to have a list of the average time required to win each event each year that a competition has taken place. I'm not sure if I am explaining this well... the average winning time of each occurrence of a specific event during the course of a year.


----------



## Tim Reynolds (Feb 7, 2012)

Carson said:


> An interesting thought:
> 
> It would be neat to have a list of the average time required to win each event each year that a competition has taken place. I'm not sure if I am explaining this well... the average winning time of each occurrence of a specific event during the course of a year.




```
2011		2010		2009		2008		2007		2006		2005		2004
333	11.299		11.900 		13.118		13.224		14.178		15.659		17.934		19.396
444	52.164		54.928		58.553		1:04.550	1:11.277	1:16.532	1:30.212	1:31.624
555	1:36.436	1:36.298	1:41.308	1:54.779	2:09.807	2:28.572	3:02.212	2:54.018
222	3.818		4.147		5.114		5.163		6.288		7.404		8.881		10.870
333bf	1:52.778	1:50.932	1:54.976	1:58.020	2:27.421	3:19.773	5:42.239	10:59.078
333oh	20.304		22.107		24.140		25.470		27.079		32.113		44.459		43.515
333fm	32.7		33.1		34.0		34.1		35.5		31.3		35.0		31.0
333ft	1:30.620	1:24.716	1:35.776	1:37.772	1:18.426	2:56.020
minx	1:26.599	1:29.640	1:40.590	1:51.690	1:50.584	2:06.223
pyram	6.709		7.318		8.422		8.906		10.923		11.911		14.193
sq1	24.774		25.596		25.850		30.769		33.249		43.849		48.855		33.210
clock	12.458		12.540		12.051		14.443		12.748		14.089		14.100		11.767
666	3:07.895	3:15.692	3:30.542
777	4:51.563	4:51.234	5:18.497
magic	1.346		1.371		1.434		1.486		1.554		1.814		2.399		2.000
mmagic	3.476		3.460		4.009		3.600		3.470		3.873		4.003
444bf	9:31.138	12:50.516	14:09.820	13:55.358	25:36.573	10:08.450	n/a		19:14.000
555bf	20:18.784	21:48.800	24:49.000	28:29.500	40:48.000	38:02.667	n/a		2:34:36.000
```

There might be some mistakes, I converted the times by hand.


----------



## Carrot (Feb 11, 2012)

http://stats.cubing.net/single_average_record_same_time.php

I might be wrong, but if person A sets NR with time x, and then person B (same nationality as person A) sets a time of x, shouldn't this show up then? 

(Henrik did 3.05 pyra single NR, some months later I had a 3.05 pyra single NR too  )


----------



## Carson (Feb 12, 2012)

What about a ranking of rolling means of 100 during competitions for 3x3x3, not including DNF's and not throwing out best or worst times? This would be based on the persons "current" rolling mean instead of their best rolling mean.


Edit: Also, what about a ranking of those people with the most lines of text on their WCA profile. I'm this could be done using the database without actually polling the site, but I would guess it would be a little more complex.


----------



## Kian (Mar 13, 2012)

Clicking on links on this site hasn't worked for me in a few weeks. Just thought it was worth reporting because the site is awesome.


----------



## antoineccantin (Mar 13, 2012)

Nothing on the site seems to work for me... (except for the stats on worldcubeassociation.com)


----------



## ardi4nto (Apr 26, 2012)

Is this project still alive?


----------



## Henrik (May 29, 2012)

I guess it died


----------



## Sebastien (May 29, 2012)

They are having problems with the webspace.


----------



## jonlin (Jun 11, 2012)

If they fix it, put Youngest/oldest solver/blindfold solver
I want my name on there


----------



## Mike Hughey (Jun 11, 2012)

jonlin said:


> If they fix it, put Youngest/oldest solver/blindfold solver
> I want my name on there



You don't need the statistics website for that - you're already on the statistics page of the main site:
http://www.worldcubeassociation.org/results/statistics.php


----------



## macky (Jun 11, 2012)

We'll get stats.cubing.net working again, but it'll take some time to do it well. The hosting service shut down the database because some queries were taking too long, for reasons I don't understand. We have to improve the queries (probably using many pre-computed tables) and test them before we can put them back online without risking getting Lucas in trouble.

It's too big of a project for me alone; I need to do math. If you have knowledge about MySQL/php and would like to lead the project or help maintain it, let me know at Gmail account smakisumi.

As a note, all the code is on github: https://github.com/cubing/stats


----------



## Stefan (Jun 11, 2012)

I don't have my new system set up yet and I'm not familiar with git(hub), but here's my code suggestion for most_solve_hours.php, maybe someone else wants to integrate it into the repository (if not, I'll do it, but I don't know when). Or maybe it can just serve as an example for how to precompute and use stuff later. I tested this code when I wrote it recently, and it did speed up the very slow original query a lot.

Do this once after every update/import of the database:

```
DROP TABLE HelperMostSolveHours;

CREATE TABLE HelperMostSolveHours
SELECT   personId, personCountryId, continentId, year,
       (sum( if(value1>0,value1,0) ) +
        sum( if(value2>0,value2,0) ) +
        sum( if(value3>0,value3,0) ) +
        sum( if(value4>0,value4,0) ) +
        sum( if(value5>0,value5,0) ))/100/60/60 hours,
        personName
FROM     Results
       JOIN Events      ON Events.id = eventId
       JOIN Competitions ON Results.competitionId = Competitions.id
       JOIN Countries    ON Results.countryId = Countries.id
WHERE    format = 'time'
GROUP BY personId, Results.countryId, year;
```

Use this (well, with the dynamic conditions instead of hardcoded year<=2008 etc, of course) in the viewer script:

```
SELECT   sum(hours) hours, personId, personName, personCountryId
FROM     HelperMostSolveHours
WHERE    continentId = '_Europe'
 AND    year <= 2008
GROUP BY personId
ORDER BY hours DESC
LIMIT    100;
```

Some timings on my PC:

Original query: 15-17 seconds

Original query simply without the "LIMIT 100": 1.0-1.7 seconds
(Very interesting, somehow telling it to produce less made it a lot slower... apparently the overhead of trying to do less work actually made it a lot *more* work.)

Precomputation: 3 seconds
Query using the precomputed table: 0.1-0.2 seconds


----------



## Noahaha (Dec 30, 2012)

How many people who have never had WRs would have at some point had one if Feliks was never a speedcuber?


----------

