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

문제 1번

-- 1. 사용자별 쿼리를 실행한 횟수의 총합
-- GROUP BY 사용 X. 우측에 새로운 컬럼 생성하기.

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번

-- 2. 주차별로 팀내에서 쿼리를 많이 실행한 수 → 랭킹 구하기
-- 팀별로 랭킹이 1위인 사람만 출력하기

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번

-- 3. (2번 문제에서 사용한 주차별 쿼리 사용) 쿼리를 실행한 시점
-- 1주 전에 쿼리를 실행한 횟수를 별도의 컬럼으로 출력

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번

-- 주문 횟수 테이블: 데이터 없으면 NULL로 표기됨.
-- 5. NULL값을 바로 전달 데이터로 채우기

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번

-- 6. (5번 완료 후) 2일 전 ~ 현재 데이터의 평균 구하기

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. app_logs 테이블에서 커스텀 세션 추가 
-- 이전 이벤트 로그와 20초 이상 차이가 나면 새로운 세션으로 정의
-- 세션은 숫자로(1,2,3...) 표시 가능

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