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

연습 문제 (1)

-- 1) 
SELECT user_id
     , visit_month
     , LEAD(visit_month, 1) OVER (PARTITION BY user_id ORDER BY visit_month) AS lead_visit_month
     , LEAD(visit_month, 2) OVER (PARTITION BY user_id ORDER BY visit_month) AS lead2_visit_month
FROM advanced.analytics_function_01
ORDER BY user_id

-- 2) 
SELECT user_id
     , visit_month
     , LEAD(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_visit_month
     , LEAD(visit_month, 2) OVER (PARTITION BY user_id ORDER BY visit_month) AS after2_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) 유저가 접속했을 때, 다음 접속까지의 간격을 구하시오.
SELECT *
     , after_visit_month - visit_month AS diff_month
FROM (
  SELECT 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의 첫번째 방문 월, 마지막 방문 월을 구하는 쿼리를 작성해주세요.
SELECT *
    , FIRST_VALUE(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS first_month 
    , LAST_VALUE(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS last_month
FROM advanced.analytics_function_01
ORDER BY user_id
       , visit_month

-- 윈도우 함수 - 데이터 범위 지정
SELECT *
     , SUM(amount) OVER () AS amount_sum
     , 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

연습 문제 (2)

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


-- 2)
WITH query_cnt_by_team AS (

  SELECT EXTRACT(WEEK FROM query_date) AS week_number
       , team
       , user
       , 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 rk
FROM query_cnt_by_team
QUALIFY rk = 1
ORDER BY week_number
       , team
       , query_cnt DESC

-- 3)
WITH query_cnt_by_team AS (

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

)

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

-- 4)
SELECT query_date
     , team
     , user
     , query_cnt
     , SUM(query_cnt) OVER (PARTITION BY user ORDER BY query_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum
FROM (
  SELECT query_date
       , team
       , user
       , COUNT(user) AS query_cnt
  FROM advanced.query_logs
  GROUP BY ALL
)
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 before_number_of_orders
FROM raw_data


-- 6)
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 * EXCEPT(number_of_orders)
       , LAST_VALUE(number_of_orders IGNORE NULLS) OVER (ORDER BY date) AS number_of_orders
  FROM raw_data

)

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


-- 7) 
WITH step1 AS (

  SELECT * EXCEPT(event_params, platform)
       , DATETIME(TIMESTAMP_MICROS(event_timestamp), "Asia/Seoul") AS event_datetime
  FROM advanced.app_logs
  WHERE user_pseudo_id = "1997494153.8491999091"
    AND event_date = '2022-08-18'

),
step2 AS (

     SELECT *
           , DATETIME_DIFF(event_datetime, prev_event_datetime, SECOND) AS second_diff
     FROM (
          SELECT *
               , LAG(event_datetime) OVER (ORDER BY event_datetime) AS prev_event_datetime
          FROM step1
          )
     ORDER BY event_datetime

)

SELECT *
     , SUM(session_start) OVER (ORDER BY event_datetime) AS session_num
FROM (
     SELECT *
           , CASE WHEN second_diff IS NULL THEN 1
                  WHEN second_diff >= 20 THEN 1
                  ELSE 0
           END AS session_start
     FROM step2
)
ORDER BY event_datetime