题解 | #牛客每个人最近的登录日期(五)#

1. 查询出有新用户登录的日期以及登录人数

select date,(select count(*) 
                 from (select min(user_id),date
                       from login 
                       group by user_id) as t
                 where t.date = l.date
                ) as new_counts
    from login l
    group by date
    order by date) as table1

2. 查询出每个日期新用户次日继续登录的人数

select count(*) as new
       from (select min(user_id),date
              from login 
              group by user_id) as t
              where t.date = DATE_ADD(l.date,INTERVAL 1 DAY)

select l.date,(select count(*) 
                from login l1
                where (user_id,DATE_ADD(l1.date,INTERVAL 1 DAY)) in (select user_id,date from login )
               and l1.date = l.date) as counts
 from login l
 group by date) as table2

3. 由于除数不能为0，所以用case语句判断如果当天没有新用户登录，所以该日的新用户留存率也就为0。

select table1.date,
                    case table1.new_counts
                    when 0 then 0.000
                    else round(table2.counts / table1.new_counts, 3)
                    end as p
from 
    (select date,(select count(*) 
                 from (select min(user_id),date
                       from login 
                       group by user_id) as t
                 where t.date = l.date
                ) as new_counts
    from login l
    group by date
    order by date) as table1
,
	(select l.date,(select count(*) 
                	from login l1
                	where (user_id,DATE_ADD(l1.date,INTERVAL 1 DAY)) in (select user_id,date from login )
                	and l1.date = l.date) as counts

from login l
group by date) as table2
where table1.date = table2.date
order by date;