인프런 커뮤니티 질문&답변

1. 탐색 함수 연습 문제

-- 1. user들의 다음 접속 월과 다다음 접속 월을 구하는 쿼리를 작성해주세요.
-- partition 유저, 접속 월 정렬, LEAD(1), LEAD(2)
SELECT
  user_id
  , visit_month
  , LEAD(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month) AS after_month
  , LEAD(visit_month, 2) OVER(PARTITION BY user_id ORDER BY visit_month) AS after2_month
FROM `advanced.analytics_function_01`
ORDER BY user_id, visit_month

-- 2. user들의 다음 접속 월과 다다음 접속 월,이전 접속 월을 구하는 쿼리를 작성해주세요.
-- partition: 유저, order: 접속 월, LEAD(1), LEAD(2), LAG(1)
SELECT
  user_id
  , visit_month
  , LEAD(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month) AS after_month
  , LEAD(visit_month, 2) OVER(PARTITION BY user_id ORDER BY visit_month) AS after2_month
  , LAG(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month) AS before_month
FROM `advanced.analytics_function_01`
ORDER BY user_id, visit_month


-- 3. 유저가 접속했을 때, 다음 접속까지의 간격을 구하시오.
-- partition: 유저, order: 접속 월, 간격: LEAD(1) - 접속 월
SELECT
	after_visit_month - visit_month AS diff_month
FROM (
  user_id
  , visit_month
  , LEAD(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month) AS after_visit_month
FROM `advanced.analytics_function_01`
)
ORDER BY user_id, visit_month

-- 4. user_id의 첫번째 방문 월, 마지막 방문월을 구하는 쿼리를 작성해주세요.
-- partition: 유저, order: 접속 월, FIRST(전체 범위), LAST(전체 범위)
SELECT DISTINCT user_id, first_visit_month, last_visit_month
FROM (
  SELECT
    user_id
    -- , visit_month
    , FIRST_VALUE(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_visit_month
    , LAST_VALUE(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_visit_month
  FROM `advanced.analytics_function_01`
)
ORDER BY user_id

2. Frame 연습 문제

-- 1. 우리 회사의 모든 주문량은?(amount_total) : SUM(amount, 전체 범위)
-- 2. 특정 주문 시점에서 누적 주문량은?(cumulative_sum) : SUM(order by 주문id)
-- 3. 고객별 주문 시점에서 누적 주문량은?(cumnulative_sum_by_user) : SUM(partition by 유저,order by 주문id)
-- 4. 최근 직전 5개의 평균 주문량은?(last_5_orders_avg_amount) : AVG(order by 주문id, 5전 ~ 1전)

SELECT
  *
	# amount_total : SUM(amount) OVER()와 동일한 결과 출력됨
  , SUM(amount) OVER(ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS amount_total
  , 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 order_id

3. 윈도우 함수 총정리 연습 문제

-- 1. 사용자별 쿼리를 실행한 총 횟수를 구하는 쿼리를 작성해주세요. 단, GROUP BY를 사용해서 집계하는 것이 아닌, query_logs 데이터의 우측에 새로운 컬럼을 만들어주세요.
-- 동명이인 없음. COUNT(query_date) OVER(partition 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) week, 주차별 실행 수 (중복제거 포함)
WITH query_log_week_cnt AS (
    SELECT 
      EXTRACT(WEEK FROM query_date) AS week_number
      , team
      , user
      , COUNT(query_date) AS query_cnt
    FROM `advanced.query_logs`
    GROUP BY ALL
)
-- 2) RANK() OVER(PARTITION BY week, team ORDER BY 주차별 실행 수 DESC) / QUALIFY rank = 1
SELECT 
  week_number
  , team
  , user
  , query_cnt
  , RANK() OVER(PARTITION BY week_number, team ORDER BY query_cnt DESC) AS team_rank
FROM query_log_week_cnt
WHERE 1=1
  QUALIFY team_rank = 1
ORDER BY week_number, team, user

-- 3. 쿼리를 실행한 시점 기준 1주 전에 쿼리 실행수를 별도의 컬럼으로 확인할 수 있는 쿼리를 작성해주세요. 

-- 1) week, 주차별 실행 수 (중복제거 포함)
WITH query_log_week_cnt AS (
    SELECT 
      EXTRACT(WEEK FROM query_date) AS week_number
      , team
      , user
      , COUNT(query_date) AS query_cnt
    FROM `advanced.query_logs`
    GROUP BY ALL
)
-- 2) LAG(쿼리cnt) OVER(partition by 유저 order by 주차)
SELECT
  user
  , team
  , week_number
  , LAG(query_cnt) OVER(PARTITION BY user ORDER BY week_number) AS prev_week_query_cnt
FROM query_log_week_cnt
ORDER BY user, team, week_number

-- 4. 시간의 흐름에 따라, 일자별로 유저가 실행한 누적 쿼리 수를 작성해주세요. 

-- 1) 일자별 쿼리 수 (집계)
WITH query_log_daily_cnt AS (
    SELECT 
      user
      , team
      , query_date
      , COUNT(query_date) AS query_cnt
    FROM `advanced.query_logs`
    GROUP BY ALL
)
-- 2) 일자별 누적 쿼리 수 : SUM(쿼리수) OVER(파티션: 유저, 정렬: 일자)
SELECT
  *
  , SUM(query_cnt) OVER(PARTITION BY user ORDER BY query_date) AS cumulative_query_cnt
FROM query_log_daily_cnt
ORDER BY user, query_date

-- 5. NULL 값 부분을 바로 이전 날짜의 값으로 채워주는 쿼리를 작성해주세요.
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
)
-- if null, lag, date 정렬 = 마지막 연속 null case 처리 불가..
-- : last value, ignore nulls 활용
SELECT 
  *
  , LAST_VALUE(number_of_orders IGNORE NULLS) OVER(ORDER BY date) AS fill_order_num
FROM raw_data
ORDER BY date

-- 6. NULL을 채운 후, 2일 전 ~ 현재 데이터의 평균을 구하는 쿼리를 작성해주세요. (이동평균)
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
)
-- last value, ignore nulls 활용
, fill_data AS (
  SELECT 
    *
    , LAST_VALUE(number_of_orders IGNORE NULLS) OVER(ORDER BY date) AS fill_order_num
  FROM raw_data
)

-- AVG(주문 수) OVER(date 정렬 rows between 2일전 ~ 현재)
SELECT
  date
  , fill_order_num
  , AVG(fill_order_num) OVER(ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM fill_data
ORDER BY date

-- 7. custom session 생성 (이전 이벤트 로그와 20초가 지나면 새로운 session)
-- 컬럼 생성: event_datetime, 이전 event_datetime, 기준 - 이전 event_datetime, >= 20초 : session_id 1
WITH app_log_datetime AS (
SELECT
  event_date
  , event_timestamp
  , 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'
)
-- 이전 event_datetime
, app_log_datetime_before AS (
  SELECT
    *
    , LAG(event_datetime) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) AS before_event_datetime
  FROM app_log_datetime
)
-- 기준 - 이전 event_datetime
, app_log_diff AS (
  SELECT
    *
    , DATETIME_DIFF(event_datetime, before_event_datetime, SECOND) AS second_diff
  FROM app_log_datetime_before
)
-- session >= 20, 첫번째 값 null 처리
, app_log_session_start AS (
  SELECT
    *
    , CASE 
        WHEN before_event_datetime IS NULL THEN 1
        WHEN second_diff >= 20 THEN 1 -- session을 나누는 기준(서비스 특성 고려하여 선정 필요, 보통 앱로그에서는 30초 60초)
        ELSE 0
        END AS session_start
  FROM app_log_diff
)
-- 누적 합을 통해 session_num 생성
SELECT 
  *
  , SUM(session_start) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) AS session_num
FROM app_log_session_start