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

김해리님의 프로필 이미지
김해리

작성한 질문수

BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)

[빠짝스터디 2주차 과제] 윈도우 함수 탐색 함수 연습 문제/ 윈도우 함수 FRAME 연습 문제/ 윈도우 함수 총정리 연습 문제

작성

·

88

·

수정됨

0

1. 탐색 함수 연습 문제

# 1. user들의 다음 접속 월과 다다음 접속 월을 구하는 쿼리를 작성해주세요.
# 쿼리를 작성하는 목표, 확인할 지표: user_id의 다음/다다음 visit_month 출력
# 쿼리 계산 방법: LEAD
# 데이터의 기간: X
# 사용할 테이블: advanced.analytics_function_01
# JOIN KEY: X
# 데이터 특징: X

SELECT
  user_id,
  visit_month,
  LEAD(visit_month, 1) 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 after_two_visit_month,
FROM advanced.analytics_function_01
ORDER BY
  user_id
# 2. user들의 다음 접속 월과 다다음 접속 월, 이전 접속 월을 구하는 쿼리를 작성해주세요.
# 쿼리를 작성하는 목표, 확인할 지표: user_id의 다음/다다음 visit_month 출력
# 쿼리 계산 방법: LEAD
# 데이터의 기간: X
# 사용할 테이블: advanced.analytics_function_01
# JOIN KEY: X
# 데이터 특징: X

SELECT
  user_id,
  visit_month,
  LEAD(visit_month, 1) 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 after_two_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
# 3. 유저가 접속했을 때, 다음 접속까지의 간격을 구하시오.

SELECT
  *,
  (after_visit_month - visit_month) AS interval_of_after_visit_month
FROM (
  SELECT
    *,
    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. 유저의 첫번째 방문 월, 마지막 방문 월을 구하는 쿼리를 작성해주세요.

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

 

2. FRAME 연습 문제

# Frame 연습 문제: 총 수량(amount_total), 수량의 누적 합(cumulativ_sum), 유저별 수량의 누적 합(cumulative_sum(user)), 최근 5개 수량의 평균(last_5_avg) 출력
# 쿼리를 작성하는 목표, 확인할 지표: 수량의 총합 또는 누적 합 구하기
# 쿼리 계산 방법: 윈도우 함수 - AVG, SUM
# 데이터의 기간: X
# 사용할 테이블: advanced.orders
# JOIN KEY: X
# 데이터 특징: X

SELECT
  *,
  SUM(amount) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS amount_total,
  SUM(amount) OVER (ORDER BY order_date, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum,
  SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum_user,
  AVG(amount) OVER (ORDER BY order_date, order_id ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS last_5_avg
FROM advanced.orders
ORDER BY order_id

 

3. 총정리 연습 문제

# 1. 사용자별 쿼리를 실행한 총 횟수를 구하는 쿼리를 작성해주세요. 우측에 새로운 컬럼을 만들어주세요.
# 쿼리를 작성하는 목표, 확인할 지표: 유저별 쿼리 실행 총 횟수
# 쿼리 계산 방법: 윈도우 함수 COUNT
# 데이터의 기간: X
# 사용할 테이블: advanced.query_logs
# JOIN KEY: X
# 데이터 특징: X

SELECT
  *,
  COUNT(user) OVER (PARTITION BY user) AS total_query_cnt
FROM advanced.query_logs
ORDER BY query_date, user
# 2. 주차별로 팀 내에서 쿼리를 많이 실행한 수를 구한 후, 실행한 수를 활용해 랭킹을 구해주세요. 단, 랭킹이 1등인 사람만 결곽가 보이도록 해주세요.
# 쿼리를 작성하는 목표, 확인할 지표: 주차별 쿼리 실행 수 랭킹, 팀 내 1등인 사람만 출력
# 쿼리 계산 방법: 윈도우 함수 COUNT, RANK
# 데이터의 기간: X
# 사용할 테이블: advanced.query_logs
# JOIN KEY: X
# 데이터 특징: X

WITH create_week_number AS (
  SELECT
    CASE
      WHEN query_date BETWEEN '2024-04-24' AND '2024-04-26' THEN 16
      ELSE 17
    END AS week_number,
    team,
    user
  FROM advanced.query_logs
), create_query_cnt AS (
  SELECT
    *,
    COUNT(user) OVER(PARTITION BY week_number, user) AS query_cnt
  FROM create_week_number
)

SELECT
  DISTINCT *,
  RANK() OVER (PARTITION BY week_number, team ORDER BY query_cnt DESC) AS team_rank
FROM create_query_cnt
QUALIFY team_rank = 1
ORDER BY week_number, team
# 3. (2번 문제에서 사용한 주차별 쿼리 사용) 쿼리를 실행한 시점 기준 1주 전에 쿼리 실행 수를 별도의 컬럼으로 확인할 수 있는 쿼리를 작성해주세요.
# 쿼리를 작성하는 목표, 확인할 지표: 주차별 쿼리 실행 수, 1주 전 쿼리 실행 수 출력
# 쿼리 계산 방법: 윈도우 함수 COUNT
# 데이터의 기간: X
# 사용할 테이블: advanced.query_logs
# JOIN KEY: X
# 데이터 특징: X

WITH create_week_number AS (
  SELECT
    user,
    team,
    CASE
      WHEN query_date BETWEEN '2024-04-24' AND '2024-04-26' THEN 16
      ELSE 17
    END AS week_number
  FROM advanced.query_logs
), create_query_cnt AS (
  SELECT
    DISTINCT *,
    COUNT(user) OVER(PARTITION BY week_number, user) AS query_cnt
  FROM create_week_number
)

SELECT 
  *,
  LAG(query_cnt) OVER (PARTITION BY user ORDER BY week_number) AS prev_week_query_cnt
FROM create_query_cnt
ORDER BY user, week_number
# 4. 시간의 흐름에 따라, 일자별로 유저가 실행한 누적 쿼리 수를 작성해주세요.
# 쿼리를 작성하는 목표, 확인할 지표: 유저별 누적 쿼리 수 시간순 출력
# 쿼리 계산 방법: 윈도우 함수 COUNT, SUM
# 데이터의 기간: X
# 사용할 테이블: advanced.query_logs
# JOIN KEY: X
# 데이터 특징: X

WITH create_query_cnt AS (
  SELECT
    DISTINCT *,
    COUNT(user) OVER (PARTITION BY user, query_date ORDER BY user, query_date) AS query_cnt
  FROM advanced.query_logs
)

SELECT
  *,
  SUM(query_cnt) OVER (PARTITION BY user ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_query_cnt
FROM create_query_cnt
ORDER BY user, query_date
# 5. 다음 데이터는 주문 횟수를 나타낸 데이터입니다. 만약 주문 횟수가 없으면 NULL로 기록됩니다. 이런 데이터에서 NULL 값이라고 되어있는 부분을 바로 이전 날짜의 값으로 채워주는 쿼리를 작성해주세요.
# 쿼리를 작성하는 목표, 확인할 지표: NULL로 작성된 주문 횟수를 이전 날짜의 값으로 출력
# 쿼리 계산 방법: 윈도우 함수 LAG
# 데이터의 기간: X
# 사용할 테이블: 쿼리에서 새로 만든 raw_data
# JOIN KEY: X
# 데이터 특징: 일자별(date) 주문횟수(number_of_orders)를 나타냄

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,
  IF (number_of_orders IS NULL, LAG(number_of_orders) OVER (ORDER BY date), number_of_orders) AS number_of_orders
FROM raw_data
ORDER BY date
# 6. 5번 문제에서 NULL을 채운 후, 2일 전 ~ 현재 데이터의 평균을 구하는 쿼리를 작성해주세요.(이동 평균)
# 쿼리를 작성하는 목표, 확인할 지표: 2일 전부터 현재까지의 number_of_orders의 평균 값 출력
# 쿼리 계산 방법: 윈도우 함수 AVG
# 데이터의 기간: X
# 사용할 테이블: 쿼리에서 새로 만든 raw_data
# JOIN KEY: X
# 데이터 특징: X

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
), fill_null AS (
  SELECT
    date,
    IF (number_of_orders IS NULL, LAG(number_of_orders) OVER (ORDER BY date), number_of_orders) AS number_of_orders
  FROM raw_data
  ORDER BY date
)

SELECT
  *,
  AVG(number_of_orders) OVER (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM fill_null
# 7. app_logs 테이블에서 Custom Session을 만들어 주세요. 이전 이벤트 로그와 20초가 지나면 새로운 Session을 만들어 주세요. Session은 숫자로 (1, 2, 3 …) 표시해도 됩니다. 2022-08-18일의 user_pseudo_id(1997494153.8491999091)은 session_id가 4까지 나옵니다.
# 쿼리를 작성하는 목표, 확인할 지표: event_date, event_timestamp, event_name, user_id, user_pseudo_id 추출 => event_datetime, before_event_datetime, second_diff, session_start, session_id 생성
# 쿼리 계산 방법: 윈도우 함수 LAG (before_event_datetime 생성 시), 
# 데이터의 기간: X
# 사용할 테이블: advanced.app_logs
# JOIN KEY: X
# 데이터 특징: X

WITH create_event_datetime 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
), create_before_event_datetime AS (
  SELECT
    *,
    LAG(event_datetime) OVER (PARTITION BY user_pseudo_id ORDER BY event_datetime) AS before_event_datetime
  FROM create_event_datetime
), create_second_diff AS (
  SELECT
    *,
    DATETIME_DIFF(event_datetime, before_event_datetime, second) AS second_diff
  FROM create_before_event_datetime
), create_session_start AS (
  SELECT
    *,
    IF ((second_diff IS NULL) OR (second_diff >= 20), 1, 0) AS session_start
  FROM create_second_diff
)

SELECT
  * EXCEPT(session_start),
  IF (session_start = 0, NULL, session_start) AS session_start,
  SUM(session_start) OVER (PARTITION BY user_pseudo_id, event_date ORDER BY event_datetime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_id
FROM create_session_start
ORDER BY user_pseudo_id, event_timestamp
sqlGoogle-Analyticsfirebasegoogle-sheetsbigquery

답변 1

0

인프런 AI 인턴님의 프로필 이미지
인프런 AI 인턴

안녕하세요, 인프런 AI 인턴입니다. 김해리님. 윈도우 함수와 관련된 다양한 연습 문제를 통해 실력을 더욱 향상시키고 계신 것 같아 기쁩니다. 윈도우 함수는 SQL에서 매우 강력한 도구이며, 데이터 분석 및 집계 작업에 많은 도움을 줄 수 있습니다.

김해리님께서 작성하신 쿼리들은 일부 개선의 여지가 있지만, 전반적으로 훌륭하게 구성되어 있습니다. 이러한 연습 문제들을 해결하는 과정에서 자주 발생할 수 있는 질문과 관련된 몇 가지 유사한 게시물과 답변을 참고하시길 권장드립니다. 아래의 링크들을 통해 더 많은 정보를 얻으실 수 있습니다.

  1. Bigquery 활용편(PDF)파일 P.229 페이지 코드 오류

    • 윈도우 함수에서 LAST_VALUE 같은 함수의 활용법을 설명하고 있으며, DEFAULT 설정의 중요성에 대해 다루고 있습니다.

  2. windows function default 값에 대하여 range between과 rows between의 차이

    • 윈도우 함수에서 RANGE와 ROWS의 차이점에 대한 토론을 제공합니다. 김해리님이 사용하신 ROWS와 RANGE의 이용 상황을 비교하면서 학습하시면 좋습니다.

이 링크들은 다양한 케이스에서 window function을 사용하는 방법과 주의해야 할 점을 안내할 수 있을 것입니다.

저는 김해리님의 이해를 돕고자 지식공유자님의 기존 답변을 참고하여 유사한 답변 링크를 추천해드리고 있습니다.
현재 베타 기능으로 답변이 만족스럽지 않을 수 있는 점 양해 부탁드립니다. 🙏
추가적으로 궁금한 점이 있으시면, 이어서 질문해 주세요. 곧 지식공유자께서 답변해 주실 것입니다.

김해리님의 프로필 이미지
김해리

작성한 질문수

전체 Q&A
질문하기