[빠짝스터디 2주차 과제] 윈도우 함수 연습 문제

문제 1번

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번

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번

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번

SELECT 
  user
  , team
  , query_date
  , query_count
  , 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번

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;

문제 6번

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번

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 1=1
  AND 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

안녕하세요, 인프런 AI 인턴입니다. 이현수님.

윈도우 함수 관련 연습 문제를 풀고 계시는 것 같습니다. 윈도우 함수는 데이터 분석에 매우 강력한 도구로, 데이터 집계 및 분석에 있어서 효율성을 높여 줍니다. 각 문제에서 사용된 윈도우 함수에 대해 간략히 설명드리겠습니다.

문제 1번에서는 기본적인 윈도우 함수 사용법을 보여줍니다. COUNT(query_date) OVER (PARTITION BY user)를 통해 각 사용자별 쿼리 수를 계산하고 있습니다.
문제 2번에서는 팀별로 쿼리 횟수를 집계하여 최다 쿼리를 수행한 사용자를 찾습니다. RANK() OVER (PARTITION BY team ORDER BY total_query_cnt DESC)를 사용하며, QUALIFY를 사용해 조건을 걸고 있습니다.
문제 3번에서는 LAG() 함수를 사용하여 이전 주의 쿼리 수를 가져오고 있습니다. 이를 통해 사용자별 쿼리 수의 변화를 추적할 수 있습니다.
문제 4번은 누적 합계를 계산하는 예시로, SUM(query_count) OVER (...) AS cumulative_query_count를 사용하여 사용자별 누적 쿼리 수를 계산합니다.
문제 5번에서는 NULL 값을 무시하고 마지막 데이터를 가져오기 위해 LAST_VALUE(ignore nulls)를 사용합니다.
문제 6번은 이동 평균을 계산하는 문제로, AVG() OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)를 통해 최근 3일의 평균을 구합니다.
문제 7번에서는 사용자 로그 데이터를 기반으로 세션을 식별하기 위한 로직을 보여줍니다. LAG() 함수와 DATETIME_DIFF()를 사용하여 로그 사이의 시간 차이를 구하고, 이를 기반으로 세션의 시작 여부를 결정합니다.

이와 관련된 추가 자료나 질문이 있으신 경우, 아래의 유사 질문과 답변을 참고하실 수 있습니다:

쿼리 최적화 및 윈도우 함수 활용 사례

저는 질문자님의 이해를 돕고자 지식공유자님의 기존 답변을 참고하여 유사한 답변 링크를 추천해드리고 있습니다. 현재 베타 기능으로 답변이 만족스럽지 않을 수 있는 점 양해 부탁드립니다. 🙏 추가적으로 궁금한 점이 있으시면, 이어서 질문해 주세요. 곧 지식공유자께서 답변해 주실 것입니다.

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