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

with user_date as
(
    select
    device_id,
    date,
    lag(date,1) over(partition by device_id order by date)as last_date
    from question_practice_detail
)
, retention_stats AS 
(
    select
    count(distinct device_id,date) as total_acts,
    count(distinct case 
           when datediff(date,last_date)=1
           then concat(device_id,'_',last_date)
           end
         ) as ret_acts
    from user_date
)
SELECT ret_acts / total_acts AS avg_ret
FROM retention_stats;