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

题目描述：sql语句查询每个日期新用户的次日留存率，结果保留小数点后面3位数(3位之后的四舍五入)，并且查询结果按照日期升序排序。

思路：基本上就是把前面几道题综合起来，当然愣是没想起来用union的我没底气说这话。o(╥﹏╥)o

步骤一：首先找到新用户的首次登录日

select user_id,
       min(date) as m_date
from login
group by user_id

步骤二：连接login找出新用户的次日留存率

select n.m_date,
       round(count(distinct l.user_id)/count(n.user_id),3) as p
from new n left join login l 
               on l.user_id=n.user_id 
               and l.date=adddate(n.m_date,interval 1 day)
group by n.m_date

步骤三：按照步骤二的输出格式，找出没有新用户的日期

select date,0.000 as p
from login
where date not in
(select min(date) from login group by user_id)

最后union组合存在新用户和不存在新用户的结果：

with new as
(
    select user_id,
           min(date) as m_date
    from login
    group by user_id
),
keep as
(
    select n.m_date,
           round(count(distinct l.user_id)/count(n.user_id),3) as p
    from new n left join login l 
               on l.user_id=n.user_id 
               and l.date=adddate(n.m_date,interval 1 day)
    group by n.m_date
),
no_new as
(
    select date,0.000 as p
    from login
    where date not in
    (select min(date) from login group by user_id)
)

select *
from keep
union
select *
from no_new
order by 1