SQL | SOLVESQL 코딩테스트 : Lv5. (JOIN/UNION)

Lv5. SQL 데이터 분석 캠프 실전반 전환율

https://solvesql.com/problems/funnel-datarian-adv/

WITH VIEW AS (
  SELECT event_timestamp_kst
    , user_pseudo_id
    , ga_session_id
  FROM ga
  WHERE page_title = '백문이불여일타 SQL 캠프 실전반'
    AND event_name = 'page_view'
), SCROLL AS (
  SELECT event_timestamp_kst
    , user_pseudo_id
    , ga_session_id
  FROM ga
  WHERE page_title = '백문이불여일타 SQL 캠프 실전반'
    AND event_name = 'scroll'
), CLICK AS (
    SELECT event_timestamp_kst
      , user_pseudo_id
      , ga_session_id
    FROM ga
    WHERE page_title = '백문이불여일타 SQL 캠프 실전반'
      AND event_name = 'SQL_advanced_form_click'
)

SELECT COUNT(DISTINCT V.user_pseudo_id, V.ga_session_id) AS pv
  , COUNT(DISTINCT S.user_pseudo_id, S.ga_session_id) AS scroll_after_pv
  , COUNT(DISTINCT C.user_pseudo_id, C.ga_session_id) AS click_after_scroll
  , ROUND(COUNT(DISTINCT S.user_pseudo_id, S.ga_session_id) / COUNT(DISTINCT V.user_pseudo_id, V.ga_session_id),3) AS pv_scroll_rate
  , ROUND(COUNT(DISTINCT C.user_pseudo_id, C.ga_session_id) / COUNT(DISTINCT V.user_pseudo_id, V.ga_session_id),3) AS pv_click_rate
  , ROUND(COUNT(DISTINCT C.user_pseudo_id, C.ga_session_id) / COUNT(DISTINCT S.user_pseudo_id, S.ga_session_id),3) AS scroll_click_rate
FROM VIEW AS V 
  LEFT JOIN SCROLL AS S ON V.user_pseudo_id = S.user_pseudo_id
    AND V.ga_session_id = S.ga_session_id
    AND V.event_timestamp_kst <= S.event_timestamp_kst
  LEFT JOIN CLICK AS C ON S.user_pseudo_id = C.user_pseudo_id
    AND S.ga_session_id = C.ga_session_id
    AND S.event_timestamp_kst <= C.event_timestamp_kst

❗집계해야하므로 COUNT() 사용
❗COUNT() 안에 DISTINCT, user_pseudo_id, ga_session_id 넣어서 고유한 유저 ID, 세션 ID 를 집계
❗COUNT() 과정에서 조건이 다 다르므로 해당하는 테이블을 약자를 넣어서 구분

❗LEFT JOIN 사용해서 VIEW를 기준으로 데이터 결합
❗결합 조건으로 user_pseudo_id, ga_session_id 사용
❗event_timestamp_kst 로 시간의 선후 관계 고려해서 데이터 결합

+) WITH 절

❗VIEW, SCROLL, CLICK에 해당하는 데이터 추출
❗SELECT 절에 event_timestamp_kst를 꼭 넣어야 함 (시간의 선후 관계를 고려해야하기 때문)
❗WHERE 절에 event_name 을 다르게 조건 걸어서 각각 테이블 생성

-> VIWE, SCROLL, CLICK 순서로 user_pseudo_id, ga_session_id 이 나열됨

Lv5. 유입 채널 별 실전반 전환율

https://solvesql.com/problems/funnel-datarian-adv-source/

WITH VIEW AS (
  SELECT event_timestamp_kst
    , source
    , medium
    , user_pseudo_id
    , ga_session_id
  FROM ga
  WHERE page_title = '백문이불여일타 SQL 캠프 실전반'
    AND event_name = 'page_view'
), SCROLL AS (
  SELECT event_timestamp_kst
    , source
    , medium
    , user_pseudo_id
    , ga_session_id
  FROM ga
  WHERE page_title = '백문이불여일타 SQL 캠프 실전반'
    AND event_name = 'scroll'
), CLICK AS (
    SELECT event_timestamp_kst
      , source
      , medium  
      , user_pseudo_id
      , ga_session_id
    FROM ga
    WHERE page_title = '백문이불여일타 SQL 캠프 실전반'
      AND event_name = 'SQL_advanced_form_click'
)

SELECT V.source, V.medium
  , COUNT(DISTINCT V.user_pseudo_id, V.ga_session_id) AS pv
  , COUNT(DISTINCT S.user_pseudo_id, S.ga_session_id) AS scroll_after_pv
  , COUNT(DISTINCT C.user_pseudo_id, C.ga_session_id) AS click_after_scroll
  , ROUND(COUNT(DISTINCT S.user_pseudo_id, S.ga_session_id) / COUNT(DISTINCT V.user_pseudo_id, V.ga_session_id),3) AS pv_scroll_rate
  , ROUND(COUNT(DISTINCT C.user_pseudo_id, C.ga_session_id) / COUNT(DISTINCT V.user_pseudo_id, V.ga_session_id),3) AS pv_click_rate
  , ROUND(COUNT(DISTINCT C.user_pseudo_id, C.ga_session_id) / COUNT(DISTINCT S.user_pseudo_id, S.ga_session_id),3) AS scroll_click_rate
FROM VIEW AS V 
  LEFT JOIN SCROLL AS S ON V.user_pseudo_id = S.user_pseudo_id
    AND V.ga_session_id = S.ga_session_id
    AND V.event_timestamp_kst <= S.event_timestamp_kst
  LEFT JOIN CLICK AS C ON S.user_pseudo_id = C.user_pseudo_id
    AND S.ga_session_id = C.ga_session_id
    AND S.event_timestamp_kst <= C.event_timestamp_kst
GROUP BY V.source, V.medium
ORDER BY pv DESC 

❗Lv5. SQL 데이터 분석 캠프 실전반 전환율 문제와 동일한 과정
❗SELECT 절에 source, medium을 꼭 넣어야 함
❗집계해야하므로 COUNT() 사용
❗COUNT() 안에 DISTINCT, user_pseudo_id, ga_session_id 넣어서 고유한 유저 ID, 세션 ID 를 집계
❗COUNT() 과정에서 조건이 다 다르므로 해당하는 테이블을 약자를 넣어서 구분
❗GROUP BY 절에 source, medium 넣어서 그룹화

❗LEFT JOIN 사용해서 VIEW를 기준으로 데이터 결합
❗결합 조건으로 user_pseudo_id, ga_session_id 사용
❗event_timestamp_kst 로 시간의 선후 관계 고려해서 데이터 결합

+) WITH 절

❗VIEW, SCROLL, CLICK에 해당하는 데이터 추출
❗SELECT 절에 event_timestamp_kst를 꼭 넣어야 함 (시간의 선후 관계를 고려해야하기 때문)
❗SELECT 절에 source, medium을 꼭 넣어야 함 (source, medium 기준으로 그룹화 해야하기 때문)
❗WHERE 절에 event_name 을 다르게 조건 걸어서 각각 테이블 생성

-> VIWE, SCROLL, CLICK 순서로 user_pseudo_id, ga_session_id 이 나열됨