인프런 커뮤니티 질문&답변

윈도우 함수 연습문제 1

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

윈도우 함수 연습문제 2

select 
*,
rank() over(partition by team, week_number order by query_cnt desc) as team_rank
from
(
  select 
  user,
  week_number,
  team,
  count(*) as query_cnt
  from
  (
    select 
    *,extract(week from query_date) as week_number
    from `advanced.query_logs`
  )
  group by all
)
qualify team_rank=1;

윈도우 함수 연습문제3

select 
*,
lag(query_cnt) over(partition by user order by week_number asc) as prev_week_query_cnt
from
(
  select 
  user,
  week_number,
  team,
  count(*) as query_cnt
  from
  (
    select 
    *,extract(week from query_date) as week_number
    from `advanced.query_logs`
  )
  group by all
);

윈도우 함수 연습문제4

select
*,
sum(query_count) over(partition by user order by query_date asc) as cumulative_query_cnt
from
(
  select
  user,
  query_date,
  team,
  count(*) as query_count
  from `advanced.query_logs`
  group by all
);

윈도우 함수 연습문제5

SELECT
 date,
 case
   when number_of_orders is null then lag(number_of_orders) over(order by date asc) 
   else number_of_orders
 end as number_of_orders
 FROM raw_data;

윈도우 함수 연습문제6

select 
*,
avg(number_of_orders) over(order by date asc rows between 2 preceding and current row) as moving_average
from
(
  SELECT
  date,
  case
    when number_of_orders is null then lag(number_of_orders) over(order by date asc) 
    else number_of_orders
  end as number_of_orders,
  FROM raw_data
)
;

윈도우 함수 연습문제7

select 
*,
sum(session_start) over(partition by user_pseudo_id order by event_timestamp asc) as session_id
from
(
  select
  *,
  case
  when time_diff is null then 1
  when time_diff >= 20 then 1
  else null
  end as session_start
  from
  (
    select
    *,
    cast((event_timestamp - before_event_timestamp)/1000000 as int) as time_diff
    from
    (
      select
      event_date,
      event_timestamp,
      event_name,
      user_id,
      user_pseudo_id,
      lag(event_timestamp) over(partition by user_pseudo_id order by event_timestamp asc) as before_event_timestamp
      from
      `advanced.app_logs`
      where user_pseudo_id='1997494153.8491999091' and event_date='2022-08-18'
    )
  )
);