题目:
题解:
SELECT login_date first_login,
count(*) register,
--差值是1时候为连续两日登录
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