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

1번

-- 1) 사용자별 쿼리를 실행한 총 횟수를 구하는 쿼리를 작성해주세요.
-- 단, GROUP BY를 사용해서 집계하는 것이 아닌 query_logs의 데이터의 우측에 새로운 컬럼을 만들어주세요.
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_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_team
QUALIFY rk = 1
ORDER BY
  week_number, query_cnt desc;

3번

-- 3) (2번 문제에서 사용한 주차별 쿼리 사용) 쿼리를 실행한 시점 기준 1주 전에 쿼리 실행 수를 별도의 컬럼으로 확인할 수 있는 쿼리를 작성헤주세요.
WITH query_cnt_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 team, user ORDER BY week_number) AS prev_week_query_cnt
FROM
  query_cnt_team

4번

-- 4) 시간의 흐름(query_date)에 따라, 일자별로 유저가 실행한 누적 쿼리 수를 작성해주세요.
WITH query_cnt_team AS (
  SELECT
    query_date,
    team,
    user,
    COUNT(user) as query_cnt
  FROM
    advanced.query_logs
  GROUP BY ALL
  ORDER BY 
    user, query_date
)
SELECT
  user,
  team,
  query_date,
  query_cnt,
  SUM(query_cnt) OVER(PARTITION BY team, user ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_query_count
FROM
  query_cnt_team
ORDER BY
  user, query_date;

5번

-- 5) 주문 횟수를 나타낸 데이터에서 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 last_value_orders
  FROM
    raw_data
)
SELECT
  *
FROM
  filled_data;

7번

-- 7) app_logs 테이블에서 Custom Session을 만들어 주세요. 이전 이벤트 로그와 20초가 지나면 새로운 Session을 만들어 주세요.
-- Session은 숫자로 (1, 2, 3 ...) 표시해도 됩니다.
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'
), diff_date AS (
  SELECT
    *,
    DATETIME_DIFF(event_datetime, pre_event_time, SECOND) AS second_diff
  FROM (
    SELECT
      *,
      LAG(event_datetime) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime ASC) AS pre_event_time
    FROM
      base
  )
), session_start AS (
  SELECT
    *,
    CASE
      WHEN pre_event_time IS NULL THEN 1
      WHEN second_diff >= 20 THEN 1
      ELSE 0
    END AS start_session
  FROM
    diff_date
)
SELECT
  *,
  SUM(start_session) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_id
FROM
  session_start
ORDER BY
  event_datetime;