You have a table of users and event times, and you need to find the shortest and longest per-user event time intervals:
drop table if exists t;
create table t( t timestamp, user smallint);
insert into t values
('2024-11-28 18:30:02', 1),('2024-11-28 18:30:05', 1),('2024-11-28 18:30:08', 1),
('2024-11-28 18:30:11', 1),('2024-11-28 18:30:15', 1),('2024-11-28 18:30:18', 1),
('2024-11-28 18:30:21', 1),('2024-11-28 18:30:23', 1),('2024-11-28 18:30:26', 1),
('2024-11-28 18:30:29', 2),('2024-11-28 18:30:32', 2),('2024-11-28 18:30:33', 2),
('2024-11-28 18:30:37', 2),('2024-11-28 18:30:40', 2),('2024-11-28 18:30:42', 2),
('2024-11-28 18:30:44', 2),('2024-11-28 18:31:01', 2),('2024-11-28 18:31:04', 2),
('2024-11-28 18:31:07', 2),('2024-11-28 18:31:10', 2);
One solution is to write a self-join on matching user and one timestamp less than the other, then in an outer query find the min(diff) and max(diff):
select t.user, min(diff), max(diff)
from (
select t.user, unix_timestamp(min(tnext.t))-unix_timestamp(t.t) as diff
from t
join t as tnext on t.user=tnext.user and t.t < tnext.t
group by t.user, t.t
) as t
group by user;
+------+-----------+-----------+
| user | min(diff) | max(diff) |
+------+-----------+-----------+
| 1 | 2 | 4 |
| 2 | 1 | 17 |
+------+-----------+-----------+
Thanks to Scott Noyes for noticing that the inner query must specify min(tnext.t). |
|