一、最基础版-次日留存率
代码如下:
select
date
,count(*)/count(B.user_id) as rate
from
(
select
user_id
,date
from
app_launch
where date = '2022-08-13' ---开始计算留存率的第一天
)a
left join
app_launch b
on a.user_id = b.user_id
and datediff(B.date.A.date) = 1
二、进阶版-多天留存率
按照某一天作为开始期,计算次日留存、2日留存、3日留存...
select
gap
,count(distinct user_id2)
from
( select
user_id
from test_info
where date = '2022-08-24' ---选择这一天为开始时间
)A
left join
(
select
user_id user_id2
,datediff(date,'2022-08-24') gap
from test_info
)B
on A.user_id = B.user_id2
group by gap
<