题目描述:sql语句查询新登录用户次日成功的留存率,即第1天登陆之后,第2天再次登陆的概率,保存小数点后面3位(3位之后的四舍五入)。
一、先找出所有用户id以及他们最开始的登录时间(表名mind_date):
select user_id,min(date)as mdate from login group by user_id
二、使用login表与一表进行连接,找出多少新用户第一天登录第二天也登陆(表名keep):
select count(l.user_id) as keep from login l join min_date m on l.user_id=m.user_id and l.date =adddate(m.mdate,interval 1 day)
三、算出所有用户数(表名total):
select count(distinct user_id) as total from login
根据公式新用户次日留存率=(第一天和第二天都登陆的用户数)/(总用户数)
最终结果:
with min_date as ( select user_id,min(date)as mdate from login group by user_id ), keep as ( select count(l.user_id) as keep from login l join min_date m on l.user_id=m.user_id and l.date =adddate(m.mdate,interval 1 day) ), total as ( select count(distinct user_id) as total from login ) select round(keep.keep/total.total,3) as p from keep,total