[빠짝스터디 2주차 과제] 윈도우 함수 연습 문제

2주차

1. 윈도우 함수 - 탐색 함수

   1. 프로덕트 측면에서 윈도움 탐색 함수를 활용해서 할 수 있는 것?

   2. 어떤 user가 앱/웹에 접속한 후, 어떤 화면으로 이동했는지를 알 수 있음. 즉 시간의 흐름에 따른 행동을 볼 수 있음(다음 ROW의 Page를 확인 가능)

   3. 엡 로그 상에서 직전 이벤트와 현재 이벤트가 동일한 것들을 필터링할 수 있음(LEAD한 값과 기존 컬럼과의 비교). 같은 Page를 연속으로 접근한 경우 하나로 처리해서 ‘퍼널’을 구할 때 활용할 수 있음.

      리텐션 쿼리를 작성할때 기준점을 만들 수 있음(유저의 첫 접속일, FIRST_VALUE 이용)

2. 윈도우 함수의 번호 지정 함수

• 코드 작성(윈도우 함수 Frame)

  # 윈도우 함수 Frame 연습 문제
  
  SELECT
    *,
    SUM(amount) OVER() AS amount_total, # over안에 아무것도 들어가지 않는 경우도 있음!, 전체의 SUM.
    SUM(amount) OVER(ORDER BY order_id) AS cumulative_sum,
    SUM(amount) OVER(PARTITION BY user_id ORDER BY order_id) AS cumulative_sum_by_user,
    AVG(amount) OVER(ORDER BY order_id ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING ) AS last_5_orders_avg_amount
  FROM advanced.orders
  ORDER BY user_id
  

3. 윈도우 함수 연습 문제

• 코드 작성

  # 문제 1) 사용자별 쿼리를 실행한 총 횟수를 구하는 쿼리 작성.
  # 단 , GROUP BY를 사용해서 집계하는 것이 아닌 데이터 우측에 새로운 컬럼 생성.
  
  SELECT
    *,
    COUNT(query_date) OVER (PARTITION BY user) AS total_query_cnt
  FROM advanced.query_logs
  order by user, query_date
  

  # 문제 2) 주차별로 팀 내에서 쿼리를 많이 실행한 수를 구한 후(먼저 집계 한 후)
  # 실행한 수를 활용해 랭킹을 구해주세요.(윈도우 함수 활용)
  # 단, 랭킹이 1등인 사람만 결과가 보이도록 해주세요.
  
  **문제의 의도 : 원본 데이터는 1 row 마다 데이터가 있고, 그걸 집계해서 사용(GROUP BY) 
  => 그 다음에 윈도우 함수 사용.**
  
  with query_cnt_by_team AS (
      SELECT
      EXTRACT(week from query_date) AS week_num,
      team,
      user,
      COUNT(query_date) AS query_cnt
      -- RANK() OVER(ORDER BY)
    FROM advanced.query_logs
    GROUP BY ALL )
  
  SELECT
    *,
    RANK() OVER(PARTITION BY week_num, team ORDER BY query_cnt DESC) AS rk
  FROM query_cnt_by_team
  QUALIFY rk = 1
  ORDER BY week_num, team, query_cnt DESC
  

  # 문제 3) (2번 문제에서 사용한 주차별 쿼리 사용) 쿼리를 실행한 시점 기준 ,
  # 1주 전에 쿼리 실행 수를 별도의 컬럼으로 확인할 수 있는 쿼리를 작성.
  
  with query_cnt_by_team AS (
      SELECT
      EXTRACT(week from query_date) AS week_num,
      team,
      user,
      COUNT(query_date) AS query_cnt
      -- RANK() OVER(ORDER BY)
    FROM advanced.query_logs
    GROUP BY ALL )
  
  SELECT
    *,
    LAG(query_cnt,1) OVER(PARTITION BY user ORDER BY week_num) AS last_week_query_cnt
  FROM query_cnt_by_team
  

  # 문제 4) 시간의 흐름(query_date)에 따라 일자별로 유저가 실행한 누적 쿼리 수를 작성해주세요.
  Fram 관련 문제(누적합)
  누적 쿼리 : 과거의 시간부터(UNBOUNDED PRECEDING)부터 현재까지(CURRENT ROWS)
  Frame의 Default 값 : UNBOUNDED PRECEDING ~ CURRENT ROWS => 출제 의도
  
  SELECT
    *,
    SUM(query_cnt) OVER(PARTITION BY user ORDER BY query_date ) AS cumulative_sum,
    SUM(query_cnt) OVER(PARTITION BY user ORDER BY query_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum2
  FROM (
    SELECT
      query_date,
      team,
      user,
      count(user) as query_cnt
    FROM advanced.query_logs
    GROUP BY ALL
    )
  QUALIFY cumulative_sum != cumulative_sum2
  ORDER BY user,query_date
  

  # 문제 5) 다음 데이터는 주문 횟수를 나타낸 데이터입니다.
  # 만약 주문 횟수가 없으면 NULL로 기록됩니다.
  # 이런 데이터에서 NULL 값이라고 되어있는 부분을 바로 이전 날짜의 값으로 채워주는 쿼리를 작성.
  
  출제 의도 : null을 제외하고 연산하고 싶을때는 IGONORE NULLS 활용!! 
  WITH raw_data AS (
    SELECT DATE '2024-05-01' AS date, 15 AS number_of_orders UNION ALL
    SELECT DATE '2024-05-02', 13 UNION ALL
    SELECT DATE '2024-05-03', NULL UNION ALL
    SELECT DATE '2024-05-04', 16 UNION ALL
    SELECT DATE '2024-05-05', NULL UNION ALL
    SELECT DATE '2024-05-06', 18 UNION ALL
    SELECT DATE '2024-05-07', 20 UNION ALL
    SELECT DATE '2024-05-08', NULL UNION ALL
    SELECT DATE '2024-05-09', 13 UNION ALL
    SELECT DATE '2024-05-10', 14 UNION ALL
    SELECT DATE '2024-05-11', NULL UNION ALL
    SELECT DATE '2024-05-12', NULL
  )
  
  SELECT
    *,
    LAST_VALUE(number_of_orders IGNORE nulls) OVER(ORDER BY date) AS last_value_orders
  FROM raw_data 
  

  # 문제 6) 5번 문제에서 NULL을 채운 후, 2일 전~ 현재 데이터의 평균을 구하는 쿼리를 작성(이동평균)
  출제 의도 : Frame을 지정할 수 있는가?
  
  WITH raw_data AS (
    SELECT DATE '2024-05-01' AS date, 15 AS number_of_orders UNION ALL
    SELECT DATE '2024-05-02', 13 UNION ALL
    SELECT DATE '2024-05-03', NULL UNION ALL
    SELECT DATE '2024-05-04', 16 UNION ALL
    SELECT DATE '2024-05-05', NULL UNION ALL
    SELECT DATE '2024-05-06', 18 UNION ALL
    SELECT DATE '2024-05-07', 20 UNION ALL
    SELECT DATE '2024-05-08', NULL UNION ALL
    SELECT DATE '2024-05-09', 13 UNION ALL
    SELECT DATE '2024-05-10', 14 UNION ALL
    SELECT DATE '2024-05-11', NULL UNION ALL
    SELECT DATE '2024-05-12', NULL
  )
  
  , filled_date AS (
    SELECT
    *,
    LAST_VALUE(number_of_orders IGNORE nulls) OVER(ORDER BY date) AS number_of_order
  FROM raw_data 
  )
  
  SELECT
    *,
    AVG(number_of_order) OVER(ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
  FROM filled_date
  

  # 문제 7) app_logs 테이블에서 Custom Session을 만들어 주세요.
  # 이전 이벤트 로그와 20호가 지나면 새로운 Session을 만들어 주세요.
  # Session은 숫자로 (1,2,3,---) 표시해도 됩니다
  # 2022-08-18일의 user_pseudo_id(1997494153.8491999091)은 sessio_id가 4까지 나옵니다. 
  
  session을 구할 때 쿼리가 길어질 수 있음. 하루에 접속을 여러번 하는 서비스
  => session 기반이 좋을 수 있고, 아니라고 하면 일자별 유저 집계가 나을 수 있음. 
  - 세션 정리
  - 이전 이벤트 로그와 현재 이벤트 로그의 diff => 초나 분을 구한다
  - 그 기준을 가지고 기준보다 높으면 새로운 세션이라고 생각한다
  - 첫번째 값엔 null이 있을 수 있어서, 이 부분도 챙겨야 한다.=> 1로 바꿔준다(Case 문 활용) 
  - 새로운 세션, session_start 값을 기반으로 누적합 => session_num이 된다! 
  
  with base AS (
    SELECT
    event_date,
    DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime,
    event_name,
    user_id,
    user_pseudo_id,
  FROM advanced.app_logs
    -- WHERE 
    --   event_date = '2022-08-18'
    --   AND user_pseudo_id = '1997494153.8491999091'
  ORDER BY event_timestamp
  ), diff_date AS (
  
  SELECT
    *,
    DATETIME_DIFF(event_datetime,prev_event_datetime, second) AS second_diff
  FROM (
    SELECT
        *,
      LAG(event_datetime,1) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) AS prev_event_datetime
        # event_datetime이랑 prev_datetime을 빼서 20초가 넘으면 새로운 세션으로 정의.
        # 20초가 넘지 않으면 기존 세션 / DATETIME_DIFF() => 차이를 구할 수 있음.
  FROM base
  )
  )
  
  SELECT
    *,
    SUM(session_start) OVER (PARTITION BY user_pseudo_id ORDER BY event_datetime) AS session_num
  FROM (
    SELECT
      *,
      CASE
        WHEN prev_event_datetime IS NULL THEN 1 
        WHEN second_diff >= 20 THEN 1 # session을 나누는 기준 초. 데이터 탐색하면서 결정. 보통 앱 록느에서는 30초, 60초 등으로 정함. 
        ELSE 0
        END AS session_start # 세션이 시작되됨을 알리는 session_start
    FROM diff_date 
  )