What is the best way to store data for statistics?

Question

MySQL

Points are awarded to users on the site, I need to display the stat for a day, a week, two weeks, a month, etc.
Those. how much they added and in what category.
I thought of only this: for each score, write in a separate table, id, score, category, date. But I'm afraid it's stupid or cumbersome. Smart people, tell me, please, this option is very bad and how is it better?

Answer 1

Normal.
If there is too much such data, you can switch to another subd, such as a clickhouse.

Answer 2

RabbitMQ/Kafka - fire an event of the "action such and such" type there for those actions that affect statistics.
Create several listeners that recalculate statistics and add them to these tables (yes, they should be of the types day, week, month, year as you need there)
When outputting, you simply get records and calculate the difference
You can recalculate statistics by cron, but with a large volume may have overlays

Answer 3

You can save simply by a specific formula. What is the person's current average score and how many parts does it consist of. For example, there were 3 achievements in total with scores of 3, 2 and 1. The average score is 2. We keep 2 as the average score and 3 as the number of achievements. Next comes the score "5", and we have stored 2(as average score) and 3(number) => 2 * 3 + 5(new score) = 11 / 4 = 2.75. We keep 2.75 and increase the count by 1.
What to do with these numbers is up to you. Save and update by weeks, months, years. It won't take up a lot of space.