Lichess correlation script

#!/bin/bash

# quick and dirty script to calculate rating correlation across rapid &
# classical time control for users in a lichess team. depends on jq,
# mysql, and wget

TEAMID="lichess-swiss"
USERLISTURL="https://lichess.org/api/team/$TEAMID/users"
MINGAMES=30
USERFILE="users"
CSVFILE="users.rating.csv"

MYSQLUSER="mysql"
MYSQLPASSWD="mysql"
MYSQLDB="lichess_rating"
MYSQLTABLE="users"

if [ ! -e "$CSVFILE" ]
then
    if [ ! -e "$USERFILE" ]
    then
        # this can take a very long time; to get a smaller sample you can CTRL-V and then examine
        # users.<hash>.part. to calculate i looked at the first 17725 users.
        #wget $USERLISTURL

        echo "Please download a portion of $USERLISTURL" 2>&1
        exit 1
    fi

    touch $CSVFILE
    echo "Processing file $USERFILE ..." 2>&1
    count="1"

    for userProf in $(grep \"id\"  users)
    do
        if [ "$(( $count%10 ))" == "0" ]
        then
            echo "    $count lines processed...." 2>&1
        fi

        echo "$userProf" | jq -r '.  | [.id, .perfs.rapid.games, .perfs.rapid.rating, .perfs.classical.games, .perfs.classical.rating] | @csv' >> $CSVFILE 2>/dev/null

        count=$(( $count + 1 ))
    done
fi

cp $CSVFILE /tmp
chmod 644 /tmp/$CSVFILE

mysql -u$MYSQLUSER -p$MYSQLPASSWD -e "CREATE DATABASE $MYSQLDB"
mysql -u$MYSQLUSER -p$MYSQLPASSWD $MYSQLDB -e "CREATE TABLE $MYSQLTABLE ( id varchar(255), rapid_games int, rapid_rating double, classical_games int, classical_rating double )"
mysql -umysql -pmysql $MYSQLDB --local_infile=1 -e "LOAD DATA INFILE '/tmp/$CSVFILE' INTO TABLE $MYSQLTABLE FIELDS TERMINATED BY ','"

echo "The correlation between rapid and classical ratings from users with at least $MINGAMES games in both is:"

mysql -umysql -pmysql $MYSQLDB -e "SELECT (AVG(rapid_rating * classical_rating) - (AVG(rapid_rating) * AVG(classical_rating))) / (STDDEV(rapid_rating) * STDDEV(classical_rating)) AS 'Pearsons r' FROM $MYSQLTABLE WHERE classical_games>$MINGAMES AND rapid_games>$MINGAMES"

echo "The average rating difference between rapid and classical ratings from users with at least $MINGAMES games in both is:"

mysql -umysql -pmysql $MYSQLDB -e "SELECT AVG(rating_diff) FROM (SELECT id, ABS(classical_rating - rapid_rating) AS rating_diff FROM users WHERE classical_games>30 AND rapid_games>30 ORDER BY rating_diff) a"

mysql -u$MYSQLUSER -p$MYSQLPASSWD -e "DROP DATABASE $MYSQLDB"