# 查询每个日期新用户的次日留存率
# 1、因为有新用户的条件，所以用user_id分组，求登录日期的最小值，则为用户的首次登录日期；
# 2、用上述结果，left join 登录表，用 user_id 连接，这样，每个用户的首次登录数据就可以和后续每次登录记录匹配上；
# 3、用2的登录日期，减去1的首次登录日期，得到，登录天数差，1就是次日登录，6是首次登录后的第七日登录，依次类推
# 4、这时，有个重点是，不能用首次登录日期去分组，聚合当天登录人数，以及次日登录人数，因为，用户分组，取最小登录日期的操作，并不能保证日期是连续的，有可能某一天，没有用户登录，日期列会存在断点，导致结果数据不准，这种情况，就需要再用 login 表，日期列分组，再和上述3结果 join，最后用连续的日期列分组，count（distinct user_id）这是每日的登录用户数，天数差为1的记录计数为次日登录人数，这样就算出次日留存率了


with dwd_user as 
(
select t1.date
      ,t2.user_id
	  ,t2.first_login_date
      ,t3.user_id as user_id2
      ,t3.date as login_date
      ,datediff(t3.date,t2.first_login_date) as days_diff
from (select date 
      from login
      group by date) t1
left join       
(select user_id
       ,min(date) as first_login_date
 from login
 group by user_id) t2
 on t1.date=t2.first_login_date
left join login as t3
on t2.user_id=t3.user_id
)
select date
      ,ifnull(round(sum(case when days_diff=1 then 1 else 0 end)/count(DISTINCT user_id2),3),0) as p
from dwd_user
group by date
order by date