首页 > 试题广场 >

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

[编程题]牛客每个人最近的登录日期(三)
  • 热度指数:126878 时间限制: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 4 1 2020-10-13
6 1 2 2020-10-13
7 1 2 2020-10-14
第1行表示user_id为2的用户在2020-10-12使用了客户端id为1的设备第一次新登录了牛客网
。。。
第4行表示user_id为2的用户在2020-10-12使用了客户端id为2的设备登录了牛客网
。。。
最后1行表示user_id为1的用户在2020-10-14使用了客户端id为2的设备登录了牛客网


请你写出一个sql语句查询新登录用户次日成功的留存率,即第1天登陆之后,第2天再次登陆的概率,保存小数点后面3位(3位之后的四舍五入),上面的例子查询结果如下:
p
0.500
查询结果表明:
user_id为1的用户在2020-10-12第一次新登录了,在2020-10-13又登录了,算是成功的留存
user_id为2的用户在2020-10-12第一次新登录了,在2020-10-13又登录了,算是成功的留存
user_id为3的用户在2020-10-12第一次新登录了,在2020-10-13没登录了,算是失败的留存
user_id为4的用户在2020-10-13第一次新登录了,在2020-10-14没登录了,算是失败的留存
故次日成功的留存率为 2/4=0.5
(sqlite里查找某一天的后一天的用法是:date(yyyy-mm-dd, '+1 day'),四舍五入的函数为round,sqlite 1/2得到的不是0.5,得到的是0,只有1*1.0/2才会得到0.5
mysql里查找某一天的后一天的用法是:DATE_ADD(yyyy-mm-dd,INTERVAL 1 DAY),四舍五入的函数为round)

示例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,4,1,'2020-10-13'),
(6,1,2,'2020-10-13'),
(7,1,2,'2020-10-14');

输出

0.500
先计算每个用户id和最早登录日期:
select l1.user_id, min(l1.date) as m_date
from login as l1
group by l1.user_id
如果用户第二天登录,则计数,除以所有用户数目,即留存率,所有sql如下:
select round(1.0*sum(case when l2.user_id=l_f.user_id 
               and l2.date=date(l_f.m_date,'+1 day') then 1
               else 0 end)/count(distinct l2.user_id),3) as p
from login as l2, (select l1.user_id, min(l1.date) as m_date
                   from login as l1
                   group by l1.user_id) as l_f



发表于 2020-08-22 10:46:49 回复(1)
select round(count(distinct if(timestampdiff(day, t1.mindate, t2.date)=1, t2.user_id, null))
        /count(distinct t1.user_id),3) as p
from
    (select user_id
            ,min(date) as mindate
    from login
    group by user_id
     ) as t1
inner join login as t2
on t1.user_id = t2.user_id
发表于 2022-02-19 20:04:28 回复(0)
先在每个人的新登录日期加一天把它作为a表  select user_id,date(min(date),+ ’1 day') from login group by user_id 
因为有的人可能只有新登录就再也没登录了,所以要在原表里验证a表的数据真实性,得到留存表

login where (user_id,date)

in (select user_id,date(min(date),+ 1 day') from login group by user_id)


发表于 2021-12-05 00:17:06 回复(0)
SELECT ROUND(COUNT(DISTINCT t1.user_id)/
(SELECT COUNT(DISTINCT l4.user_id)
 FROM login l4 ),3) AS 'p' 
FROM (
SELECT *,DATE_ADD(l.`date`,INTERVAL 1 DAY)AS 'nday'FROM login l)AS t1
JOIN login l2 
ON l2.`date`=t1.nday AND l2.`user_id`=t1.user_id

发表于 2021-08-15 17:41:47 回复(0)
select round(
        
       (
           select count(distinct l1.user_id) as count1 
           from login l1 join login l2 on l1.user_id = l2.user_id
           where datediff(l2.date, l1.date) = 1
       )
       /
       (select count(distinct user_id) as count1 from login)

       , 3)

编辑于 2021-04-15 17:41:23 回复(0)
要统计牛客新登录用户的次日成功的留存率,首先把公式列出来:
(第一天登录的新用户并且第二天也登录的用户)/(总用户)即为新登录用户的次日成功的留存率
总用户其实挺好算,如下:
select count(distinct user_id) from login
找到每个用户第一天登陆的日子,其实挺好找,和前面找最近登录的日子差不多,一个是max,一个是min:
select user_id,min(date) from login group by user_id
比如上面查找语句是1,2020-10-12;那么如果找到一个结果为1,2020-10-13的那么是不是就符合结果了,于是可以如下写:
select user_id,date(min(date),'+1 day') from login group by user_id
这样就可以找到所有的在第一天登录的新用户并且第二天也登录的用户,以及第二天的日期
所以从这个里面找到所有的count(distinct user_id)除以总用户就可以得到结果了,于是整个sql语句如下:
select 
round(count(distinct user_id)*1.0/(select count(distinct user_id) from login) ,3)
from login
where (user_id,date)
in (select user_id,date(min(date),'+1 day') from login group by user_id);

 
mysql的解法为:
select 
round(count(distinct user_id)*1.0/(select count(distinct user_id) from login) ,3)
from login
where (user_id,date)
in (select user_id,DATE_ADD(min(date),INTERVAL 1 DAY) from login group by user_id);



编辑于 2020-11-05 16:00:12 回复(39)
1、时间函数:subdate(date,interval 1 day)
2、满足条件的用户记录3条,用户2个——用户去重
SELECT   COUNT(DISTINCT a.user_id) AS user_cr
FROM login a,login b 
WHERE a.user_id = b.user_id 
    AND a.date = SUBDATE(b.date, INTERVAL 1 DAY) 

3、全部代码
SELECT   ROUND((SELECT   COUNT(DISTINCT a.user_id) AS user_cr
FROM login a,login b 
WHERE a.user_id = b.user_id 
AND a.date = SUBDATE(b.date, INTERVAL 1 DAY) )/COUNT(DISTINCT c.user_id) ,3) AS p
FROM login c


发表于 2021-10-09 22:37:27 回复(0)
select round(count(distinct t1.id) / (select count(distinct user_id) from login ),3)
from
(select user_id id,
date,
lead(date,1)over(partition by user_id order by date) date2
from login) t1
where  datediff(date2,date)=1;
使用窗口函数lead
发表于 2021-08-23 15:41:39 回复(0)
/*-- 第一步:计算总用户数
select count(distinct user_id) from login;
-- 第二步:找到每个用户第一天登录的日子
select user_id,min(date) from login group by user_id;
-- 第三步:使用date()函数寻找第二天也登录的用户
select user_id, date(min(date),'+1 day') from login group by user_id;
*/
-- 写出完整SQL语句
select round(count(distinct user_id)*1.0/(select count(distinct user_id) from login),3) as p
from login
where (user_id, date) in (select user_id, date(min(date),'+1 day') from login group by user_id);
发表于 2020-09-09 15:35:49 回复(1)
思路:1.首先建立窗口函数,算出每一行对应的首次登陆时间
select *,min(date) over(partition by user_id) firstday
from login;
2.把该表设为a表,查找a表中登陆时间和首次登陆时间之差为1的数据,统计用户id出现的次数
select round(count(distinct a.user_id)
 from
  (select *,min(date) over(partition by user_id) firstday
   from login) a
where datediff(date,firstday)=1;
3.算得第二日留存的用户数,用这个数除以总用户数,并保留三位小数即可

最终代码:
select round(count(distinct a.user_id)/
(select count(distinct user_id)
 from login),3)
 from
  (select *,min(date) over(partition by user_id) firstday
   from login) a
where datediff(date,firstday)=1;



编辑于 2021-03-12 15:00:18 回复(3)
  • 留存率 = 第一天且下一天也登陆的用户数 / 总用户数
  • 总用户数
    SELECT COUNT(DISTINCT user_id) FROM login
  • 第一天且下一天也登陆的用户数
    SELECT COUNT(DISTINCT user_id)
    FROM login
    WHERE (user_id, date) in (
      select user_id, DATE_ADD(min(date), INTERVAL 1 DAY)
      from login
      GROUP by user_id
    )
  • 汇总两部分代码,计算留存率
    SELECT ROUND(COUNT(DISTINCT user_id) * 1.0 / (SELECT COUNT(DISTINCT user_id) FROM login), 3) as p
    FROM login
    WHERE (user_id, date) in (
      select user_id, DATE_ADD(min(date), INTERVAL 1 DAY)
      from login
      GROUP by user_id
    )
发表于 2021-08-10 14:26:36 回复(4)
1. 先做出一个子表能达到每一个id对应的第一次登陆日期和次日登陆日期(若没有则为null)
SELECT l1.user_id, MIN(l1.date)as firstdate, l2.date as nextdate
    FROM login l1 LEFT JOIN login l2 ON l1.user_id = l2.user_id AND l2.date = l1.date + 1
    GROUP BY l1.user_Id
结果如下:
u_id first_date next_date
2 2020-10-12 2020-10-13
1 2020-10-12
2020-10-13
3 2020-10-12
Null
4 2020-10-13
Null
2. 然后SELECT中直接COUNT(nextdate)/COUNT(firstdate) 就能得到概率了
SELECT ROUND(COUNT(nextdate)/COUNT(firstdate),3)
FROM (SELECT l1.user_id, MIN(l1.date)as firstdate, l2.date as nextdate
    FROM login l1 LEFT JOIN login l2 ON l1.user_id = l2.user_id AND l2.date = l1.date + 1
    GROUP BY l1.user_Id) as T


发表于 2021-09-01 02:45:18 回复(4)
select round(count(b.id)*1.0 / count(a.user_id),3) p
from (select user_id,min(date) logday from login group by user_id) a
left join login b  
on a.user_id=b.user_id and b.date=date(a.logday, '+1 day')

发表于 2020-10-21 20:48:27 回复(3)
select round(count(distinct l1.user_id)*1.0/(select count(distinct user_id)  from login),3)
from login l1, login l2
where l1.user_id = l2.user_id and l2.date = date(l1.date,'+1 day')
and l1.date = (select min(date) from login where user_id=l1.user_id)
内连接第二条件保证l1表为新用户,第一条件保证新用户第二天登陆。
查询总用户数如下:
select count(distinct user_id)  from login
因此,计算新用户留存率如下:
select round(count(distinct l1.user_id)*1.0/(select count(distinct user_id)  from login),3)



发表于 2020-08-31 00:02:44 回复(4)
要统计次日的留存率,其实就是计算总用户与第二天上线的用户,p=第二天上线用户/总用户。
首先得到每个用户的第一次登录日期first_date,然后计算date-first_date=1的用户数,就是留存的用户。
SELECT ROUND(SUM(CASE WHEN date-first_date=1 THEN 1 ELSE 0 END)
             / COUNT(DISTINCT user_id), 3) p FROM 
(SELECT user_id, date,
MIN(date) OVER(PARTITION BY user_id) first_date FROM login ) t


发表于 2021-11-18 14:54:28 回复(0)

解法

-- 总用户数
-- 个人觉得 这里不应该求总用户数量,而是前一天的登陆用户,第二天登陆的比例才是留存
with t1 as (
    select count(distinct user_id) as count from login
-- 
),
-- 次日登陆,这里并不是从数据库中筛选第二天登陆的数据
-- 只是为了找到该 user_id 在 min(date) 加一天的日期
-- min(date) 
t2 as (
    select user_id, 
    DATE_ADD(min(date),INTERVAL 1 DAY) date 
    from login group by user_id
)
select round(
        count(distinct user_id) * 1.0 / (select count from t1),
        3
    )
    from login
    -- min(date) 加一天登陆了,说明次日留存了
    where (user_id, date) in (select user_id, date from t2)


编辑于 2021-05-27 15:39:54 回复(1)

select
round(COUNT(distinct case when datediff(b.date, a.date) = 1 then a.user_id end) / count(distinct a.user_id), 3)
from login a join login b on a.user_id = b.user_id

发表于 2021-09-02 15:15:19 回复(0)
SELECT
    round(
        sum(if(adddate(m_date,1)=s_date,1,0))
        /
        (SELECT count(DISTINCT user_id) FROM login)
    ,3) p
FROM(
    SELECT
      min(date) over(PARTITION BY user_id) m_date,
      lead(date) over(PARTITION BY user_id ORDER BY date) s_date
    FROM
      login
    )t
编辑于 2021-06-27 19:58:33 回复(2)
auu头像 auu
只需要使用LEFT JOIN 就好,关键要用ON 保留所有客户的数据
select 
round(count(distinct b.user_id)/count(distinct a.user_id),3) as p
from login a 
left join login b
on a.user_id=b.user_id
and datediff(b.date,a.date)=1

发表于 2022-11-09 09:23:51 回复(0)
此解法可以计算任意天数的留存率,不仅仅是第二天的留存率!!!
如果像讨论中大佬们在where的时候限制表格,那么只能一个查询解决一个留存率的问题,但是在面试中往往会遇到让你算1、3、7、30天的留存率,这种情况最好是把条件放大select中用if条件去计算,可以一次性计算多个留存率,只需要改datediff()=xxxx就行了。
另外用窗口函数开窗就可以少用一次表连接,实际根据数据量来决定用哪一个吧!
SELECT round(count(if(datediff(date, new_date) = 1,1,null)) / (SELECT count(distinct user_id) FROM login),3)
FROM login l join  
(SELECT user_id
,min(date) new_date
FROM login
GROUP BY user_id) A
on l.user_id = A.user_id
;



发表于 2022-06-29 10:08:27 回复(1)