sql练习:计算用户的平均次日留存率

记录一下今天遇见的一道sql题

要求结果:

首先得知道平均次日留存率的计算公式

p=(这段时间内所有用户连续两天登录的次数)/(这段时间内所有用户登录的总次数)

求这段时间内所有用户登录的日期为表a

select distinct device_id,date from question_practice_detail

将用户登录过的日期全部减一天得到表b

select distinct device_id,date_sub(date,interval 1 day) as datefrom question_practice_detail

a表和b表进行左外连接,求某个用户前一天是否登录

select * from 
(select distinct device_id,date from question_practice_detail ) a
left join 
(select distinct device_id,date_sub(date,interval 1 day) as datefrom question_practice_detail
) b
on a.device_id = b.device_id and a.date = b.date

求用户连续两天都登录的次数 

select a.device_id , if(b.device_id is not null,1,0) temp
from
(select distinct device_id,date from question_practice_detail ) a
left join 
(select distinct device_id,date_sub(date,interval 1 day) as datefrom question_practice_detail
) b
on a.device_id = b.device_id and a.date = b.date

求平均次日留存率

select avg (if(b.device_id is not null,1,0)) as avg_ret
from
(select distinct device_id,date from question_practice_detail ) a
left join 
(select distinct device_id,date_sub(date,interval 1 day) as datefrom question_practice_detail
) b
on a.device_id = b.device_id and a.date = b.date