Find max time difference between consecutive user transactions

user_2827771 0
Viewed 57

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.

mysql sql
1 Answers

The query can use LAG() to peek at the previous row (according to any ordering) and compute the time difference. Then it can use MAX to find the maximum difference between rows:

For example:

  SELECT user_id, MAX(diff) AS max_diff
  FROM (
    SELECT *,
      TIMESTAMPDIFF(second, LAG(ts) 
        OVER(PARTITION BY user_id ORDER BY ts), ts) AS diff,
      LAG(transaction_id) OVER(PARTITION BY user_id ORDER BY ts) AS prev_tx_id
    FROM t
  )
  GROUP BY user_id
  HAVING max_diff IS NOT NULL
user_64361910
Related