<FRAME 문제>

1번문제: 총 수량(amount_total), 수량의 누적 합(cumulativ_sum), 유저별 수량의 누적 합(cumulative_sum(user)), 최근 5개 수량의 평균(last_5_avg) 출력

• 쿼리를 작성하는 목표, 확인할 지표: 수량의 총합 또는 누적 합 구하기

• 쿼리 계산 방법: 윈도우 함수 - AVG, SUM

• 데이터의 기간: X

• 사용할 테이블: advanced.orders

• JOIN KEY: X

• 데이터 특징: X

SELECT 
  order_id,  -- 각 주문의 고유 식별자
  order_date,  -- 주문 날짜
  user_id,  -- 주문한 사용자 ID
  amount,  -- 주문 금액
  
  SUM(amount) OVER (ORDER BY order_date, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS amount_total,
  -- 주문 데이터 전체의 총 금액을 계산합니다.
  -- 'ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING'은 모든 행을 참조한다는 의미로, 테이블의 전체 합계를 계산합니다.
  
  SUM(amount) OVER (ORDER BY order_date, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum,
  -- 날짜와 주문 ID 순서로 누적 합계를 계산합니다.
  -- 'ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW'는 현재 행까지의 모든 이전 행을 포함하여 누적 합계를 계산합니다.
  
  SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum_by_user,
  -- 사용자별로 누적 합계를 계산합니다.
  -- 'PARTITION BY user_id'는 각 사용자별로 데이터를 분리해 누적 합계를 계산하도록 합니다.
  -- 즉, 각 사용자가 주문한 금액의 누적 합계를 구합니다.
  
  AVG(amount) OVER (ORDER BY order_date, order_id ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS last_5_orders_avg_amount
  -- 현재 행을 기준으로 바로 이전 5개의 행의 주문 금액 평균을 계산합니다.
  -- 'ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING'은 현재 행 이전의 5개 행에서 1개 행까지 포함하여 평균을 구합니다.
  -- 이를 통해 최근 5건의 주문에 대한 평균 금액을 알 수 있습니다.
  
FROM advanced.orders  -- advanced 데이터셋의 orders 테이블에서 데이터를 가져옵니다.

--QUALIFY last_5_orders_avg_amount >= 150
-- 마지막 5건의 주문 평균 금액이 150 이상인 주문만을 결과로 필터링하기 위한 조건입니다. 현재는 주석 처리되어 있어 실행되지 않습니다.

ORDER BY order_id;  -- 주문 ID 기준으로 결과를 정렬하여 각 주문에 대해 계산된 값을 순서대로 확인합니다.

<총 정리 문제>

1번문제:사용자별 쿼리를 실행한 총 횟수를 구하는 쿼리를 작성해주세요. 우측에 새로운 컬럼을 만들어주세요.

SELECT
  *  -- 모든 열을 선택합니다. advanced.query_logs 테이블의 모든 컬럼을 그대로 포함합니다.
  
  , COUNT(user) OVER (PARTITION BY user) AS total_query_cnt
  -- 'user'별로 쿼리 실행 횟수를 계산합니다.
  -- 'PARTITION BY user'를 사용하여 각 사용자별로 나누고, 그 안에서 해당 사용자가 몇 번의 쿼리를 실행했는지 계산합니다.
  -- 이 값은 각 사용자가 테이블에 몇 번 등장했는지를 의미합니다.
  
FROM advanced.query_logs  -- advanced 데이터셋의 query_logs 테이블에서 데이터를 가져옵니다.

ORDER BY query_date, user;  -- 'query_date'와 'user'를 기준으로 데이터를 정렬합니다. 'query_date'를 우선적으로, 같은 날짜 내에서는 'user'를 기준으로 정렬합니다.

2번문제: 주차별로 팀 내에서 쿼리를 많이 실행한 수를 구한 후, 실행한 수를 활용해 랭킹을 구해주세요. 단, 랭킹이 1등인 사람만 결곽가 보이도록 해주세요.

SELECT 
  *  -- 모든 열을 선택합니다. 서브쿼리에서 생성된 모든 컬럼을 포함합니다.

  , RANK() OVER (PARTITION BY week_number, team ORDER BY query_cnt DESC) AS team_rank
  -- RANK() 함수를 사용하여 각 팀 내에서 쿼리 실행 횟수(query_cnt)에 따라 랭킹을 부여합니다.
  -- 'PARTITION BY week_number, team'은 팀과 주차별로 데이터를 나누어 그 안에서 랭킹을 계산합니다.
  -- 'ORDER BY query_cnt DESC'는 쿼리 실행 횟수에 따라 랭킹을 내림차순으로 정렬합니다.
  -- 이 결과로 각 팀 내에서 쿼리 실행 횟수에 따라 랭킹이 부여된 'team_rank'라는 새로운 열이 추가됩니다.

FROM 
  (SELECT 
    EXTRACT(WEEK FROM query_date) AS week_number  -- query_date에서 주차를 추출하여 'week_number' 열을 만듭니다.
    , team  -- 팀을 나타내는 컬럼을 선택합니다.
    , user  -- 사용자를 나타내는 컬럼을 선택합니다.
    , COUNT(user) AS query_cnt  -- 사용자가 쿼리를 실행한 횟수를 집계하여 'query_cnt'라는 열로 만듭니다.
  FROM advanced.query_logs  -- advanced 데이터셋의 query_logs 테이블에서 데이터를 가져옵니다.
  GROUP BY ALL)  -- 모든 선택된 컬럼을 그룹화합니다. 여기서는 'week_number', 'team', 'user'로 데이터를 그룹화합니다.

QUALIFY team_rank = 1
-- QUALIFY 절은 필터링 조건을 적용하는 역할을 하며, 여기서는 RANK가 1인 경우만 선택합니다.
-- 즉, 각 팀 내에서 쿼리 실행 횟수가 가장 많은 사용자(1등)만 필터링됩니다.

ORDER BY week_number, team;  -- 주차별로, 그리고 각 주차 내에서 팀별로 데이터를 정렬합니다.

3번문제: (2번 문제에서 사용한 주차별 쿼리 사용) 쿼리를 실행한 시점 기준 1주 전에 쿼리 실행 수를 별도의 컬럼으로 확인할 수 있는 쿼리를 작성해주세요.

SELECT 
  *  -- 모든 열을 선택합니다. 서브쿼리에서 생성된 모든 컬럼을 포함합니다.

  , LAG(query_cnt) OVER (PARTITION BY user ORDER BY week_number) AS prev_week_query_count 
  -- LAG() 함수를 사용하여 이전 주차(week)의 쿼리 실행 수를 가져옵니다.
  -- 'PARTITION BY user'를 통해 각 사용자별로 데이터를 나누고, 각 사용자에 대해 'week_number' 순서로 정렬하여 이전 주차의 값을 가져옵니다.
  -- 결과적으로, 이전 주차의 쿼리 실행 횟수를 'prev_week_query_count'라는 새로운 열로 추가합니다.

FROM 
  (SELECT 
    user  -- 쿼리를 실행한 사용자를 나타내는 열입니다.
    , team  -- 사용자가 속한 팀을 나타내는 열입니다.
    , EXTRACT(WEEK FROM query_date) AS week_number  -- query_date에서 주차(week)를 추출하여 'week_number'라는 열로 만듭니다.
    , COUNT(user) AS query_cnt  -- 사용자가 실행한 쿼리 횟수를 집계하여 'query_cnt' 열로 만듭니다.
  FROM advanced.query_logs  -- advanced 데이터셋의 query_logs 테이블에서 데이터를 가져옵니다.
  GROUP BY ALL)  -- 선택된 모든 열을 그룹화합니다.

ORDER BY user, week_number;  -- 최종 결과를 사용자별로 정렬하고, 그다음 각 사용자 내에서 주차별로 정렬합니다.

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

SELECT 
  *  -- 모든 열을 선택합니다. 서브쿼리에서 생성된 모든 컬럼을 포함합니다.

  , SUM(query_cnt) OVER (PARTITION BY user ORDER BY query_date) AS cumulative_query_count
  -- SUM 윈도우 함수를 사용하여 누적 쿼리 실행 수를 계산합니다.
  -- 'PARTITION BY user'를 통해 각 사용자별로 데이터를 나누고, 각 사용자에 대해 'query_date' 순서로 정렬하여 누적 값을 계산합니다.
  -- 결과적으로, 각 사용자에 대한 누적 쿼리 실행 횟수가 'cumulative_query_count'라는 새로운 열로 추가됩니다.

FROM
  (SELECT 
    user  -- 쿼리를 실행한 사용자를 나타내는 열입니다.
    , team  -- 사용자가 속한 팀을 나타내는 열입니다.
    , query_date  -- 쿼리가 실행된 날짜를 나타내는 열입니다.
    , COUNT(user) AS query_cnt  -- 사용자가 쿼리를 실행한 횟수를 집계하여 'query_cnt' 열로 만듭니다.
  FROM advanced.query_logs  -- advanced 데이터셋의 query_logs 테이블에서 데이터를 가져옵니다.
  GROUP BY ALL)  -- 선택된 모든 열을 그룹화하여 중복되지 않게 집계합니다.

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 number_of_orders
  -- NULL 값을 제외하고 마지막으로 발견된 'number_of_orders' 값을 가져옵니다.
  -- 'ORDER BY date'를 사용하여 날짜 순서대로 정렬하며,
  -- 각 행에서 가장 최근의 유효한 (NULL이 아닌) 'number_of_orders' 값을 반환합니다.
  
FROM raw_data
-- 데이터가 저장된 'raw_data' 임시 테이블에서 데이터를 조회합니다.

ORDER BY date;
-- 최종 결과를 날짜 순서대로 정렬하여 출력합니다.

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

WITH raw_data AS (
  -- 임시 데이터를 정의합니다. 날짜(date)와 해당 날짜의 주문 수(number_of_orders)를 포함한 테이블을 생성합니다.
  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 
  *
  -- 모든 열을 선택한 뒤, 이동 평균(moving_avg)을 추가로 계산합니다.
  , ROUND(AVG(number_of_orders) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS moving_avg
  -- AVG 함수를 사용하여 현재 행을 포함해 이전 두 행의 값에 대한 이동 평균을 계산합니다.
  -- 계산된 결과를 소수점 첫 번째 자리까지 반올림합니다.
FROM (
  SELECT
    date,
    -- IFNULL 함수를 사용하여 NULL 값을 보완합니다.
    -- 이전 날짜의 주문 수를 가져와 현재 행의 주문 수가 NULL인 경우 보완합니다.
    IFNULL(number_of_orders, LAG(number_of_orders) OVER (ORDER BY date)) AS number_of_orders
  FROM raw_data
)
-- 최종 결과를 날짜 순서로 정렬하여 출력합니다.
ORDER BY date;

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 (
  -- Step 1. 기초 데이터 추출 및 이전 이벤트 시간 계산
  SELECT 
    event_date,  -- 이벤트가 발생한 날짜
    event_timestamp,  -- 마이크로초 단위의 이벤트 발생 시각
    DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime,  
    -- 이벤트 발생 시각을 'Asia/Seoul' 시간대로 변환하여 가독성을 높임
    event_name,  -- 이벤트의 이름
    user_id,  -- 사용자 ID
    user_pseudo_id,  -- 사용자 고유의 익명화된 ID
    DATETIME(TIMESTAMP_MICROS(LAG(event_timestamp) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp)), 'Asia/Seoul') AS before_event_datetime
    -- 이전 이벤트의 발생 시각을 'Asia/Seoul' 시간대로 변환
    -- LAG() 함수를 사용해 각 사용자의 이전 이벤트 발생 시각을 가져옴
  FROM advanced.app_logs
  WHERE event_date = '2022-08-18'  -- 특정 날짜의 이벤트만 필터링
    AND user_pseudo_id = '1997494153.8491999091'  -- 특정 사용자에 대한 로그만 조회
),

-- Step 2. 세션 유지 시간 및 신규 세션 여부 계산
session_info AS (
  SELECT 
    *,
    TIMESTAMP_DIFF(event_datetime, before_event_datetime, SECOND) AS second_diff, 
    -- 이전 이벤트와 현재 이벤트 사이의 시간 차이를 초 단위로 계산
    CASE 
      WHEN TIMESTAMP_DIFF(event_datetime, before_event_datetime, SECOND) >= 20 
        OR TIMESTAMP_DIFF(event_datetime, before_event_datetime, SECOND) IS NULL THEN 1 
      -- 두 이벤트 사이의 시간 차이가 20초 이상이거나 이전 이벤트가 없는 경우 새로운 세션 시작으로 간주
      ELSE NULL 
    END AS session_start  -- 새로운 세션이 시작되면 1, 그렇지 않으면 NULL
  FROM base
)

-- Step 3. 신규 세션 ID 세팅
SELECT 
  *,
  SUM(session_start) OVER (PARTITION BY user_pseudo_id ORDER BY event_datetime) AS session_id
  -- 각 사용자의 이벤트 시퀀스에서 새로운 세션이 시작될 때마다 session_start 값을 누적하여 세션 ID를 부여
FROM session_info
ORDER BY event_date, event_timestamp;  -- 이벤트 발생 시각을 기준으로 정렬하여 결과를 출력