DECLARE @startDate nvarchar(MAX);

DECLARE @endDate nvarchar(MAX);


SET @startDate = '2020-01-07';


SET @endDate = '2020-01-10';


-- Esta é 2% mais rápida, mas usa coisas que eu não sei como se fazem no mysql
-- Pelo que eu tive a ver, tens de fazê-las à mão, por isso se calhar até acaba por ficar mais lenta

WITH playerScoring AS
  (SELECT s.PlayerId,
          s.Score,
          s.[Date]
   FROM dbo.[Snapshot] s),
     firstDateScoring AS
  (SELECT TOP 1 WITH TIES *
   FROM playerScoring snap
   WHERE [Date] >= @startDate
   ORDER BY ROW_NUMBER() OVER(PARTITION BY snap.PlayerId
                              ORDER BY snap.[Date] ASC)),
     secondDateScoring AS
  (SELECT TOP 1 WITH TIES *
   FROM playerScoring snap
   WHERE [Date] <= @endDate
   ORDER BY ROW_NUMBER() OVER(PARTITION BY snap.PlayerId
                              ORDER BY snap.[Date] DESC))
SELECT p.Id Id,
       p.[Username] Player,
       ss.Score - fs.Score Delta
FROM dbo.[Player] p
JOIN firstDateScoring fs ON fs.PlayerId = p.Id
JOIN secondDateScoring ss ON ss.PlayerId = p.Id;


-- Está é 2% mais lenta, mas é muito mais fácil de a transitar para mysql
-- Já agora, copiei esta na net. Google meu puto.

SELECT score.PlayerId Id,
       score.Player,
       score.EndScore-score.StartScore Delta
FROM
  (SELECT p.Id PlayerId,
          p.[Username] Player,

     (SELECT TOP 1 s.Score
      FROM dbo.[Snapshot] s
      WHERE s.[Date] >= @startDate
        AND s.PlayerId = p.Id
      ORDER BY s.[Date] ASC) AS StartScore,

     (SELECT TOP 1 s.Score
      FROM dbo.[Snapshot] s
      WHERE s.[Date] <= @endDate
        AND s.PlayerId = p.Id
      ORDER BY s.[Date] DESC) AS EndScore
   FROM dbo.Player p) score
WHERE score.EndScore IS NOT NULL
  AND score.StartScore IS NOT NULL