MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- these examples correspond to the request:
-- https://www.datenbankforum.com/threads/big-query-ga4-kundensegmente-mit-zeitlicher-abhaengigkeit-mit-sql-bilden.6788/post-41722
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
('user1', TIMESTAMP'2023^04^21 13*00*00', 'Page_View1'),
('user1', TIMESTAMP'2023^04^21 13*05*00', 'Page_View1'),
('user1', TIMESTAMP'2023^04^21 13*05*30', 'Page_View3'),
('user1', TIMESTAMP'2023^04^21 13*06*00', 'Page_View2'),
('user2', TIMESTAMP'2023^04^21 13*00*00', 'Page_View2'),
('user2', TIMESTAMP'2023^04^21 13*10*00', 'Page_View1'),
('user3', TIMESTAMP'2023^04^21 13*00*00', 'Page_View1'),
('user3', TIMESTAMP'2023^04^21 14*00*00', 'Page_View2'),
('user4', TIMESTAMP'2023^04^21 13*00*00', 'Page_View2'),
('user4', TIMESTAMP'2023^04^21 13*05*00', 'Page_View1'),
('user4', TIMESTAMP'2023^04^21 15*00*00', 'Page_View2'),
('user4', TIMESTAMP'2023^04^21 13*00*00', 'Page_View2'),
('user4', TIMESTAMP'2023^04^21 13*05*00', 'Page_View2'),
('user4', TIMESTAMP'2023^04^21 15*00*00', 'Page_View1');
Select * from event;

Records: 14  Duplicates: 0  Warnings: 14




event_user
event_ts
event_class


user1
2023-04-21 13:00:00
Page_View1

user1
2023-04-21 13:05:00
Page_View1

user1
2023-04-21 13:05:30
Page_View3

user1
2023-04-21 13:06:00
Page_View2

user2
2023-04-21 13:00:00
Page_View2

user2
2023-04-21 13:10:00
Page_View1

user3
2023-04-21 13:00:00
Page_View1

user3
2023-04-21 14:00:00
Page_View2

user4
2023-04-21 13:00:00
Page_View2

user4
2023-04-21 13:05:00
Page_View1

user4
2023-04-21 15:00:00
Page_View2

user4
2023-04-21 13:00:00
Page_View2

user4
2023-04-21 13:05:00
Page_View2

user4
2023-04-21 15:00:00
Page_View1


-- basic statement (for the records)
-- 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 ('Page_View1', 'Page_View2');


event_user
event_ts
event_class
event_user_lead
event_ts_lead
event_class_lead


user1
2023-04-21 13:00:00
Page_View1
user1
2023-04-21 13:05:00
Page_View1

user1
2023-04-21 13:05:00
Page_View1
user1
2023-04-21 13:06:00
Page_View2

user1
2023-04-21 13:06:00
Page_View2
user2
2023-04-21 13:00:00
Page_View2

user2
2023-04-21 13:00:00
Page_View2
user2
2023-04-21 13:10:00
Page_View1

user2
2023-04-21 13:10:00
Page_View1
user3
2023-04-21 13:00:00
Page_View1

user3
2023-04-21 13:00:00
Page_View1
user3
2023-04-21 14:00:00
Page_View2

user3
2023-04-21 14:00:00
Page_View2
user4
2023-04-21 13:00:00
Page_View2

user4
2023-04-21 13:00:00
Page_View2
user4
2023-04-21 13:00:00
Page_View2

user4
2023-04-21 13:00:00
Page_View2
user4
2023-04-21 13:05:00
Page_View1

user4
2023-04-21 13:05:00
Page_View1
user4
2023-04-21 13:05:00
Page_View2

user4
2023-04-21 13:05:00
Page_View2
user4
2023-04-21 15:00:00
Page_View2

user4
2023-04-21 15:00:00
Page_View2
user4
2023-04-21 15:00:00
Page_View1

user4
2023-04-21 15:00:00
Page_View1
null
null
null


-- solving part 2)
-- User hat nach dem Page_View1 als nächstes Event den Page_View2 ausgelöst
-- by using window functions, which are quite suitable in this case 
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
       ) x
where x.event_user = x.event_user_lead      -- applying crucial comparison criteria
  and event_class = 'Page_View1'            -- applying desired filters for follow up event
  and event_class_lead = 'Page_View2'


event_user
event_ts
event_class
event_user_lead
event_ts_lead
event_class_lead
timespan


user3
2023-04-21 13:00:00
Page_View1
user3
2023-04-21 14:00:00
Page_View2
3600

user4
2023-04-21 13:05:00
Page_View1
user4
2023-04-21 13:05:00
Page_View2
0


-- 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, but anyway)

-- solving part 1)
-- User hat zeitlich [irgendwann] nach dem Page_View1 das Event Page_View2 ausgelöst
-- by using a plain join on same data with different filters 

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 = 'Page_View1' 
   and ep.event_class = 'Page_View2'
   and el.event_ts < ep.event_ts  -- or other criteria


event_user
event_ts
event_class
event_ts_ep
event_class_ep
timespan


user1
2023-04-21 13:05:00
Page_View1
2023-04-21 13:06:00
Page_View2
60

user1
2023-04-21 13:00:00
Page_View1
2023-04-21 13:06:00
Page_View2
360

user3
2023-04-21 13:00:00
Page_View1
2023-04-21 14:00:00
Page_View2
3600

user4
2023-04-21 13:05:00
Page_View1
2023-04-21 15:00:00
Page_View2
6900


-- 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
event_user	event_ts	event_class
user1	2023-04-21 13:00:00	Page_View1
user1	2023-04-21 13:05:00	Page_View1
user1	2023-04-21 13:05:30	Page_View3
user1	2023-04-21 13:06:00	Page_View2
user2	2023-04-21 13:00:00	Page_View2
user2	2023-04-21 13:10:00	Page_View1
user3	2023-04-21 13:00:00	Page_View1
user3	2023-04-21 14:00:00	Page_View2
user4	2023-04-21 13:00:00	Page_View2
user4	2023-04-21 13:05:00	Page_View1
user4	2023-04-21 15:00:00	Page_View2
user4	2023-04-21 13:00:00	Page_View2
user4	2023-04-21 13:05:00	Page_View2
user4	2023-04-21 15:00:00	Page_View1