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

1. 윈도우 함수 연습문제 1번

/* 1. 사용자별 쿼리를 실행한 횟수의 총합을 보여주는 쿼리를 작성하세요 
 단, GROUP BY를 통해 집계하는게 아니라 우측에 새로운 칼럼으로 만들어주세요 */

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

2. 윈도우 함수 연습문제 2번

/* 2. 주차별로 팀내에서 쿼리를 많이 실행한 수를 구한 후, 실행한 수를 활용해
랭킹을 구해주세요. 단, 팀별로 랭킹이 1위인 사람만 보여주세요 */

-- 풀이1: 서브쿼리 사용

with base as (
  select 
    extract(week from query_date) as week_number,
    user,
    team,
    count(query_date) as total_query_cnt
  from 
    `advanced.query_logs`
  group by  
    week_number, user, team
)
select 
  week_number,
  team,
  user,
  total_query_cnt,
  ranking_in_team
from (
  select 
    week_number,
    team,
    user,
    total_query_cnt,
    rank() over (partition by team order by total_query_cnt desc) as ranking_in_team
  from   
    base 
)
where 
  ranking_in_team = 1
order by  
  week_number, team; 

-- 풀이2: QUALIFY 사용

with base as (
  select 
    extract(week from query_date) as week_number,
    user,
    team,
    count(query_date) as total_query_cnt
  from 
    `advanced.query_logs`
  group by  
    week_number, user, team
)
select 
  week_number,
  team,
  user,
  total_query_cnt,
  rank() over (partition by team order by total_query_cnt desc) as ranking_in_team
from 
  base
qualify 
  ranking_in_team = 1
order by  
  week_number, team; 

3. 윈도우 함수 연습문제 3번

/* 3. (2번 문제에서 사용한 주차별 쿼리 사용) 쿼리를 실행한 시점
1주 전에 쿼리를 실행한 횟수를 별도의 칼럼으로 확인할 수 있는 쿼리를 짜주세요 */

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

4. 윈도우 함수 연습문제 4번

/* 4. 시간의 흐름별로(일자별로) 유저가 쿼리한 횟수의 누적합을 구하세요 */

select 
  user,
  team,
  query_date,
  query_count,

-- 윈도우함수의 FRAME의 DEFAULT값이 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW임
  sum(query_count) over (partition by user order by query_date 
  rows between unbounded preceding and current row) as cumulative_query_count
from (
  select 
    user, 
    team,
    query_date,
    count(query_date) as query_count
  from   
    `advanced.query_logs`
  group by 
    1,2,3
)
order by 
  user,query_date;

5. 윈도우 함수 연습문제 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 
  date,
  last_value(number_of_orders ignore nulls) over (order by date) as number_of_orders
from   
  raw_data;

-- 기본적으로 FIRST_VALUE, LAST_VALUE 연산시에 NULL도 포함하여 출력하지만
-- IGNORE NULLS를 사용하면 NULL 제외한 값으로 출력됨 

6. 윈도우 함수 연습문제 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,
    last_value(number_of_orders ignore nulls) over (order by date) as number_of_orders
  from   
    raw_data
)

7. 윈도우 함수 연습문제 7번

/* app_logs 테이블에서 커스텀세션을 만들어주세요. 이전 이벤트로그와 20초 이상 차이가 나면
새로운 세션을 만들어주세요. 세션은 숫자로(1,2,3...) 표시해도 됩니다. */

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
  where 
    event_date = '2022-08-18'
)
  select 
    *,
    datetime_diff(event_datetime,before_event_datetime, second) as second_diff,
    case when 
      datetime_diff(event_datetime,before_event_datetime, second) is null or 
      datetime_diff(event_datetime,before_event_datetime, second) >= 20 then 1 else 0 end as session_start,
    sum(case when datetime_diff(event_datetime,before_event_datetime, second) >= 20 then 1 else 0 end)
      over (partition by user_pseudo_id order by event_datetime) + 1 as session_temp
  from   
    base