Add your own statistics

You can add your own statistics from plain SQL or any programming language with POST capabilities you like.

It is suggested that you setup a local database using the develop export (you can find instructions here in this repository or in the main one).

Plain SQL

You'll need to wrap it in yaml format for some configuration. You'll find a lot of examples here.

General YAML structure

title: Statistics title
explanation: Some context for whoever reads your statistics.
displayMode: How you want your statistics to be displayed. DEFAULT (all statistics are shown), SELECTOR (lets the user to use a select)
groupName: For grouping statistics (better user experience). Eg.: Competitors, Countries, Events, Results
queries: List of queries to be executed

Query structure

headers: # Statistics table headers
  - Header 1
  - Header 2
keys: # Used for statistics display, like the one that will be displayed in the selector
  - key 1
keyColumnIndex: 0 # If your statistics can be grouped within the query result, you can provide an index instead of a custom key. Passing 0, the first column will be used as key.
showPositions: true or false
positionTieBreakerIndex: In case of ties, a dash will be used for consecutive lines considering this column
sqlQuery: |- # The query to be executed
  select * from Results r
  where r.eventId = '333'
sqlQueryCustom: |- # The user will be redirected for the query database page with a placeholder in every :CAPITAL_WITH_COLON.
  select * from Results r
  where r.eventId = ':EVENT_ID'

Step by step

This assumes you already completed local database setup.

• Write your statistics yaml request

title: First names
groupName: Competitors
queries:
  - keys:
      - Person
    showPositions: true
    headers:
      - Person
      - ID
    sqlQuery: |-
        select name, p.id from Persons p 
        order by name
        limit 5
    sqlQueryCustom: |-
        select name, p.id from Persons p
        where countryId = ':COUNTRY'
        order by name
        limit 5
  - keys:
      - Email
    showPositions: true
    headers:
      - Person
      - ID
    sqlQuery: |-
        select * from users u
        order by email
        limit 5
    sqlQueryCustom: |-
        select * from users u
        where u.country_iso2 = ':COUNTRY_ISO'
        order by email
        limit 5

• Save the file as .yml in the folder server/src/main/resources/statistics-request-list. Example: query-example.yml

• Start the server

    cd server
    ./gradlew bootRun
  

• You can optionally start the frontend to check your statistics

    cd client
    yarn start:local
  

• Go to http://localhost:8080/swagger-ui/index.html#/statistics-controller-impl/generateFromSqlUsingPOST, click try it out

• In filename, write query-example and hit execute.

• Check it in the frontend by visiting http://localhost:3000/statistics-list

Reference

https://statistics-api.worldcubeassociation.org/swagger-ui/index.html#/statistics-controller-impl/sqlToStatisticsUsingPOST

Any programming language

• Start the server

    cd server
    ./gradlew bootRun
  

• You can optionally start the frontend to check your statistics

    cd client
    yarn start:local
  

• Write your software in any programming language you like following the structure documented in

https://statistics-api.worldcubeassociation.org/swagger-ui/index.html#/statistics-controller-impl/createStatisticsUsingPOST

• Content is the table result

• Post it to

http://localhost:8080/statistics/create