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

문제

solvesql.com

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

SQLite

내가 작성한 정답

with a as (select user_pseudo_id, event_timestamp_kst t,event_name,ga_session_id,
                lag(event_timestamp_kst) over (order by event_timestamp_kst) pre
            from ga
            where user_pseudo_id = 'a8Xu9GO6TB'),
    b as (select user_pseudo_id, t,pre, event_name, ga_session_id,
          row_number() over(order by t) num
          from a
          where (strftime('%s',t)-strftime('%s',pre) >= 600) or pre is null ),
    c as (select *, (select max(num) from b where b.t<=a.t) new_session_id
          from a)
select user_pseudo_id, t event_timestamp_kst, event_name, ga_session_id, new_session_id
from c
order by t;

내가 이전에 작성한 정답

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