题解 | 计算用户的平均次日留存率
计算用户的平均次日留存率
https://www.nowcoder.com/practice/126083961ae0415fbde061d7ebbde453
select sum(if(liu='留存',1,0))/count(device_id) avg_ret from (select device_id, date, if(datediff(date,lead(date,1)over(partition by device_id order by date))=-1,'留存','不留存') as liu from (select distinct device_id,date from question_practice_detail) as t1) as t2
挺抽象的
开窗口函数的那个表始终没办法解决去重问题,单加一个distinct也解决不了问题,于是参照某位大佬,又在后面补了一个子查询才行