题解 | 计算用户的平均次日留存率

# 解法1
#使用select 函数筛选出日期非空的数据,if(条件，值1，值2)
# select avg(if(b.device_id is not null,1,0)) 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)date
#     from question_practice_detail
# )b
# on a.device_id = b.device_id and a.date = b.date
#解法2，先左连接得出第一天回答但是第二天未回答的人数，之后在b表重进行筛选出第二天也回答的人
select count(distinct b.device_id,b.date)/count(distinct a.device_id,a.date) as avg_ret
from question_practice_detail a left JOIN question_practice_detail b
on a.date = b.date-1 and a.device_id = b.device_id