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

1. 윈도우 함수 연습문제

(1) 사용자별 쿼리를 실행한 총 횟수를 구하는 쿼리는 작성해주세요.

단, GROUP BY를 사용해서 집계하는 것이 아닌 query_logs의 데이터의 우측에 새로운 컬럼을 만들어주세요.

SELECT *,
  COUNT(query_date) OVER(PARTITION BY user) AS cnt_query
FROM advanced.query_logs

(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) AS rk
FROM query_cnt_by_team
QUALIFY rk = 1
ORDER BY week_number, team, query_cnt DESC

*주차별 데이터는 EXTRACT 사용

(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) OVER(PARTITION BY user ORDER BY week_number) AS prev_week_query_cnt
FROM query_cnt_by_team

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

-- 누적 쿼리 : frame. 과거의 시간(UNBOUNDED PRECEDING) 부터 CUREENT ROW 까지
-- frame의 default 
SELECT *,
  SUM(query_cnt) OVER(PARTITION BY user ORDER BY query_date) AS cumulative_sum
FROM (
SELECT
  query_date,
  team,
  user,
  COUNT(user) AS query_cnt
FROM advanced.query_logs
GROUP BY ALL 
)
-- QUALIFY cumulative_sum != cumulative_sum2 
-- WHERE, QUALIFY 조건 설정해서 2가지 조건이 같은지 비교 -> 같으면 != 연산 결과에 반환하는 값 없음
ORDER BY user, query_date

(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

*IGNORE NULLS 사용

(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

*WITH문 두번 사용 시, ',' 사용

(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_date,
  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
  *,
  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_num
-- session을 구할 때 쿼리가 길어질 수 있음. 하루에 접속을 여러번 하는 서비스 -> session 기반이 좋을 수 있고, 아니라고 하면 유저 집계가 나올 수 있음
  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
  )

*세션 기준을 직접 정의 가능