MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.30


create table event 
  (event_user varchar(10), event_ts timestamp,
  event_class varchar(20));
 SELECT TIMESTAMP'2023^04^30 11*30';


TIMESTAMP'2023^04^30 11*30'


2023-04-30 11:30:00


insert into event
values('hans', TIMESTAMP'2023^04^30 11*30', 'landing'),
('frans', TIMESTAMP'2023^04^30 11*31', 'registration'),
('frans', TIMESTAMP'2023^04^30 11*32', 'login'),
('hans', TIMESTAMP'2023^04^30 11*33', 'login'),
('hansi', TIMESTAMP'2023^04^30 11*34', 'profile'),
('hansi', TIMESTAMP'2023^04^30 11*35', 'security'),
('hans', TIMESTAMP'2023^04^30 11*36', 'profile'),
('hansi', TIMESTAMP'2023^04^30 11*37', 'security 2f');

Records: 8  Duplicates: 0  Warnings: 8


-- basic statement
-- using window functions to find specific data constellation
-- might be helpful with special situations
-- and gives an idea about arising problems in data retrival
select ec.*,
       lead(event_user,1) over (order by event_user, event_ts) event_user_lead,
       lead(event_ts,1) over (order by event_user, event_ts) event_ts_lead,
       lead(event_class,1) over (order by event_user, event_ts) event_class_lead
  from event ec
 where event_class in ('login', 'profile');


event_user
event_ts
event_class
event_user_lead
event_ts_lead
event_class_lead


frans
2023-04-30 11:32:00
login
hans
2023-04-30 11:33:00
login

hans
2023-04-30 11:33:00
login
hans
2023-04-30 11:36:00
profile

hans
2023-04-30 11:36:00
profile
hansi
2023-04-30 11:34:00
profile

hansi
2023-04-30 11:34:00
profile
null
null
null


select x.* ,                                -- calculate timespan of two events
       timestampdiff (second, x.event_ts, x.event_ts_lead ) as timespan
  from (
select ec.*,                                -- using same window functions like above
       lead(event_user,1) over (order by event_user, event_ts) event_user_lead,
       lead(event_ts,1) over (order by event_user, event_ts) event_ts_lead,
       lead(event_class,1) over (order by event_user, event_ts) event_class_lead
  from event ec
 where event_class in ('login', 'profile')
       ) x
where x.event_user = x.event_user_lead      -- applying crucial comparison criteria


event_user
event_ts
event_class
event_user_lead
event_ts_lead
event_class_lead
timespan


hans
2023-04-30 11:33:00
login
hans
2023-04-30 11:36:00
profile
180


-- same intention, different approach
-- might be more powerful with lots of noisy and similar data (millions of intermediate records) 
--   (which is not given in example data set)
select el.*,
       ep.event_ts as event_ts_ep,
       ep.event_class as event_class_ep,
       timestampdiff (second, el.event_ts, ep.event_ts ) as timespan
  from event el join event ep 
    on el.event_user = ep.event_user 
   and el.event_class = 'login' 
   and ep.event_class = 'profile'
   and el.event_ts < ep.event_ts  -- or other criteria


event_user
event_ts
event_class
event_ts_ep
event_class_ep
timespan


hans
2023-04-30 11:33:00
login
2023-04-30 11:36:00
profile
180


-- same intention, different approach
-- might be more powerful with lots of noisy and similar data
select el.*,
       ep.event_ts as event_ts_ep,
       ep.event_class as event_class_ep,
       timestampdiff (second, el.event_ts, ep.event_ts ) as timespan
  from event el join event ep 
    on el.event_user = ep.event_user 
   and el.event_class = 'login' 
   and ep.event_class = 'profile'      
-- the condition below could be used on large datasets avoiding aggregations with large results
                                       -- here we can play with timespan variations
   and timestampdiff (second, el.event_ts, ep.event_ts ) between 120 and 190
                                       -- depending on how much matching events are wanted
   -- and timestampdiff (second, el.event_ts, ep.event_ts ) between 120 and 179
   --     uncomment the one above to understand the use of the parameters
   -- and timestampdiff (second, el.event_ts, ep.event_ts ) < 180
   --     or this more simple example 


event_user
event_ts
event_class
event_ts_ep
event_class_ep
timespan


hans
2023-04-30 11:33:00
login
2023-04-30 11:36:00
profile
180
event_user	event_ts	event_class	event_user_lead	event_ts_lead	event_class_lead
frans	2023-04-30 11:32:00	login	hans	2023-04-30 11:33:00	login
hans	2023-04-30 11:33:00	login	hans	2023-04-30 11:36:00	profile
hans	2023-04-30 11:36:00	profile	hansi	2023-04-30 11:34:00	profile
hansi	2023-04-30 11:34:00	profile	null	null	null