# 思路:新用户次日登陆用户数/总用户数
# 1.次日登陆用户数
# (1)查询每个用户第一次登录时间
# select user_id,min(date)
# from login
# group by user_id
# (2)筛选出第一次登陆之后次日仍然登陆的用户 -- 子查询
# SELECT user_id,date
# from login
# where (user_id,date) in (
# select user_id,date_add(min(date),interval 1 day)
# from login
# group by user_id
# )
# (3)新用户次日登陆用户数
# SELECT count(distinct user_id)
# from login
# where (user_id,date) in (
# select user_id,date_add(min(date),interval 1 day)
# from login
# group by user_id
# )
# 2. 总用户数
# SELECT count(distinct user_id)
# from login
# 3.次日登陆用户数/总用户数
SELECT round(count(distinct user_id)/(SELECT count(distinct user_id) from login ),3)
from login
where (user_id,date) in (
select user_id,date_add(min(date),interval 1 day)
from login
group by user_id
)