首页 > 试题广场 >

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

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

热度指数：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

算法知识视频讲解

Mysql

説

SELECT
    l.date,
    CASE
        WHEN SUM(CASE WHEN l.date < l1.date THEN 1 ELSE 0 END) = 0 THEN 0.000
        ELSE ROUND(
            SUM(CASE WHEN l1.date = DATE_ADD(l.date, INTERVAL 1 DAY) THEN 1 ELSE 0 END) /
            SUM(CASE WHEN l.date < l1.date THEN 1 ELSE 0 END), 3
        )
    END AS p
FROM
    login l
LEFT JOIN (
    SELECT
        user_id,
        date
    FROM (
        SELECT
            user_id,
            date,
            ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY date ASC) AS rk
        FROM login
    ) s
    WHERE rk = 2
) l1 ON l.user_id = l1.user_id
GROUP BY l.date;

发表于 2026-01-13 01:20:42 回复(0)

Mysql

牛客130287280号

with firstLogin as(
    select
        user_id,
        min(date) as startDay
    from login 
    group by user_id 
),
loginInfromation as (
    select
        login.user_id,
        date,
        startDay
    from login left join firstLogin on login.user_id = firstLogin.user_id
    where date != startDay
    order by date
),
 regDateNums as(
    select
        startDay,
        count(*) as yeap2
    from firstLogin group by startDay
 ),
 dateList as (
    select distinct 
        date
    from login 
 )
select
    date,
    coalesce (round(yeap*1.0/yeap2,3),0.000) as p
from(select
    dateList.date,
    coalesce(yeap2,0) as yeap2,
    coalesce(yeap,0) as yeap
from dateList left join (select
    regDateNums.startDay,
    yeap2,
    yeap
from regDateNums left join (
 select
    startDay,
    sum(case
    when startDay + interval 1 day = date then 1 else 0 end) as yeap
from loginInfromation group by startDay) as temp on  regDateNums.startDay = temp.startDay) as temp3
on temp3.startDay = dateList.date
) as temp4

发表于 2025-12-26 14:46:35 回复(0)

Mysql

总有一天会学成的

select cal.date, 
ifnull(round(count(distinct second_date.user_id)/count(distinct first_date.user_id),3),0) p
from
(select distinct date from login) cal
left join (
    select user_id ,min(date) date from login
    group by user_id
) first_date
on cal.date = first_date.date
left join(
    select user_id,date from login
) second_date
on first_date.user_id = second_date.user_id
and second_date.date = date_add(first_date.date,interval 1 day)
group by cal.date
order by cal.date

发表于 2025-12-21 21:07:58 回复(0)

Mysql

矫健的小刺猬在看牛客

with new as
(
    select
        user_id
        , min(date) as dt
    from login
    group by user_id
)
select
    p1.date
    , ifnull(p, 0) as p
from
    (
        select
            distinct date
        from login
    ) p1
left join
    (
        select
            dt as date
            , ifnull(round(count(t2.user_id) / count(t1.user_id), 3), 0) as p
        from new t1 left join login t2 
            on t1.user_id = t2.user_id and datediff(date, dt) = 1
        group by dt
        order by date
    ) p2
on p1.date = p2.date

发表于 2025-12-11 15:25:22 回复(0)

Mysql

牛客475559997号

WITH t AS

(

SELECT

date

,user_id AS id

,DENSE_RANK() OVER(PARTITION BY user_id ORDER BY date ASC) AS date_rank

FROM login

)

SELECT

t1.date

,ROUND(IFNULL((SUM(IF(t1.date_rank=1,IF(DATEDIFF(t2.date,t1.date)=1,1,0),0))/SUM(IF(t1.date_rank=1,1,0))),0),3) AS p

FROM t AS t1

LEFT JOIN t AS t2 ON t1.id=t2.id AND t2.date_rank=2

GROUP BY

t1.date

ORDER BY

t1.date ASC

发表于 2025-11-25 16:16:24 回复(0)

Mysql

牛客656551536号

with all_date as(
    select distinct date
    from login
),
newid as (
    select user_id, min(date) newdate
    from login
    group by user_id
),
twoid as (select l.user_id, l.date twodate
    from newid n
    left join login l on n.user_id = l.user_id
    and l.date = n.newdate +1
),
daily_stats as(
    select n.newdate, count(t.user_id) a, count(n.user_id) b
    from newid n
    left join twoid t on n.user_id = t.user_id
    group by n.newdate
)
select al.date, round(coalesce(da.a*1.0 / da.b, 0), 3) p
    from all_date al
    left join daily_stats da on al.date = da.newdate
    order by al.date

可读性维护性更强

发表于 2025-09-20 04:41:47 回复(0)

Mysql

xczay

with A as(
    select user_id,min(date) mdt
    from login
    group by user_id
)
select date,if(p is null,0.000,p) p
from (
    select t1.date date,round(d2/d1,3) p
    from (
        select date,sum(if(date=A.mdt,1,0)) d1
        from login l
        join A
        on l.user_id=A.user_id
        group by date
    ) t1
    left join(
        select date,sum(if(date=A.mdt+1,1,0)) d2
        from login l
        join A
        on l.user_id=A.user_id
        group by date
    ) t2
    on t1.date=t2.date-1
) t
order by date;

发表于 2025-08-27 09:15:57 回复(0)

Mysql

来碗擦圪抖

with t1 as (select user_id,
                   date,
                   row_number() over (partition by user_id order by date) as new_flag
            from login),
     t2 as (select user_id,
                   date_sub(date, interval 1 day) as new_date,
                   new_flag - 1                   as new_new_flag
            from t1),
     t3 as (select new_date, sum(new_new_flag) as flag
            from t2
            group by new_date),
     t4 as (select date, sum(new_flag) as flag
            from (select user_id,
                         date,
                         row_number() over (partition by user_id order by date) as new_flag
                  from login) t
            where new_flag = 1
            group by date),
     t5 as (select t4.date, t3.flag / t4.flag as P
            from t4
                     left join t3 on t4.date = t3.new_date),
     t6 as (select date from login group by date order by date)
select t6.date,
       round(ifnull(P, 0), 3) as P
from t6
         left join t5 on t6.date = t5.date;

这段代码本地能运行处正确结果，但是发布上来就报错

发表于 2025-08-05 10:40:59 回复(0)

Mysql

大方的灰太狼在研究求职打法

select c.date,ifnull(p,0.000) p
from
(
select distinct date
from login
) c
left join 
(select date,round(avg(case when ifnull(datediff(date1,date),0)=1 then 1 else 0 end),3) p
from
(
    select user_id,date,lead(date)over(partition by user_id order by date) date1,   row_number()over(partition by user_id order by date) rn from login
) a
where rn=1
group by date
) b
on c.date=b.date

发表于 2025-05-28 17:12:16 回复(0)

Mysql

胖咪是个宝

select date,
ifnull(
round(sum(case when datediff(date_2, date) = 1 and date = u_m_date then 1 else 0 end) /
count(distinct(case when date = u_m_date then user_id end)) ,
3), 0) p
from (
select l1.user_id, l1.date, min(l1.date) over(partition by l1.user_id) u_m_date, l2.date date_2
from login l1
join login l2 on l1.user_id = l2.user_id
) tb1
group by date
order by date;

发表于 2025-05-17 18:56:27 回复(2)

Mysql

牛客164735395号

with a as (select min(date) date,user_id from login group by user_id),
b as(select date , user_id , count(*)over(partition by date) cnt from a),
c as(select distinct l.date,b.user_id,b.cnt from b right join login l on b.date = l.date)
select c.date ,
round(ifnull(count(l.user_id)/c.cnt,0),3) p 
from login l 
join login m on l.user_id = m.user_id and m.date-l.date = 1 
right join c on c.user_id = l.user_id and c.date=l.date
group by c.date,c.cnt

a:以user_id进行分组取最小日期,即该user_id的创建日期

b:以创建日期分组计数,即同一天创建的账号总数(留存率的分母)

c:引入原表日期以替代创建日期中的null值,方便后续计算

主:主表自连加上c表3连,其中c表右连接,即可取出第二天也登陆过的新账号user_id,并且其他行的值为null,便于count作为分子

P.S.:CTE串怎么了,我爱CTE串

发表于 2025-05-05 20:04:11 回复(0)

Mysql

在迎接offer的斜杠青年很粗心

select l8.date ,ifnull(round(math / one , 3),0) as p from

(select l6.date,l6.one,ifnull(l7.tow,0) as math from

(select l5.date,ifnull(l4.tr,0) as one from

(select date from login group by date) as l5

left join

(select u,count(u) as tr from

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

on l5.date = l4.u) as l6

left join

(select l1.date - 1 as date,ifnull(l2.cr,0) as tow from

(select date from login group by date) as l1

left join

(select one_day ,count(user_id) as cr from

(select a.user_id,a.one_day from

(select user_id ,date_add(min(date),interval 1 day) as one_day from login group by user_id) as a

inner join

on a.user_id = b.user_id

and a.one_day = b.date) as ce

group by one_day) as l2

on l1.date = l2.one_day) as l7

on l6.date = l7.date) as l8 ;

发表于 2025-04-11 17:17:00 回复(0)

Mysql

kukuju

-- 俺只能做出上半部分，下半部分参考坛友union有登录没注册的概率

with t as(

select login.date, reg_date, datediff(date,reg_date) as diff
from login
left join
(select user_id,min(date) as reg_date
from login
group by user_id
)t1
on login.user_id=t1.user_id
)
select reg_date as 'date',
round(sum(if(diff=1,1,0))/count(if(diff=0,1,null)),3) as p
from t
group by reg_date
union
select login.date, 0.000 as p
from login
where login.date not in (
select min(date)
from login
group by user_id)
order by date

发表于 2025-04-09 16:58:47 回复(0)

Mysql

牛客479220516号

select

t3.date,

round(ifnull (t2.p, 0), 3)

from

(

select distinct

l3.date

from

) t3

left join (

select

t1.first_login,

count(l2.date) / count(t1.first_login) p

from

(

select

l1.user_id,

min(date) first_login

from

group by

l1.user_id

) t1

left join login l2 on t1.user_id = l2.user_id

and date_add (t1.first_login, interval 1 day) = l2.date

group by

t1.first_login

) t2 on t3.date = t2.first_login

发表于 2025-04-07 09:50:11 回复(0)

Mysql

P1nk_

with a as (
    select user_id,date,min(date) over(partition by user_id) fd,
    lead(date,1,0) over(partition by user_id order by date) ld
    from login
)
select distinct date,
ifnull(round(count(case when date=fd and datediff(ld,date)=1 then user_id end)/(count(case when date=fd then user_id end)),3),0)
from a
group by date;

发表于 2025-03-30 20:25:53 回复(0)

Mysql

牛客577520109号

SELECT t4.date,

(case when ROUND((t3.user_counter/ t4.new),3) is null

then 0.000

else ROUND((t3.user_counter/ t4.new),3)

end)

as p

FROM

(SELECT t2.date, COUNT(user_id) as user_counter

FROM (SELECT distinct login.date from login) as t2 left join (SELECT distinct user_id,

DATE_SUB(c.date, INTERVAL 1 DAY) AS yeday

from (

select *

FROM login l3

WHERE (l3.user_id, l3.date) IN (

SELECT

l2.user_id,

DATE_ADD(l2.date, INTERVAL 1 DAY) AS next_day

FROM login l2

WHERE (l2.user_id, l2.date) IN (

SELECT

user_id,

MIN(l.date) AS first_login

FROM login l

GROUP BY user_id

)

) as c

) as t1 on t1.yeday = t2.date

group by t2.date) as t3,

(SELECT a.date, COUNT(b.user_id) as new

FROM (select distinct login.date from login ) a left join (SELECT l5.user_id, min(l5.date) as m

FROM login l5

group by l5.user_id) as b on a.date = b.m

group by a.date

) as t4

where t4.date = t3.date

这个是我的完成的代码为什么这里要加入 distinct才能过
FROM (SELECT distinct login.date from login) as t2 left join (SELECT distinct user_id,

发表于 2025-03-28 15:01:48 回复(0)

Mysql

EMP01

select t.date, round(case when ratio is null then 0 else ratio end,3) from (select distinct date from login) t

left outer join

(select r.date date, c/b ratio from(select date, case when a is null then 0 else a end c from(select date, count(user_id) a from(select date, user_id from login x

where not exists

(select * from login y

where x.date>y.date

and x.user_id=y.user_id)) z

where exists

(select * from login

where login.date=z.date+1 and login.user_id=z.user_id)

group by date) p) r

join (select date, count(user_id) b from login x

where not exists

(select * from login y

where x.date>y.date

and x.user_id=y.user_id)

group by date) as q on r.date=q.date) s on t.date=s.date

发表于 2025-03-26 10:58:33 回复(0)

提交观点

问题信息

SQL

难度：

294条回答 4467收藏 7679浏览

通过挑战的用户

查看代码

牛客81723...

2023-03-08 23:37:46
牛客51600...

2023-03-06 13:44:41
牛客15063...

2023-02-02 00:40:01
牛客51982...

2022-10-20 14:08:44
牛儿在飞

2022-10-13 21:43:42

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

输入

输出

问题信息

热门推荐

通过挑战的用户

相关试题