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

문제 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 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
	*,
	LAG(query_cnt) OVER (PARTITION BY user ORDER BY week_number) AS prev_week_query_cnt
FROM table

문제4.

WITH query_count_table AS (
	SELECT
		*,
		COUNT(*) AS query_count
	FROM advanced.query_logs
	GROUP BY ALL
)

SELECT
	*,
	SUM(query_count) OVER (PARTITION BY user ORDER BY query_date) AS cululative_query_count
FROM query_count_table

문제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,
		 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'
		 AND user_pseudo_id = "1997494153.8491999091"
		 
), diff_data AS(
SELECT
	*,
	
	DATETIME_DIFF(event_datetime, prev_event_datetime, SECOND) AS second_diff
FROM
	(
	SELECT
		*,
		# 직전 이벤트 시간을 prev_event_datetime으로 만들기
		LAG(event_datetime, 1) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) AS prev_event_datetime
	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 NULL
			END AS session_start
	FROM diff_data
)
ORDER BY event_datetime