WCA Database Question(s)

[ProStar]

I've just decided to play around with the WCA database and generate some statistics based around it. Being a Mac user, I decided to utilize the WCA Statistics page's online database query.

I'm having an issue that appears to be a discrepancy in the database (although I'm sure is due to a gap in my understanding). I was looking at the total scrambles generated per year, using the below command:

SELECT SUBSTR(competitionId, -4, 4) AS Year, COUNT(*) AS Total333Scrambles FROM Scrambles WHERE eventId = '333' GROUP BY SUBSTR(competitionId, -4, 4) ORDER BY SUBSTR(competitionId, -4, 4)

I'm operating under the assumption that the last four characters of every competition name is the year it was held in (which as far as I can tell is correct). The command generates these results:

#	Year	Total333Scrambles
1	2014	16848
2	2015	27566
3	2016	37309
4	2017	52582
5	2018	62359
6	2019	67248
7	2020	12188
8	2021	11656
9	2022	52973
10	2023	34616

Which sums to 375345 (the expected value). My first question is: why does the scrambles table only go back to 2014? My assumption is that the table didn't exist and scramble records weren't kept before this, but it's worth asking someone who actually knows.

After I saw this, I thought this number seemed a bit low. Because of this, I decided to check the total scrambles via the results table instead, since I assumed each recorded time should require exactly one scramble (although I did consider possible discrepancies such as extras that may have been generated but never used, as I wasn't sure what qualified something to go in the scrambles table). Using this code:

SELECT SUBSTR(competitionId, -4, 4) AS Year, COUNT(*) * 5 AS TotalResults FROM Results WHERE eventId = '333' GROUP BY SUBSTR(competitionId, -4, 4)

I multiplied the total results value by 5 because each row in the results table accounts for an entire average. This of course won't be 100% accurate (for example, the 1982 worlds did only 3 solves instead of 5), but I figured it was close enough for my purposes. I got these results from that query:

#	Year	TotalResults
1	1982	95
2	2003	650
3	2004	2355
4	2005	5735
5	2006	9740
6	2007	18220
7	2008	39170
8	2009	66770
9	2010	89370
10	2011	110735
11	2012	119080
12	2013	156160
13	2014	224815
14	2015	287870
15	2016	393795
16	2017	569320
17	2018	692940
18	2019	774120
19	2020	131795
20	2021	111295
21	2022	658700
22	2023	463685

Which sums to 4926415 (the same as selecting the count of all 3x3 results and multiplying by 5). Even looking at just 2023, there are 34616 entries in the scrambles table, and 463685 in the results table.

So my main question is: am I misunderstanding the structure/purpose of the results and scrambles table? If so, what is the true purpose of these tables? I'm relatively confident my code itself is not to blame, as I validated all my counts and grouping as much as I could. Although I'm also far from an expert in SQL so it's quite possible I'm missing the mistake I made in my queries.

Any help would be appreciated!

 

[Lucas Garron]

Which sums to 375345 (the expected value). My first question is: why does the scrambles table only go back to 2014? My assumption is that the table didn't exist and scramble records weren't kept before this, but it's worth asking someone who actually knows.

Indeed. We didn't require scrambles to be sent with competition results until Jan. 2013:

• https://www.worldcubeassociation.org/archive/forum_topics/529
• https://www.worldcubeassociation.org/regulations/history/files/2013-01-01/#1c3a

 

[DGCubes]

I assumed each recorded time should require exactly one scramble (although I did consider possible discrepancies such as extras that may have been generated but never used, as I wasn't sure what qualified something to go in the scrambles table).

You're right that each time should require exactly one scramble (besides edge cases like extras), but every scramble does not require exactly one result! Most scrambles are used for an entire group of competitors which explains the discrepancy.

 

[ProStar]

You're right that each time should require exactly one scramble (besides edge cases like extras), but every scramble does not require exactly one result! Most scrambles are used for an entire group of competitors which explains the discrepancy.

Of course! I completely missed that connection, thanks for pointing it out