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

1. 사용자별 쿼리를 실행한 총 횟수

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

2. 주차별 팀 내 쿼리 실행횟수 1등

SELECT
  *,
  RANK() OVER(PARTITION BY week_number,team ORDER BY query_cnt DESC) AS team_rank
FROM (
  SELECT
    EXTRACT(week FROM query_date) AS week_number,
    team,
    user,
    COUNT(user) AS query_cnt
  FROM advanced.query_logs
  GROUP BY ALL
) AS query_logs_byweek
QUALIFY team_rank = 1

3. 쿼리 실행시점 기준 1주전 쿼리 실행 횟수

SELECT
  *,
  LAG(query_cnt,1) OVER(ORDER BY week_number) AS prev_week_query_cnt
FROM (
  SELECT
    EXTRACT(week FROM query_date) AS week_number,
    team,
    user,
    COUNT(user) AS query_cnt
  FROM advanced.query_logs
  GROUP BY ALL
) AS query_logs_byweek

4. 시간의 흐름에 따라, 일자별 유저의 누적 쿼리 실행 수

SELECT
  *,
  SUM(query_count) OVER(PARTITION BY user ORDER BY query_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_query_count
FROM (
  SELECT 
    *,
    COUNT(user) AS query_count
  FROM advanced.query_logs 
  GROUP BY ALL
) AS query_logs_byday

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,
 IF(number_of_orders IS NULL, LAST_VALUE(number_of_orders IGNORE NULLS) OVER(ORDER BY date), number_of_orders) AS number_of_orders
FROM raw_data
ORDER BY date

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

SELECT 
  *,
  AVG(number_of_orders) OVER(ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM (
  SELECT
  date,
  IF(number_of_orders IS NULL, LAST_VALUE(number_of_orders IGNORE NULLS) OVER(ORDER BY date), number_of_orders) AS number_of_orders
  FROM raw_data
) AS data

7. app_logs테이블에서 custom session 만들기. 이전 이벤트 로그와 20초가 지나면 새로운 session을 표시(숫자로 1,2,3,..)

WITH base AS (
SELECT
  event_date,
  DATETIME(TIMESTAMP_MICROS(event_timestamp),'Asia/Seoul') AS event_datetime,
  event_name,
  user_pseudo_id
FROM advanced.app_logs
WHERE 
  user_pseudo_id = '1997494153.8491999091'
  AND event_date = '2022-08-18'
)
SELECT 
  *,
  SUM(session_start) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) AS session_num
FROM (
  SELECT
    *,
    CASE 
      WHEN second_diff IS NULL THEN 1
      WHEN second_diff >= 20 THEN 1
    END AS session_start
  FROM (
    SELECT
      *,
      Lag(event_datetime,1) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) AS before_event_datetime,
      DATETIME_DIFF(event_datetime, Lag(event_datetime,1) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime),SECOND) AS second_diff
    FROM base
  ) AS A
) AS B