[퍼널 분석] solvesql | 난이도 4 | 페이지에서 스크롤을 내렸을까?

페이지에서 스크롤을 내렸을까?

 

전체 세션을 입문반 페이지를 본 세션과 안 본 세션으로 나누고, 입문반 페이지를 본 세션에 대해서 스크롤을 한 세션과 하지 않은 세션으로 세분화 해 집계하려 한다. 참고로 개별 세션의 개수를 세어주려면 user_pseudo_id 와 ga_session_id 컬럼을 모두 고려할 것.

 

문제 조건

1. 입문반 페이지를 본 세션은 아래 조건으로 찾는다

• page_title = “백문이불여일타 SQL 캠프 입문반”
• event_name = “page_view”

2. 입문반 페이지 내에서 스크롤을 한 세션은 아래 조건으로 찾는다

• page_title = “백문이불여일타 SQL 캠프 입문반”
• event_name = “scroll”

3. 동일 세션 내에서 이벤트들 간의 선후 관계를 고려할 것

 

Solution

WITH pv AS (
  SELECT user_pseudo_id
       , ga_session_id
       , event_timestamp_kst AS pv_at
  FROM ga
  WHERE page_title = '백문이불여일타 SQL 캠프 입문반'
    AND event_name = 'page_view'

), scroll AS (
  SELECT user_pseudo_id
       , ga_session_id
       , event_timestamp_kst AS scroll_at
  FROM ga
  WHERE page_title = '백문이불여일타 SQL 캠프 입문반'
    AND event_name = 'scroll'

)
SELECT (SELECT COUNT(DISTINCT user_pseudo_id, ga_session_id) FROM ga) AS total
     , (SELECT COUNT(DISTINCT user_pseudo_id, ga_session_id) FROM ga)
     - COUNT(DISTINCT p.user_pseudo_id, p.ga_session_id) AS pv_no
     , COUNT(DISTINCT p.user_pseudo_id, p.ga_session_id)
     - COUNT(DISTINCT s.user_pseudo_id, s.ga_session_id) AS pv_yes_scroll_no
     , COUNT(DISTINCT s.user_pseudo_id, s.ga_session_id) AS pv_yes_scroll_yes
FROM pv p 
LEFT JOIN scroll s ON p.user_pseudo_id = s.user_pseudo_id -- 같은 개별 세션에 대해 JOIN (user_pseudo_id, ga_session_id)
                  AND p.ga_session_id = s.ga_session_id
                  AND p.pv_at <= s.scroll_at

 

✅ pv CTE:

입문반 페이지에 방문한 세션의 테이블을 정의한다

 

✅ scroll CTE:

입문반 페이지에서 스크롤 이벤트를 발생시킨 세션의 테이블을 정의한다

 

✅ 최종 SELECT절:

• 입문반 페이지를 봤지만 스크롤은 하지 않은 세션에 대해서도 집계해야 하기 때문에 INNER JOIN이 아닌 LEFT JOIN을 수행
• 동일한 개별 세션에 대해 pv, scroll 테이블을 조인
• 두 이벤트 발생 시각 선후관계를 고려하는 조건까지 추가

✔️ 전체 세션 수

✔️ 입문반 페이지를 안 본 세션 수

✔️ 입문반 페이지를 봤지만 입문반 페이지 스크롤은 하지 않은 세션 수

✔️ 입문반 페이지를 봤고 입문반 페이지 스크롤도 한 세션 수