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

1. 윈도우 함수 탐색 함수 연습 문제

1) 유저의 다음 접속 월과 다다음 접속 월 구하기

SELECT
  user_id
  , visit_month
  , LEAD(visit_month) 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

2) 이전 접속월 구하기

SELECT
  user_id
  , visit_month
  , LAG(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS before_visit_month
FROM advanced.analytics_function_01

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
)

4) 첫번째와 마지막 방문 월 구하기

SELECT
  user_id
  , visit_month
  , FIRST_VALUE(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS first_visit_month
  , LAST_VALUE(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS last_visit_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 user_cumulative_sum
  , AVG(amount) OVER (ORDER BY order_id ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS last_5_avg
FROM advanced.orders

3. 윈도우 함수 연습문제

1) 사용자 별 쿼리를 실행한 총 횟수 구하는 쿼리를 작성해주세요.

SELECT
  *,
  COUNT(query_date) OVER(PARTITION BY user ORDER BY query_date) AS total_query_cnt # 38 row 
FROM `advanced.query_logs`

2) 주차별로 팀내에서 쿼리를 많이 실행한 수를 구한후, 실행한수를 활용해 랭킹을 구해주세요.

WITH base 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 team ORDER BY query_cnt) AS rk
FROM base
QUALIFY rk = 1 
ORDER BY week_number, team, query_cnt DESC 

3) 쿼리를 실행한 시점 기준1주전에 쿼리 실행 수를 별도의 컬럼으로 확인할 수 있는 쿼리를 작성해주세요

WITH base 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_cnt 
FROM base

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

SELECT
  *,
  SUM(query_cnt) OVER(PARTITION BY user ORDER BY query_date) AS cumulative_sum,
  SUM(query_cnt) OVER(PARTITION BY user ORDER BY query_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum2
FROM(
  SELECT
    query_date,
    team,
    user,
    COUNT(user) AS query_cnt
  FROM advanced.q

5) 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) 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_date 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_date

7) app_logs테이블에서 CustomSession을 만들어주세요. 이전 이벤트 로그와 20초가 지나면 새로운 Session을 만들어주세요.

WITH base AS(

  SELECT
    EVENT_DATE,
    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"
)
, diff_date AS(SELECT
      *, 
      DATETIME_DIFF(event_datetime, prev_event_datetime, SECOND) AS second_diff
    FROM (
      SELECT
      *,
      LAG(EVENT_DATETIME, 1) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) AS prev_event_datetime
    FROM base
    ORDER BY base.event_datetime))

SELECT
  *,
  SUM(session_start) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) AS session_num

FROM (
    SELECT
    *,
    CASE
      WHEN prev_event_datetime IS NULL THEN 1
      WHEN second_diff >= 20 THEN 1 
      ELSE 0
      END AS session_start
  FROM diff_date)