题解 | 计算用户的平均次日留存率
#对原始数据进行去重
with data as
(
select distinct
device_id
,date
from question_practice_detail
)
select
sum(if(datediff(next_date,date)=1,1,0))/count(device_id) as avg_ret
from
(
select
device_id
,date
#每一个日期跟一个next_date,没有的话用0补充，确保后续count正确
,ifnull(lead(date,1)over(partition by device_id order by date),0) as next_date
from data
)a