연습과제

Retain User를 New + Current + Resurrected + Dormant User로 나누는 쿼리를 작성해 보세요.

SQL

#event_week
WITH base AS (
	SELECT
		DISTINCT
		user_pseudo_id,
		DATE_TRUNC(DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul'), WEEK(MONDAY)) AS event_week
	FROM advanced.app_logs
),
week_data AS (
	#first_week, prev_week, next_week
	SELECT
		*,
		MIN(event_week) OVER(PARTITION BY user_pseudo_id) AS first_week,
		LAG(event_week, 1) OVER(PARTITION BY user_pseudo_id ORDER BY event_week) AS prev_week,
		LEAD(event_week, 1) OVER(PARTITION BY user_pseudo_id ORDER BY event_week) AS next_week
	FROM base
),
status_data AS (
	#user_status
	SELECT
		user_pseudo_id,
		event_week,
		CASE
			WHEN event_week = first_week THEN 'New'
			WHEN DATE_DIFF(event_week, prev_week, WEEK(MONDAY)) = 1 THEN 'Current'
			WHEN DATE_DIFF(event_week, prev_week, WEEK(MONDAY)) > 1 THEN 'Resurrected'
			ELSE NULL
		END AS user_status
	FROM week_data

	UNION ALL

	SELECT
		user_pseudo_id,
		DATE_ADD(event_week, INTERVAL 1 WEEK) AS event_week,
		'Dormant' AS user_status
	FROM week_data
	WHERE
		(next_week IS NULL)
		OR (DATE_DIFF(next_week, event_week, WEEK(MONDAY)) > 1)
)

#user_cnt 및 정렬
SELECT
	event_week,
	user_status,
	COUNT(DISTINCT user_pseudo_id) AS user_cnt
FROM status_data
GROUP BY ALL
ORDER BY
	event_week,
	CASE user_status
		WHEN 'New' THEN 1
		WHEN 'Current' THEN 2
		WHEN 'Resurrected' THEN 3
END;

https://docs.google.com/spreadsheets/d/12mi7dBN0ytltrxhChkJg0tOmhxpLps6PlkEMYXJCY2o/edit?usp=sharing