https://www.youtube.com/watch?v=5x3_simMOEE

DROP TABLE IF EXISTS Event;

CREATE TABLE Event (
    session_id INT,
    event_type VARCHAR(50),
    event_time DATETIME
);

INSERT INTO [Event] (session_id, event_type, event_time)
VALUES
(1, 'login', '2024-10-01 08:00:00'),
(1, 'view', '2024-10-01 08:05:00'),
(1, 'purchase', '2024-10-01 08:10:00'),
(2, 'login', '2024-10-01 09:00:00'),
(2, 'add_to_cart', '2024-10-01 09:05:00'),
(2, 'purchase', '2024-10-01 09:10:00'),
(3, 'login', '2024-10-01 10:00:00'),
(3, 'view', '2024-10-01 10:05:00'),
(3, 'purchase', '2024-10-01 10:10:00'),
(4, 'login', '2024-10-01 10:30:00'),
(4, 'search', '2024-10-01 10:35:00'),
(4, 'view', '2024-10-01 10:40:00'),
(4, 'add_to_cart', '2024-10-01 10:45:00'),
(4, 'purchase', '2024-10-01 10:50:00'),
(5, 'login', '2024-10-01 11:00:00'),
(5, 'search', '2024-10-01 11:05:00'),
(5, 'view', '2024-10-01 11:10:00'),
(5, 'view', '2024-10-01 11:15:00'),
(5, 'purchase', '2024-10-01 11:20:00'),
(6, 'login', '2024-10-01 11:30:00'),
(6, 'view', '2024-10-01 11:35:00'),
(6, 'add_to_cart', '2024-10-01 11:40:00'),
(6, 'purchase', '2024-10-01 11:45:00'),
(7, 'login', '2024-10-01 12:00:00'),
(7, 'search', '2024-10-01 12:05:00'),
(7, 'view', '2024-10-01 12:10:00'),
(7, 'view', '2024-10-01 12:15:00'),
(7, 'add_to_cart', '2024-10-01 12:20:00'),
(7, 'purchase', '2024-10-01 12:25:00');

WITH
ROW_NUMBER()

WITH cte1 as (
select session_id,event_type 
	,row_number() OVER (partition by session_id order by event_time) as rn
from event
)
,cte2 as (
select session_id,event_type 
	,row_number() OVER (partition by session_id order by event_time) as rn2
from event
)
,cte3 as (
SELECT cte1.event_type as Event_1,cte2.event_type as Event_2,c.event_type as Event_3,cte1.session_id
FROM cte1 left join cte2 ON cte1.session_id = cte2.session_id and rn=rn2-1
left join cte1 c on cte1.session_id = c.session_id and rn2=c.rn-1
where cte2.event_type is not null and c.event_type is not null)

select Event_1,Event_2,Event_3,COUNT(session_id) as cnt
from cte3
group by Event_1,Event_2,Event_3
order by cnt desc

LEAD()

with cte as (
select session_id
	,event_type as event_1 
	,lead(event_type,1) OVER (partition by session_id order by event_time) as event_2
    ,lead(event_type,2) OVER (partition by session_id order by event_time) as event_3
from event
)
select 
	event_1,event_2,event_3,count(session_id) as cnt
FROM cte
where event_2 is not null and event_3 is not null
group by event_1,event_2,event_3;