MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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');
   
SELECT * FROM skills;
SELECT * FROM skill_scores;


id
name


1
skill 1

2
skill 2

3
skill 3




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


SET @current_date := '2020-07-08';
SET @interval := 1;
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;


player_id
name
score
gain


1
skill 1
20
7

1
skill 2
15
5

2
skill 1
17
5


SET @interval := 7;
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;


player_id
name
score
gain


1
skill 1
20
10

1
skill 2
15
14

2
skill 1
17
6


SET @interval := 31;
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;


player_id
name
score
gain


1
skill 1
20
10

1
skill 2
15
14

2
skill 1
17
6


SELECT DATEDIFF('2020-07-08 00:00:00', '2020-06-06 00:00:00')


DATEDIFF('2020-07-08 00:00:00', '2020-06-06 00:00:00')


32
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