Artful Common Queries

Find shortest & longest per-user event time intervals

from the Artful Common Queries page

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
('2014-11-28 18:30:02',  1),('2014-11-28 18:30:05',  1),('2014-11-28 18:30:08',  1),
('2014-11-28 18:30:11',  1),('2014-11-28 18:30:15',  1),('2014-11-28 18:30:18',  1),
('2014-11-28 18:30:21',  1),('2014-11-28 18:30:23',  1),('2014-11-28 18:30:26',  1),
('2014-11-28 18:30:29',  2),('2014-11-28 18:30:32',  2),('2014-11-28 18:30:33',  2),
('2014-11-28 18:30:37',  2),('2014-11-28 18:30:40',  2),('2014-11-28 18:30:42',  2),
('2014-11-28 18:30:44',  2),('2014-11-28 18:31:01',  2),('2014-11-28 18:31:04',  2),
('2014-11-28 18:31:07',  2),('2014-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).

Last updated 17 Aug 2019