'Find max time difference between consecutive user transactions

I have a user transactions table that look like the following:

USER_ID    TRANSACTION_ID     TIMESTAMP
user1      id1                2022-01-19 15:21:27.000
user1      id2                2022-01-20 15:21:27.000
user1      id3                2022-01-25 15:21:27.000
user2      id1                2022-01-18 15:21:27.000
user2      id2                2022-01-28 15:21:27.000
user2      id3                2022-01-29 15:21:27.000

I'd like to write a query that could find the max time difference between each user's transaction times (days is preferred but anything else would do it as well). For the above the desired output would be:

USER_ID     MAX_DIFF
user1       5 (time diff between id2 and id3)
user2       10 (time diff between id1 and id2)

I have thought about ordering rows based on TIMESTAMP then use an approach similar to one posted here:

SELECT
    t1.ts,
    t1.number,
    t1.number - COALESCE(t2.number, t1.number) AS diff
FROM yourTable t1
LEFT JOIN yourTable t2
    ON t1.id = t2.id + 1
ORDER BY
    t1.ts;

Problem is that transaction ids in my table are random uuids and can not be used in such a fashion. Any help is much appreciated.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source