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

2. 윈도우 함수

주요 Point💡

• 윈도우 함수 FRAME 개념은 익숙해지는 것이 중요

• qualify 개념 ⭐

• 누적합 개념 사용해 세션 구하기 !! </aside>

2-1. 윈도우 함수 rows, range calculation

• 연습문제 FRAME

  /*
  - 토픽: frame 연습문제
  - 추가 컬럼
    -- 1) amount_total
    -- 2) cumulative_sum
    -- 3) cumulative_sum_by_user
    -- 4) 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
      , sum(amount) over(order by order_id rows between 5 preceding and 1 preceding) as last_5_orders_avg_amount
  from advanced.orders
  ;
  

2-2. Qualify 개념

• 윈도우 함수로 생성된 변수를 having 처리하는 것처럼 사용할 수 있음

  • E.g. qualify amount_total ≥ 500

2-3. 윈도우 함수 연습문제 모음

• 윈도우 함수 연습문제

  /*
  - 연습문제 1
  - window 활용 > partition by 활용 목적
    */
  
  select *
    , count(*) over(partition by user) as total_query_cnt
  from advanced.query_logs 
  order by user, query_date
  ;
  
  /*
  - 연습문제 2
  - window 활용 > partition by & qualify 활용 (훨씬 편하다!)
  - 해설 풀이 자체는 처음에 group by 쓰면 더 유용 (그럴 것 같음)
    */
    
  with tmp as (
  select week_number
      , team
      , user
      , count(*) over(partition by week_number, team, user) as query_cnt
  from 
  (
    select *
         , extract(week from query_date) as week_number
    from advanced.query_logs
  ) t
  )
  select *
    , row_number() over(partition by week_number, team order by query_cnt desc) as team_rnk
  from tmp 
  qualify team_rnk = 1
  order by week_number, team
  ;
  
  /*
  - 연습문제 3
  - window 활용 > lag 함수 목적
    */
    with tmp as (
  select user
       , team
       , week_number
       , count(*) as query_count
  from 
  (
   select *
         , extract(week from query_date) as week_number
    from advanced.query_logs
  ) t
  group by user, team, week_number
    )
    select *
      , lag(query_count) over(partition by user order by week_number) as prev_week_query_count
    from tmp t
    ;
  
  /*
  - 연습문제 4
  - window 활용 > 누적 count 목적
  - order by, partition by 개념 명확히 익히기에 좋음
  - 해설 풀이와는 다름 (group by 후 > query_cnt를 sum하는 방식으로 풀이함) > 출제의도 frame 개념 파악
    */
  
    select distinct user
        , team
        , query_date
        , count(*) over(partition by user, query_date) as query_count
         ,count(*) over(partition by user order by query_date) as cumulative_query_count
    from advanced.query_logs
    order by 1, 2, 3
    ;
    
    
    /*
  - 연습문제 6
  - window 활용 > moving_avg 산출 문제
  */
  
  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
    , coalesce(number_of_orders, lag(number_of_orders) over(order by date)) as number_of_orders1
    , Last_value(number_of_orders ignore nulls) over(order by date) as number_of_orders2
  FROM raw_data
  ;
  
  /*
  - 연습문제 5
  - window 활용 > coalesce & lag 활용목적
    */
  
  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 *
      , avg(number_of_orders) over(order by date rows between 2 preceding and current row) as moving_avg
  from 
  (
    SELECT date
    -- , coalesce(number_of_orders, lag(number_of_orders) over(order by date)) as number_of_orders1
        , Last_value(number_of_orders ignore nulls) over(order by date) as number_of_orders
    FROM raw_data
  )
  ;
  

2-4. 세션 구하기 문제

• 세션 구하기 연습문제

  /*
  - 연습문제 7
  - session 구하기
  - diff & 누적합 개념으로 세션 구하는 것이 포인트 (누적합 개념 신선!)  */
  
  with base as (
  select event_date
    , datetime(timestamp_micros(event_timestamp), 'Asia/Seoul') as event_datetime
    , event_name
    , user_id
    , user_pseudo_id
  from advanced.app_logs
  ), diff_date as (
  select *
    , datetime_diff(event_datetime, prev_event_datetime, second) as second_diff
  from 
  (
  select *
    , lag(event_datetime) over(partition by user_pseudo_id order by event_datetime) as prev_event_datetime
  from base
  )
  )
  
  select *
    , sum(Session_Start) over(partition by user_pseudo_id order by event_datetime) as session_num
  from 
  (
  select  *
  , case when prev_event_Datetime is null then 1
         when second_diff >= 20 then 1
        else 0 
    end as session_start 
  from diff_date
  )
  order by event_Datetime, user_pseudo_id
  ;