Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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 - ругается что много циклов и крашится
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement