1. 题目需求
从用户登录明细表(user_login_detail)中首次登录算作当天新增,第二天也登录了算作一日留存
结果如下:
2. 需要用到的表
用户登录明细表:user_login_detail
3. 查询sql
SELECT login_date first_login,
count(*) register,
cast(sum(if(datediff(lead_date,login_date)=1,1,0))/count(*) as decimal(16,2)) retention
from(
SELECT user_id,login_date,
lead(login_date)over(partition by user_id order by login_date) lead_date,
rank()over(partition by user_id order by login_date) rk
from(
select user_id,date_format(login_ts,'yyyy-MM-dd') login_date
from user_login_detail
group by user_id,date_format(login_ts,'yyyy-MM-dd')
)t1
)t2
where rk=1
group by login_date