用户次留问题
已知某款应用的用户登录日志表(login_table):
求此应用2月份用户的次留,3日、7日用户留存率。
select user_login_date ,retained_1_uv / retained_uv as retained_1_ctr --次留率 ,retained_3_uv / retained_uv as retained_1_ctr --3日留存率 ,retained_7_uv / retained_uv as retained_1_ctr --7日留存率 from ( select user_login_date --登录日期 ,count(distinct b.userid) as retained_uv --当日活跃用户 ,count(distinct case when datediff(user_active_date,user_login_date)=1 then b.userid else null end) as retained_1_uv --次留用户数 ,count(distinct case when datediff(user_active_date,user_login_date)=3 then b.userid else null end) as retained_3_uv --3日留存用户数 ,count(distinct case when datediff(user_active_date,user_login_date)=7 then b.userid else null end) as retained_7_uv --7日留存用户数 from ( select userid ,login_date as user_login_date from login_table where login_date>='2021/2/1' and login_date<='2021/2/28' group by userid,login_date ) a left join ( select userid ,login_date as user_active_date from login_table where login_date>='2021/2/1' and login_date<='2021/2/28' group by userid,login_date ) b on a.userid=b.userid group by user_login_date )
思路解析:
1、先按(日期,用户)去重,得到用户每日登录表 a
2、先按(日期,用户)去重,得到用户每日活跃表 b
3、通过用户关联a、b表,计算登录时间(user_login_date)与 活跃时间(user_active_date)的时间差,得到当日登录、次留、3日留存、7日留存用户数
4、计算留存率
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步