Diff grouped rows on SQL / AWS Athena and AWS Quicksight

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

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

Advertisement

Answer