How do I get the date_diff from previous rows?

To find the difference in time between consecutive dates in a result set, Presto offers window functions. Take the example table below which contains sample data of users who watched movies.


select * from movies.ratings_csv limit 10;

Screen Shot 2021 08 24 at 4.11.28 PM

select userid, date_diff('day', timestamp, lag(timestamp) over (partition by userid order by  timestamp desc)) as timediff from ratings_csv order by userid desc limit 10;

Screen Shot 2021 08 24 at 4.14.36 PM

The lag(x, y, start, end) function fetches the value of column x at row offset y and calculates the difference. When no offset is provided, the default value is 1 (previous row). Notice, that the first row in timediff is NULL due to not having a previous row.