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

#1

SELECT
  *,
  LEAD(visit_month, 1) OVER(PARTITION BY user_id ORDER BY visit_month) AS lead_visit_month1,
  LEAD(visit_month, 2) OVER(PARTITION BY user_id ORDER BY visit_month) AS lead_visit_month2 
FROM advanced.analytics_function_01
ORDER BY user_id;

#2

SELECT
  *,
  LEAD(visit_month, 1) OVER(PARTITION BY user_id ORDER BY visit_month) AS after_visit_month1,
  LEAD(visit_month, 2) OVER(PARTITION BY user_id ORDER BY visit_month) AS after_visit_month2,
  LAG(visit_month, 1) OVER(PARTITION BY user_id ORDER BY visit_month) AS before_visit_month1
FROM advanced.analytics_function_01
ORDER BY
  user_id,
  visit_month;

#3

SELECT
  *,
  after_visit_month - visit_month AS diff
FROM (
  SELECT
    *,
    LEAD(visit_month, 1) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_visit_month,
  FROM advanced.analytics_function_01
);

#4

SELECT
  *,
  FIRST_VALUE(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first,
  LAST_VALUE(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last
FROM advanced.analytics_function_01;

#5

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

#6

WITH query_cnt_cnt AS (
  SELECT
    EXTRACT(WEEK FROM query_date) AS week_number,
    team,
    user,
    COUNT(*) AS query_cnt
  FROM advanced.query_logs
  GROUP BY
    week_number,team,user
)
SELECT
  *,
  RANK() OVER(PARTITION BY week_number, team ORDER BY query_cnt DESC) AS rk
FROM query_cnt_cnt
QUALIFY rk = 1
ORDER BY
  week_number,
  team,
  query_cnt DESC;

#7

WITH query_cnt_cnt AS (
  SELECT
    EXTRACT(WEEK FROM query_date) AS week_number,
    team,
    user,
    COUNT(*) AS query_cnt
  FROM advanced.query_logs
  GROUP BY
    week_number,team,user
)
SELECT
  *,
  LAG(query_cnt, 1) OVER (PARTITION BY user ORDER BY week_number) AS prev_week_query_cnt
FROM query_cnt_cnt;

#8

WITH query_cnt AS (
  SELECT
    *,
    COUNT(*) AS query_cnt_cnt
  FROM advanced.query_logs
  GROUP BY
    user, team, query_date
)
SELECT
  *,
  SUM(query_cnt_cnt) OVER (PARTITION BY user ORDER BY query_date ) AS query_cnt_cnt_cnt
FROM query_cnt;

#9

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;

#10

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 number_of_orders
  FROM raw_data
)
SELECT
  *,
  AVG(number_of_orders) OVER(ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM filled_data

#11

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
  1=1
  AND event_date = "2022-08-18"
  AND user_pseudo_id = "1997494153.8491999091"
) , diff_date AS (
  SELECT
    *,
    DATETIME_DIFF(event_datetime, prev_event_datetime, SECOND) AS second_diff
    # second_diff 기반으로 새로운 세션의 시작일지, 아닐지를 판단할 수 있음
  FROM(
    SELECT
      *,
      LAG(event_datetime, 1) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) AS prev_event_datetime
      -- DATETIME_DIFF() => 차이를 구할 수 있음
    FROM base
  )
)
SELECT
  *,
  SUM(session_start) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) AS session_num
FROM (
  SELECT
    *,
    CASE
      WHEN prev_event_datetime IS NULL THEN 1
      WHEN second_diff >= 20 THEN 1 
      ELSE 0
      END AS session_start
  FROM diff_data
  ORDER BY
    event_datetime
)