윈도우 함수(탐색 함수) 연습 문제

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

SELECT *
     , LEAD(visit_month, 1) OVER(PARTITION BY user_id ORDER BY visit_month) AS next1_visit_month
     , LEAD(visit_month, 2) OVER(PARTITION BY user_id ORDER BY visit_month) AS next2_visit_month
FROM advanced.analytics_function_01
ORDER BY user_id, visit_month

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

SELECT *
     , LEAD(visit_month, 1) OVER(PARTITION BY user_id ORDER BY visit_month) AS next1_visit_month
     , LEAD(visit_month, 2) OVER(PARTITION BY user_id ORDER BY visit_month) AS next2_visit_month
     , LAG(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month) AS before_visit_month
FROM advanced.analytics_function_01
ORDER BY user_id, visit_month

문제 3. user가 접속했을 때 다음 접속까지의 간격을 구하시오.

SELECT *
     , next_visit_month - visit_month AS diff_month
FROM (
  SELECT *
       , LEAD(visit_month, 1) OVER(PARTITION BY user_id ORDER BY visit_month) AS next_visit_month
  FROM advanced.analytics_function_01
  ORDER BY user_id, visit_month
  ) AS tmp

추가 문제. 유저의 첫 번째 방문 월, 마지막 방문 월을 구하는 쿼리를 작성해주세요.

SELECT *
     , 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, visit_month

윈도우 함수(프레임, QUALIFY) 연습 문제

문제 1.

-- amount_total: 전체 SUM
-- cumulative_sum: row 시점에 누적 SUM
-- cumulative_sum_user: row 시점에 유저별 누적 SUM
-- last_5_avg: order_id 기준으로 정렬하고, 직전 5개의 주문의 평균 amount

SELECT *
     , SUM(amount) OVER(ORDER BY order_id 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_user
     , AVG(amount) OVER(ORDER BY order_id ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS last_5_avg
FROM `inflearn-bigquery-437203.advanced.orders`
ORDER BY order_id

문제 2. QUALIFY 로 윈도우 함수 조건 걸기

SELECT *
     , SUM(amount) OVER(ORDER BY order_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS amount_total_1
     , SUM(amount) OVER() AS amout_total_2 -- OVER 안에 아무것도 쓰지 않는 경우도 있다. 그러면 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_user
     , AVG(amount) OVER(ORDER BY order_id ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS last_5_avg
FROM `inflearn-bigquery-437203.advanced.orders`
QUALIFY amount_total_1 >= 500

윈도우 함수 연습 문제 (1~7번)

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

SELECT *
     , COUNT(*) OVER(PARTITION BY user) AS total_query_cnt
FROM advanced.query_logs

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

WITH query_cnt_by_team AS (
     SELECT EXTRACT(WEEK FROM query_date) AS week_number
          , team
          , user
          , COUNT(*) AS query_cnt
     FROM advanced.query_logs
     GROUP BY ALL
     )

SELECT *
     , RANK() OVER(PARTITION BY week_number, team ORDER BY query_cnt DESC) AS rk
FROM query_cnt_by_team
QUALIFY rk = 1  -- 윈도우 함수 결과에 대해 조건 걸 때 사용
ORDER BY week_number, team, query_cnt DESC

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

WITH query_cnt_by_team AS (
     SELECT EXTRACT(WEEK FROM query_date) AS week_number
          , team
          , user
          , COUNT(*) AS query_cnt
     FROM advanced.query_logs
     GROUP BY ALL
     )

SELECT user
     , team
     , week_number
     , query_cnt
     , LAG(query_cnt, 1) OVER(PARTITION BY user ORDER BY week_number) as prev_week_query_cnt
FROM query_cnt_by_team
ORDER BY user, team, week_number

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

SELECT *
     , SUM(query_cnt) OVER(PARTITION BY user ORDER BY query_date) AS cumulative_query_cnt
FROM (
     SELECT *
          , COUNT(*) AS query_cnt
     FROM advanced.query_logs
     GROUP BY ALL) AS tmp 

-- 윈도우 함수 집계 함수 프레임 Default
-- : RANGE 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 *
     , LAST_VALUE(number_of_orders IGNORE NULLS) OVER(ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS last_value_orders
FROM raw_data

문제 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
)
, filled_data AS (
SELECT *
     , LAST_VALUE(number_of_orders IGNORE NULLS) OVER(ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS last_value_orders
FROM raw_data
)

SELECT date
     , last_value_orders
     , AVG(last_value_orders) OVER(ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM filled_data

문제 7. 세션 id 만들기

WITH step1 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"
  )

-- 이전 이벤트가 발생한 시각을 나타내는 컬럼 추가 
, step2 AS (
  SELECT *
       , LAG(event_datetime, 1) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) AS before_event_datetime
  FROM step1
  )

-- 현재 이벤트와 이전 이벤트의 발생 시간의 차이를 나타내는 컬럼 추가
, step3 AS (
  SELECT *
       , DATETIME_DIFF(event_datetime, before_event_datetime, SECOND) AS second_diff
  FROM step2
  )

-- 세션 id 부여하는 컬럼 추가
SELECT *
     , SUM(session_start) OVER (PARTITION BY user_pseudo_id ORDER BY event_datetime) AS session_id
FROM (
    SELECT *
        , IF(second_diff IS NULL OR second_diff > 20, 1, NULL) AS session_start
    FROM step3
    ) AS tmp