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

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

全部评论

相关推荐

洲明科技 海外销售 全额一万三(20%绩效)
点赞 评论 收藏
分享
评论
1
2
分享

创作者周榜

更多
牛客网
牛客企业服务