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

탐색 함수 연습 문제

-- 문제 1) user들의 다음 접속 월과 다다음 접속 월을 구하는 쿼리를 작성해주세요.

-- SELECT 
--   user_id, 
--   visit_month, 
--   LEAD(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) as next_visit_month,
--   LEAD(visit_month, 2) OVER (PARTITION BY user_id ORDER BY visit_month) as next_next_visit_month
-- FROM advanced.analytics_function_01 

-- 문제 2) user들의 다음 접속 월과 다다음 접속 월, 이전 접속 월을 구하는 쿼리를 작성해주세요.
--  SELECT 
--   user_id, 
--   visit_month, 
--   LEAD(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) as next_visit_month,
--   LEAD(visit_month, 2) OVER (PARTITION BY user_id ORDER BY visit_month) as next_next_visit_month,
--   LAG(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) as last_visit_month
-- FROM advanced.analytics_function_01 

-- 추가 문제 : 이 데이터셋을 기준으로 user_id의 첫 방문월, 마지막 방문월을 구하는 쿼리를 작성해주세요. 
SELECT 
  DISTINCT user_id,
  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 

Frame

PIVOT 연습 문제에서 사용한 테이블 활용 
SELECT 
 *,  
 SUM(amount) OVER () as amount_total, 
 SUM(amount) OVER (ORDER BY order_id) as cumluative_sum, 
 SUM(amount) OVER (PARTITION BY user_id ORDER BY order_id) as cumluative_sum_user,
 AVG(amount) OVER (ORDER BY order_id ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) as last_5_avg
FROM advanced.orders
ORDER BY order_id

윈도우 함수 연습문제

-- 1) 사용자별 쿼리를 실행한 총 횟수를 구하는 쿼리를 작성해주세요. 단, GROUP BY를 사용해서 집계하는 것이 아닌 query_logs의 데이터의 우측에 새로운 컬럼을 만들어주세요.

-- SELECT 
--   *,
--   COUNT(*) OVER (PARTITION BY user) as total_query_count
-- FROM advanced.query_logs 

-- 2) 주차별로 팀 내에서 쿼리를 많이 실행한 수를 구한 후, 실행한 수를 활용해 랭킹을 구해주세요. 단, 랭킹이 1등인 사람만 결과가 보이도록 해주세요
-- SELECT 
--   DISTINCT *,
--   RANK() OVER (PARTITION BY week_number, team  ORDER BY query_cnt desc) as team_rank
-- FROM 
--   (
--   SELECT 
--     week_number,
--     team,
--     user, 
--     COUNT(query_date) OVER (PARTITION BY user, week_number) as query_cnt
--   FROM 
--     (SELECT 
--       *,
--       EXTRACT(WEEK FROM query_date) as week_number
--     FROM advanced.query_logs 
--     )
--   )
-- QUALIFY team_rank = 1 
-- ORDER BY week_number, team

-- 문제 의도 : 원본 데이터는 row 마다 데이터가 있고, 그걸 집계해서 활용. GROUP BY 사용 후에 윈도우 함수


-- 3) (2번 문제에서 사용한 주차별 쿼리 사용) 쿼리를 실행한 시점 기준 1주 전에 쿼리 실행 수를 별도의 컬럼으로 확인할 수 있는 쿼리를 작성해주세요

-- SELECT 
--   *, 
--   LAG(query_cnt) OVER (PARTITION BY user ORDER BY week_number) as prev_week_query_count
-- FROM 
--   (SELECT 
--     distinct week_number,
--     team,
--     user, 
--     COUNT(query_date) OVER (PARTITION BY user, week_number) as query_cnt
--   FROM 
--     (SELECT 
--       *,
--       EXTRACT(WEEK FROM query_date) as week_number
--     FROM advanced.query_logs 
--     )
--   )
-- ORDER BY user, week_number

-- 4) 시간의 흐름에 따라, 일자별로 유저가 실행한 누적 쿼리 수를 작성해주세요
-- SELECT 
--   distinct *,
--   COUNT(query_date) OVER (PARTITION BY user, query_date ORDER BY query_date) as query_cnt, 
--   COUNT(query_date) OVER (PARTITION BY user ORDER BY query_date) as cumulative_query_cnt
-- FROM advanced.query_logs 
-- ORDER BY user, query_date

-- 출제 의도 : Default Frame 이해 // 집계분석에서  Frame의 Default 값 = BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

-- 5) 다음 데이터는 주문 횟수를 나타낸 데이터입니다. 만약 주문 횟수가 없으면 NULL로 기록됩니다. 이런 데이터에서 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
--   )
-- SELECT 
--   date, 
--   CASE WHEN number_of_orders is not null THEN number_of_orders
--   ELSE LAG(number_of_orders) OVER (ORDER BY date) 
--   END AS number_of_orders
-- FROM raw_data
-- 수정 
-- SELECT 
--   *,
--   LAST_VALUE(number_of_orders IGNORE NULLS) OVER (ORDER BY date) as last_value_orders
-- FROM raw_data
-- 출제 의도 : first_value, last_value 사용할 때 null을 포함하고 싶으면 ignore nulls 활용

-- 6) 5번 문제에서 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
--   )

-- SELECT 
--   *, 
--   AVG(number_of_orders) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg 
-- FROM  
--   (SELECT 
--     date, 
--     CASE WHEN number_of_orders is not null THEN number_of_orders
--     ELSE LAG(number_of_orders) OVER (ORDER BY date) 
--     END AS number_of_orders
--   FROM raw_data
--   )



-- 7) app_logs 테이블에서 Custom Session을 만들어 주세요. 이전 이벤트 로그와 20초가 지나면 새로운 Session을 만들어 주세요. Session은 숫자로 (1, 2, 3 ...) 표시해도 됩니다
-- 2022-08-18일의 user_pseudo_id(1997494153.8491999091)은 session_id가 4까지 나옵니다

WITH base AS (
  SELECT 
  event_date,
  DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') as event_datetime,
  user_id, 
  user_pseudo_id
  FROM advanced.app_logs
  WHERE event_date = '2022-08-18'
    and user_pseudo_id = '1997494153.8491999091'
  ),
  diff_data AS (
  SELECT 
    *,
    DATETIME_DIFF(event_datetime, prev_event_datetime, SECOND) as second_diff
  FROM (
    SELECT 
      *,
      LAG(event_datetime) OVER (PARTITION BY user_pseudo_id ORDER BY base.event_datetime) as prev_event_datetime
    FROM base
    )
  )


SELECT 
  *,
  SUM(session_start) OVER (PARTITION BY user_pseudo_id ORDER BY event_datetime) AS session_num
  -- session 을 구할 때 쿼리가 길어질 수 있음. 하루에 접속을 여러번 하는 서비스 -> session 기반이 좋을 수 있고, 아니라면 일자별 유저 집계가 나을 수 있음
FROM (
  SELECT 
    *, 
    CASE 
      WHEN second_diff IS NULL THEN 1 
      WHEN second_diff >= 20 THEN 1 #세션을 나누는 기준. 데이터 탐색 후 결정 / 보통 앱 로그는 30초 or 60초 
      ELSE 0 
      END as session_start
  FROM diff_data
  )
ORDER BY event_datetime

-- 세션 정리 
-- 이전 이벤트 로그와 현재 이벤트 로그의 diff => 초나 분을 구한다
-- 기준을 가지고 그 기준 보다 높으면 새로운 세션이라고 한다
-- 첫번째 값엔 null이 있을 수 있어, 이 부분도 챙겨야 한다
-- 새로운 세션, session_start 기반으로 누적합 => session_num이 된다