빠짝스터디 2주차 윈도우 함수

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


select
  *,
  count(query_date) over(partition by user) as total_query_cnt
from
  advanced.query_logs
order by 1, 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
  *,
  rank() over(partition by week_number, team order by query_cnt desc ) as rk
from
  query_cnt_by_team
qualify rk = 1
order by 1, 2, 4 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
  *,
  lag(query_cnt, 1) over(partition by user order by week_number) as prev_week_query_cnt
from
  query_cnt_by_team

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

select
  *,
  sum(query_cnt) over (partition by user order by query_date rows between unbounded preceding and current row) as cumulative_sum
from
  (
  select
    query_date,
    user,
    count(user) as query_cnt
  from
    advanced.query_logs
  group by all
  )
order by 2, 1

-- 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
),

raw_data2 as(
  select
    *,
    last_value(raw_data.number_of_orders ignore nulls) over(order by date) as last_value_orders
  from
    raw_data
)
select
  *
from
  raw_data2

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
),

raw_data2 as(
  select
    *,
    last_value(raw_data.number_of_orders ignore nulls) over(order by date) as last_value_orders
  from
    raw_data
)


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

select
  * except(number_of_orders),
  avg(last_value_orders) over (order by date rows between 2 preceding and current row) as moving_avg
from
  raw_data2

--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,
    datetime(timestamp_micros(event_timestamp), 'Asia/Seoul') as event_datetime,
    event_name,
    user_id,
    user_pseudo_id
  from
    advanced.app_logs
  where
    event_date = "2022-08-18"
    and user_pseudo_id = "1997494153.8491999091"
  order by event_timestamp
),

diff_data as 
  (
  select
    *,
    datetime_diff(event_datetime, prev_event_datetime, second) as second_diff
  from
    (
    select
      *,
      lag(event_datetime, 1) over(partition by user_pseudo_id order by event_datetime) as prev_event_datetime
    from
      base
    order by 
      event_datetime
    )
  )

select
  *,
  sum(session_start) over(partition by user_pseudo_id order by event_datetime) as session_number
from
  (
  select
    *,
    case
      when prev_event_datetime is null then 1
      when second_diff >= 20 then 1
      else null
    end as session_start
  from
    diff_data
  )