데이터리안 - SQL 데이터 분석캠프 실전반- 3주차: 추가연습문제

본 내용은 데이터리안 'SQL 데이터 분석 캠프 실전반'을 수강하며 작성한 내용입니다.

입문반 페이지를 본 세션 찾기

WITH pv AS (
  -- '백문이불여일타 SQL 캠프 입문반' 페이지를 방문한 사용자 중에서 'page_view' 이벤트가 발생한 경우를 선택하는 CTE (Common Table Expression)
  SELECT
    user_pseudo_id,
    ga_session_id
  FROM
    ga
  WHERE
    page_title = '백문이불여일타 SQL 캠프 입문반'
    AND event_name = 'page_view'
)

SELECT
  COUNT(DISTINCT ga.user_pseudo_id, ga.ga_session_id) AS total,
  -- 전체 사용자 세션의 고유 수를 계산
  COUNT(DISTINCT ga.user_pseudo_id, ga.ga_session_id) - COUNT(DISTINCT pv.user_pseudo_id, pv.ga_session_id) AS pv_no,
  -- 'page_view' 이벤트가 발생하지 않은 사용자 세션의 고유 수를 계산
  COUNT(DISTINCT pv.user_pseudo_id, pv.ga_session_id) AS pv_yes
  -- 'page_view' 이벤트가 발생한 사용자 세션의 고유 수를 계산
FROM
  ga
  -- 기본 데이터 테이블인 'ga'를 기준으로 함
LEFT JOIN
  pv ON ga.user_pseudo_id = pv.user_pseudo_id
    AND ga.ga_session_id = pv.ga_session_id;
    -- 'pv' CTE에서 선택한 조건과 일치하는 사용자 및 세션 ID를 기준으로 왼쪽 조인 수행

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

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

SELECT COUNT(DISTINCT ga.user_pseudo_id, ga.ga_session_id) AS total
     , COUNT(DISTINCT ga.user_pseudo_id, ga.ga_session_id) - COUNT(DISTINCT pv.user_pseudo_id, pv.ga_session_id) AS pv_no
     , COUNT(DISTINCT pv.user_pseudo_id, pv.ga_session_id) - COUNT(DISTINCT scroll.user_pseudo_id, scroll.ga_session_id) AS pv_yes_scroll_no
     , COUNT(DISTINCT scroll.user_pseudo_id, scroll.ga_session_id) AS pv_yes_scroll_yes
FROM ga
  LEFT JOIN pv ON ga.user_pseudo_id = pv.user_pseudo_id
              AND ga.ga_session_id = pv.ga_session_id
  -- 'ga' 테이블의 user_pseudo_id 및 ga_session_id와 'pv' CTE에서 선택한 조건에 맞는 데이터를 연결.
  -- 이 조인은 '백문이불여일타 SQL 캠프 입문반' 페이지를 방문한 사용자 중 
  -- 'page_view' 이벤트가 발생한 경우를 가져옴. 
  -- 만약 'page_view' 이벤트가 발생하지 않은 경우에도 'ga' 테이블의 데이터는 남아있음
  LEFT JOIN scroll ON pv.user_pseudo_id = scroll.user_pseudo_id
                  AND pv.ga_session_id = scroll.ga_session_id
  --   'pv' CTE의 결과와 'scroll' CTE에서 선택한 조건에 맞는 데이터를 연결합니다.
  -- 이 조인은 '백문이불여일타 SQL 캠프 입문반' 페이지를 방문한 사용자 중에서 
  -- 'page_view' 이벤트가 발생한 경우와 'scroll' 이벤트가 발생한 경우를 가져옵니다. 
  -- 만약 'scroll' 이벤트가 발생하지 않은 경우에도 'pv' CTE의 데이터는 남아 있습니다.

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

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'
), click AS (
  SELECT user_pseudo_id
       , ga_session_id
       , event_timestamp_kst AS click_at
  FROM ga
  WHERE page_title = '백문이불여일타 SQL 캠프 실전반'
    AND event_name = 'SQL_advanced_form_click'
)

SELECT COUNT(DISTINCT pv.user_pseudo_id, pv.ga_session_id) AS pv
     , COUNT(DISTINCT scroll.user_pseudo_id, scroll.ga_session_id) AS scroll_after_pv
     , COUNT(DISTINCT click.user_pseudo_id, click.ga_session_id) AS click_after_scroll
     , ROUND(COUNT(DISTINCT scroll.user_pseudo_id, scroll.ga_session_id) / COUNT(DISTINCT pv.user_pseudo_id, pv.ga_session_id), 3) AS pv_scroll_rate
     , ROUND(COUNT(DISTINCT click.user_pseudo_id, click.ga_session_id) / COUNT(DISTINCT pv.user_pseudo_id, pv.ga_session_id), 3) AS pv_click_rate
     , ROUND(COUNT(DISTINCT click.user_pseudo_id, click.ga_session_id) / COUNT(DISTINCT scroll.user_pseudo_id, scroll.ga_session_id), 3) AS scroll_click_rate
FROM pv
  LEFT JOIN scroll ON pv.user_pseudo_id = scroll.user_pseudo_id
                  AND pv.ga_session_id = scroll.ga_session_id
                  AND pv_at <= scroll_at
  LEFT JOIN click ON scroll.user_pseudo_id = click.user_pseudo_id
                 AND scroll.ga_session_id = click.ga_session_id
                 AND scroll_at <= click_at

유입 채널 별 실전반 전환율

WITH pv AS (
  SELECT user_pseudo_id
       , ga_session_id
       , event_timestamp_kst AS pv_at
       , source
       , medium
  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'
), click AS (
  SELECT user_pseudo_id
       , ga_session_id
       , event_timestamp_kst AS click_at
  FROM ga
  WHERE page_title = '백문이불여일타 SQL 캠프 실전반'
    AND event_name = 'SQL_advanced_form_click'
)

SELECT pv.source
     , pv.medium
     , COUNT(DISTINCT pv.user_pseudo_id, pv.ga_session_id) AS pv
     , COUNT(DISTINCT scroll.user_pseudo_id, scroll.ga_session_id) AS scroll_after_pv
     , COUNT(DISTINCT click.user_pseudo_id, click.ga_session_id) AS click_after_scroll
     , ROUND(COUNT(DISTINCT scroll.user_pseudo_id, scroll.ga_session_id) / COUNT(DISTINCT pv.user_pseudo_id, pv.ga_session_id), 3) AS pv_scroll_rate
     , ROUND(COUNT(DISTINCT click.user_pseudo_id, click.ga_session_id) / COUNT(DISTINCT pv.user_pseudo_id, pv.ga_session_id), 3) AS pv_click_rate
     , ROUND(COUNT(DISTINCT click.user_pseudo_id, click.ga_session_id) / COUNT(DISTINCT scroll.user_pseudo_id, scroll.ga_session_id), 3) AS scroll_click_rate
FROM pv
  LEFT JOIN scroll ON pv.user_pseudo_id = scroll.user_pseudo_id
                  AND pv.ga_session_id = scroll.ga_session_id
                  AND pv_at <= scroll_at
  LEFT JOIN click ON scroll.user_pseudo_id = click.user_pseudo_id
                 AND scroll.ga_session_id = click.ga_session_id
                 AND scroll_at <= click_at
GROUP BY pv.source, pv.medium
ORDER BY pv DESC

180. Consecutive Numbers

SELECT DISTINCT num AS ConsecutiveNums
-- num 앞에 DISTINCT 써줌으로써 로그 테이블에 여러 번 동일한 숫자가 등장하는 경우에도 쿼리 결과에는 해당 숫자가 한 번만 나타날 것
FROM (
  SELECT num
      , LEAD(num, 1) OVER (ORDER BY id) AS num_1after
      , LEAD(num, 2) OVER (ORDER BY id) AS num_2after
  FROM logs
) AS nums
WHERE num = num_1after
  AND num = num_2after

511. Game Play Analysis I

SELECT player_id
     , event_date AS first_login
FROM (
  SELECT player_id
      , event_date
      , ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY event_date) AS rown
  FROM activity
) activity_rown
WHERE rown = 1