Database design for monthly leaderboards

Question

I have a system where users can make predictions on events. There are about two events a week. When the event ends the system scores all the predictions and updates this in the database. Here is an approximate representation of my current DB strucutre:

Users table

ID	username	score
1	alice	12
2	bob	22

Games table

ID	gameTitle	gameTime	questions	answers
1	Football match	2019-08-11 14:00:00	{json}	{json}
2	Hockey game	2019-07-11 15:00:00	{json}	{json}

Predictions table:

ID	gameID (FK)	userID (FK)	prediction	score	rank
1	1	1	{json}	6	1
2	1	2	{json}	4	2
3	2	1	{json}	2	2

So initially 'score' and 'rank' in predictions are NULL, and then after the event ends I run a script that goes through all entries for that game and scores it for the user and generates a rank.

With this structure I can do things like:

• List the highest scoring users of all time (SELECT from USERS, score descending)
• List the rankings for each event (SELECT rank from PREDICTIONS where gameID = X, descending)

However, what I now want to do is be able to get time-constrained rankings. So I want to be able to see which users scored highest for all events in the current month, or for the month of February etc.

I know I could do this manually by going through the Games table and getting all gameIDs that are in the given month, and then going through the Predictions table for all predictions with these IDs, and adding up the score for each user and then sending this. However, that seems crazily inefficient and resource-intensive. Especially considering that this will be an API call

I'm therefore wondering how I can accommodate this in my current database - if it's possible and/or advised, or if I should use some other technology.

• One idea would be have to generate a new table for each month, and update and pull from this when relevant.

• Another would be to have a 'monthly score' column in the User table, that resets each month. However, this wouldn't have historical data, which would be nice (though I guess could always be generated)

I feel like there might be some database paradigm trick that I'm missing, so it'd be great to hear a proposed solution for this.

Answer 1

After thinking around a bit more, a solution I think could work would be to have a new table of monthlyScores, structured like this:

+----+--------------+----------+--------+
| ID |  userID (FK) |   month  |  score |
+----+--------------+----------+--------+
|  1 |            1 |  03/2019 |     12 |
|  2 |            1 |  04/2019 |     23 |
|  3 |            2 |  03/2019 |      3 |
|  4 |            1 |  05/2019 |     22 |
|  5 |            2 |  06/2019 |     11 |
+----+--------------+----------+--------+

Where month is the current month, I could increment this value for the current months' games, thus meaning I could provide a leaderboard of the leaders for the current month. Similarly I could display scores for each user for previous months.

Posting this here as an answer as I feel like it could work. That said, as you can probably gather I'm very new to databases and so would be interested to hear about other solutions, and/or if this is advisable!

Answer 2

SELECT ...
    FROM ( getting all gameIDs that are in the given month ) AS x
    JOIN ... ON ...

See how you can use a "derived table" to do the first step of your chain? It is a simple SELECT gameIDs FROM ... WHERE ....

You may end up with

SELECT ...
    FROM ( SELECT ...
               FROM ( SELECT ...
                    ) AS x
         ) AS y

or even deeper.

Answer 3

I was able accomplish this with the following query:

SELECT u.username, SUM(score) as monthlyScore
FROM ( SELECT id FROM `games` WHERE MONTH(gameTime) = 8 ) g
INNER JOIN `predictions` p
    on g.id = p.gameId
INNER JOIN `users` u
    on p.userId = u.id
    GROUP BY userId  
ORDER BY `monthlyScore`  DESC

I'm not sure how this ranks in terms of efficiency. This query will called every time a user loads the leaderboards page, but it works.