题解 | #计算用户的平均次日留存率#
with a1 as (
select distinct device_id,date
from question_practice_detail
),
a as(
select device_id,date,count(*) over(partition by device_id order by date range between current row and interval 1 day following) as ct
from a1
),
a2 as (
select count(*) as ct1
from a
where a.ct = 2
),
a3 as (
select count(*) as ct2
from a1
)
select ct1/ct2 as avg_ret
from a2,a3
窗口函数秒
这世界真是越来越看不明白