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

문제 1. 

SELECT *,
lead(visit_month, 1) OVER(PARTITION BY user_id ORDER BY visit_month) AS next_1month,   lead(visit_month, 2) OVER(PARTITION BY user_id ORDER BY visit_month) AS next_2month, 
FROM   advanced.analytics_function_01  

문제 2. 

SELECT *,
  lead(visit_month, 1) OVER(PARTITION BY user_id ORDER BY visit_month) AS next_1month,
  lead(visit_month, 2) OVER(PARTITION BY user_id ORDER BY visit_month) AS next_2month,
  lag(visit_month, 1) OVER(PARTITION BY user_id ORDER BY visit_month) AS pre_1month
FROM
  advanced.analytics_function_01

문제 3. 

SELECT *,
SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date, order_id
) AS cumulative_sum_by_user

문제 4. 

SELECT *,
AVG(amount) OVER (ORDER BY order_date, order_id 
ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS last_5_orders_avg_amout

문제 1. 

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

문제 2. 

WITH table 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 ORDER BY query_cnt) AS team_rank
FROM table
QUALIFY team_rank = 1

문제 3. 

WITH base AS (
  SELECT
    EXTRACT(week FROM query_date) AS week_number,
    team,
    user,
    count(user) AS query_cnt
  FROM
    advanced.query_logs
  GROUP BY
    1,2,3
)
SELECT *,
  lag(query_cnt) OVER(PARTITION BY team, user ORDER BY week_number asc) AS prev_week_query_cnt
FROM base
QUALIFY team_rank = 1


문제 4. 

WITH base AS (
  SELECT
    user,
    team,
    query_date,
    COUNT(user) as query_count
  FROM
    advanced.query_logs
  GROUP BY
    1,2,3
)
SELECT
  user,
  team,
  query_date,
  query_count,
  SUM(query_count) OVER(PARTITION BY team, user ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_query_count
FROM
  base
ORDER BY
  team, 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,
  IF(number_of_orders is null , last_value(number_of_orders IGNORE NULLS) OVER(ORDER BY date asc), number_of_orders) AS number_of_orders_not_null
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
),
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

문제 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
  FROM
    advanced.app_logs
  WHERE
    event_date = '2022-08-18'
), diff_date AS (
  SELECT
    *,
    DATETIME_DIFF(event_datetime, pre_event_time, second) AS date_diff_sec
  FROM (
    SELECT
      *,
      LAG(event_datetime, 1) 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 date_diff_sec >= 20 THEN 1
  END AS start_session
FROM
  diff_date
)
SELECT
  event_date,
  event_datetime,
  event_name,
  user_id,
  user_pseudo_id,
  date_diff_sec,
  SUM(start_session) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) AS session_id
FROM
  session_start
ORDER BY
  user_pseudo_id, event_datetime;