[SQL문제풀기] 세션 재정의하기

문제

solvesql.com

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

SQLite

내가 작성한 오답

with a as (select row_number() over (order by event_timestamp_kst) t, *
            from ga
            where user_pseudo_id = 'S3WDQCqLpK'),
    b as (select a.user_pseudo_id, a.t, b.t,
                  b.event_timestamp_kst s,
                  a.event_timestamp_kst e,
                  row_number() over (order by b.event_timestamp_kst) rn
          from a a
          left join a b on a.t+1 = b.t
          where strftime('%s',b.event_timestamp_kst) - strftime('%s',a.event_timestamp_kst) >= 3600
                or (a.t = 1 or b.event_timestamp_kst is null))
select b.user_pseudo_id, b.s session_start, bb.e session_end
from b b
join b bb on b.rn+1 = bb.rn
where b.s is not null
order by session_start

with a as (select row_number() over (order by event_timestamp_kst) t, *
            from ga
            where user_pseudo_id = 'S3WDQCqLpK'),
    b as (select a.user_pseudo_id, a.t, b.t,
                  b.event_timestamp_kst s,
                  a.event_timestamp_kst e,
                  row_number() over (order by b.event_timestamp_kst) rn
          from a a
          left join a b on a.t+1 = b.t
          where strftime('%s',b.event_timestamp_kst) - strftime('%s',a.event_timestamp_kst) >= 3600
                or (a.t = 1 or b.event_timestamp_kst is null))
select b.user_pseudo_id, b.s session_start, bb.e session_end
from b b
left join b bb on b.rn+1 = bb.rn
where b.s is not null
order by session_start

내가 작성한 정답

with a as (select row_number() over (order by event_timestamp_kst) t, *
            from ga
            where user_pseudo_id = 'S3WDQCqLpK'),
    b as (select a.user_pseudo_id, a.t, b.t,
                  b.event_timestamp_kst s,
                  a.event_timestamp_kst e,
                  row_number() over (order by b.event_timestamp_kst) rn
          from a a
          left join a b on a.t+1 = b.t
          where strftime('%s',b.event_timestamp_kst) - strftime('%s',a.event_timestamp_kst) >= 3600
                or (a.t = 1 or b.event_timestamp_kst is null))
select b.user_pseudo_id, b.s session_start, ifnull(bb.e,b.s) session_end
from b b
left join b bb on b.rn+1 = bb.rn
where b.s is not null
order by session_start;

내가 이전에 작성한 정답

1. a 테이블에서 row_number으로 시간의 흐름에 따라 번호를 매겼다.

2. b 테이블에서 a 테이블끼리 join하여 시간차이가 1시간이상 난 레코드들을 걸러내고

3. row_number으로 번호를 매겼다. → 다음에 self join하기 위함

4. 최종쿼리에서 b테이블을 셀프조인해서 시작과 끝을 맞췄다.

with a as (select row_number() over (order by event_timestamp_kst) t,
	                event_timestamp_kst, 
	                user_pseudo_id
          from ga
          where user_pseudo_id = 'S3WDQCqLpK'),
    b as (select row_number() over(order by a.event_timestamp_kst) tt,
                  b.event_timestamp_kst ss,
                  a.event_timestamp_kst ee,
                  b.user_pseudo_id
          from a
          full join a b on a.t+1 = b.t
          where strftime('%s',b.event_timestamp_kst) - strftime('%s',a.event_timestamp_kst) >= 3600
                or b.event_timestamp_kst is null 
                or a.event_timestamp_kst is null )
select c.user_pseudo_id, c.ss session_start, b.ee session_end
from b 
join b c on b.tt = c.tt+1