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

윈도우함수(탐색함수) 연습문제

연습문제1

-- 문제 1) user들의 다음 접속 월과 다다음 접속 월을 구하는 쿼리를 작성해주세요
select 
  user_id,
  visit_month,
  lead(visit_month) over (partition by user_id order by visit_month asc) as after_visit_month,
  lead(visit_month, 2) over (partition by user_id order by visit_month asc) as after_visit_month
from `advanced.analytics_function_01`
order by user_id;

연습문제2

-- 문제2) user들의 다음 접속 월과 다다음 접속 월, 이전 접속 월을 구하는 쿼리를 작성해주세요.
select
  *,
  lead(visit_month) over (partition by user_id order by visit_month asc) as after_visit_month,
  lead(visit_month, 2) over (partition by user_id order by visit_month asc) as after_two_visit_month,
  lag(visit_month) over(partition by user_id order by visit_month asc) as before_visit_month
from `advanced.analytics_function_01`
order by user_id, visit_month;

연습문제3

-- 3번 유저가 접속했을 때 다음 접속까지의 간격을 구하시오
select
  *,
  after_visit_month - visit_month as diff

from(
select
  *,
  lead(visit_month, 1) over (partition by user_id order by visit_month) as after_visit_month

from `advanced.analytics_function_01`
)
order by user_id, visit_month;

윈도우 함수 frame 연습문제

SELECT
  *
  , SUM(amount) OVER(ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 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
  , AVG(amount) OVER(ORDER BY order_id ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS last_5_orders_avg_amount
FROM `advanced.orders`
ORDER BY order_id

윈도우 함수 연습문제

연습문제1

-- 1. 사용자별 쿼리를 실행한 총 횟수를 구하는 쿼리를 작성해주세요 단, group by를 사용해서 집계하는것이 아닌 quary_log의 데이터의 우측에 새로운 컬럼을 작성해주세요
select 
  *,
  count(query_date) over (partition by user) as total_query_cnt
from `advanced.query_logs`
order by user,query_date;

연습문제2

-- 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 week_number, team, query_cnt desc;

연습문제3

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

-- 4. 시간에 흐름에 따라 일자별로 유저가 실행한 누적 쿼리 수를 작성해주세요(query_date)
select
 *,
 sum(query_cnt) over(partition by user order by query_date) as cumulative_sum,
 sum(query_cnt) over(partition by user order by query_date rows between unbounded preceding and current row) as cumulative_sum2
from(
select
  query_date,
  team,
  user,
  count(user) as query_cnt
from `advanced.query_logs`
group by all
)
order by user, query_date;

연습문제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
  *,
  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
)

, filled_data as(
  select
    * except(number_of_orders),
    last_value(number_of_orders ignore nulls) over(order by date) as number_of_orders
  from raw_data
)

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

연습문제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,
 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"
), diff_Data as(

  select
  *,
  from(
    select
      *,
      lag(event_datetime, 1) 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_data
)

order by event_datetime