Need advice on implementing statistics?

Question

MySQL

Good afternoon, can you tell me about the implementation:
There is a user who plays different games on the site. I need to keep statistics of his wins, defeats, rating and be able to output this for different games.
The task there, of course, is more complicated, I'll try to describe it in a nutshell
. The player enters the result on the site, and you need to do
1. Move him along the tournament grid.
2. Write down somehow in the statistics a victory, a defeat, a draw, a rating of statistics, so that later in the profile you can see his achievements in different games.
3. Make the issuance of awards, for example, for the 100th victory or something like that.
How do I organize the database
at the moment there are tables user and user_profile, I thought to make user_stat but how to write down there .... every game? or create a value when creating a user and then only update it, but how then to calculate the stat for a certain game.
Website made in Yii2

Answer 1

Separate table:
stat_id
user_id
game_id
user_vin
user_lost
user_reting
And then Join where necessary

Answer 2

, I thought to make user_stat but how to write there

Make a user_stat and record every game.

Answer 3

user: id, name
clan: id, name
game_result: id(u_int), user_id(u_int), opponent_id(u_int), game_id(u_int), isWinner(bool), score(longint), timestamp(timestamp), parent_game_result(u_int ), user_clan(u_int), opponent_clan(u_int)

Answer 4

you can increment the user's counters, for example, in the user_game_stat table (by triggers or in business logic when the result is known)
the structure, for example, can be like this (id, user_id, game_type, win_count, lost_count, total_games) - you can add a date field here so that it has infa according to the user's status for some period of time (for example, for today, or for yesterday, or in general for periods of 4 hours)
1. movement in what form? change of position in relation to the previous day? in general for every day concerning all?
- in the first case, it will be enough for you to add a field with a position for the previous period and for the current one and aggregate the data once every N period
- in the second, the logic is a little more complicated, but you can store an additional field with a rating at a point in time in user_game_stat, as a result, you can easily understand the positions of any players at a point in time in the game (you can easily get the top for any period within the minimum period) and from these on the same data, you can build a real top at the moment (i.e. a kind of aggregate, you can specifically for each game, but when there is a big load, you will eventually go into aggregates at intervals)
* it all depends on what exactly you need, maybe even a mixture of both options will be more appropriate, depends on the tasks
2. user_game_stat - stores game statistics for intervals (or global, depending on which way to go) - you can use it, but in general, you can display statistics for a specific game in the profile based on the games table (you have a connection with users and the result of the game ), or do you need counters? then you have a table with them for each game
3. counters for the game? (check at the moment the counter changes or every N time?) you can aggregate the data once an hour and see if the rewards are a completely different story :)
ps maybe even mysql is not the best solution for you, but in general, for an adequate assessment, there is little information about the task, about the load, but I hope in general the idea from the above is clear;) :)