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

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

大佬的思维就是不一样，通过使用if函数，判断device_id是否存在就能求出来，而且还顺带求出了总数