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

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

-- 출제의도: 윈도우 함수(LEAD)를 사용하여 파티션을 나눠 데이터를 탐색할 수 있는가? 
SELECT 
  user_id
  , visit_month AS visit_month_m0
  , LEAD(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS visit_month_m1
  , LEAD(visit_month, 2) OVER (PARTITION BY user_id ORDER BY visit_month) AS visit_month_m2
FROM advanced.analytics_function_01
ORDER BY user_id;

강의 문제 2) user들의 다음 접속월과 다다음 접속월, 이전 접속월을 구하는 쿼리를 작성해주세요

-- 출제의도: 윈도우 함수(LEAD)와 (LAG)을 함께 사용하여 파티션을 나눠 데이터를 탐색할 수 있는가? 
SELECT 
  user_id
  , visit_month AS visit_month_m0
  , LEAD(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS visit_month_m1
  , LEAD(visit_month, 2) OVER (PARTITION BY user_id ORDER BY visit_month) AS visit_month_m2
  , LAG(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS visit_month_p1
FROM advanced.analytics_function_01
ORDER BY user_id;

강의 문제 3) Frame 설정을 활용한 윈도우 함수 사용

SELECT 
  order_id
  , order_date
  , user_id
  , amount
  , SUM(amount) OVER (ORDER BY order_date, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS amount_total
  , SUM(amount) OVER (ORDER BY order_date, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum
  , SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum_by_user
  , AVG(amount) OVER (ORDER BY order_date, order_id ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS last_5_orders_avg_amount
FROM advanced.orders
--QUALIFY last_5_orders_avg_amount >= 150
ORDER BY order_id;

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

-- 출제의도: 윈도우 함수의 집계 함수 중 COUNT를 사용할 수 있는가? 
SELECT
  *
  , COUNT(user) OVER (PARTITION BY user) AS total_query_cnt
FROM advanced.query_logs
ORDER BY query_date, user;

윈도우 함수 연습문제 2) 주차별로 팀 내에서 쿼리를 많이 실행한 수를 구한 후, 실행한 수를 활용해 랭킹을 구해주세요. 단, 랭킹이 1등인 사람만 결과가 보이도록 해주세요.

-- 출제의도: GROUP BY와 윈도우 함수(순위)를 함께 사용할 수 있는가?
SELECT 
  *
  , RANK() OVER (PARTITION BY week_number, team ORDER BY query_cnt DESC) AS team_rank
FROM 
  (SELECT 
    EXTRACT(WEEK FROM query_date) AS week_number
    , team
    , user
    , COUNT(user) AS query_cnt
  FROM advanced.query_logs
  GROUP BY ALL)
QUALIFY team_rank = 1
ORDER BY week_number, team;

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

-- 출제의도: GROUP BY와 윈도우 함수(LAG)를 함께 사용할 수 있는가?
SELECT 
  *
  , LAG(query_cnt) OVER (PARTITION BY user ORDER BY week_number) AS prev_week_query_count 
FROM 
  (SELECT 
    user
    , team
    , EXTRACT(WEEK FROM query_date) AS week_number
    , COUNT(user) AS query_cnt
  FROM advanced.query_logs
  GROUP BY ALL)
  ORDER BY user, week_number;

윈도우 함수 연습문제 4) 시간 흐름에 따라, 일자별로 유저가 실행한 누적 쿼리 수를 작성해주세요.

-- *FRAME의 DEFAULT 값: UNBOUNDED PRECEDING ~ CURRENT ROW
-- 출제의도: GROUP BY와 윈도우 함수(SUM-누계합)를 함께 사용할 수 있는가?
SELECT 
  *
  , SUM(query_cnt) OVER (PARTITION BY user ORDER BY query_date) AS cumulative_query_count
FROM
(SELECT 
  user
  , team
  , query_date
  , COUNT(user) AS query_cnt
FROM advanced.query_logs
GROUP BY ALL)
ORDER BY user, query_date;

윈도우 함수 연습문제 5) 다음 데이터는 주문 횟수를 나타낸 데이터입니다. 만약 주문 횟수가 없으면 NULL로 기록됩니다. 이런 데이터에서 NULL 값이라고 되어있는 부분을 바로 이전 날짜의 값으로 채워주는 쿼리를 작성해주세요.

-- 출제의도: 윈도우 함수(LAST_VALUE)에서 IGNORE 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
--   date
--   , IFNULL(number_of_orders, LAG(number_of_orders) OVER (ORDER BY date)) AS number_of_orders
-- FROM raw_data
-- ORDER BY date;

SELECT 
  *
  , LAST_VALUE(number_of_orders IGNORE NULLS) OVER (ORDER BY date) AS number_of_orders
FROM raw_data
ORDER BY date;

윈도우 함수 연습문제 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 
  *
  , ROUND(AVG(number_of_orders) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) 
      , 1) AS moving_avg
FROM(
  SELECT
    date
    , IFNULL(number_of_orders, LAG(number_of_orders) OVER (ORDER BY date)) AS number_of_orders
  FROM raw_data)
ORDER BY date;

윈도우 함수 연습문제 7) app_logs 테이블에서 Custom Session을 만들어주세요. 이전 이벤트 로그와 20초가 지나면 새로운 Session을 만들어 주세요.

-- *Session은 숫자로(1,2,3…) 표시해도 됩니다. 
-- **2022-08-18일의 user_pseudo_id(1997494153. 8491999091)은 session_id가 4까지 나옵니다
-- 출제의도: 윈도우 함수를 웹 로그 데이터에 적용하여 활용할 수 있는가?

-- Step 1. Session 정보 추출 
WITH base AS(
  SELECT 
    event_date
    , event_timestamp
    , DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime
    , event_name
    , user_id
    , user_pseudo_id
    , DATETIME(TIMESTAMP_MICROS(LAG(event_timestamp) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp)), 'Asia/Seoul') AS before_event_datetime
  FROM advanced.app_logs
  WHERE event_date = '2022-08-18'
    AND user_pseudo_id = '1997494153.8491999091'
),

-- Step 2. 세션 유지 시간 및 신규 세션 여부 계산
session_info AS(
  SELECT 
    *
    , TIMESTAMP_DIFF(event_datetime, before_event_datetime, SECOND) AS second_diff
    , CASE 
        WHEN TIMESTAMP_DIFF(event_datetime, before_event_datetime, SECOND) >= 20 
          OR TIMESTAMP_DIFF(event_datetime, before_event_datetime, SECOND) IS NULL THEN 1 
        ELSE NULL 
      END AS session_start
  FROM base
)

-- Step 3. 신규 세션 id 세팅
SELECT 
  *
  , SUM(session_start) OVER (PARTITION BY user_pseudo_id ORDER BY event_datetime) AS session_id
FROM session_info
ORDER BY event_date, event_timestamp;