Diff grouped rows on SQL / AWS Athena and AWS Quicksight

Question

I have this data about user tracking (the user and session is not ordered):

user   session    cummulative_time_spent
A      1          2
A      1          5
A      1          10
A      2          3
B      5          1
B      5          200
B      6          5
B      6          6

I want to create time spent that difference the rows that grouped by session. My expected result is like this:

user   session    cummulative_time_spent    duration
A      1          2                         2
A      1          5                         3
A      1          10                        5
A      2          3                         3
B      5          1                         1
B      5          200                       199
B      6          5                         5
B      6          6                         1

I don't know how to do that on SQL (AWS Athena) or Quicksight.

Answer 1

You can use lag():

select
    t.*,
    cumulative_time_spent 
        - coalesce(
            lag(cumulative_time_spent) 
                over(partition by user, session order by cumulative_time_spent), 
            0
        ) duration
from mytable

Answer 2

As GMB points out, the function you want is lag(). However, it has a three-argument form that simplifies the calculation:

select t.*,
       (cumulative_time_spent -
        lag(cumulative_time_spent, 1, cumulative_time_spent) over
           (partition by user, session order by cumulative_time_spent)
       ) as duration
from mytable