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

탐색함수 연습문제

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

LEAD는 반드시 정렬이 먼저 되어야 함 -> ORDER BY 추가

SELECT *,
       LEAD(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month ASC) AS lead_visit_month,
       LEAD(visit_month,2) OVER (PARTITION BY user_id ORDER BY visit_month ASC) AS lead2_visit_month



FROM advanced.analytics_function_01


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


SELECT *,
       LEAD(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month ASC) AS after_visit_month,
       LEAD(visit_month,2) OVER (PARTITION BY user_id ORDER BY visit_month ASC) AS after2_visit_month,
       LAG(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month ASC) AS before_visit_month

ORDER BY user_id, visit_month       
FROM advanced.analytics_function_01


문제 3) 유저가 접속 했을 때, 다음 접속까지의 간격


SELECT *,
       after_visit_month - visit_month AS diff

FROM
(       
    SELECT *,
    LEAD(visit_month, 1) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_visit_month

    FROM advanced.analytics_function_01
)

FRAME 연습문제

-- amount_total: 전체 SUM
-- cumulative_sum : row 시점에 누적 SUM
-- cumulative_sum_by_user : row 시점에 유저별 누적 SUM
-- last_5_orders_avg_amount : order_id 기준으로 정렬하고, 직전 5개 주문의 평균 amount
-- 집계분석함수() OVER(PARTITION BY ~~~ ORDER BY ~~~ ROWS BETWEEN A AND B)



SELECT *,
       SUM(amount) OVER() AS amount_total,
-- OVER()에 아무것도 들어가지 않을 수 있음
       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)
       

FROM advanced.orders      
ORDER BY user_id, order_date

***QUALIFY 연습문제

QUALIFY(조건 설정)



WHERE 대신 QUALIFY를 사용하면 윈도우 함수의 결과에 대해 필터링할 수 있음
WHERE과 같이 사용하는 경우엔 WHERE 아래에 작성하면 됨

 SELECT
 order_id,
 order_date,
 user_id,
 amount,
 SUM(amount) OVER (PARTITION BY user_id) AS amount_total
 FROM advanced.orders
 WHERE 1=1
 QUALIFY amount_total >= 500

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

문제 1

SELECT user,
       team,
       query_date,
       COUNT(query_date) OVER (PARTITION BY user) AS total_query_cnt
FROM advanced.query_logs
ORDER BY user, query_date


문제 2 

WITH base AS (
    SELECT 
          EXTRACT(week FROM query_date) AS week_number,
          user,
          team,
          COUNT(query_date) AS total_query_cnt
  FROM advanced.query_logs
  GROUP BY week_number, user, team
)

SELECT 
      week_number,
      team,
      user,
      total_query_cnt,
      RANK() OVER (PARTITION BY team ORDER BY total_query_cnt DESC) AS ranking_in_team
FROM base
QUALIFY ranking_in_team = 1
ORDER BY week_number, team

문제 2

WITH base AS (
    SELECT 
          EXTRACT(week FROM query_date) AS week_number,
          user,
          team,
          COUNT(query_date) AS total_query_cnt
  FROM advanced.query_logs
  GROUP BY week_number, user, team
)

SELECT 
      week_number,
      team,
      user,
      total_query_cnt,
      RANK() OVER (PARTITION BY team ORDER BY total_query_cnt DESC) AS ranking_in_team
FROM base
QUALIFY ranking_in_team = 1
ORDER BY week_number, team

문제 3

WITH base AS (
    SELECT 
          EXTRACT(week FROM query_date) AS week_number,
          user,
          team,
          COUNT(query_date) AS query_cnt
  FROM advanced.query_logs
  GROUP BY week_number, user, team
)

SELECT 
      user,
      team,
      week_number,
      query_cnt,
      LAG(query_cnt) OVER (PARTITION BY user ORDER BY week_number) AS prev_week_query_cnt
FROM base 
ORDER BY user, week_number

문제 4

문제 4

SELECT 
      user,
      team,
      query_date,
      query_count,
      SUM(query_count) OVER (PARTITION BY user ORDER BY query_date,
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_query_count
FROM (
      SELECT 
          user, 
          team,
          query_date,
          COUNT(query_date) AS query_count
  FROM advanced.query_logs
  GROUP BY 1,2,3
)
ORDER BY user, query_date

문제 5

문제 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 
      date,
      LAST_VALUE(number_of_orders ignore nulls) OVER (ORDER BY date) AS 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
)

SELECT 
      date, 
      number_of_orders,
      AVG(number_of_orders) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM (
      SELECT 
            date,
            LAST_VALUE(number_of_orders ignore nulls) OVER (ORDER BY date) AS number_of_orders
      FROM raw_data
)

문제 7

문제 7


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 event_timestamp) AS before_event_datetime
  FROM advanced.app_logs
  WHERE 1=1
  AND event_date = '2022-08-18'
)

SELECT
    *,
    DATETIME_DIFF(event_datetime, before_event_datetime, SECOND) AS second_diff,
    CASE WHEN 
      DATETIME_DIFF(event_datetime, before_event_datetime, SECOND) IS NULL
          OR DATETIME_DIFF(event_datetime, before_event_datetime, SECOND) >= 20 THEN 1 
      ELSE 0 
    END AS session_start,
    SUM(CASE WHEN DATETIME_DIFF(event_datetime, before_event_datetime, SECOND) >= 20 THEN 1 ELSE 0 END)
    OVER (PARTITION BY user_pseudo_id ORDER BY event_datetime) + 1 AS session_temp
FROM base