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.
Example:
select * from movies.ratings_csv limit 10;
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;
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.