[빠짝스터디 2주차 과제] 윈도우 함수 연습문제

탐색 함수 연습문제 1

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

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

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

-- 다음접속월 LEAD
-- 다다음접속월 LEAD
-- 이전접속월 LAG
SELECT 
	*,
	LEAD(visit_month, 1) 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 after_two_visit_month,
	LAG(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month ASC) AS before_visit_month
FROM advanced.analytics_function_01

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

-- user_id | visit_month | after_visit_month | diff_month
-- 일단 LEAD, LAG 구하고 after visit month 에서 visit month 뺴기
SELECT *,
  (after_visit_month - visit_month) AS diff_month
FROM (
  SELECT *,
    LEAD(visit_month, 1) OVER(PARTITION BY user_id ORDER BY visit_month) AS after_visit_month,
  FROM advanced.analytics_function_01
) 
ORDER BY user_id, visit_month ASC

• 별칭을 정의하고 실행 한 뒤 다음 쿼리에서 그걸 이용해야 하기 때문에 서브쿼리를 사용해야함

길이가 짧은 쿼리

SELECT
	*,
	LEAD(visit_month, 1) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_visit_month,
	LEAD(visit_month, 1) OVER (PARTITION BY user_id ORDER BY visit_month) - visit_month AS diff_month
FROM advanced.analytics_function_01

길이는 더 짧지만 같은 코드가 중복되기 때문에 코드를 수정해야 할 경우 두 번 수정해야함.

4. user_id 의 첫번째 방문 월, 마지막 방문 월을 구하는 쿼리를 작성해주세요

-- 첫번째방문 : FIRST_VALUE
-- 마지막방문 : LAST_VALUE
SELECT 
	*,
	FIRST_VALUE(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_visit_month,
	LAST_VALUE(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_visit_month
FROM advanced.analytics_function_01
ORDER BY user_id, visit_month

FRAME 연습문제 2

1. SUM : amount_total 구하기

2. 누적합 : cumulative_sum 구하기

3. 유저별 누적합 : cumulative_sum(user)

4. 직전 5개 평균값 : last_5_avg ** 직전이므로 current row 포함되면 안 됨

SELECT 
	*,
	-- amount 전체 합
	SUM(amount) OVER() AS amount_total,
	-- 누적 합 cumulative_sum
	SUM(amount) OVER(ORDER BY order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as cumulative_sum,
	-- 유저별 누적합 cumulative_sum(user)
	SUM(amount) OVER(
		PARTITION BY user_id ORDER BY order_id
		ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
		) as cumulative_sum_user,
	-- 직전 5개 주문의 평균값 last_5_avg
	AVG(amount) OVER(ORDER BY order_id ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) as last_5_avg
FROM advanced.orders
ORDER BY order_id

윈도우 함수 연습문제 3

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

SELECT 
  *,
  COUNT(query_date) OVER(
	  PARTITION BY user ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
		 ) as total_query_cnt
FROM advanced.query_logs
ORDER BY query_date

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

WITH weekly AS ( 
-- 날짜를 주로 변환
  SELECT 
    *,
    EXTRACT(WEEK FROM query_date) AS week_number
  FROM advanced.query_logs
),
querycnt AS (
-- 쿼리 실행 횟수 구하기
  SELECT 
    week_number,
    team,
    user,
    COUNT(query_date) OVER (PARTITION BY week_number, user) AS query_cnt
  FROM
    weekly
),
team_ranks AS (
  -- 팀 내에서 쿼리 많이 작성한 사람 랭크 매겨서, 주차별로 파티션 나누고, 랭크 1만 보이게
 SELECT
  week_number,
  team,
  user,
  query_cnt,
  ROW_NUMBER() OVER(PARTITION BY week_number, team ORDER BY query_cnt DESC) AS team_rank
  -- 랭킹 행이 여러개 출력되어서 ROW_NUMBER 로 하나만 나오게 구함 
 FROM
  querycnt
)
SELECT 
  week_number,
  team,
  user,
  query_cnt,
  team_rank
FROM team_ranks
WHERE team_rank = 1
ORDER BY week_number ASC, team ASC;

-- GROUP BY 이용
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 team_rank
FROM
  query_cnt_by_team
QUALIFY team_rank =1
ORDER BY week_number, team, query_cnt DESC

3. 2번 문제에서 사용한 주차별 쿼리 사용해 쿼리를 실행한 시점 기준 1주전 에 쿼리 실행 수를 별도의 컬럼으로 확인할 수 있는 쿼리를 작성해주세요

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
  user,
  team,
  week_number,
  query_cnt,
  LAG(query_cnt) OVER (PARTITION BY team, user ORDER BY week_number, query_cnt) AS prev_week_query_cnt
FROM
  query_cnt_by_team
ORDER BY user, team

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

-- 시간의 흐름에 따라 => query_date 기준
-- 유저별, 일자별 쿼리수 합계 => SUM() OVER(PARTITION BY user ~
-- 누적 쿼리수 계산 새 컬럼 cumulative_query_cnt
=> SUM() OVER(PARTITION BY  ORDER BY ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_query_cnt

WITH querycnt AS(
 SELECT 
    user,
    team,
    query_date,
    COUNT(user) AS query_count
  FROM advanced.query_logs
  GROUP BY ALL
  ORDER BY user ASC, query_count DESC
)
SELECT
  user,
  team,
  query_date,
  query_count,
  SUM(query_count) OVER(PARTITION BY team, user ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_query_cnt
FROM
  querycnt
ORDER BY user, query_date ASC, query_count DESC

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

SELECT
  *,
  LAST_VALUE(number_of_orders IGNORE NULLS) OVER(ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS last_orders
FROM raw_data

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

SELECT
  date,
  last_orders,
  AVG(last_orders) OVER(ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg
FROM(
  SELECT
    *,
    LAST_VALUE(number_of_orders IGNORE NULLS) OVER(ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS last_orders
  FROM raw_data
)

7. app_logs 테이블에서 Custom Session을 만들어주세요. 이전 이벤트 로그와 20초가 지나면 새로운 Session을 만들어주세요 Session은 숫자로 (1,2,3...) 표시해도 됩니다. 2022-08-18일의 user_pseudo_id(1997494153.8491999091)은 session_id가 4까지 나옵니다.

WITH time2 AS (
  SELECT
    event_date,
    event_timestamp,
    TIMESTAMP_MICROS(event_timestamp) + INTERVAL 9 HOUR AS event_datetime,
-- 구글링 검색하다가 한국 시간 기준으로 변환하는 방법찾아서 적용해봄
    event_name,
    user_id,
    user_pseudo_id
  FROM advanced.app_logs
),
lagged_data AS (
  SELECT
    *,
    LAG(event_datetime) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) AS before_event_datetime
  FROM
    time2
-- 결과 짧게 보려고 날짜 필터링
  WHERE
    event_date = '2022-08-18'
),
session_data AS (
  SELECT
    *,
    TIMESTAMP_DIFF(event_datetime, before_event_datetime, SECOND) AS second_diff
  FROM
    lagged_data
)
SELECT
  *,
  -- session_start: 첫 이벤트에만 1, 나머지는 NULL
  IF(before_event_datetime IS NULL, 1, NULL) AS session_start,
  -- session_id: 첫 이벤트에서 1로 시작하고, 이후 20초 이상 차이가 생길 때마다 1씩 증가
  SUM(IF(before_event_datetime IS NULL OR second_diff >= 20, 1, 0)) OVER (
    PARTITION BY user_pseudo_id
    ORDER BY event_datetime
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS session_id
FROM
  session_data
ORDER BY 
  user_pseudo_id, event_datetime