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.


3 Answers 3


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!

    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

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

or even deeper.


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.