5

I have a requirement to build a leaderboard of users of a fantasy football game. The simplified database of the game is as follows:

  • users have squad_players in squads for each matches (related by transfer_period)
  • players have match_points for each matches
  • squad_players have position, priority. The priority is the order of substitutions if a squad_players does not appear in a matches
  • squads have formations which determine the maximum number of players to select from of each position ordered by priority

The database is MySQL 5.6 and the maximum number of users is 10K.

I am able to inner join (in order) squad_players, squads, matches, match_points to get the point of each squad_players who played (players who didn't play have no match_points).

I am struggling to SUM the points of x squad_players of each squads where x is in formations and determined by position of the squad_players.

I have tried to modify versions of Top n-rows with group by with correlated subqueries but without success.

What should my approach be here?


My Expected Output is something like this

|---------|-----------|------------|----------|
| User ID |  SQUAD ID | POSITION   |  POINTS  |  
|---------|-----------|------------|----------|
|   12    |    34     | defender   |    12    |
|---------|-----------|------------|----------|
|   12    |    34     | forward    |    2     |
|---------|-----------|------------|----------|
|  117    |    31     | midfielder |    5     |
|---------|-----------|------------|----------|

Notes:

  • I must add the outputs may not reflect the problem statement.

  • If you look into the Sample Dataset Link, you will find a view named normalized_squad_player_points that is the denormalized table I want to work with to create different stats.

Final Output for the leaderboard will be something like this

|---------------------|------------------|
|      User ID        |       Points     |
|---------------------|------------------|
|          12         |         34       |
|---------------------|------------------|
|          56         |         32       |
|---------------------|------------------|
|          117        |         31       |
|---------------------|------------------|

Additional information

  • 1
    It would be much better if you provided the CREATE TABLE statements, sample data (INSERT a few rows) and wanted result set. Reading your question, it is not very clear which which names refer to tables and which to columns – ypercubeᵀᴹ May 2 '18 at 8:19
  • @ypercubeᵀᴹ , thanks for the suggesstion. Added a SQLfiddle (sqlfiddle.com/#!9/4559cd) for the schema – Sakibul Alam May 2 '18 at 8:33
  • @ypercubeᵀᴹ , added the expected outputs. But I must add the outputs may not reflect the problem statement. – Sakibul Alam May 2 '18 at 8:59
1

The solution I ended up implementing was to break up the query into simpler ones based on the different permutations of the positions and formations. I also needed to add several tables to store intermediate results. I ran all the queries (around 42) in a transaction.

This worked out pretty well and completed in reasonable time without overloading the server resources.

I guess trying to do it in one giant query was a bad decision from the beginning. Breaking it up into smaller queries allowed the process to be maintainable and flexible.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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