List users, ordered by accuracy of soccer match predictions

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 |
+-----------+---------+-----------+----------------+---------------------+---------------+--------------------------+

• The ORed condition on the playday probably causes a bad plan, i would suggest splitting the query in two parts, UNION ALL one Select for the season and one for the matchday. Btw, if you add some Create/Inserts or provide a Fiddle it would be really helpfull. – dnoeth Mar 12 '17 at 11:53

Most of the joins currently happen in the sub-query within your SELECT statement. This is fine for a small number of records, but typically results in RBAR (row by agonizing row) performance.

For readability and to ensure we get that SET based performance we really want, consider the below query:

SELECT
p_p.points_id,
p_p.user_id,
p_p.league_playday,
pred_cnt.correctpredictions_count
FROM
predictions_points p_p
JOIN
(
SELECT
p.user_id,
m.league_playday,
COUNT(*) AS correctpredictions_count
FROM
predictions p
JOIN
matches m
ON m.match_id = p.match_id
WHERE
#Greater than 3 points is a correct prediction
p.points_amount >=3
GROUP BY
p.user_id,
m.league_playday
) pred_cnt
ON pred_cnt.user_id = p_p.user_id
#Get correct predictions for individual play days and the whole season (league_playday=0)
AND (pred_cnt.league_playday = p_p.league_playday OR p_p.league_playday=0)
WHERE
p_p.league_id=:league_id


Based on your above requirements, I believe you aggregate on the user_id and league_playday combination. The subquery in the JOIN will aggregate as a set first and then match back to prediction_points. I also added some comments in for the "magic numbers" just to make it more clear.

If you are just using the results to update your table, it looks like you have a primary key on points_id which would be all you need to return with the new count.

• With this suggested query, the value of correctpredictions_count is off (the value I'm getting is the amount of matches + amount of points instead of the amount of correct predictions) and the result set is ordered by user_id and league_playday instead of points and correct predictions. – Max Oct 9 '16 at 8:39
• My mistake, I misread the schema and see what you mean. I've updated the query to still follow the reasoning behind my original answer. I hope it is still useful to you and thank you for responding on the error! – vanlee1987 Oct 11 '16 at 18:36
• I also fixed the typos you suggested, thanks! – vanlee1987 Oct 11 '16 at 18:41
• Hi, I never got this query to work. I'm getting a lot of duplicates in the result set and some are missing. – Max Mar 11 '17 at 12:18