[빠짝스터디 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