目录
一、题目
查询每天新增用户数以及他们次日留存率和三十日留存率
二、步骤
1、先求出每个用户最早的登录日期(辅助列)
SELECT user_id,login_date,MIN(login_date) over (PARTITION by user_id ORDER BY login_date ) as first_login from tb_user_login;
2、连表
将有辅助列的表与原表进行关联,关联的条件不仅为id相等,还有一个条件就是其登录的时间与最早的登录时间的差值
SELECT user_id,login_date,MIN(login_date) over (PARTITION by user_id ORDER BY login_date ) as first_login from tb_user_login)t1
LEFT JOIN tb_user_login as t2
on t1.user_id = t2.user_id and DATEDIFF(t2.login_date,first_login)=1
left JOIN tb_user_login as t3
on t1.user_id = t3.user_id and DATEDIFF(t3.login_date,first_login)=29
GROUP BY first_login,uesr_id;
3、统计
COUNT(DISTINCT t1.user_id) as 新增用户数,
COUNT(DISTINCT t2.user_id) /COUNT(DISTINCT t1.user_id) as 次日留存,
COUNT(DISTINCT t3.user_id) /COUNT(DISTINCT t1.user_id) as 三十日留存
4、源码
SELECT user_id,login_date,MIN(login_date) over (PARTITION by user_id ORDER BY login_date ) as first_login from tb_user_login;
# 查看每个用户登录的日期
SELECT
first_login,
COUNT(DISTINCT t1.user_id) as 新增用户数,
COUNT(DISTINCT t2.user_id) /COUNT(DISTINCT t1.user_id) as 次日留存,
COUNT(DISTINCT t3.user_id) /COUNT(DISTINCT t1.user_id) as 三十日留存
from (SELECT user_id,login_date,MIN(login_date) over (PARTITION by user_id ORDER BY login_date ) as first_login from tb_user_login)t1
LEFT JOIN tb_user_login as t2
on t1.user_id = t2.user_id and DATEDIFF(t2.login_date,first_login)=1
left JOIN tb_user_login as t3
on t1.user_id = t3.user_id and DATEDIFF(t3.login_date,first_login)=29
GROUP BY first_login;