3497. Analyze Subscription Conversion

Tables

erDiagram
	UserActivity {
		user_id int PK
		activity_date date PK
		activity_type varchar PK "free_trial, paid, cancelled"
		activity_duration int
	}
Mermaid
복사

Question

A subscription service wants to analyze user behavior patterns. The company offers a 7-day free trial, after which users can subscribe to a paid plan or cancel. Write a solution to:

Find users who converted from free trial to paid subscription

Calculate each user's average daily activity duration during their free trial period (rounded to 2 decimal places)

Calculate each user's average daily activity duration during their paid subscription period (rounded to 2 decimal places)

Return the result table ordered by user_id in ascending order.

The result format is in the following example.

DataFlow

first join date free trial + 7 days user_id group by -> AVG

Answer

WITH join_dt AS (
	SELECT
		*,
		MIN(activity_date) OVER(PARTITION BY user_id ORDER BY activity_date ASC)AS first_join
	FROM UserActivity
)

SELECT
	user_id,
	ROUND(AVG(CASE WHEN activity_type = 'free_trial' THEN activity_duration END), 2) AS trial_avg_duration,
	ROUND(AVG(CASE WHEN (first_join < activity_date) AND (activity_type = 'paid') THEN activity_duration END), 2) AS paid_avg_duration
FROM join_dt
GROUP BY user_id
HAVING paid_avg_duration IS NOT NULL
ORDER BY user_id ASC;
SQL
복사

날짜가 아니라, COUNT 사용 필터도 가능하다

SELECT
	user_id,
	ROUND(AVG(CASE WHEN activity_type='free_trial' THEN activity_duration ELSE NULL END),2) AS trial_avg_duration,
	ROUND(AVG(CASE WHEN activity_type='paid' THEN activity_duration ELSE NULL END),2) AS paid_avg_duration
FROM useractivity
WHERE user_id IN (
	SELECT
		user_id
	FROM useractivity
	GROUP BY user_id
	HAVING COUNT(DISTINCT CASE WHEN activity_type='free_trial' THEN 1 END)>0
	AND COUNT(DISTINCT CASE WHEN activity_type='paid' THEN 1 END)>0
)
GROUP BY user_id
ORDER BY user_id;
SQL
복사