how to track score gains in mysql

Question

I would like to display a players current score as well as how many points they have gained within a selected time frame.

I have 2 tables

skills table

+----+---------+---------------------+
| id |  name   |     created_at      |
+----+---------+---------------------+
|  1 | skill 1 | 2020-06-05 00:00:00 |
|  2 | skill 2 | 2020-06-05 00:00:00 |
|  3 | skill 3 | 2020-06-05 00:00:00 |
+----+---------+---------------------+

scores table

+----+-----------+----------+-------+---------------------+
| id | player_id | skill_id | score |     created_at      |
+----+-----------+----------+-------+---------------------+
|  1 |         1 |        1 |     5 | 2020-06-06 00:00:00 |
|  2 |         1 |        1 |    10 | 2020-07-06 00:00:00 |
|  3 |         1 |        2 |     1 | 2020-07-06 00:00:00 |
|  4 |         2 |        1 |    11 | 2020-07-06 00:00:00 |
|  5 |         1 |        1 |    13 | 2020-07-07 00:00:00 |
|  6 |         1 |        2 |    10 | 2020-07-07 00:00:00 |
|  7 |         2 |        1 |    12 | 2020-07-07 00:00:00 |
|  8 |         1 |        1 |    20 | 2020-07-08 00:00:00 |
|  9 |         1 |        2 |    15 | 2020-07-08 00:00:00 |
| 10 |         2 |        1 |    17 | 2020-07-08 00:00:00 |
+----+-----------+----------+-------+---------------------+

my expected results are:-

24 hour query

+-----------+---------+-------+------+
| player_id |  name   | score | gain |
+-----------+---------+-------+------+
|         1 | skill 1 |    20 |    7 |
|         1 | skill 2 |    15 |    5 |
+-----------+---------+-------+------+

7 day query

+-----------+---------+-------+------+
| player_id |  name   | score | gain |
+-----------+---------+-------+------+
|         1 | skill 1 |    20 |   10 |
|         1 | skill 2 |    15 |   14 |
+-----------+---------+-------+------+

31 day query

+-----------+---------+-------+------+
| player_id |  name   | score | gain |
+-----------+---------+-------+------+
|         1 | skill 1 |    20 |   15 |
|         1 | skill 2 |    15 |   14 |
+-----------+---------+-------+------+

so far I have the following, but all this does is return the last 2 records for each skill, I am struggling to calculate the gains and the different time frames

SELECT player_id, skill_id, name, score 
FROM (SELECT player_id, skill_id, name, score, 
      @skill_count := IF(@current_skill = skill_id, @skill_count + 1, 1) AS skill_count,
      @current_skill := skill_id 
      FROM skill_scores 
      INNER JOIN skills 
        ON skill_id = skills.id 
      WHERE player_id = 1
      ORDER BY skill_id, score DESC
     ) counted 
WHERE skill_count <= 2

I would like some help figuring out the query I need to build to get the desired results, or is it best to do this with php instead of in the db?

EDIT:-

MYSQL 8.0.20 dummy data id's are primary_key auto increment but I didnt ad that for simplicity:-

CREATE TABLE skills
   (
      id bigint,
      name VARCHAR(80)
   );
   
   CREATE TABLE skill_scores
   (
      id bigint,
      player_id bigint,
      skill_id bigint, 
      score bigint,
      created_at timestamp
   );
 
   INSERT INTO skills VALUES (1, 'skill 1'); 
   INSERT INTO skills VALUES (2, 'skill 2'); 
   INSERT INTO skills VALUES (3, 'skill 3'); 
 
   INSERT INTO skill_scores VALUES (1, 1, 1 , 5, '2020-06-06 00:00:00');
   INSERT INTO skill_scores VALUES (2, 1, 1 , 10, '2020-07-06 00:00:00');
   INSERT INTO skill_scores VALUES (3, 1, 2 , 1, '2020-07-06 00:00:00');
   INSERT INTO skill_scores VALUES (4, 2, 1 , 11, '2020-07-06 00:00:00');
   INSERT INTO skill_scores VALUES (5, 1, 1 , 13, '2020-07-07 00:00:00');
   INSERT INTO skill_scores VALUES (6, 1, 2 , 10, '2020-07-07 00:00:00');
   INSERT INTO skill_scores VALUES (7, 2, 1 , 12, '2020-07-07 00:00:00');
   INSERT INTO skill_scores VALUES (8, 1, 1 , 20, '2020-07-08 00:00:00');
   INSERT INTO skill_scores VALUES (9, 1, 2 , 15, '2020-07-08 00:00:00');
   INSERT INTO skill_scores VALUES (10, 2, 1 , 17, '2020-07-08 00:00:00');

user_10138734 · Answer

WITH cte AS (
SELECT id, player_id, skill_id,
       FIRST_VALUE(score) OVER (PARTITION BY player_id, skill_id ORDER BY created_at DESC) score, 
       FIRST_VALUE(score) OVER (PARTITION BY player_id, skill_id ORDER BY created_at DESC) - FIRST_VALUE(score) OVER (PARTITION BY player_id, skill_id ORDER BY created_at ASC) gain,
       ROW_NUMBER() OVER (PARTITION BY player_id, skill_id ORDER BY created_at DESC) rn
FROM skill_scores
WHERE created_at BETWEEN @current_date - INTERVAL @interval DAY AND @current_date
)
SELECT cte.player_id, skills.name, cte.score, cte.gain
FROM cte
JOIN skills ON skills.id = cte.skill_id
WHERE rn = 1
ORDER BY player_id, name;

fiddle

Ps. I don't understand where gain=15 is taken for 31-day period - the difference between '2020-07-08 00:00:00' and '2020-06-06 00:00:00' is 32 days.

user_12084446 · Answer

Well i think you need a (temporary) table for this. I will call it "player_skill_gains". Its basically the players skills ordered by created_at and with an auto_incremented id:

CREATE TABLE player_skill_gains
(`id` int PRIMARY KEY AUTO_INCREMENT NOT NULL
                                          , `player_id` int
                                         , skill_id int
                                         , score int
                                         , created_at date)
;

INSERT INTO player_skill_gains(player_id, skill_id, score, created_at)
SELECT player_skills.player_id AS player_id
  , player_skills.skill_id
  , SUM(player_skills.score) AS score
, player_skills.created_at
FROM player_skills
GROUP BY player_skills.id, player_skills.skill_id, player_skills.created_at
ORDER BY player_skills.player_id, player_skills.skill_id, player_skills.created_at ASC;

Using this table we can relatively easily select the last skill for each row (id-1). Using this we can calculate the gains:

SELECT player_skill_gains.player_id, skills.name, player_skill_gains.score
  , player_skill_gains.score - IFNULL(bef.score,0) AS gain
  , player_skill_gains.created_at
FROM player_skill_gains
  INNER JOIN skills ON player_skill_gains.skill_id = skills.id
  LEFT JOIN player_skill_gains AS bef ON (player_skill_gains.id - 1) = bef.id
      AND player_skill_gains.player_id = bef.player_id
      AND player_skill_gains.skill_id = bef.skill_id

For the different queries you want to have (24 hours, 7 days, etc.) you just have to specify the needed where-part for the query.

You can see all this in action here: http://sqlfiddle.com/#!9/1571a8/11/0

how to track score gains in mysql

2 Answers