首页 > 试题广场 >

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

[编程题]牛客每个人最近的登录日期(五)

热度指数：177927 时间限制：C/C++ 1秒，其他语言2秒空间限制：C/C++ 256M，其他语言512M
算法知识视频讲解

牛客每天有很多人登录，请你统计一下牛客每个日期新用户的次日留存率。
有一个登录(login)记录表，简况如下:

id	user_id	client_id	date
1	2	1	2020-10-12
2	3	2	2020-10-12
3	1	2	2020-10-12
4	2	2	2020-10-13
5	1	2	2020-10-13
6	3	1	2020-10-14
7	4	1	2020-10-14
8	4	1	2020-10-15

第1行表示user_id为2的用户在2020-10-12使用了客户端id为1的设备登录了牛客网，因为是第1次登录，所以是新用户
......
第4行表示user_id为2的用户在2020-10-13使用了客户端id为2的设备登录了牛客网，因为是第2次登录，所以是老用户
......
最后1行表示user_id为4的用户在2020-10-15使用了客户端id为1的设备登录了牛客网，因为是第2次登录，所以是老用户

请你写出一个sql语句查询每个日期新用户的次日留存率，结果保留小数点后面3位数(3位之后的四舍五入)，并且查询结果按照日期升序排序，上面的例子查询结果如下:

date	p
2020-10-12	0.667
2020-10-13	0.000
2020-10-14	1.000
2020-10-15	0.000

查询结果表明:
2020-10-12登录了3个(user_id为2，3，1)新用户，2020-10-13，只有2个(id为2,1)登录，故2020-10-12新用户次日留存率为2/3=0.667;
2020-10-13没有新用户登录，输出0.000;
2020-10-14登录了1个(user_id为4)新用户，2020-10-15，user_id为4的用户登录，故2020-10-14新用户次日留存率为1/1=1.000;

2020-10-15没有新用户登录，输出0.000;

(注意:sqlite里查找某一天的后一天的用法是:date(yyyy-mm-dd, '+1 day')，sqlite里1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5)

示例1

输入

drop table if exists login;
CREATE TABLE `login` (
`id` int(4) NOT NULL,
`user_id` int(4) NOT NULL,
`client_id` int(4) NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`id`));

INSERT INTO login VALUES
(1,2,1,'2020-10-12'),
(2,3,2,'2020-10-12'),
(3,1,2,'2020-10-12'),
(4,2,2,'2020-10-13'),
(5,1,2,'2020-10-13'),
(6,3,1,'2020-10-14'),
(7,4,1,'2020-10-14'),
(8,4,1,'2020-10-15');

输出

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

算法知识视频讲解

Winnie嘉嘉

SELECT a.date, ROUND(COUNT(b.user_id) * 1.0/COUNT(a.user_id), 3) AS p
FROM (
    SELECT user_id, MIN(date) AS date
    FROM login
    GROUP BY user_id) a
LEFT JOIN login b
ON a.user_id = b.user_id
AND b.date = date(a.date, '+1 day')
GROUP BY a.date
UNION
SELECT date, 0.000 AS p
FROM login
WHERE date NOT IN (
    SELECT MIN(date)
    FROM login
    GROUP BY user_id)
ORDER BY date;

发表于 2020-08-27 22:46:31 回复(27)

可乐熊🍓

首先明确若要求次日留存率公式为

$\frac{第一天�%***陆后第二天仍�%***录的用户数}{第一天�%***录的用户数}$

① 我们用user_id的最小日期作为第一天登录的日期以此来求第一天登录的用户数

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

② 用第一天登录的用户与login表联立

并用以下条件表示login 表为第一天登录了第二天也登陆的用户

left join login
on login.user_id = a.user_id
and login.date = date_add(a.date, interval 1 day)

③一定要注意此时不包含第一天没有新用户登录的情况！！！需要再有一个有所有日期的表格！！！并且最终的分组要根据该表格的date来分组

完整代码如下所示

select b.date
       ,ifnull(round(count(distinct login.user_id)*1.0 / count(a.user_id),3),0)as p
from (select distinct(date) 
      from login)b
left join 
(select user_id
             ,min(date) as date
      from login 
      group by user_id) a 
on b.date = a.date
left join login
on login.user_id = a.user_id
and login.date  = date_add(a.date, interval 1 day)
group by b.date;

编辑于 2021-02-19 10:11:28 回复(5)

SunburstRun

这个题目如果你做过牛客每个人最近的登录日期(三)和牛客每个人最近的登录日期(四)，那么解决起来会容易很多，做过(四)https://blog.nowcoder.net/n/a2f955514f824bb888f9d7726421e809我们知道每个日期的新用户求法为:

select login.date,ifnull(n1.new_num,0)
from login 
left join 
(select l1.date,count(distinct l1.user_id) as new_num
from login l1
where l1.date =
(select min(date) from login where user_id=l1.user_id)
group by l1.date) n1
on login.date = n1.date
group by login.date

做过(三) https://blog.nowcoder.net/n/1dad24440b3e45949dfda03cb1f06a9e的话，我们可以轻松得到每个日期新用户次日还登录的人的个数的sql语句如下:

select l1.date,count(distinct l1.user_id)
from login l1
join login l2 on l1.user_id=l2.user_id and l2.date=date((l1.date),'+1 day') 
where l1.date =
(select min(date) from login where user_id=l1.user_id)
group by l1.date;

得到的结果如下:

2020-10-12|2
2020-10-14|1

又在(四)里面提到过联立主表，将查询次日还登录的人的sql也联立主表：

(select login.date,ifnull(n1.new_num,0) as second_login_num
from login 
left join 
(select l1.date,count(distinct l1.user_id) as new_num
from login l1
join login l2 on l1.user_id=l2.user_id and l2.date=date((l1.date),'+1 day') 
where l1.date =
(select min(date) from login where user_id=l1.user_id)
group by l1.date) n1
on login.date = n1.date
group by login.date) second_login

就可以得到:

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

的结果了，然后将这2个表联立相除，得到的结果保留3为，用ifnull判断0/0的情况，那么就可以得到完整结果了：

select second_login.date, round(ifnull(second_login.second_login_num *1.0/ first_login.first_num,0),3)
from (select login.date,ifnull(n1.new_num,0) as second_login_num
from login 
left join 
(select l1.date,count(distinct l1.user_id) as new_num
from login l1
join login l2 on l1.user_id=l2.user_id and l2.date=date((l1.date),'+1 day') 
where l1.date =
(select min(date) from login where user_id=l1.user_id)
group by l1.date) n1
on login.date = n1.date
group by login.date) second_login

join 

(select login.date,ifnull(n1.new_num,0) as first_num
from login 
left join 
(select l1.date,count(distinct l1.user_id) as new_num
from login l1
where l1.date =
(select min(date) from login where user_id=l1.user_id)
group by l1.date) n1
on login.date = n1.date
group by login.date) first_login

on second_login.date=first_login.date

发表于 2020-08-20 15:09:45 回复(9)

牛客511806937号

/*看了三天好多题解和讨论区的解法，看的头昏眼花还是理解不了，终于找到这个容易拆解的简单表达方式，希望能降低同学们的理解成本
第一部分是表示新用户登录后，第二天又登录的情况，格式是选取新用户第一天登录的时间，以及对应这个时间的留存率
其实这个表达出的内容，已经是很接近题目要求的答案了
这里理解的难点在于题目要求每个日期新用户的次日留存率，这个留存率套用这一个公式A是不是会有遗漏
那举个栗子如果那天留存率是零，其实是选不出user_id的,是空值，又因为空值是无法代入计算公式的，
其实仔细想想这个公式本来就是回避掉空值只显示第二天有登录的用户才能做计算的
所以我们这个次日留存率的表达式需要完善，可以拆解为二部分
第一部分是新用户登陆后，次日又登录的留存率 A
第二部分是新用户登陆后，次日没有登录的留存率 B
其实A和B存在互斥关系，但它们的结合就是完整集合
举个栗子，一个完整的圆，把她一分为二，A和B部分，她们合起来是才完整集但她们互相排斥
瓶子里水量也可以等于瓶子里的水量，加空气的部分。。。空气的部分也属于瓶子里的空间
映射到题目中第二部分B的值在留存率的分子里外显表现就是零，但这个零的逻辑位置也是我们需要的，
计算机需要我们把所有的逻辑补完才能成功运行
即：留存率=(A+B)/分母
这里A,B,分母的外显数值和内里逻辑代码都需要一一对应，否则可能会因为内外不一致或者缺失导致误差
很常见的是计算出来的数值都对或者根本不知道怎么搞，其实就是里逻辑没有梳理清楚，只缘身在此山中要跳出来看整体*/

/*下面是代码部分，输入*/
select a.md,round( count(b.user_id)*1.0/ count(a.user_id),3) p
/*a的集合里的user_id就是首次登录的时间对应的user_id数量就是分母
相对的b集合的user_id是关联了a集合里的user_id,且登录时间的限制是在首次登录的基础上
次日又登录了，即分子部分,需要结合上下代码看*/
from (select user_id,min(date) md -- 特别注意这里的别名md
from login
group by user_id) a -- 首次登录的时间对应的各个项目
left join login b -- 首次登录的基础上，次日又登录时间对应的各个项目
on a.user_id = b.user_id
and b.date = date(a.md,'+1 day') -- mysql用date_add(a.md,interval 1 day)
group by a.md -- 将首次登录的时间做分组
union -- 把两个选择select用union联结起来，union联结的每个查询包含相同的列、表达式或聚集函数必须兼容
select date,0.000 p -- 次日没有登录的留存率默认就是零，小数点后三位保持与上面的select格式一致
from login
where date not in (select min(date)-- 排除（上一个select里排除的时间部分），即那些留存率为零的日期。
from login
group by user_id)

-- order by date （测试下来不加也适合本题）

以下是没有注释的样子：

select a.md,round( count(b.user_id)*1.0/ count(a.user_id),3) p
from (select user_id,min(date) md
from login
group by user_id) a
left join login b
on a.user_id = b.user_id
and b.date = date(a.md,'+1 day')
group by a.md
union
select date,0.000 p
where date not in (select min(date)
from login
group by user_id)

发表于 2020-10-26 14:21:31 回复(4)

肖大神

用前后函数加开窗函数：lead(expr,n) #返回分区函数中位于后n行的expr的值

select 
	date,
	#sum(if(rank1=1,1,0)) #当日新用户总数
	#lead(sum(if(rank1=2,1,0)),1) over(order by date) #次日留存用户总数
	#次日留存率 = 次日留存用户总数/当日用户总数
	round(ifnull(lead(sum(if(rank1=2,1,0)),1) over(order by date)/sum(if(rank1=1,1,0)),0),3) as p
from (select *,row_number() over(partition by user_id order by date) as rank1 from login) as t
group by date 
order by date;

发表于 2021-01-14 19:42:16 回复(11)

多吃早睡

目前为止遇到的较难sql题，两种解题方法

这种方法可能时间复杂度上稍许大一些，使用三表连接，主要思想还是count不会数出字段的空值

select b.date, round(count(l2.date)/count(b.date), 3) as p
from (
    select l1.user_id, min(l1.date) first_date, date_add(min(l1.date), interval 1 day) next_date
    from login as l1
    group by l1.user_id
) as a
left join login l2
on a.user_id=l2.user_id and a.next_date=l2.date  # 有第二天的被保存下来了
right join (
    select distinct date
    from login
) as b
on a.first_date=b.date
group by b.date
order by b.date asc

第二种方法，受该题热度第一启发，对上面解法做了改进，三表连接性能稍微差一点，因此使用union来处理当天不存在新用户的数据
注意：union自带去重效果

select a.first_date as date, round(count(l2.date)/count(a.first_date), 3) as p
from (
  select l1.user_id, min(l1.date) first_date, date_add(min(l1.date), interval 1 day) next_date
  from login as l1
  group by l1.user_id
) as a
left join login l2
on a.user_id=l2.user_id and a.next_date=l2.date  # 有第二天的被保存下来了，没有第二天的为null
group by a.first_date
union
select l2.date, 0.000 as p
from login as l2
where l2.date not in (
  select min(date)
  from login
  group by user_id
)
order by date asc

编辑于 2020-11-12 22:38:48 回复(4)

wengJJ

面对这种复杂查询，要试着拆分需求，再进行组合，逻辑会清晰点

Task: 查询每个日期新用户的次日留存率

关键点：新用户、次日留存率

T1（首次登录标记为1，作为新用户标签，记作new_u）：

(select user_id,date,(case 
when (user_id,date) in 
(select user_id,MIN(date) from login group by user_id) 
then 1 else null end) as new_u
from login) as t1

T2（次日登录标记为2，作为次日留存标签，记作rlog）：

(select user_id, date, 2 as rlog
from login
where (user_id,date) in 
(select user_id,date_add(date,interval 1 day) from login)) t2

T1和T2进行左连接（T1为全表，进行左连接可将次日登录标签进行（2\None）标记)

对new_u和rlog进行计数，次日登录标签总数/新用户标签总数（rlog/new_u）即可完成此答题

备注：由于题目需要将不存在表示为0.000，这里使用case方法进行值分配（不为空则为计数值，为空则为0.000）

select t1.date,(case when round(count(t2.rlog) / count(t1.new_u),3) then
                round(count(t2.rlog) / count(t1.new_u),3) else 0.000 end) as p
from
(select user_id,date,(case when (user_id,date) in (select user_id,MIN(date) from login group by user_id) then 1 else null end) as new_u
from login) as t1
left join
(select user_id, date, 2 as rlog
from login
where (user_id,date) in 
(select user_id,date_add(date,interval 1 day) from login)) t2
on t1.user_id=t2.user_id
group by date

发表于 2021-04-05 21:26:21 回复(4)

Mysql

我叫谢小盟，叫我查尔斯好了

##1
##每个日期新用户的次日留存率（按日期分类，统计人数）
##从内向外写，先写case when再套上round和ifnull


select date
       ,ifnull
        
        
        
       (round(
            
        (sum(
            case when 
            
              (user_id, date_add(date, interval 1 day) ) in
              (select user_id, date from login) ##条件一：保证第二天有登录，第二天登陆的记录在第一天也有
             
            and 
              
              (user_id, date) in 
              (select user_id, min(date) from login group by user_id)##条件二：这一天是这个用户的第一天登录
            
            then 1 else 0 end))##两个条件都满足，则为1，否则为0，然后进行sum
                      /
        (sum(
            case when 
              
              (user_id, date)in
              (select user_id, min(date) from login group by user_id)##这一天是这个用户的第一天
               then 1 else 0 end))
           
        , 3) ##round到第三位
        , 0) as p #如果是nell则返回0



from login
group by date
order by date;

发表于 2021-09-09 19:48:45 回复(0)

qstalking

#明确问题：12号的新用户次留是指在12号是第一次登录，并且在13号也登录了。
#分母：当前日期新用户的特征是当前日期=该用户所有登录日期的最小值

#分子：当前日期作为前一天有该用户的登录记录并且是第一次登录。（12号作为前一天有这个人，说明13号有这个人）

【易错点】因为分母有可能为0，所以用ifnull(不为null时返回的值，为null时返回的值)，这里指定为null时返回0

select date
        ,ifnull(round((sum(case when (user_id,date)in
            (select user_id,date_add(date,interval -1 day) 
             from login)  and (user_id,date)in (select user_id,min(date)from login group by user_id)
            then 1 else 0 end))/
        (sum(case when (user_id,date)in
            (select user_id,min(date)from login group by user_id)
            then 1 else 0 end)),3),0)as p
from login
group by date
order by date;

编辑于 2021-02-16 11:33:00 回复(5)

Mysql

不知道起什么名的小白

select      t1.date
            ,ifnull(round(count(t3.user_id)/count(t2.user_id), 3), 0.000) as p 
from(
            --  所有日期，方便最后计算日期，留存率
            select      date
            from        login
            group by    date  )t1 
left join(
            --  每个用户最早登录的日期，作为新用户
            select      user_id, min(date) as first_login
            from        login
            group by    user_id  )t2 
            on t1.date = t2.first_login
left join(
            --  为统计每个用户隔天登录的次日留存作准备
            select      user_id, date as new_login
            from        login
            group by    user_id, date  )t3 
            on t2.user_id = t3.user_id and datediff(t3.new_login, t2.first_login) = 1
group by    t1.date;  -- t1.date会有很多行

发表于 2022-09-13 14:50:29 回复(0)

Sqlite

平安喜乐_Ada

1. 窗口函数对所有用户的登录情况进行排序

SELECT user_id, date, 
ROW_NUMBER() OVER(PARTITION BY user_id order BY date) rnk FROM login

2. 左连login - 判断条件为同一用户且次日登录的用户

LEFT JOIN login l ON a.user_id = l.user_id 
AND DATEDIFF(l.date, a.date) = 1

这两步就能够对所有用户进行排序且找到次日登录的用户信息

3. 直接求 - 先对日期分组

group by a.date

判断如果rnk = 1,就求用户的次日登录情况个数和当前登录新用户个数

count(IF(rnk = 1, l.date, null)) 和 count(IF(rnk = 1, a.date, null))

否则证明该日期不是新登录用户，个数求得为null,后面用ifnull换成0即可

整体代码如下：

SELECT a.date, 
    ifnull(round(count(IF(rnk = 1, l.date, null))/count(IF(rnk = 1, a.date, null)), 3), 0)
    FROM
    (
        SELECT user_id, date, 
        ROW_NUMBER() OVER(PARTITION BY user_id order BY date ) rnk 
        FROM login
        )a 
    LEFT JOIN login l ON a.user_id = l.user_id 
    AND DATEDIFF(l.date, a.date) = 1
group by a.date

发表于 2021-10-10 11:52:53 回复(3)

Mysql

牛客395521783号

mysql 解答方法

一、求出每个日期的新用户数量
先用min求出每个用户第一次登陆的日期，则该日期下该用户为当天新用户，形成子表b；再用login表

左连接b，使用sum、case when 和group by 计算出每天的新用户总数c。

select l1.date, sum(case when l1.date = b.m_d then 1 else 0 end) c

from login l1 left join (

    select user_id, min(date) m_d

    from login

    group by user_id) b

on l1.user_id = b.user_id

group by l1.date

二、计算每个日期次日留存的新用户数量

查询有日新增用户的日期和用户形成子表关联login表，使用case when、sum计算每天次日留存的用户数量，再次关联login表拿到没有新增用户的日期和次日留存的用户数

select distinct l.date, ifnull(e.r,0) r1

from login l left join (

    select a.m_d,

    sum(case when l.date = date_add(a.m_d, interval 1 day) then 1 else 0 end) r

    from login l left join(

      select user_id, min(date) m_d

    from login

      group by user_id) a

    on l.user_id = a.user_id

    group by a.m_d) e

on l.date = e.m_d

三、根据日期关联第一步和第二部帅选的结果，拿次日留存的用户数和每日新用户总数计算次日留存率

select s.date, ifnull(round(s.r1/f.c,3),0)

from(

    select distinct l.date, ifnull(e.r,0) r1

    from login l left join (

    select a.m_d,

    sum(case when l.date = date_add(a.m_d, interval 1 day) then 1 else 0 end) r

    from login l left join(

      select user_id, min(date) m_d

      from login

      group by user_id) a

    on l.user_id = a.user_id

    group by a.m_d) e

    on l.date = e.m_d) s

join(

    select l1.date, sum(case when l1.date = b.m_d then 1 else 0 end) c

    from login l1 left join (

    select user_id, min(date) m_d

    from login

      group by user_id) b

    on l1.user_id = b.user_id

group by l1.date) f

on s.date = f.date

发表于 2021-09-14 15:54:50 回复(0)

地瓜烤红薯

一共三步：

1、先算出每天的新用户中有多少个第二天还登陆了（当天时间 -- 第一次登录时间差值为1 ）

select o.date, sum(case  when o.sd =1 then 1 else 0 end) as n from 
(select user_id ,login.date ,
julianday(lead(login.date ,1,0)over(partition by user_id order by date))-julianday(min(date)over(partition by user_id order by date)) as sd 
 from login) o group by o.date

2、算出每天有多少个新用户（完全复制上一题的答案即可）

select a.date ,sum(case a.date when a.fd then 1 else 0 end) as new
from 
(select user_id ,login.date ,min(login.date) over (partition by user_id order by login.date)as fd
from login) a group by a.date

3、将上面两步得出的表格进行连接，然后做除法算比率。（注意:0不能为分母要用case when 处理）

select c.date , round(1.0*c.n/ (case when b.new =0 then 1 else b.new end ),3)
from
(select o.date, sum(case when o.sd =1 then 1 else 0 end) as n from
(select user_id ,login.date ,
julianday(lead(login.date ,1,0) over(partition by user_id order by date))-julianday(min(date)over(partition by user_id order by date))
as sd from login) o group by o.date) c
left join
((select a.date ,sum(case a.date when a.fd then 1 else 0 end) as new from
(select user_id ,login.date ,min(login.date) over (partition by user_id order by login.date)as fd
from login) a group by a.date)) b
on c.date = b.date

发表于 2020-09-03 18:48:09 回复(0)

Yuancheng

题目求的是每个日期新用户的次日留存率

次日留存率 = 次日登录的新用户/新用户

所以我们解题时表的基础内容应该是每天的新用户

新用户表可以通过子查询求得用户最初登录时间表和 login 表inner join得到

次日登录的新用户可以通过利用条件date(l1.date,'+1 day') = l2.date来与新用户表进行left join 得到

原始表	原始表	求新用户	求次日登录新用户
id	今天的日期	首次登录	第二天登录
0001	2020-10-12	2020-10-12	0001
0002	2020-10-12	2020-10-12
0003	2020-10-12	2020-10-12
0001	2020-10-13

（这个表只是举个例子）

按道理来说我们只需要通过group by date然后

(l1.user_id = l3.user_id and l2.date = l1.date) or null) / count(l2.date = l1.date or null) 就可以计算出次日留存率了

次日登录 and 新用户新用户

但是因为会存在当日没有新用户的情况，存在除数为0的情况，所以我们可以选择使用case when来避免分母为0的情况。

select l1.date as date,
case when count(l2.date = l1.date or null) = 0 then 0.0 -- 新用户为0
else
round(count(l1.user_id = l3.user_id and l2.date = l1.date or null)*1.000/ count(l2.date = l1.date or null) ,3)
end as p
from 
(login as l1 left join (select user_id,min(date) as date from login  group by user_id) as l2
on l2.user_id =l1.user_id and l2.date =l1.date -- 新用户
left join login as l3 
on date(l1.date,'+1 day') = l3.date and l1.user_id = l3.user_id) -- 次日登录新用户
group by l1.date
order by l1.date

编辑于 2020-08-21 00:25:50 回复(2)

Mysql

冷千千

需求：每天新用户的次日留存率。

方法1：窗口函数和ifnull.每天新用户数是没有上次登陆日期的用户，次日留存是下次登陆日期间隔为1天的用户。

#每天新用户的次日留存率。
select 
date
,round(ifnull(count(distinct case when l is null and datediff(n,date)=1 then user_id else null end)/count(distinct case when l is null then user_id else null end),0),3)as summ
from
(select 
*,lead(date)over(partition by user_id)as n-- 下一行
,lag(date)over(partition by user_id)as l-- 上一行
from login)a1
group by date

方法2：当日无新增的用union all连接。找出当日新用户和次日新用户。

select 
a.date
,round(count(b.user_id)/count(a.user_id),3)

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

left JOIN
(select 
user_id,date
from login)b 
on a.user_id=b.user_id and b.date=date_add(a.date,interval 1 day)

group by a.date

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

发表于 2022-02-03 22:31:15 回复(1)

Mysql

LilOwer

with t as(
select
	*,
	case 
		when (ROW_NUMBER() over(partition by user_id order by date))=1 then 1 else 0 
	end tag
from login)
select
	date,
	ifnull(round(lead(sum(if(tag=0,1,0)) ,1,0) over()/sum(tag),3),0) p
from t 
group by date

发表于 2021-11-14 17:14:23 回复(0)

美丽的回笼觉觉主在刷代码

纠结如何计算非首次登陆日期的数值纠结了快半小时。

原来可以直接用not in。真好又补上一个盲点。

SELECT first_day AS "date", 
    ROUND(COUNT(next_day) / COUNT(first_day),3) AS p
FROM (
    SELECT t.user_id, t.first_day, l.date AS next_day
    FROM (
        SELECT DISTINCT user_id, 
            MIN(date) OVER (PARTITION BY user_id) AS first_day
        FROM login
    ) t
    LEFT JOIN login l ON l.user_id = t.user_id
        AND l.date = DATE_ADD(t.first_day, INTERVAL 1 DAY)
) tt 
GROUP BY first_day
UNION
SELECT DISTINCT date, 0.000 AS p 
FROM login
WHERE date NOT IN (
   select min(date) from login group by user_id
)
ORDER BY date

发表于 2024-10-17 10:22:36 回复(0)

Mysql

一颗宏心

select t.date,ifnull(round(count(l.date)/sum(t.rn=1),3),0) as p
from(
    select user_id,date,
    row_number() over(partition by user_id order by date) as rn
    from login
)t 
left join login l 
on t.user_id = l.user_id and datediff(t.date,l.date) = -1 and t.rn = 1
group by t.date
order by t.date;

发表于 2022-12-04 17:02:37 回复(0)

Mysql

牛客200725243号

分享一个不一样的思路，

先分别用窗口函数把用户第一次登录a和第二次的登录b算出并且用用户id连接，

然后再筛选第一天是第二天的前一天，

满足条件的作为分子sum(if(a.date=date_sub(b.date,interval 1 day),1,0))，第一天所有注册的作为分母count（*），就可以算出留存率，最后将结果再次与原表链接，去重日期，ifnull留存率即可。

完整如下：

select distinct l.date,ifnull(ak.aa,0.000)
from login l left join (select a.date,round(sum(if(a.date=date_sub(b.date,interval 1 day),1,0))/count(*),3) aa
from (select user_id,date
from (select user_id,date,rank()over(partition by user_id order by date) t_r
from login) a where a.t_r=1) a
join
(select user_id,date
from (select user_id,date,rank()over(partition by user_id order by date) t_r
from login) a where a.t_r=2) b
on a.user_id=b.user_id
group by a.date) ak on l.date=ak.date

发表于 2022-07-22 19:14:31 回复(0)

Mysql

素兮淑清

我的思路是先把新增的用户都给找出来（用窗口函数），然后和每天的用户根据用户id和日期（日期调整到前一天，12号调整为11号，以此类推）join起来，这样就可以看到新增用户和第二天是否还有这个用户的具体情况，最后用group by 数一下人数相除就行，这题我想了好长时间，虽然时间很长但也是自己想出来的。我说的可能不清楚，如果有需要可以结合代码看（mysql可跑通）

select x.date,
       case when p is null then 0.000
       else p end as p
from
(select distinct date from login order by date asc) as x

left join

(select date, round(count(re_user)/count(new_user),3) as p
from
(select c.date, c.user_id as new_user, d.user_id as re_user
from
(select date,user_id from 
(select *,
       dense_rank () over (partition by user_id order by date asc) as date_rank
from login) as a
where date_rank = 1
order by date_rank asc,date asc) as c

left join 

(select date_sub(date,interval 1 day) as day_lead,user_id from login
order by date asc, user_id asc) as d

on c.date = d.day_lead and c.user_id = d.user_id) as z

group by date) as y

on x.date = y.date

发表于 2022-03-13 14:36:34 回复(0)