[SQL문제풀기] 세션 유지 시간을 10분으로 재정의하기

문제

solvesql.com

https://solvesql.com/problems/redefine-session-2/

SQLite

내가 작성한 정답

with a as (select * ,
                  case 
                    when strftime('%s', event_timestamp_kst) - strftime('%s', prev)  >= 600 
                          or prev is null then 1
                    else 0  end ns
            from (select user_pseudo_id, event_timestamp_kst, event_name, ga_session_id,
						             LAG(event_timestamp_kst) OVER (ORDER BY event_timestamp_kst) prev
                  from ga
                  where user_pseudo_id = 'a8Xu9GO6TB'))
select user_pseudo_id, event_timestamp_kst, event_name, ga_session_id,
       sum(ns) over (order by event_timestamp_kst) new_session_id
from a 
order by event_timestamp_kst