SpeedSolving.com results export

On this page we offer the SpeedSolving.com Weekly Competition results for download in several formats so you can use/analyze them at large. We will usually update once per week when the results are finalized. The files are numbered and dated so you can see whether you have the newest version already.

SpeedSolving Database Format

Original format as used by the Speedsolving Weekly Competition software.
SQL: SS_export_20190509.sql.zip (11.4 MB) (permalink)
SQL statements, for import into SQL databases.
TSV: SS_export_20190509.tsv.zip (10.5 MB) (permalink)
Tab-separated values, for spreadsheets in OpenOffice.org, Excel, etc.


WCA Database Format

SpeedSolving Weekly Competition data transformed to more or less match the database schema used by the WCA for their database exports.
SQL: SSWCA_export_20190509.sql.zip (9 MB) (permalink)
SQL statements in the WCA database schema, for import into WCA-compatible SQL databases.
TSV: SSWCA_export_20190509.tsv.zip (8.2 MB) (permalink)
Tab-separated values in the WCA database schema, for spreadsheets in OpenOffice.org, Excel, etc.


The included README.md file contains the terms of use and a general description:

# SpeedSolving.com – Results Database Export

- Date: May 09, 2019
- Export Format Version: 1.0.0
- Contact: Mike Hughey ([email protected])
- Website: https://www.speedsolving.com/competitions

## Description

This database export contains public information about the SpeedSolving.com
Weekly Competitions, which have been held weekly at the website since early
2007.

## Goal

The goal of this database export is to provide members of the speedcubing
community a practical way to perform analysis on competition information for
statistical and personal purposes.

## Allowed Use

The information in this file may be re-published, in whole or in part, as long
as users are clearly notified of the following:

> This information is based on competition results owned and maintained by 
> SpeedSolving.com, published at https://speedsolving.com/competitions/results
> as of May 09, 2019.

## Acknowledgements

The SpeedSolving.com Weekly Competition was first organized and run by Arnaud
van Galen in 2007. A database was created and a front end for competing was
provided by Oscar Roth Andersen in 2012.

The competition, database, and software are now maintained by Mats Bergsten
and Mike Hughey.

## Date and Format Version

The export contains a `metadata.json` file, with the following fields:

| Field                   | Sample Value                  |
|-------------------------|-------------------------------|
| `export_date`           | `"2018-09-19T05:04:58+02:00"` |
| `export_format_version` | `"1.0.0"`                     |
| `export_type`           | `"speedsolving.com"`          |

`export_type` contains either "speedsolving.com" or "WCA", depending on the type of export.

If you regularly process this export, we recommend that you check the
`export_format_version` value in your program and and review your code if the
major part of the version (the part before the first `.`) changes.

NOTE THE FOLLOWING API ENDPOINT IS NOT YET IMPLEMENTED!!!  (Will hopefully be implemented soon)
---
If you are processing the exported data using an automated system, we recommend
using a cron job to check the API endpoint at:
https://www.speedsolving.com/competitions/api/v0/export/public
You can use the `export_date` to detect if there is a new export, and the
`sql_url` and `tsv_url` will contain the URLs for the corresponding downloads.
---

## Format (version 1.0.0)

The database export consists of these tables:

| Table                                   | Contents                                           |
|-----------------------------------------|----------------------------------------------------|
| events                                  | Events that have been held at SpeedSolving.com     |
| scrambles                               | Scrambles                                          |
| userlist                                | Competitor ids and usernames                       |
| weeklyFmcSolves                         | Competitor-supplied FMC solutions and explanations |
| weeklyResults                           | Competition results per week+event+round+person    |

Most of the tables should be self-explanatory, but here are a few specific details:

### Scrambles

`scrambles` stores all scrambles. Scrambles for competitions prior to 2012 are not yet stored
in the database, but we intend to add them at some point in the future.

For all events with more than one scramble per week, the scramble entry in the database is
comprised of multiple newline-separated scrambles.However, newlines can cause compatibility
issues with TSV parsers. Therefore, in the TSV version of the data we replace each newline for
scrambles with the `|` character.

### Results

Values of the `Results` table can be interpreted as follows:

- The result values are in the following fields: `result`,  `solve1`, `solve2`, `solve3`,
`solve4`, `solve5`, `best`, and `average`.
- The value `-1` means DNF (Did Not Finish).
- The value `-2` means DNS (Did Not Start).
- The value `0` means "no result". For example a result in a `best-of-3` round
  has a value of `0` for the `value4`, `value5`, and `average` fields.
- Positive values depend on the event.
  - Most events have values represented in seconds. For example, 86.53 means 1 minute and
    26.53 seconds.
  - For fewest moves, the value stored is an integer, the number of moves.
    - Fewest moves averages are stored as 100 times the average, rounded.
  - Multibld results are stored in the `multiBLD` column, encoding the time as well
    as the number of cubes attempted and solved. The encoding is similar to, but not exactly
    the same as, that of the WCA. This is a decimal value, which can be interpreted ("decoded")
    as follows:

        0DDTTTTMM
        difference    = 99 - DD
        timeInSeconds = TTTT (9999 means unknown)
        missed        = MM
        solved        = difference + missed
        attempted     = solved + missed

    In order to encode data, use the following procedure:

        solved        = # cubes solved
        attempted     = # cubes attempted
        missed        = # cubes missed = attempted - solved
        DD            = 99 - (solved - missed)
        TTTT          = solve time in seconds
        MM            = missed

    Note that this is designed so that a smaller decimal value means a better
    result. This format does not support more than 99 attempted cubes, or times
    greater than 9999 seconds (about 2.77 hours).

### WCA export

The WCA export is provided so that software already written to support WCA exports can
also be used against the SpeedSolving.com export. Data from the SpeedSolving.com
database have been mapped to the WCA tables, as much as is possible. Fields that have no match
in the SpeedSolving.com database have been provided logical default values, as much as possible.