首页 > 试题广场 >

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

[编程题]牛客每个人最近的登录日期(五)
  • 热度指数：177980 时间限制：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
算法知识视频讲解
添加回答
Sqlite
牛客758552793号头像 牛客758552793号
单纯想分享一下自己想了很久的代码，久到写完甚至忘记了思路，强行撸出来的垃圾，但是吧。。能跑就行了 
select date, ifnull(sum(case when d2 is null then 0 else 1 end)*1.0/ 
sum(case when d3 is null then 0 else 1 end),0.000) as p from
(
select a.user_id, date, lead(d2) over() as d2,d3 from 
(select * from
(select l.*,row_number() over(partition by user_id order by date) as rk from login l)
where rk < 3) a
left join
(select user_id, date(date,'+1 day') as d2,date as d1 from
(select a.*, row_number() over(partition by user_id order by date) as s from login a)
where s = 1) b
on a.user_id = b.user_id and a.date = b.d2
left join
(select user_id, date as d3 from
(select a.*, row_number() over(partition by user_id order by date) as s from login a)
where s = 1) c
on c.user_id = a.user_id and c.d3 = a.date
)
group by date

发表于 2022-04-20 02:00:41 回复(0)
Sqlite
呆毛儿本呆头像 呆毛儿本呆 
select a.date,
case when 
round(count(distinct c.user_id)*1.0/count(b.user_id)*1.0,3) 
then 
round(count(distinct c.user_id)*1.0/count(b.user_id)*1.0,3)
else 0.000 end as p
from
(select distinct date from login) a
left join
(select user_id,min(date)as date
from login
group by user_id)b
on a.date=b.date
left join login c
on b.user_id=c.user_id
and c.date=date(b.date,'+1 day')
group by a.date
照高赞改的sqlite版
发表于 2022-03-22 20:22:05 回复(0)
Sqlite
牛客618466018号头像 牛客618466018号 
select A.date,ifnull(round(B.lc/A.new,3),0)
from
(select a.date,sum(case when b.rk = 1 then 1 else 0 end) new
from (select distinct date from login) a
left join
(select date,user_id,
rank() over (partition by user_id order by date) rk
from login) b
on a.date = b.date
group by a.date) A
left join
(select date_sub(a.date,interval 1 day) date,sum(case when b.rk = 1 then 1 else 0 end) lc
from (select distinct date from login) a
left join
(select date,user_id,
rank() over (partition by user_id order by date) rk
from login
where (date,user_id) in (select date_add(date,interval 1 day),user_id from login)) b
on a.date = b.date
group by a.date) B
on A.date = B.date;

发表于 2022-01-07 19:18:13 回复(0)
Sqlite
骨碌圆头像 骨碌圆
不懂为什么在线无法运行，但是线下就运行成功了。在此记录思路
step1：筛选出所有用户的登录日期，记做表d 
select distinct date from login
step2：先筛选出各用户，首次登录日期。记做表fd。再将表d左连接表fd:（由此可进一步得到登录日的新用户数，这也是新用户次日留存率的分母） 
select * FROM
(select distinct date from login)d
left join 
(select user_id,min(date) min_date from login
GROUP BY user_id) fd
on d.date=fd.min_date
;
step3：将步骤2所得表左连接表login，且筛选出第二日仍登录的新用户数。由此对登录日进行聚合（注意使用ifnull()函数） 
select d.date,ifnull(round(count(l.user_id)/count(fd.user_id),3),0) p FROM
(select distinct date from login)d
left join 
(select user_id,min(date) min_date from login
GROUP BY user_id) fd
on d.date=fd.min_date
left join login l
on fd.user_id=l.user_id and DATE_ADD(fd.min_date,INTERVAL 1 day)=l.date
group by d.date
order by d.date;





发表于 2021-10-20 16:05:38 回复(0)
Sqlite
牛客768480990号头像 牛客768480990号 
select date,(case count(d1) when 0 then 0.000 else round(count(d2)*1.0/count(d1),3) end )as p
from
(select login.user_id,date,a.d1
from login
left join(select user_id,min(date) as d1
from login
group by user_id) a
on login.date=a.d1
and login.user_id=a.user_id) m
left join
 (select user_id,d2
from (select user_id,date(date,'-1 day') as d2,ROW_NUMBER() OVER(partition by user_id order by date) as t
from login)
where t=2)  b
on m.d1=b.d2
and m.user_id=b.user_id
group by date
order by date

发表于 2021-10-19 17:27:52 回复(0)
Sqlite
平安喜乐_Ada头像 平安喜乐_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)
Sqlite
没有鱼丸没有粗面头像 没有鱼丸没有粗面 
select t.date
    ,case when count(t.user_id) != 0 then sum(case when c.user_id is not null then 1 else 0 end)*1.000/count(t.user_id) 
    else 0.000 end as p
from(
    select 
        a.date
        ,b.user_id
    from login a 
    left join(
        select user_id,min(date) as date from login group by user_id
        ) b 
    on a.user_id = b.user_id
         and a.date = b.date
    )t
left join login c
on t.user_id = c.user_id
and date(t.date,'+1 day') = c.date
group by t.date;

发表于 2021-10-09 18:37:04 回复(0)
Sqlite
牛客693697574号头像 牛客693697574号

select t1.date, t2.date,count(t2.date)/count(t1.date)
from login t1
left join login t2 on t1.user_id = t2.user_id and t1.date=t2.date-1
group by t1.date;
发表于 2021-09-27 15:22:17 回复(0)
提交观点

问题信息

SQL
难度：
9条回答 7680浏览

热门推荐

通过挑战的用户

查看代码

相关试题

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

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

扫描二维码，关注牛客公众号