title: using window function lag() to calculate percentage change
body: <p>I'm trying to calculate the difference between each row that each shares a specific attribute. For example, I have a week's worth of playtime of three games (RDR2, GTA5, Spyro) and I have calculated what percent of each day is spent on each game and I want to see what the percent change is day over day. </p>
  
  <p>Hence, I wrote:</p>
  
  <p><code>percent - LAG(percent, 1) OVER (PARTITION BY game ORDER BY time DESC) AS games_delta</code></p>
  
  <p>but instead of getting the desired output of:</p>
  
  <pre><code> |-------------------------------|
   | TIME    GAME   PERCENT  DELTA |
   |-------------------------------|
   | Dec 10  RDR2   25       15    |
   | Dec 10  GTA5   50       40    |
   | Dec 10  Spyro  25      -55    |
   | Dec 9   RDR2   10      -22    |
   | Dec 9   GTA5   10       10    |
   | Dec 9   Spyro  80       13    |
   | Dec 8   RDR2   33       ...
   | Dec 8   GTA5   0        ...
   | Dec 8   Spyro  67       ...
  </code></pre>
  
  <p>I get multiplicities of the games and totally wrong <code>game-delta</code>!:</p>
  
  <pre><code> |-------------------------------|
   | TIME    GAME   PERCENT  DELTA |
   | Dec 10  RDR2   25       2     |
   | Dec 10  RDR2   25       4     |
   | Dec 10  RDR2   25      -4     |
   | Dec 10  RDR2   25       4     |
   | Dec 10  GTA5   10       5  ...
  </code></pre>
  
  <p>Any suggestions? Thanks in advance!</p>
  
  <p><em>Edit:</em></p>
  
  <pre><code>SELECT  time,
          game,
          percent,
          games_delta
  </code></pre>
  
  <p>I also have the following in my <code>GROUP BY</code> and <code>ORDER BY</code>:</p>
  
  <pre><code>GROUP BY time, game, percent, games_delta
  ORDER BY time DESC
  </code></pre>
  
  <p><em>Edit 2:</em></p>
  
  <p>Here's my CTE: </p>
  
  <pre><code>cte_table4 (time, games_delta) AS
  (
      SELECT time,
              percent - LAG(percent, 1) OVER (PARTITION BY game ORDER BY time DESC) AS games_delta
  
      FROM cte_table3
  )
  </code></pre>