'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 |
|---|
