题解 | 计算用户的平均次日留存率
select cast(sum(if(diff = 1,1,0))/count(1) as decimal(16,4)) as avg_ret from ( select device_id, date, datediff( LEAD(date) OVER (PARTITION BY device_id ORDER BY date),date) AS diff from ( select device_id, date from question_practice_detail group by 1,2 )t1 )t2
使用开窗函数并计算日期差=1
查看5道真题和解析
可以看看我帖子简历写法
