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

牛客每个人最近的登录日期(五)

http://www.nowcoder.com/practice/ea0c56cd700344b590182aad03cc61b8

个人拙见，看了一些大佬的做法有些没看懂，分享一下自己的做法

①获取每个日期的新用户数量：

基于思路：题解 | #SQL69 牛客每个人最近的登录日期(四)#_牛客博客 (nowcoder.net) （具体看这个）

select date,sum(
    (case when (user_id,date) 
     in 
     (select user_id, min(date) from login group by user_id)
then 1 else 0 end))
from login 
group by date

我们能获取到每个日期的新用户数量列表A：

2020-10-12|3
2020-10-13|0
2020-10-14|1
2020-10-15|0
---------------------------------------

②获取每个日期的新用户留存数量

1）首先获取 “如果新用户留存“的日期列表，即日期加一天：select user_id,date(min(date),'+1 day') from login group by user_id ;

  1|2020-10-13
            2|2020-10-13
            3|2020-10-13
            4|2020-10-15
2）然后case when (user_id,date) in 上表中，则标注1，否则0 ：select *, case when  (user_id,date) in (select user_id,date(min(date),'+1 day') from login group by user_id) then 1 else 0 end from login;

得到

            1|2|1|2020-10-12|0
            2|3|2|2020-10-12|0
            3|1|2|2020-10-12|0
            4|2|2|2020-10-13|1
            5|1|2|2020-10-13|1
            6|3|1|2020-10-14|0
            7|4|1|2020-10-14|0
            8|4|1|2020-10-15|1
3）用group by加和：select date as date,sum( case when  (user_id,date) in (select user_id,date(min(date),'+1 day') from login group by user_id) then 1 else 0 end) as cnt from login group by date;
          得到列表B：

                2020-10-12|0
                2020-10-13|2
                2020-10-14|0
                2020-10-15|1
---------------------------------------------------

③对比数据：

列表A ： 2020-10-12|3 列表B：2020-10-12|0
                2020-10-13|0   2020-10-13|2
                2020-10-14|1   2020-10-14|0
                2020-10-15|0   2020-10-15|1

基于我的思路，列表B中的数据表示，2020-10-13日有2个10-12的新用户留存了，因此，留存率应该交叉计算，但是sql中貌似没有这样的操作，

因此，我将列表B中的日期全部减了一天，进而left join到列表A中（因为需要保留列表A中的日期），即：

列表A ：列表B'：2020-10-11|0

  2020-10-12|3 2020-10-12|2   2020-10-12|3|2
                2020-10-13|0 left join 2020-10-13|0 = 2020-10-13|0|0
                2020-10-14|1 2020-10-14|1   2020-10-14|1|1
                2020-10-15|0   2020-10-15|0|none

④最后，计算后两列的商即可得到次留存率，（由于left join产生的NULL值，以及0为分母产生的NULL值，需要用coalesce（）消除）

            2020-10-12|0.667
            2020-10-13|0.000
            2020-10-14|1.000
            2020-10-15|0.000

思考：由于列表A和列表B存在日期上的差别，在left join的过程中会出现NULL，那么会不会存在数据丢失的情况？

我的理解：并不会，列表A和B都是基于同一个表的，因此列表B可以完整的查询到新用户是否留存，

而在此题中，2020-10-15就算有新用户，由于该数据是最后一条，因而，列表B’不可能存在10-16号的数据，因此10-15号是否有新用户，均不影响10-15号的留存率，因为我们不知道（所以暂设为0）

总之，列表A中的最后一条数据，不论数值是多少，都无法计算留存率。所以也不存在数据丢失的情况了。

全部代码如下：

select n_new.date, round(coalesce(coalesce(n_renew.cnt,0)*1.0 / n_new.cnt,0),3)
from 
(select date,sum(
    (case when (user_id,date) 
     in 
     (select user_id, min(date) from login group by user_id)
then 1 else 0 end)) as cnt
from login 
group by date) as n_new

left join

(select date(date,'-1 day') as date,sum(
case when 
    (user_id,date) in (select user_id,date(min(date),'+1 day') from login group by user_id) 
    then 1 else 0 end) as cnt
from login
group by date) as n_renew 

on n_new.date = n_renew.date
;