用户留存分析—SQL

rubyw

已于 2022-04-30 00:04:21 修改

阅读量844

点赞数

分类专栏： SQL 用户行为分析文章标签： sql 数据库 mysql 数据分析

于 2022-04-30 00:01:34 首次发布

本文链接：https://blog.youkuaiyun.com/rubyw/article/details/124506757

版权

SQL 同时被 2 个专栏收录

7 篇文章

订阅专栏

用户行为分析

4 篇文章

订阅专栏

这篇博客详细介绍了如何通过SQL查询计算应用的用户留存率，包括次日留存、3日留存、7日留存和30日留存。通过对每日新增用户的统计和后续日活跃用户的追踪，揭示了用户粘性的关键指标，为产品优化和用户增长策略提供数据支持。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SELECT
	log_day '日期',
	count( user_id_day0 ) '新增数量',
	count( user_id_day1 ) / count( user_id_day0 ) '次日留存率',
	count( user_id_day3 ) / count( user_id_day0 ) '3日留存率',
	count( user_id_day7 ) / count( user_id_day0 ) '7日留存率',
	count( user_id_day30 ) / count( user_id_day0 ) '30日留存率' 
FROM
	(
	SELECT DISTINCT
		log_day,
		a.user_id_day0,
		b.user_id AS user_id_day1,
		c.user_id AS user_id_day3,
		d.user_id AS user_id_day7,
		e.user_id AS user_id_day30 
	FROM
		( SELECT DISTINCT 
				date AS log_day, 
				user_id AS user_id_day0 
				FROM 
				data
				GROUP BY user_id 
				ORDER BY log_day 
				) a
		LEFT JOIN data b ON DATEDIFF(b.date, a.log_day ) = 1 
		AND a.user_id_day0 = b.user_id
		LEFT JOIN data c ON DATEDIFF(c.date, a.log_day ) = 2 
		AND a.user_id_day0 = c.user_id
		LEFT JOIN data d ON datediff(d.date, a.log_day ) = 6 
		AND a.user_id_day0 = d.user_id
		LEFT JOIN data e ON datediff(e.date, a.log_day ) = 29 
		AND a.user_id_day0 = e.user_id 
	) temp 
GROUP BY
	log_day
ORDER BY log_day;