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