MySQL query to eloquent

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-09 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 |
+-----------+---------+-------+------+

I have the following mysql query:-

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 AND player_id=1
)

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;

which returns the data correctly.

What is the best way to set this up in laravel?

so far I have setup a hasMany relationship between player and scores and have a scopeWilthskills to return the skill name but now I need to filrer the results like in the query above.