윈도우 함수 - LEAD, LAG, FIRST_VALUE, LAST_VALUE

문제 1

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

답:

# 문제 : user들의 다음 접속 월과 다다음 접속 월을 구하는 쿼리를 작성해주세요.
# 쿼리를 작성하는 목표, 확인할 지표 : 
# 쿼리 계산 방법 : LEAD 1,2
# 데이터의 기간 : 
# 사용할 테이블 : 
# Join KEY : 
# 데이터 특징 : 
# 작성 흐름 : FROM - JOIN - WHERE - GROUP BY - HAVING - SELECT- ORDER BY - LIMIT 

SELECT
  user_id,
  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_visit_month2
FROM `advanced.analytics_function_01`

# 전체 순서도 정하려면 ORDER BY를 해야한다!!
ORDER BY 
	user_id

문제 2

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

답:

# 문제 : user들의 다음 접속 월과 다다음 접속 월, 이전 접속 월을 구하는 쿼리를 작성해주세요.
# 쿼리를 작성하는 목표, 확인할 지표 : 
# 쿼리 계산 방법 : LEAD 1, 2 / LAG 1
# 데이터의 기간 : 
# 사용할 테이블 : 
# Join KEY : 
# 데이터 특징 : 
# 작성 흐름 : FROM - JOIN - WHERE - GROUP BY - HAVING - SELECT- ORDER BY - LIMIT 

SELECT
  user_id,
  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_visit_month2,
  LAG(visit_month,1) OVER (PARTITION BY user_id ORDER BY visit_month) AS before_visit_month
FROM `advanced.analytics_function_01`
ORDER BY
	user_id

문제 3

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

답:

# 문제 : 사용자별 쿼리를 실행한 총 횟수를 구하는 쿼리를 작성해주세요. 단, GROUP BY를 사용해서 집계하는 것이 아닌 query_logs의 데이터의 우측에 새로운 컬럼을 만들어주세요.
# 쿼리를 작성하는 목표, 확인할 지표 : 총 횟수 구하기 / 우측 새로운 컬럼 = 윈도우함수 / 사용자별
# 쿼리 계산 방법 : count, partition, 
# 데이터의 기간 : 
# 사용할 테이블 : query_logs
# Join KEY : 
# 데이터 특징 : 
# 작성 흐름 : FROM - JOIN - WHERE - GROUP BY - HAVING - SELECT- ORDER BY - LIMIT 

SELECT
  user,
  team,
  query_date,
  -- COUNT(user) OVER (PARTITION BY user) AS total_query_cnt
  #쿼리 수를 구하는 거니까 user를 쓰는건 살짝 의미가 다름
  COUNT(query_date) OVER (PARTITION BY user) AS total_query_cnt
FROM advanced.query_logs
ORDER BY
  query_date

문제 4

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

답:

# 문제 : 주차별로 팀 내에 서 쿼리를 많이 실행한 수를 구한 후, 실행한 수를 활용해 랭킹을 구해주세요. 단, 랭킹이 1등인 사람만 결과가 보이도록 해주세요
# 쿼리를 작성하는 목표, 확인할 지표 : 주차 별 / 쿼리 실행 수 / 랭킹 / 1등만
# 쿼리 계산 방법 : count, rank
# 데이터의 기간 : 
# 사용할 테이블 : query_logs
# Join KEY : 
# 데이터 특징 : 
# 작성 흐름 : FROM - JOIN - WHERE - GROUP BY - HAVING - SELECT- ORDER BY - LIMIT 
WITH week_user_cnt AS(
SELECT
  EXTRACT(WEEK FROM query_date) AS week_number,
  team,
  user,
  COUNT(query_date) AS query_cnt
  # 윈도우함수는 중복을 만들 수 밖에 없으니까, GROUP BY로 집계한 후에 랭킹을 하는게 더 낫다!

FROM advanced.query_logs
GROUP BY
  team, user, week_number
ORDER BY
  week_number
 ),

#WITH - 같은 WITH 안에서는 쉼표로 이으면 된다!!!

cnt_ranking AS (
  SELECT
-- DISTINCT
  week_number,
  team,
  user,
  query_cnt,
  RANK() OVER (PARTITION BY week_number, team ORDER BY query_cnt DESC) AS team_rank
FROM week_user_cnt AS wuc
)
SELECT
  week_number,
  team,
  user,
  query_cnt,
  team_rank
FROM cnt_ranking
WHERE
  team_rank = 1

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ
  
#QUALIFY를 사용한 버전

# 1. 주차별로 팀 내 쿼리 실행 수 => 윈도우 함수 배웠으니까! 윈도우 함수!
  # 윈도우 함수 쓰고, 또 랭킹 써도 괜찮음. 마지막에 중복이 있을거라서 중복 처리를 해줘야 합니다. => DISTINCT / GROUP BY
  # GROUP BY로 집계를 한 후에, 윈도우 함수를 쓰는 방법
# 2. 랭킹 구해보자
# 3. 랭킹 1만 남기자

WITH base AS (
  SELECT
    EXTRACT(WEEK FROM query_date) AS week_number,
    team,
    user,
    COUNT(query_date) AS cnt
  FROM advanced.query_logs
  GROUP BY ALL
)

SELECT
  *,
  RANK() OVER (PARTITION BY week_number, team ORDER BY cnt) AS rn
FROM base
QUALIFY rn = 1 # 서브쿼리를 안써도 되는 방법!

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ

#윈도우 함수 버전 풀이

WITH base AS (
  SELECT
    EXTRACT(WEEK FROM query_date) AS week_number,
    team,
    user,
    COUNT(query_date) AS cnt
  FROM advanced.query_logs
  GROUP BY ALL
), user_cnt_total AS (
  SELECT
    DISTINCT
      user,
      team,
      query_date,
      week_number,
      COUNT(query_date) OVER (PARTITION BY team, user, week_number) AS cnt
  FROM (
    SELECT
      user,
      team,
      query_date,
      EXTRACT(WEEK FROM query_date) AS week_number,
      # Unrecognized name: week_number at [25:52] => SELECT에서 이제 만든거라서 그 시점에 알 수 없음
    FROM advanced.query_logs
  )
)

SELECT
  *,
  RANK() OVER (PARTITION BY week_number, team ORDER BY cnt) AS rn
FROM user_cnt_total
QUALIFY rn = 1

# 많이 이슈를 겪는 것 : 서브쿼리. 굉장히 길어질 것
-- WITH base AS (
--   SELECT
--     id,
--     kor_name # cnt
--     -- RANK() OVER(PARTITION user ORDER BY cnt) # SELECT절의 결과는 그 SELECT에서 바로 사용할 수 없음!!!
--   FROM basic.pokemon
-- )

-- SELECT
--   RANK() OVER(PARTITION user ORDER BY cnt) # SELECT절의 결과는 그 SELECT에서 바로 사용할 수 없음!!!
-- FROM base

# 2번 문제. 결과를 보니까 중복이 생겨요!
  # 윈도우 함수 => 원본 데이터에서 새로운 컬럼에 값을 추가한다(Row 수가 그대로)
    # 중복 제거가 필요함. DISTINCT
    SELECT 
      DISTINCT
        col,
        col2,
        col3
  # GROUP BY => 원본 데이터를 특정 컬럼 기준으로 집계해서 계산한다(Row 수가 줄어들 수 있음)
    # user, team, query_date => GROUP BY 집계를 해서 week, team, user, query_cnt를 만들고 그 후에 rank를 추가하면 어떨까?
  # week, team, user, query_cnt, rank