#StackBounty: #sql #mysql List users, ordered by accuracy of soccer match predictions

Bounty: 100

I have a database filled with predictions of soccer matches. I need a solution to calculate the rankings from the database. There are 2 rankings: one for the entire season (playday=0) and one for each matchday (called playday in the code).

I have 3 tables:

  1. matches
  2. predictions
  3. predictions_points

To give you a better insight in the database, here’s some example data:

matches: contains soccer matches information.

+----------+--------------+---------------------+------------+-----------+----------------+--------------+-----------------+--------------+-----------------+
| match_id | match_status |   match_datetime    | match_info | league_id | league_playday | home_team_id | home_team_score | away_team_id | away_team_score |
+----------+--------------+---------------------+------------+-----------+----------------+--------------+-----------------+--------------+-----------------+
|        1 |            3 | 2016-07-29 20:30:00 |            |         1 |              1 |            1 |               0 |            2 |               2 |
|        2 |            3 | 2016-07-30 18:00:00 |            |         1 |              1 |            5 |               1 |            4 |               2 |
|        3 |            3 | 2016-07-30 20:00:00 |            |         1 |              1 |            3 |               1 |            6 |               0 |
|        4 |            3 | 2016-07-30 20:00:00 |            |         1 |              1 |            7 |               3 |            8 |               0 |
+----------+--------------+---------------------+------------+-----------+----------------+--------------+-----------------+--------------+-----------------+

predictions: contains users predictions and the amount of points received per prediction.

+---------------+----------+---------+-----------------+-----------------+--------------------+
| prediction_id | match_id | user_id | home_team_score | away_team_score | predictions_points |
+---------------+----------+---------+-----------------+-----------------+--------------------+
|             1 |        1 |       1 |               0 |               1 |                  1 |
|             2 |        2 |       1 |               1 |               2 |                  3 |
|             3 |        3 |       1 |               2 |               0 |                  1 |
|             4 |        4 |       1 |               2 |               0 |                  1 |
|             5 |        1 |       2 |               0 |               2 |                  3 |
|             6 |        2 |       2 |               1 |               2 |                  3 |
|             7 |        3 |       2 |               1 |               0 |                  3 |
|             8 |        4 |       2 |               0 |               0 |                  0 |
+---------------+----------+---------+-----------------+-----------------+--------------------+

predictions_points contains the points per playday (or entire season when playday = 0) and the ranking (which we can not use for the query).

+-----------+---------+-----------+----------------+---------------------+---------------+
| points_id | user_id | league_id | league_playday | league_user_ranking | points_amount |
+-----------+---------+-----------+----------------+---------------------+---------------+
|         1 |       1 |         1 |              0 |                   2 |            51 |
|         2 |       2 |         1 |              0 |                   1 |            59 |
|         3 |       1 |         1 |              1 |                   2 |             6 |
|         4 |       2 |         1 |              1 |                   1 |             9 |
+-----------+---------+-----------+----------------+---------------------+---------------+

If there is a draw (in amount of points between users), I want to order them based on the amount of predictions they had 100% correct (they earned 1 point for a prediction with wrong score but correct win/draw/loss – and earned at least 3 points for a correct score).

(Please note that the league_user_ranking field from the predictions_points table gets updated based on the result set of this query. So we can not use it for the query.)

The following query works, but I feel like there’s room for improvement:

     SELECT *, (
        SELECT COUNT(*) FROM predictions p
            INNER JOIN matches m
            ON m.match_id = p.match_id
        WHERE p.user_id=p_p.user_id 
        AND (m.league_playday=p_p.league_playday OR p_p.league_playday=0)
        AND p.prediction_points>=3
     ) AS correctpredictions_count
     FROM
     predictions_points p_p
     WHERE
     p_p.league_id=:league_id
     ORDER BY
     p_p.league_playday ASC, p_p.points_amount DESC, correctpredictions_count DESC

UPDATE/EDIT: I see that my question got bumped to the homepage. I am live-testing the code with 15 other soccer enthousiasts based on the results of the current Belgian soccer season. At the moment, this query takes about 10 seconds on a database with 3000 predictions (15 users, 8 matches per playday, 30 playdays) on a Raspberry Pi 3 running Raspbian Lite.

Expected result set:

    +-----------+---------+-----------+----------------+---------------------+---------------+--------------------------+
    | points_id | user_id | league_id | league_playday | league_user_ranking | points_amount | correctpredictions_count |
    +-----------+---------+-----------+----------------+---------------------+---------------+--------------------------+
    |         2 |       2 |         1 |              0 |                   1 |            59 |                        7 |
    |         1 |       1 |         1 |              0 |                   2 |            51 |                        6 |
    |         4 |       2 |         1 |              1 |                   1 |             9 |                        2 |
    |         3 |       1 |         1 |              1 |                   2 |             6 |                        1 |
    |         5 |       1 |         1 |              2 |                   1 |             7 |                        2 |
    |         6 |       2 |         1 |              2 |                   2 |             7 |                        1 |
    +-----------+---------+-----------+----------------+---------------------+---------------+--------------------------+


Get this bounty!!!