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

강의 2-4: 연습 문제1

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

SELECT
  *,
  LEAD(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month) AS next_visit_month,
  LEAD(visit_month, 2) OVER(PARTITION BY user_id ORDER BY visit_month) AS second_next_visit_month
FROM `bqmaster.advanced.analytics_function_01`
ORDER BY user_id

강의 2-4: 연습 문제2

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

SELECT
  *,
  LEAD(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month) AS next_visit_month,
  LEAD(visit_month, 2) OVER(PARTITION BY user_id ORDER BY visit_month) AS second_next_visit_month,
  LAG(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month) AS previous_visit_month
FROM `bqmaster.advanced.analytics_function_01`
ORDER BY user_id

강의 2-4: 연습 문제3

user가 접속했을 때, 다음 접속까지의 간격을 구하시오.

SELECT
  *,
  LEAD(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month) - visit_month AS next_visit_month_diff
FROM `bqmaster.advanced.analytics_function_01`
ORDER BY user_id

-- 서브 쿼리 활용
SELECT
  user_id,
  visit_month,
  next_visit_month - visit_month AS next_visit_month_diff
FROM (
  SELECT
    *,
    LEAD(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month) AS next_visit_month
  FROM `bqmaster.advanced.analytics_function_01`
)
ORDER BY user_id

강의 2-4: 연습 문제4

이 데이터셋을 기준으로 user_id의 첫번째 방문 월, 마지막 방문 월을 구하는 쿼리를 작성해주세요.

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 `bqmaster.advanced.analytics_function_01`
ORDER BY user_id

강의 2-8: 연습 문제1

amount_total, cumulative_sum, cumulative_sum_by_user, last_5_orders_avg_amount 컬럼 구하기

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 `bqmaster.advanced.orders`
ORDER BY order_id

강의 2-11: 연습 문제1

사용자별 쿼리를 실행한 총 횟수를 구하는 쿼리를 작성해주세요. 단, GROUP BY를 사용해서 집계하는 것이 아닌, query_logs의 데이터의 우측에 새로운 컬럼을 만들어주세요.

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

강의 2-11: 연습 문제2

주차별로 팀 내에서 쿼리를 많이 실행한 수를 구한 수, 실행한 수를 활용해 랭킹을 구해주세요. 단, 랭킹이 1등인 사람만 결과가 보이도록 해주세요.

SELECT
  *,
  RANK() OVER(PARTITION BY week_number, team ORDER BY query_cnt DESC) AS team_rank
FROM (
  SELECT
    EXTRACT(WEEK FROM query_date) AS week_number,
    team,
    user,
    COUNT(user) query_cnt
  FROM `bqmaster.advanced.query_logs`
  GROUP BY ALL
)
QUALIFY
  team_rank = 1
ORDER BY week_number, team

강의 2-11: 연습 문제3

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

SELECT
  *,
  LAG(query_count) OVER(PARTITION BY user ORDER BY week_number) AS prev_week_query_count
FROM (
  SELECT
    user,
    team,
    EXTRACT(WEEK FROM query_date) AS week_number,
    COUNT(user) query_count
  FROM `bqmaster.advanced.query_logs`
  GROUP BY ALL
)
ORDER BY user, team

강의 2-11: 연습 문제4

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

SELECT
  *,
  SUM(query_count) OVER(PARTITION BY user ORDER BY query_date) AS cumulative_query_count
FROM (
  SELECT
    *,
    COUNT(user) AS query_count
  FROM `bqmaster.advanced.query_logs`
  GROUP BY ALL
)
ORDER BY user, query_date

강의 2-11: 연습 문제5

다음 데이터는 주문 횟수를 나타낸 데이터입니다. 만약 주문 횟수가 없으면 NULL로 기록됩니다. 이런 데이터에서 NULL 값이라고 되어있는 부분을 바로 이전 날짜의 값으로 채워주는 쿼리를 작성해주세요.

SELECT
  date,
  LAST_VALUE(number_of_orders IGNORE NULLS) OVER(ORDER BY date) AS number
FROM raw_data
ORDER BY date

강의 2-11: 연습 문제6

5번 문제에서 NULL을 채운 후, 2일 전 ~ 현재 데이터의 평균을 구하는 쿼리를 작성해주세요(이동 평균)

SELECT
  *,
  AVG(number) 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
  FROM raw_data
)
ORDER BY date

강의 2-11: 연습 문제7

app_logs 테이블에서 Custom Session을 만들어 주세요. 이전 이벤트 로그와 20초가 지나면 새로운 Session을 만들어 주세요. Session은 숫자로(1, 2, 3) 표시해도 됩니다.

WITH base AS (
  SELECT
    event_date,
    DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime,
    event_name,
    user_id,
    user_pseudo_id
  FROM `bqmaster.advanced.app_logs`
), add_before_datetime AS(
  SELECT
    *,
    LAG(event_datetime) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) AS before_event_datetime
  FROM base
), add_second_diff AS (
  SELECT
    *,
    DATE_DIFF(event_datetime, before_event_datetime, SECOND) AS second_diff
  FROM add_before_datetime
), add_session_start AS (
  SELECT
    *,
    CASE
      WHEN second_diff IS NULL THEN 1
      WHEN second_diff >= 20 THEN 1
      ELSE NULL
    END AS session_start
  FROM add_second_diff
)

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