[빠짝스터디 2주차 과제] 윈도우 함수(연습문제) - 탐색함수 / Frame / 총정리

탐색함수 연습문제

문제 1.

SELECT
	*,
	LEAD(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month) AS next_visit_month,
	LEAD(visit_month, 2) OVER (PARTITION BY user_id ORDER BY visit_month) AS nnext_visit_month
FROM advanced.analytics_function_01

문제2.

SELECT
	*,
	LEAD(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month) AS next_visit_month,
	LEAD(visit_month, 2) OVER (PARTITION BY user_id ORDER BY visit_month) AS nnext_visit_month,
	LAG(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month) AS previous_visit_month
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 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 UNION ALL
)

SELECT
	*,
	LAST_VALUE(number_of_order IGNORE NULLS) OVER (ORDER BY date) AS last_value_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
), 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
	*,
	#이전 이벤트 시간과 현재 이벤트시간의 간격을 SECOND 초단위로 구하기 / second_diff를 기반으로 새로운 세션의 시작일지 아닐지 판단
	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