一、思路:
(1)使用窗口函数,先按用户分组,再按日期升序排序,再两两拼接(最后一个默认和null拼接),即lead(date) over (partition by device_id order by date),
用lead函数将同一用户连续两天的记录拼接起来。
(2)
检查date2和date1的日期差是不是为1,是则为1(次日留存了),否则为0(次日未留存),取avg即可得平均概率。
二、代码:
select
avg(if(datediff(date2,date1)=1,1,0)) as avg_ret
from
(
select distinct
device_id,
date as date1,
#窗口函数
lead (date) over (
partition by
device_id
order by
date
) as date2
from
(
select distinct
device_id,
date
from
question_practice_detail as a
) as a1
) as a2
注:牛客网的在线编程题