查询每日新用户的次日留存率

1.先查询首次登录的新用户;

select user_id, min(date) from login group by user_id;

2.查询第二天登录的新用户;

select 
     t1.user_id, t2.user_id, t1.date
from 
    (select user_id, min(date) as date from login group by user_id) t1     
left join
    login t2
on 
    t1.user_id = t2.user_id and t2.date = date_add(t1.date, interval 1 day)

3.按首次登录的日期分组,第二天登录的新用户总数/第一天登录的新用户总数

select 
     t1.date as date, count(t2.user_id)/count(t1.user_id) as p
from 
    (select user_id, min(date) as date from login group by user_id) t1     
left join
    login t2
on 
    t1.user_id = t2.user_id and t2.date = date_add(t1.date, interval 1 day)
group by t1.date   

4.并集

union

5.当天没有新用户登录的日期

select 
    distinct(date) , '0.000'
from
    login
where
    date not in (select min(date) from login group by user_id);

6.留存率记得四舍五入，按日期排序，完整的SQL语句

select 
     t1.date as date, round(count(t2.user_id)/count(t1.user_id),3) as p
from 
    (select user_id, min(date) as date from login group by user_id) t1     
left join
    login t2
on 
    t1.user_id = t2.user_id and t2.date = date_add(t1.date, interval 1 day)
group by t1.date  
union
select 
    distinct(date) as date, '0.000' as p
from
    login
where
    date not in (select min(date) from login group by user_id)
order by date