Odd WCA stats/ Stats request Thread

[TheDubDubJr]

Spoiler: Percentage evolution of the current top 10 posters in this thread

http://i61.tinypic.com/2qs76ew.png
http://i61.tinypic.com/x0tnrb.png

Spoiler: Data in case someone wants to have fun with this as well

post number, post id, member id (0 means guest), member name

blah blah data

It would be interesting to see thread stats for the Accomplishment Thread also.

 

[TheDubDubJr]

Spoiler: Worst World Rank for people who have completed everything

[TABLE="class:grid,align:left"]
[TR][TD]Cuber[/TD][TD]Worst World Rank[/TD][TD]Event (Time)[/TD][/TR]
[TR][TD]Antoine Cantin[/TD][TD="align:right"]186[/TD][TD="align:right"]Square-1 Average (25.71)[/TD][/TR]
[TR][TD]Evan Liu[/TD][TD="align:right"]294[/TD][TD="align:right"]OH Average (18.59)[/TD][/TR]
[TR][TD]John Brechon[/TD][TD="align:right"]318[/TD][TD="align:right"]MultiBLD (4/5 27:55)[/TD][/TR]
[TR][TD]Daniel Sheppard[/TD][TD="align:right"]330[/TD][TD="align:right"]OH Average (18.91)[/TD][/TR]
[TR][TD]Bence Barát[/TD][TD="align:right"]343[/TD][TD="align:right"]Skewb Single (6.69)[/TD][/TR]
[TR][TD]Simon Westlund[/TD][TD="align:right"]536[/TD][TD="align:right"]Skewb Single (8.21)[/TD][/TR]
[TR][TD]Ben Whitmore[/TD][TD="align:right"]587[/TD][TD="align:right"]OH Average (21.02)[/TD][/TR]
[TR][TD]Jan Bentlage[/TD][TD="align:right"]590[/TD][TD="align:right"]3x3 Average (11.62)[/TD][/TR]
[TR][TD]Ivan Zabrodin[/TD][TD="align:right"]719[/TD][TD="align:right"]OH Single (17.66)[/TD][/TR]
[TR][TD]Corey Sakowski[/TD][TD="align:right"]721[/TD][TD="align:right"]2x2 Average (4.10)[/TD][/TR]
[TR][TD]Walker Welch[/TD][TD="align:right"]846[/TD][TD="align:right"]OH Single (18.31)[/TD][/TR]
[TR][TD]Jiawen Wu (吴嘉文)[/TD][TD="align:right"]869[/TD][TD="align:right"]3x3 Single (10.00)[/TD][/TR]
[TR][TD]Callum Hales-Jepp[/TD][TD="align:right"]884[/TD][TD="align:right"]3x3 Average (12.27)[/TD][/TR]
[TR][TD]Timothy Sun[/TD][TD="align:right"]889[/TD][TD="align:right"]Pyraminx Average (8.17)[/TD][/TR]
[TR][TD]Sébastien Auroux[/TD][TD="align:right"]903[/TD][TD="align:right"]3x3 Average (12.30)[/TD][/TR]
[TR][TD]Akash Rupela[/TD][TD="align:right"]1009[/TD][TD="align:right"]2x2 Single (2.57)[/TD][/TR]
[TR][TD]Jakob Kogler[/TD][TD="align:right"]1009[/TD][TD="align:right"]3x3 Average (12.48)[/TD][/TR]
[TR][TD]Riley Woo[/TD][TD="align:right"]1053[/TD][TD="align:right"]Square-1 Single (41.74)[/TD][/TR]
[TR][TD]Henrik Buus Aagaard[/TD][TD="align:right"]1121[/TD][TD="align:right"]4x4 Single (48.36)[/TD][/TR]
[TR][TD]Andreas Pohl[/TD][TD="align:right"]1372[/TD][TD="align:right"]Pyraminx Single (6.26)[/TD][/TR]
[TR][TD]Yunqi Ouyang (欧阳韵奇)[/TD][TD="align:right"]1983[/TD][TD="align:right"]OH Single (23.66)[/TD][/TR]
[TR][TD]Nikhil Mande[/TD][TD="align:right"]2241[/TD][TD="align:right"]4x4 Single (58.47)[/TD][/TR]
[TR][TD]Tomoaki Okayama (岡山友昭)[/TD][TD="align:right"]4646[/TD][TD="align:right"]3x3 Average (17.19)[/TD][/TR]
[TR][TD]Hong Zhang (张宏)[/TD][TD="align:right"]5120[/TD][TD="align:right"]OH Average (46.30)[/TD][/TR]
[TR][TD]Mike Hughey[/TD][TD="align:right"]7018[/TD][TD="align:right"]3x3 Average (19.84)[/TD][/TR]
[/TABLE]

 

[Stefan]

It would be interesting to see thread stats for the Accomplishment Thread also.

From another thread:

Sorry, I probably won't. That one dwarfs the two I did, and I don't have access to the database but wrote a little "crawler" and analyzer. It's largely automatic but it does need a little attention and it's a slow and bad method. I'd like to see similar stats as well (like highest number of posts by one person in one thread), but they should be done properly with access to the database or at least some export. Maybe Pat could be convinced to make something like a list of (threadId, postId, memberId) triples available...

 

[Pro94]

Who has the most solves (all events combined) without DNFing or DNSing?

I was not able to create a table like Stefan. Here you can find the answer with also other stats about DNF and DNS: http://speedcubing.it/forum/Thread-WCA-Notizie-Live-Statistiche-e-Altro?pid=64717#pid64717

 

[Stefan]

I was not able to create a table like Stefan.

What do you mean? Your tables look pretty much like mine.

 

[Pro94]

What do you mean? Your tables look pretty much like mine.

For example when I try to export query result from SQL in HTML I get this code:

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><title>query data</title>
<style type="text/css"> <!--
.normal {  font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; font-weight: normal; color: #000000}
.medium {  font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 15px; font-weight: bold; color: #000000; text-decoration: none}
--></style>
</head>
<body>
<h3>query result</h3><table border=1>
<tr>
<td bgcolor=silver class='medium'>NAME</td><td bgcolor=silver class='medium'>Solves</td><td bgcolor=silver class='medium'>DNS</td><td bgcolor=silver class='medium'>DNF</td></tr>
<tr>
<td class='normal' valign='top'>Zhinan Jiang (江志楠)</td>
<td class='normal' valign='top'>66</td>
<td class='normal' valign='top'>4</td>
<td class='normal' valign='top'>0</td>
</tr>
</table>
</body></html>

And for table here i should use this:

[table="width: 500"]
[tr]
	[td][/td]

[/tr]
[/table

Am I exporting in a wrong way?

Stefan, do you use only SQL export to post table here? Also in some stats when you click on someone's name you can see his WCA profile, how did you do that?

 

[Cale S]

Are there any countries that appear in the top 10 of every official result?

Out of all the competitions from this year, what's the average number of events held?

 

[Bindedsa]

Are there any countries that appear in the top 10 of every official result?

No.

Spoiler

3x3 Single
Netherlands
Australia
United Kingdom
USA
Japan
Poland
Thailand

3x3 Average
Netherlands
Australia
United Kingdom
USA

4x4 Single
Netherlands
Australia
United Kingdom
USA

4x4 Average
Netherlands
Australia
USA

5x5 single
Australia
USA

5x5 average
Australia
USA

2x2 single
Australia

2x2 average
Australia

3x3 BLD single

 

[cubizh]

Stefan, do you use only SQL export to post table here? Also in some stats when you click on someone's name you can see his WCA profile, how did you do that?

It's explained in this thread: http://www.speedsolving.com/forum/showthread.php?49120-WCA-forum-statistics-tool

 

[Pro94]

It's explained in this thread: http://www.speedsolving.com/forum/showthread.php?49120-WCA-forum-statistics-tool

Ah, thanks. Totally missed this part of the sentece:

contain the results nicely formatted and documented, ready to copy&paste into the forum

 

[Torch]

I noticed that every competition in Iran has been a multi-day competition. Could someone rank the countries by what percentage of their competitions have been multi-day?

 

[Stefan]

How about pairs of competitors with different nationalities? I think this could be more interesting

Dominated by a handful of people in Europe.
Best pair in USA is Shelley+Lucas.

Spoiler: Pairs at most competitions representing different countries (minimum 30)

Using data from WCA_export471_20140913 and Stefan's WCA Statistics Tools.

[TABLE="class:grid,align:left"]
[TR][TD]personId[/TD][TD]personId[/TD][TD]competitions[/TD][/TR]
[TR][TD]Ton Dennenbroek[/TD][TD]Maria Oey[/TD][TD="align:right"]79[/TD][/TR]
[TR][TD]Ton Dennenbroek[/TD][TD]Lars Vandenbergh[/TD][TD="align:right"]73[/TD][/TR]
[TR][TD]Ron van Bruchem[/TD][TD]Lars Vandenbergh[/TD][TD="align:right"]62[/TD][/TR]
[TR][TD]Sébastien Auroux[/TD][TD]François Courtès[/TD][TD="align:right"]62[/TD][/TR]
[TR][TD]Erik Akkersdijk[/TD][TD]Sébastien Auroux[/TD][TD="align:right"]60[/TD][/TR]
[TR][TD]Lars Vandenbergh[/TD][TD]Arnaud van Galen[/TD][TD="align:right"]59[/TD][/TR]
[TR][TD]Arnaud van Galen[/TD][TD]Maria Oey[/TD][TD="align:right"]54[/TD][/TR]
[TR][TD]Lars Vandenbergh[/TD][TD]François Courtès[/TD][TD="align:right"]54[/TD][/TR]
[TR][TD]Lars Vandenbergh[/TD][TD]Erik Akkersdijk[/TD][TD="align:right"]53[/TD][/TR]
[TR][TD]Erik Akkersdijk[/TD][TD]Maria Oey[/TD][TD="align:right"]52[/TD][/TR]
[TR][TD]Ron van Bruchem[/TD][TD]Maria Oey[/TD][TD="align:right"]51[/TD][/TR]
[TR][TD]Lars Vandenbergh[/TD][TD]Maria Oey[/TD][TD="align:right"]51[/TD][/TR]
[TR][TD]Arnaud van Galen[/TD][TD]François Courtès[/TD][TD="align:right"]50[/TD][/TR]
[TR][TD]Ton Dennenbroek[/TD][TD]François Courtès[/TD][TD="align:right"]49[/TD][/TR]
[TR][TD]Arnaud van Galen[/TD][TD]Sébastien Auroux[/TD][TD="align:right"]48[/TD][/TR]
[TR][TD]Erik Akkersdijk[/TD][TD]François Courtès[/TD][TD="align:right"]47[/TD][/TR]
[TR][TD]Maria Oey[/TD][TD]François Courtès[/TD][TD="align:right"]47[/TD][/TR]
[TR][TD]Shelley Chang[/TD][TD]Lucas Garron[/TD][TD="align:right"]46[/TD][/TR]
[TR][TD]Erik Akkersdijk[/TD][TD]Nora Christ[/TD][TD="align:right"]46[/TD][/TR]
[TR][TD]Jeremy Fleischman[/TD][TD]Lucas Garron[/TD][TD="align:right"]44[/TD][/TR]
[TR][TD]Shelley Chang[/TD][TD]Takao Hashimoto (橋本貴夫)[/TD][TD="align:right"]44[/TD][/TR]
[TR][TD]Maria Oey[/TD][TD]Mats Valk[/TD][TD="align:right"]44[/TD][/TR]
[TR][TD]Ton Dennenbroek[/TD][TD]Sébastien Auroux[/TD][TD="align:right"]43[/TD][/TR]
[TR][TD]Maria Oey[/TD][TD]Sébastien Auroux[/TD][TD="align:right"]42[/TD][/TR]
[TR][TD]Devin Corr-Robinett[/TD][TD]Takao Hashimoto (橋本貴夫)[/TD][TD="align:right"]42[/TD][/TR]
[TR][TD]Rama Temmink[/TD][TD]Maria Oey[/TD][TD="align:right"]42[/TD][/TR]
[TR][TD]Ron van Bruchem[/TD][TD]François Courtès[/TD][TD="align:right"]41[/TD][/TR]
[TR][TD]Mats Valk[/TD][TD]François Courtès[/TD][TD="align:right"]41[/TD][/TR]
[TR][TD]Jeremy Fleischman[/TD][TD]Takao Hashimoto (橋本貴夫)[/TD][TD="align:right"]40[/TD][/TR]
[TR][TD]Lars Vandenbergh[/TD][TD]Clément Gallet[/TD][TD="align:right"]40[/TD][/TR]
[TR][TD]Erik Akkersdijk[/TD][TD]Fabian Auroux[/TD][TD="align:right"]39[/TD][/TR]
[TR][TD]Lars Vandenbergh[/TD][TD]Mats Valk[/TD][TD="align:right"]39[/TD][/TR]
[TR][TD]Lars Vandenbergh[/TD][TD]Joël van Noort[/TD][TD="align:right"]39[/TD][/TR]
[TR][TD]Maria Oey[/TD][TD]Hanneke Rijks[/TD][TD="align:right"]39[/TD][/TR]
[TR][TD]Ron van Bruchem[/TD][TD]Sébastien Auroux[/TD][TD="align:right"]39[/TD][/TR]
[TR][TD]Mats Valk[/TD][TD]Sébastien Auroux[/TD][TD="align:right"]38[/TD][/TR]
[TR][TD]François Courtès[/TD][TD]Hanneke Rijks[/TD][TD="align:right"]38[/TD][/TR]
[TR][TD]Arnaud van Galen[/TD][TD]Zoé de Moffarts[/TD][TD="align:right"]37[/TD][/TR]
[TR][TD]Lars Vandenbergh[/TD][TD]Sébastien Auroux[/TD][TD="align:right"]36[/TD][/TR]
[TR][TD]Lars Vandenbergh[/TD][TD]Rama Temmink[/TD][TD="align:right"]36[/TD][/TR]
[TR][TD]François Courtès[/TD][TD]Jan Bentlage[/TD][TD="align:right"]35[/TD][/TR]
[TR][TD]François Courtès[/TD][TD]Geert Bicler[/TD][TD="align:right"]34[/TD][/TR]
[TR][TD]Sébastien Auroux[/TD][TD]Hanneke Rijks[/TD][TD="align:right"]34[/TD][/TR]
[TR][TD]Devin Corr-Robinett[/TD][TD]Lucas Garron[/TD][TD="align:right"]34[/TD][/TR]
[TR][TD]Arnaud van Galen[/TD][TD]Geert Bicler[/TD][TD="align:right"]34[/TD][/TR]
[TR][TD]Clément Gallet[/TD][TD]Arnaud van Galen[/TD][TD="align:right"]34[/TD][/TR]
[TR][TD]Ton Dennenbroek[/TD][TD]Clément Gallet[/TD][TD="align:right"]33[/TD][/TR]
[TR][TD]Lucas Garron[/TD][TD]Edward Lin[/TD][TD="align:right"]33[/TD][/TR]
[TR][TD]Ton Dennenbroek[/TD][TD]Geert Bicler[/TD][TD="align:right"]33[/TD][/TR]
[TR][TD]Maria Oey[/TD][TD]Geert Bicler[/TD][TD="align:right"]33[/TD][/TR]
[TR][TD]Jaap Scherphuis[/TD][TD]Lars Vandenbergh[/TD][TD="align:right"]33[/TD][/TR]
[TR][TD]Adam Zamora[/TD][TD]Takao Hashimoto (橋本貴夫)[/TD][TD="align:right"]33[/TD][/TR]
[TR][TD]Lars Vandenbergh[/TD][TD]Hanneke Rijks[/TD][TD="align:right"]33[/TD][/TR]
[TR][TD]Sébastien Auroux[/TD][TD]Geert Bicler[/TD][TD="align:right"]32[/TD][/TR]
[TR][TD]Lars Vandenbergh[/TD][TD]István Kocza[/TD][TD="align:right"]32[/TD][/TR]
[TR][TD]Lucas Garron[/TD][TD]Justin Adsuara[/TD][TD="align:right"]31[/TD][/TR]
[TR][TD]Erik Akkersdijk[/TD][TD]Jan Bentlage[/TD][TD="align:right"]31[/TD][/TR]
[TR][TD]Joël van Noort[/TD][TD]Maria Oey[/TD][TD="align:right"]31[/TD][/TR]
[TR][TD]Dave Campbell[/TD][TD]Emile Compion[/TD][TD="align:right"]31[/TD][/TR]
[TR][TD]Ron van Bruchem[/TD][TD]Clément Gallet[/TD][TD="align:right"]31[/TD][/TR]
[TR][TD]Takao Hashimoto (橋本貴夫)[/TD][TD]Edward Lin[/TD][TD="align:right"]30[/TD][/TR]
[TR][TD]François Courtès[/TD][TD]Laura Ohrndorf[/TD][TD="align:right"]30[/TD][/TR]
[TR][TD]Jan Bentlage[/TD][TD]Geert Bicler[/TD][TD="align:right"]30[/TD][/TR]
[TR][TD]Erik Akkersdijk[/TD][TD]Niklas Spies[/TD][TD="align:right"]30[/TD][/TR]
[/TABLE]

Spoiler: SQL

CREATE TABLE participations
SELECT distinct Results.personId, personCountryId, competitionId
FROM Results
WHERE personId in (SELECT personId FROM Results GROUP BY personId HAVING count(distinct competitionId)>=30);

SELECT a.personId, b.personId, count(*) competitions
FROM participations a, participations b
WHERE a.competitionId=b.competitionId and a.personId<b.personId and a.personCountryId<>b.personCountryId
GROUP BY a.personId, b.personId
HAVING competitions>=30
ORDER BY competitions desc;

DROP TABLE participations;

 

[Stefan]

Best pair in USA is Shelley+Lucas.

Hmm. Lucas is only the second-best ambassador to USA, far behind Takao:

Spoiler: Best ambassadors to USA (minimum 5 competitions)

Using data from WCA_export471_20140913 and Stefan's WCA Statistics Tools.

[TABLE="class:grid,align:left"]
[TR][TD]Ambassador[/TD][TD]Country[/TD][TD]Competitions[/TD][/TR]
[TR][TD]Takao Hashimoto (橋本貴夫)[/TD][TD]Japan[/TD][TD="align:right"]79[/TD][/TR]
[TR][TD]Lucas Garron[/TD][TD]Germany[/TD][TD="align:right"]49[/TD][/TR]
[TR][TD]Chester Lian[/TD][TD]Malaysia[/TD][TD="align:right"]28[/TD][/TR]
[TR][TD]Shotaro Makisumi (牧角章太郎)[/TD][TD]Japan[/TD][TD="align:right"]28[/TD][/TR]
[TR][TD]Dave Campbell[/TD][TD]Canada[/TD][TD="align:right"]27[/TD][/TR]
[TR][TD]Chia-Wei Lu (呂家維)[/TD][TD]Taiwan[/TD][TD="align:right"]27[/TD][/TR]
[TR][TD]Lars Petrus[/TD][TD]Sweden[/TD][TD="align:right"]27[/TD][/TR]
[TR][TD]Ilkyoo Choi (최일규)[/TD][TD]Korea[/TD][TD="align:right"]26[/TD][/TR]
[TR][TD]Aina Hashimoto (橋本愛那)[/TD][TD]Japan[/TD][TD="align:right"]21[/TD][/TR]
[TR][TD]Keanu László Vestil[/TD][TD]Hungary[/TD][TD="align:right"]17[/TD][/TR]
[TR][TD]Justin Jaffray[/TD][TD]Canada[/TD][TD="align:right"]17[/TD][/TR]
[TR][TD]Rob Stuart[/TD][TD]United Kingdom[/TD][TD="align:right"]15[/TD][/TR]
[TR][TD]Wojciech Poljanowski[/TD][TD]Poland[/TD][TD="align:right"]13[/TD][/TR]
[TR][TD]Sarah Strong[/TD][TD]Canada[/TD][TD="align:right"]13[/TD][/TR]
[TR][TD]Everest Shi[/TD][TD]Canada[/TD][TD="align:right"]13[/TD][/TR]
[TR][TD]Jasmine Lee[/TD][TD]Australia[/TD][TD="align:right"]12[/TD][/TR]
[TR][TD]Yuxuan Chen (陈雨璇)[/TD][TD]China[/TD][TD="align:right"]11[/TD][/TR]
[TR][TD]Sikan Li[/TD][TD]China[/TD][TD="align:right"]11[/TD][/TR]
[TR][TD]Hongfei Tian (田洪飞)[/TD][TD]China[/TD][TD="align:right"]11[/TD][/TR]
[TR][TD]Louis Cormier[/TD][TD]Canada[/TD][TD="align:right"]11[/TD][/TR]
[TR][TD]Guanda Fu (符冠达)[/TD][TD]China[/TD][TD="align:right"]10[/TD][/TR]
[TR][TD]Yu Yang Hsieh[/TD][TD]Taiwan[/TD][TD="align:right"]10[/TD][/TR]
[TR][TD]Peter Still[/TD][TD]Australia[/TD][TD="align:right"]10[/TD][/TR]
[TR][TD]Thompson Clarke[/TD][TD]Canada[/TD][TD="align:right"]10[/TD][/TR]
[TR][TD]Jedrick Remonde[/TD][TD]Philippines[/TD][TD="align:right"]9[/TD][/TR]
[TR][TD]Dene Beardsley[/TD][TD]New Zealand[/TD][TD="align:right"]9[/TD][/TR]
[TR][TD]Zheng Li (李政)[/TD][TD]China[/TD][TD="align:right"]9[/TD][/TR]
[TR][TD]Richard Jay S. Apagar[/TD][TD]Philippines[/TD][TD="align:right"]9[/TD][/TR]
[TR][TD]Eric Limeback[/TD][TD]Canada[/TD][TD="align:right"]8[/TD][/TR]
[TR][TD]Alfonso Plata[/TD][TD]Mexico[/TD][TD="align:right"]8[/TD][/TR]
[TR][TD]Warren Liao[/TD][TD]Taiwan[/TD][TD="align:right"]8[/TD][/TR]
[TR][TD]Baian Liu[/TD][TD]China[/TD][TD="align:right"]8[/TD][/TR]
[TR][TD]Anthony Brooks[/TD][TD]Belize[/TD][TD="align:right"]8[/TD][/TR]
[TR][TD]Jason Song[/TD][TD]Korea[/TD][TD="align:right"]8[/TD][/TR]
[TR][TD]SeungBeom Cho (조승범)[/TD][TD]Korea[/TD][TD="align:right"]8[/TD][/TR]
[TR][TD]Guanyang Yu[/TD][TD]China[/TD][TD="align:right"]8[/TD][/TR]
[TR][TD]Seung-Woo Choi[/TD][TD]Korea[/TD][TD="align:right"]8[/TD][/TR]
[TR][TD]Stefan Pochmann[/TD][TD]Germany[/TD][TD="align:right"]8[/TD][/TR]
[TR][TD]Kristopher De Asis[/TD][TD]Canada[/TD][TD="align:right"]7[/TD][/TR]
[TR][TD]Derek Han[/TD][TD]Korea[/TD][TD="align:right"]7[/TD][/TR]
[TR][TD]Tomoya Ide[/TD][TD]Japan[/TD][TD="align:right"]7[/TD][/TR]
[TR][TD]Alain Cormier[/TD][TD]Canada[/TD][TD="align:right"]7[/TD][/TR]
[TR][TD]Tristan Penson[/TD][TD]United Kingdom[/TD][TD="align:right"]7[/TD][/TR]
[TR][TD]Daiki Hashimoto (橋本大輝)[/TD][TD]Japan[/TD][TD="align:right"]7[/TD][/TR]
[TR][TD]Aditya Dargan[/TD][TD]India[/TD][TD="align:right"]7[/TD][/TR]
[TR][TD]Jack Moseley[/TD][TD]Canada[/TD][TD="align:right"]6[/TD][/TR]
[TR][TD]Armin Askari[/TD][TD]Canada[/TD][TD="align:right"]6[/TD][/TR]
[TR][TD]Cameron Almasi[/TD][TD]Iran[/TD][TD="align:right"]6[/TD][/TR]
[TR][TD]Nan Ma[/TD][TD]China[/TD][TD="align:right"]6[/TD][/TR]
[TR][TD]Ron van Bruchem[/TD][TD]Netherlands[/TD][TD="align:right"]6[/TD][/TR]
[TR][TD]Vidar Klungre[/TD][TD]Norway[/TD][TD="align:right"]6[/TD][/TR]
[TR][TD]Shreyas Nagananda Talamakki[/TD][TD]India[/TD][TD="align:right"]6[/TD][/TR]
[TR][TD]Lars Vandenbergh[/TD][TD]Belgium[/TD][TD="align:right"]6[/TD][/TR]
[TR][TD]Wuqiong Fan[/TD][TD]China[/TD][TD="align:right"]6[/TD][/TR]
[TR][TD]Emile Compion[/TD][TD]South Africa[/TD][TD="align:right"]6[/TD][/TR]
[TR][TD]Jianneng Li[/TD][TD]Canada[/TD][TD="align:right"]6[/TD][/TR]
[TR][TD]Breandan Vallance[/TD][TD]United Kingdom[/TD][TD="align:right"]6[/TD][/TR]
[TR][TD]Elliot Penson[/TD][TD]United Kingdom[/TD][TD="align:right"]6[/TD][/TR]
[TR][TD]Micael Boulet[/TD][TD]Canada[/TD][TD="align:right"]6[/TD][/TR]
[TR][TD]Nahckjoon Kim[/TD][TD]Korea[/TD][TD="align:right"]6[/TD][/TR]
[TR][TD]Jungwoo Kim[/TD][TD]Korea[/TD][TD="align:right"]6[/TD][/TR]
[TR][TD]Mudit Mathur[/TD][TD]India[/TD][TD="align:right"]5[/TD][/TR]
[TR][TD]Rong Ding (丁融)[/TD][TD]China[/TD][TD="align:right"]5[/TD][/TR]
[TR][TD]Forte Shinko[/TD][TD]Canada[/TD][TD="align:right"]5[/TD][/TR]
[TR][TD]Greg French[/TD][TD]Canada[/TD][TD="align:right"]5[/TD][/TR]
[TR][TD]Andre Cantin[/TD][TD]Canada[/TD][TD="align:right"]5[/TD][/TR]
[TR][TD]Sittinon Sukhaya (สิทธินนท์ สุขายะ)[/TD][TD]Thailand[/TD][TD="align:right"]5[/TD][/TR]
[TR][TD]Aman Karunakaran[/TD][TD]India[/TD][TD="align:right"]5[/TD][/TR]
[TR][TD]Louise Cormier[/TD][TD]Canada[/TD][TD="align:right"]5[/TD][/TR]
[TR][TD]Yin Jia Qiu[/TD][TD]China[/TD][TD="align:right"]5[/TD][/TR]
[TR][TD]Jacob Hutnyk[/TD][TD]Canada[/TD][TD="align:right"]5[/TD][/TR]
[TR][TD]Antoine Cantin[/TD][TD]Canada[/TD][TD="align:right"]5[/TD][/TR]
[TR][TD]Joey Gouly[/TD][TD]United Kingdom[/TD][TD="align:right"]5[/TD][/TR]
[TR][TD]Kathy Zhang[/TD][TD]Canada[/TD][TD="align:right"]5[/TD][/TR]
[TR][TD]Kamaru-Deen Lawal[/TD][TD]Nigeria[/TD][TD="align:right"]5[/TD][/TR]
[TR][TD]Sven Gowal[/TD][TD]Switzerland[/TD][TD="align:right"]5[/TD][/TR]
[TR][TD]Robert Kubik[/TD][TD]Canada[/TD][TD="align:right"]5[/TD][/TR]
[TR][TD]Amod Mathur[/TD][TD]India[/TD][TD="align:right"]5[/TD][/TR]
[TR][TD]Craig Bouchard[/TD][TD]Canada[/TD][TD="align:right"]5[/TD][/TR]
[TR][TD]Matt Walter[/TD][TD]Canada[/TD][TD="align:right"]5[/TD][/TR]
[TR][TD]Anthony Orji[/TD][TD]Nigeria[/TD][TD="align:right"]5[/TD][/TR]
[TR][TD]Patrick Crowne[/TD][TD]United Kingdom[/TD][TD="align:right"]5[/TD][/TR]
[TR][TD]Kaustubh Nihul[/TD][TD]India[/TD][TD="align:right"]5[/TD][/TR]
[/TABLE]

Spoiler: SQL

SELECT personId Ambassador, personCountryId Country, count(distinct competitionId) Competitions
FROM Results join Competitions on Competitions.id=competitionId
WHERE personCountryId<>'USA' and Competitions.countryId='USA'
GROUP BY personId, personCountryId
HAVING Competitions >= 5
ORDER BY Competitions desc;

 

[Stefan]

when I try to export query result from SQL in HTML

I do use the MySQL Workbench which offers HTML export, but I never even thought about that. But even if that "worked" here, I'd still prefer my own tool because of its additional functionality like automatically turning a personId into a link to the WCA site with the person's name as text or formatting 6227 to "1:02.27". I have btw made some improvements in the meantime, but haven't committed them all yet.

Edit: I guess such additional functionality could be implemented in MySQL as functions and if not automatically, then one could at least apply them explicitly like maybe "link(personId)" instead of "personId". I'll put that on my todo-list for alternatives.

 

[Stefan]

Edit: I guess such additional functionality could be implemented in MySQL as functions and if not automatically, then one could at least apply them explicitly like maybe "link(personId)" instead of "personId". I'll put that on my todo-list for alternatives.

Here's a first attempt:

[COLOR="#FF0000"][B]CREATE FUNCTION link[/B][/COLOR] (personId text) RETURNS text
RETURN concat('[noparse][url=https://www.worldcubeassociation.org/results/p.php?i=', personId, ']',
              (SELECT name from Persons WHERE id=personId and subId=1),
              '[/url][/noparse]');

[COLOR="#FF0000"][B]CREATE FUNCTION format_time[/B][/COLOR] (centiseconds int) RETURNS text
RETURN if(centiseconds < 6000, round(centiseconds/100, 2), 'todo...');

SELECT [COLOR="#FF0000"][B]link(personId)[/B][/COLOR] Cuber, [COLOR="#FF0000"][B]format_time(best)[/B][/COLOR] Record
FROM RanksSingle join Persons on Persons.id=personId
WHERE eventId = '333bf' and countryId = 'Italy'
ORDER BY best
LIMIT 10;

Spoiler: Exporting as HTML gives me

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><title>Data</title>
</head>
<body>
<table border=1>
<tr>
<td bgcolor=silver class='medium'>Cuber</td>
<td bgcolor=silver class='medium'>Record</td>
</tr>

<tr>
<td class='normal' valign='top'>[url=https://www.worldcubeassociation.org/results/p.php?i=2013SOLI02]Alessandro Solito[/url]</td>
<td class='normal' valign='top'>25.44</td>
</tr>

<tr>
<td class='normal' valign='top'>[url=https://www.worldcubeassociation.org/results/p.php?i=2011TRON02]Sebastiano Tronto[/url]</td>
<td class='normal' valign='top'>27.06</td>
</tr>

<tr>
<td class='normal' valign='top'>[url=https://www.worldcubeassociation.org/results/p.php?i=2009COLO03]Matteo Colombo[/url]</td>
<td class='normal' valign='top'>38.28</td>
</tr>

<tr>
<td class='normal' valign='top'>[url=https://www.worldcubeassociation.org/results/p.php?i=2011MONT02]Kevin Montano[/url]</td>
<td class='normal' valign='top'>53.08</td>
</tr>

<tr>
<td class='normal' valign='top'>[url=https://www.worldcubeassociation.org/results/p.php?i=2009LEVR01]Marco Levrero[/url]</td>
<td class='normal' valign='top'>todo...</td>
</tr>

<tr>
<td class='normal' valign='top'>[url=https://www.worldcubeassociation.org/results/p.php?i=2009DIMP01]Mariano D'Imperio[/url]</td>
<td class='normal' valign='top'>todo...</td>
</tr>

<tr>
<td class='normal' valign='top'>[url=https://www.worldcubeassociation.org/results/p.php?i=2010MAZZ02]Marco Mazzuoccolo[/url]</td>
<td class='normal' valign='top'>todo...</td>
</tr>

<tr>
<td class='normal' valign='top'>[url=https://www.worldcubeassociation.org/results/p.php?i=2008CIRN01]Fabrizio Cirnigliaro[/url]</td>
<td class='normal' valign='top'>todo...</td>
</tr>

<tr>
<td class='normal' valign='top'>[url=https://www.worldcubeassociation.org/results/p.php?i=2009BOSI01]Alberto Bosia[/url]</td>
<td class='normal' valign='top'>todo...</td>
</tr>

<tr>
<td class='normal' valign='top'>[url=https://www.worldcubeassociation.org/results/p.php?i=2007POLI01]Lorenzo Vigani Poli[/url]</td>
<td class='normal' valign='top'>todo...</td>
</tr>
</table>
</body></html>

I might actually use this at least while developing queries. For example:

-- Person name
CREATE FUNCTION pn (personId text) RETURNS text
RETURN (SELECT name from Persons WHERE id=personId and subId=1);

-- Competition name
CREATE FUNCTION cn (competitionId text) RETURNS text
RETURN (SELECT cellName from Competitions WHERE id=competitionId);

-- Format time
CREATE FUNCTION ft (centiseconds int) RETURNS text
RETURN if(centiseconds < 6000,
          round(centiseconds/100, 2),
          if(centiseconds < 60*6000,
             concat(floor(centiseconds/6000), ':', lpad(round(mod(centiseconds, 6000)/100, 2), 5, '0')),
             'hours? really?'));

-- Format (ratio as) percentage
CREATE FUNCTION fp (ratio double, digits int) RETURNS text
RETURN concat(round(ratio * 100, digits), '%');

-- Demo
SELECT pn('2003POCH01'), cn('WC2013'), ft(6227), fp(17/42, 2);

Results in:
+------------------+-------------------------+----------+--------------+
| pn('2003POCH01') | cn('WC2013')            | ft(6227) | fp(17/42, 2) |
+------------------+-------------------------+----------+--------------+
| Stefan Pochmann  | World Championship 2013 | 1:02.27  | 40.48%       |
+------------------+-------------------------+----------+--------------+

 

[Nathan Dwyer]

if Scotland gains independence from the UK, how will that change the NR situation in the British Isles?

 

[CHJ]

if Scotland gains independence from the UK, how will that change the NR situation in the British Isles?

doubt it would matter, independence is a stupid idea anyway

 

[bobthegiraffemonkey]

(As a Scot) it would make NRs less interesting: I'd have no competition for BLD, and CHJ would get a free pass into top 3 for BBLD p). That's just my opinion though, and also I don't care about other events much. In the event of independence, I'd probably still pay some attention to the 'UK' ranks, even though they would no longer count, maybe this could be kept track of somewhere if it happens?

(Please keep any replies cubing-related, I'd hate for a political argument to break out here.)

 

[kinch2002]

if Scotland gains independence from the UK, how will that change the NR situation in the British Isles?

My guess is that all results will stay as UK results (including current NRs set by Scots). Then whoever has Scottish nationality will start setting results ranked under Scotland.
Hopefully it won't happen anyway - the cubers I've spoken to don't seem to be in favour of independence from a cubing standpoint

 

[AvGalen]

Scotland independent? Wait, what?
Also, if Scotland would be independent that means the Kingdom is not United anymore, so we can't call the other guys UK anymore!