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

1.

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

select *
     , count(query_date) over(partition by user) as total_query_cnt
  from advanced.query_logs
 order by user, query_date

2.

--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) as query_cnt     
  from advanced.query_logs
 group by all
  ) as base
qualify team_rank = 1
order by 1,2,3 

3.

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

select *
     , lag(query_cnt) over(partition by user order by week_number) as prev_week_query_cnt
  from
(
select EXTRACT(WEEK FROM query_date) as week_number
     , team
     , user
     , count(user) as query_cnt     
  from advanced.query_logs
 group by all
  ) as base
 order by user

4.

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

select *
     , sum(query_cnt) over(partition by user order by query_date) as cumulative_query_cnt
  from (
        select user
            , team
            , query_date
            , count(user) as query_cnt     
          from advanced.query_logs
        group by all
        ) as base
 order by user

5.

--5) 다음 데이터는 주문 횟수를 나타낸 데이터입니다. 만약 주문 횟수가 없으면 NULL로 기록됩니다. 이런 데이터에서 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 *
     , ifnull(number_of_orders,last_value(number_of_orders ignore nulls) over(order by date)) as last_value_orders
  from raw_data

6.

--6) 5번 문제에서 NULL을 채운 후, 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
      , ifnull(number_of_orders,last_value(number_of_orders ignore nulls) over(order by date)) as number_of_orders
   from raw_data) as base

7.

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

--2022-08-18일의 user_pseudo_id(1997494153.8491999091)은 session_id가 4까지 나옵니다.

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
)

, diff_data as
(
  select *
       , if(second_diff > 20 or second_diff is null, 1, null) as session_start
    from
      (
        select *
            , DATETIME_DIFF(event_datetime, before_event_datetime, second) as second_diff
          from base
      )
)
 
select *
     , sum(session_start) over(order by event_timestamp) as session_num
  from diff_data  
-- where event_date = '2022-08-18'
--   and user_pseudo_id = '1997494153.8491999091'
order by event_timestamp