SELECT agg.USER_ID, agg.SESSION_ID, LISTAGG(agg.PAGE,';') SESSION_HISTORY
FROM (
    SELECT  ss0.USER_ID, ss0.session_id, ss0.SESSION_START, ss0.next_session,
    RANK() over(PARTITION BY ss0.USER_ID, ss0.session_id ORDER BY ss0.USER_ID, ss0.session_id, vp.happened_at) SESSION_ACTION_ID,
    vp.page
    from(
    SELECT  p2.USER_ID, p2.page, p2.happened_at SESSION_START,
        ROW_NUMBER() over(ORDER BY p2.user_id,p2.happened_at) session_id,
        COALESCE(LAG(p2.happened_at ) over(PARTITION BY p2.USER_ID ORDER BY p2.user_id,p2.happened_at desc), DATE_ADD('YEAR',1,p2.happened_at))next_session
        FROM (
            SELECT  p1.USER_ID, p1.PAGE, p1.happened_at,
            DATEDIFF('SECOND',COALESCE(LAG(p1.happened_at) OVER (PARTITION BY p1.USER_ID ORDER BY p1.happened_at),DATE_ADD('second',-3601,p1.happened_at)),p1.happened_at) session_lag
            FROM test.VIMBOX_PAGES p1
            ORDER BY USER_ID,happened_at
        ) p2
        WHERE (p2.session_lag)>3600
        ORDER BY USER_ID,happened_at
    )ss0
    LEFT JOIN test.VIMBOX_PAGES vp ON vp.USER_ID = ss0.USER_ID AND vp.happened_at>=ss0.SESSION_START AND vp.happened_at<ss0.next_session
    order by vp.happened_at
)AGG
GROUP BY agg.USER_ID, agg.SESSION_ID
having
POSITION('rooms.homework-showcase' in SESSION_HISTORY)>0
AND POSITION('rooms.view.step.content' in SESSION_HISTORY) BETWEEN POSITION('rooms.homework-showcase' in SESSION_HISTORY) AND POSITION('rooms.lesson.rev.step.content' in SESSION_HISTORY)
--having REGEXP_COUNT(SESSION_HISTORY,'.*rooms\.homework-showcase.+rooms\.view\.step\.content.+rooms\.lesson\.rev\.step\.content')>0 - ругается что много циклов и крашится