[PostgreSQL] 7. 사용자 등록 수의 추이와 경향 보기

다음과 같이 SNS 서비스에서 수집된 데이터의 예시로 실습한다.

---

1. 등록 수의 추이와 경향 보기

• 사용자 등록이 필요한 서비스에서 등록 수는 중요한 지표이다.
• 등록자가 감소 경향을 보인다면 서비스를 활성화하기 어려워진다는 의미이고,
• 반대로 등록자가 증가 경향을 보인다면 사용자가 서비스에서 이탈할지 아닐지를 분석해서 서비스 활성화와 연결할 수 있어야 한다.
• 사용자 이탈 여부를 살피기 전에, 먼저 현재 등록 수를 파악해본다.

▶︎ 날짜별 등록 수의 추이

SELECT register_date
	, count(DISTINCT user_id) AS register_count
FROM mst_users 
GROUP BY 1
ORDER BY 1

 

▶︎ 월별 등록 수의 추이

• 등록 날짜에서 연-월을 추출하고, 이를 기준으로 그룹바이한다.
• 등록 수는 count(distinct ..)를 사용하고
• 전월 대비 비교를 하기 위해 LAG 윈도우 함수를 사용한다.

WITH 
mst_users_with_year_month AS ( 
	SELECT *
		, substring(register_date, 1, 7) AS year_month		
	FROM mst_users
)
SELECT year_month
	, count(DISTINCT user_id) AS register_count
	, LAG(count(DISTINCT user_id)) OVER (ORDER BY year_month) AS last_month_count
	, 1.0 * count(DISTINCT user_id) / LAG(count(DISTINCT user_id)) OVER (ORDER BY year_month) AS month_over_month_ratio
FROM mst_users_with_year_month
GROUP BY year_month
;

결과 예시

 

▶︎ 등록 디바이스별 추이

• 등록 디바이스에 따라 사용자 행동이 다를 수 있다. 따라서 디바이스에 따라 사용자 분석을 하면 또다른 경향을 살필 수 있을 것이다.
• 여러 장치를 사용하는 멀티 디바이스 사용자에 대한 고려도 추가한다.

WITH 
mst_users_with_year_month AS ( 
	SELECT *
		, substring(register_date, 1, 7) AS year_month		
	FROM mst_users
)
SELECT year_month
	, count(DISTINCT user_id) AS register_count
	, count(DISTINCT CASE WHEN register_device = 'pc' THEN user_id end) AS register_pc
	, count(DISTINCT CASE WHEN register_device = 'sp' THEN user_id END) AS register_sp
	, count(DISTINCT CASE WHEN register_device = 'app' THEN user_id END) AS register_app
FROM mst_users_with_year_month
GROUP BY year_month
;