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

1. 탐색 함수 연습 문제

-- 문제1. user들의 다음 접속 월과 다다음 접속 월을 구하는 쿼리를 작성하세요.
SELECT
  *,
  LEAD(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month) as lead_visti_month,
  LEAD(visit_month, 2) OVER(PARTITION BY user_id ORDER BY visit_month) as lead2_lead_visti_month,
FROM `advanced.analytics_function_01`

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

2. Frame 연습 문제

SELECT
  *,
  SUM(amount) OVER() 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의 데이터의 우측에 새로운 컬럼을 만들어주세요.
SELECT
  user,
  team,
  query_date,
  COUNT(user) OVER(PARTITION BY user) AS total_query_cnt
FROM advanced.query_logs

-- 문제2. 주차 별로 팀 내에서 쿼리를 많이 실행한 수를 구한 후,실행한 수를 활용해 랭킹을 구해주세요. 단,랭킹이 1등인 사람만 결과가 보이도록해주세요.
WITH base AS (
SELECT
  EXTRACT(WEEK FROM query_date) AS week_number,
  user,
  team,
  COUNT(user) AS query_cnt
FROM advanced.query_logs
GROUP BY ALL
)

SELECT
  *,
  RANK() OVER(PARTITION BY week_number, team ORDER BY query_cnt DESC) AS rnk
FROM base
QUALIFY
  rnk = 1
ORDER BY
  week_number, team, query_cnt DESC

-- 문제3. (2번 문제에서 사용한 주 차별 쿼리 사용)쿼리를 실행한 시점 기준 1주 전에 쿼리 실행 수를 별도의 컬럼으로 확인할 수 있는 쿼리를 작성해주세요.
WITH base AS (
SELECT
  EXTRACT(WEEK FROM query_date) AS week_number,
  user,
  team,
  COUNT(user) AS query_cnt
FROM advanced.query_logs
GROUP BY ALL
)

SELECT
  *,
  LAG(query_cnt) OVER(PARTITION BY user ORDER BY week_number) AS lag1_query_cnt
FROM base
ORDER BY
  user, week_number

-- 문제4. 시간의 흐름에 따라, 일자 별로 유저가 실행한 누적 쿼리 수를 작성해주세요.
WITH base AS (
SELECT
  query_date,
  team,
  user,
  COUNT(query_date) AS query_cnt,
FROM advanced.query_logs
GROUP BY ALL
)

SELECT
  *,
  SUM(query_cnt) OVER(PARTITION BY user ORDER BY query_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_query_cnt,
  SUM(query_cnt) OVER(PARTITION BY user ORDER BY query_date) AS cumulative_query_cnt2
FROM base
ORDER BY
  user, query_date

-- 문제5. 시간의 흐름에 따라, 일자 별로 유저가 실행한 누적 쿼리 수를 작성해주세요.
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 -- 범위 디폴트 값이 현재 행부터 이전 모든 행까지의 기준이기 때문에 null 값인 경우 직전에 있는 행 값을 가져옴
FROM raw_data

-- 문제6. 5번 문제에서 NULL을 채운 후,2일 전 ~ 현재 데이터의 평균을 구하는 쿼리를 작성해주세요 (이동평균)
SELECT
  date,
  last_value_orders,
  AVG(last_value_orders) OVER(ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM (
  SELECT
    *,
    LAST_VALUE(number_of_orders IGNORE NULLS) OVER(ORDER BY date) AS last_value_orders -- 범위 디폴트 값이 현재 행부터 이전 모든 행까지의 기준이기 때문에 null 값인 경우 직전에 있는 행 값을 가져옴
  FROM raw_data
 )

-- 문제7. app_logs 테이블에서 CustomSession을 만들어주세요. 이전 이벤트 로그와 20초가 지나면 새로운 Session을 만들어주세요. Session은 숫자로 (1,2,3…)표시해도 됩니다.
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,
  LAG(DATETIME(TIMESTAMP_MICROS(event_timestamp), "Asia/Seoul")) OVER(PARTITION BY user_pseudo_id ORDER BY DATETIME(TIMESTAMP_MICROS(event_timestamp), "Asia/Seoul")) AS before_event_datetime,
FROM `advanced.app_logs`
WHERE
  1=1
  AND event_date = "2022-08-18"
  AND user_pseudo_id = "1997494153.8491999091"
),

session AS (
SELECT
  *,
  CASE
    WHEN second_diff IS NULL THEN 1
    WHEN second_diff > 20 THEN 1
    -- ELSE NULL
  END AS session_start
FROM (
  SELECT
    *,
    DATETIME_DIFF(event_datetime, before_event_datetime, SECOND) AS second_diff
  FROM base
  ) 
)

SELECT
  *,
  SUM(session_start) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) AS session_id
FROM session