Anyone know SQL that can help with a WCA Database Query?

[UncleFrank]

I don't know SQL, so I'm basically just copy/pasting.

I found these SQL queries in the WCA forums -
https://forum.worldcubeassociation....singles-in-wca-results-with-year-filter/36406

and use them to query the WCA Database -
https://statistics.worldcubeassociation.org/database-query

I can get them to work just fine, for example using 2022 for [year] and 222 for [event], but I would like to alter them a bit and not sure what/how to do it.
Can I somehow change the [year] to a year range? Like, instead of using the single year 2022, I want 4 years in a row, like the range 2020-2023.

 

[tslwn]

I didn't know you could query the WCA database! That's very cool.

As a former SQL developer, I'd be more than happy to help.

For example, this query returns the best 10 single times for 3x3 in competitions between 2020 and 2023.

SELECT r.personName,
-- convert the start date to a year
YEAR(c.start_date) AS year,
c.name AS competitionName,
-- convert the best and average times to seconds
r.best/100 AS best,
r.average/100 AS average
FROM Results AS r,
Competitions AS c,
Events AS e
-- join the Competitions table to the Results table
WHERE c.id = r.competitionId
-- join the Events table to the Results table
AND e.id = r.eventId
-- filter Events by name
AND e.name = "3x3x3 Cube"
-- filter Competitions by date
AND YEAR(c.start_date) <= 2023
AND YEAR(c.end_date) >= 2020
-- filter Results to valid average times
AND r.average > 0
-- sort the rows in ascending order of best time
ORDER BY r.best ASC
-- only return 10 rows
LIMIT 10

If there is anything else you would like to know, just ask!

 

[UncleFrank]

I didn't know you could query the WCA database! That's very cool.

As a former SQL developer, I'd be more than happy to help.

For example, this query returns the best 10 single times for 3x3 in competitions between 2020 and 2023.

....

If there is anything else you would like to know, just ask!

Thanks for the help.
Sorry, I should have clarified, I specifically want one of the queries listed in the wca forum post I linked:

Best Averages per Person for a Specific Year:

SELECT personId, personName, MIN(average) AS bestAverage, CountryId
FROM Results
WHERE competitionId LIKE '%2022' AND average > 0 AND eventId = '222' AND personName LIKE '%'
GROUP BY personId, personName
ORDER BY bestAverage

which works fine,
but the only thing I want to change is from one year, 2022, to a range of years, like 2020-2023 or even more, like 2014-2024.
So instead of every person's best avg for one year, I want every person's best avg during that range.

 

[ProStar]

SELECT personId, personName, MIN(average) AS bestAverage, CountryId
FROM Results
WHERE (competitionId LIKE '%2022' OR competitionId LIKE ‘%2023’ OR competitionId LIKE ‘%2024’) AND average > 0 AND eventId = '222' AND personName LIKE '%'
GROUP BY personId, personName
ORDER BY bestAverage

Probably a much more efficient way to do it, but this should work. To change the year range, just add “competitionId LIKE ‘%Year’” for each of the years you want to search. Although I think my phone has autocorrected to the wrong single quotes, so you may get a syntax error from those.

Maybe you could grab part of the compeitiomId, convert it to a Date/DateTime, and then measure the date with > and < signs? I’m on my phone so I can’t really test that out at the moment, but that approach could probably work as well.

 

[MattP98]

You can join to the Competitions table to retrieve the start/end dates of the comp and query using those. The competition year is determined by the end date of the competition I believe.

SELECT r.personId, r.personName, MIN(r.average) AS bestAverage, r.CountryId, c.name, c.end_date FROM Results r
JOIN Competitions c ON r.competitionId = c.id
WHERE r.average > 0
AND r.eventId = '222'
AND c.end_date >= '2022-01-01'
AND c.end_date <= '2022-12-31'
GROUP BY r.personId
ORDER BY bestAverage

Just replace the dates in the where clause with the dates you want to query.

 

[UncleFrank]

SELECT personId, personName, MIN(average) AS bestAverage, CountryId
FROM Results
WHERE (competitionId LIKE '%2022' OR competitionId LIKE ‘%2023’ OR competitionId LIKE ‘%2024’) AND average > 0 AND eventId = '222' AND personName LIKE '%'
GROUP BY personId, personName
ORDER BY bestAverage

Probably a much more efficient way to do it, but this should work. To change the year range, just add “competitionId LIKE ‘%Year’” for each of the years you want to search. Although I think my phone has autocorrected to the wrong single quotes, so you may get a syntax error from those.

Maybe you could grab part of the compeitiomId, convert it to a Date/DateTime, and then measure the date with > and < signs? I’m on my phone so I can’t really test that out at the moment, but that approach could probably work as well.

You can join to the Competitions table to retrieve the start/end dates of the comp and query using those. The competition year is determined by the end date of the competition I believe.

SELECT r.personId, r.personName, MIN(r.average) AS bestAverage, r.CountryId, c.name, c.end_date FROM Results r
JOIN Competitions c ON r.competitionId = c.id
WHERE r.average > 0
AND r.eventId = '222'
AND c.end_date >= '2022-01-01'
AND c.end_date <= '2022-12-31'
GROUP BY r.personId
ORDER BY bestAverage

Just replace the dates in the where clause with the dates you want to query.

Nice!
Both of these work great. They both give the same results if I use the same range.
But I think the 2nd one will allow me to fine tune the date range even more, if I want.

Much appreciated!

Now I wish the results weren't limited to 100/page max.
Wouldn't happen to be any way around that, is there? or is that a fixed limit of the site?

 

[tslwn]

I'd go for the second option for greater flexibility around dates.

As for the 100/page limit, I don't know. You could always download an export of the results and analyse the data with a spreadsheet: https://www.worldcubeassociation.org/export/results

 

[UncleFrank]

You could always download an export of the results and analyse the data with a spreadsheet: https://www.worldcubeassociation.org/export/results

I've tried that, but the Results.tsv file is 450mb, too big to open in a spreadsheet.
What I'd like is the RanksAverage.tsv file from that export, but limited to only results after 2014. That's what I was trying to do with these database queries.

I could download the .sql export but then I have no clue what to do with it. If I could query that with one of the above queries, that would be great.

 

[tslwn]

Hang on, I can do that in Python.

 

[tslwn]

Here's a set of RankAverage_Event CSV files for each event since 2014, e.g.

personId,personName,best
2018KHAN28,Zayn Khanani,101
2019WANY36,Yiheng Wang (王艺衡),108
2012PATE01,Antonie Paterakis,109

I haven't included the world, continent, and national ranks in the output, but they are sorted in ascending order of best average, so the row numbers indicate the world ranks.

 

[UncleFrank]

Here's a set of RankAverage_Event CSV files for each event since 2014, e.g.

personId,personName,best
2018KHAN28,Zayn Khanani,101
2019WANY36,Yiheng Wang (王艺衡),108
2012PATE01,Antonie Paterakis,109

I haven't included the world, continent, and national ranks in the output, but they are sorted in ascending order of best average, so the row numbers indicate the world ranks.

Nice!
Does that include 2014?
This is a big help.

If it's not too much work, can you do one more? Do the same thing for the RanksSingle file, best single. Can it output to a single file instead of a separate one for each event, and include a column for the event id, like the RanksSingle and RanksAverage files are?

Thanks!

 

[tslwn]

Nice!
Does that include 2014?
This is a big help.

If it's not too much work, can you do one more? Do the same thing for the RanksSingle file, best single. Can it output to a single file instead of a separate one for each event, and include a column for the event id, like the RanksSingle and RanksAverage files are?

Thanks!

Sure thing, I can do that tomorrow.

 

[tslwn]

Done: https://github.com/tslwn/wca/tree/main/output

The CSVs should match the format of the RanksAverage and RanksSingle tables exactly, i.e., they should include world, continent, and national ranks and person and event IDs.

 

[UncleFrank]

Nice!
Thanks1