记录一下今天遇见的一道sql题
要求结果:
首先得知道平均次日留存率的计算公式
p=(这段时间内所有用户连续两天登录的次数)/(这段时间内所有用户登录的总次数)
求这段时间内所有用户登录的日期为表a
select distinct device_id,date from question_practice_detail
将用户登录过的日期全部减一天得到表b
select distinct device_id,date_sub(date,interval 1 day) as date
from 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 date
from 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 date
from 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 date
from question_practice_detail
) b
on a.device_id = b.device_id and a.date = b.date
