首页 > 试题广场 >

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

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

热度指数：128358 时间限制：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位之后的四舍五入)，上面的例子查询结果如下:

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

算法知识视频讲解

Mysql

朽木无阴

select round(count(l1.user_id)/count(l.user_id),3) p

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

left join (select distinct user_id,date from login) l1

on l.user_id=l1.user_id and l1.date=date_add(l.date,interval 1 day)

发表于 2024-04-28 00:08:46 回复(0)

Mysql

FMW

（借鉴大牛的）

整合思路：

1、次日成功登录，意味着同一个user_id在min(date)次日也登录了；

2、需要满足条件。即user_id, date存在于检索后的表格中；

3、检索表格即查询user_id, DATE_ADD(min(date), INTERVAL 1 DAY)；

4、满足条件后计算的user_id的数量(count(user_id))即为次日也登录成功的用户；

5、满足条件的用户数/总用户数即得出比率；

6、最后用ROUND函数精确到三位小数。

具体代码如下：

SELECT 
    ROUND(count(user_id)/(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
);

发表于 2024-04-11 12:34:31 回复(0)

Mysql

要双休的三文鱼很独特

select
round(count(lo.user_id)/count(mi.user_id),3) as p
from 
    ( select 
            user_id
            ,min(date) as md 
            from login 
            group by 1
    ) mi 
left join login lo 
on mi.user_id=lo.user_id and date=date_add(md,interval 1 day)

编辑于 2024-04-08 16:12:15 回复(0)

Mysql

注意脚下

select round(count(distinct(a.user_id))/(select count(distinct(user_id)) from login),3)

from (

SELECT user_id,date,DATE_ADD(date,INTERVAL 1 DAY) hh

,lead(date,1)over(partition by user_id order by date) dd

from login

) a

where a.hh=a.dd

编辑于 2024-03-09 20:10:43 回复(0)

Mysql

安之ccy

select
    round(count(b.date) / count(*), 3) 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 DATEDIFF (b.date, a.date) = 1

发表于 2023-12-27 17:49:03 回复(0)

Mysql

摆烂了的伊泽瑞尔很有胆量

with c as (
        select
            count(distinct user_id) cnt
        from
            login
        ),
    b as (
        select
            count(distinct user_id) cnt1
        from
            (
                select
                    user_id,
                    date,
                    lag (date, 1, 0) over (
                        partition by
                            user_id
                    ) l
                from
                    login
            ) d
        where
            datediff (date, l) = 1
    )
select round(cnt1/cnt,3) from c, b

既然不好处理，就先都查出来再计算

发表于 2023-12-27 13:40:07 回复(0)

Mysql

FaFasama

窗口函数解题：

SELECT ROUND(SUM(IF(DATE_ADD(min_date, INTERVAL 1 DAY) = date, 1, 0))/

COUNT(DISTINCT user_id), 3) AS p

FROM

(SELECT *,

MIN(date)OVER(partition by user_id) AS min_date

FROM login) AS l

编辑于 2023-12-19 21:20:07 回复(0)

Mysql

TATA_x

用窗口函数写的太繁琐了，思路不好，记录一下。

select round((select count(*) from (select count(1) from (select *,rank()over(partition by user_id order by date ) rk from login )xb

where rk=1 or rk=2

group by user_id

having max(date)-min(date)=1)xb2)/count(distinct user_id),3)p from login;

发表于 2023-11-20 17:19:06 回复(0)

Mysql

卡咯嘉来咯

select
  round(
    (
      select count(1) from login inner join (
              select
                user_id,
                date_add(min(date), INTERVAL 1 DAY) date
              from login group by user_id
        ) t2 on t2.user_id = login.user_id and t2.date = login.date
    ) / count(distinct user_id),
    3
  ) p
from
  login;

思路：

1. 公式：第一天登录后第二天也登录的人数 / 总人数

2.求总人数 count(distinct user_id)

3. 第一天登录后第二天的日期

select
    user_id,
    date_add(min(date), INTERVAL 1 DAY) date
from login group by user_id

3.loginz中符合上述条件的人数

select count(1) from login 
    inner join (
              select
                user_id,
                date_add(min(date), INTERVAL 1 DAY) date
              from login group by user_id
    ) t2 on t2.user_id = login.user_id and t2.date = login.date

发表于 2023-10-25 11:50:09 回复(0)

Mysql

后街小行家

#方法1     方法选的不太对，用这个开窗函数做辅助列后面筛选条件处理起来麻烦的很
with cte as (
select user_id,date,row_number()over(partition by user_id order by date) as rn
from login
group by user_id,date  )
select round(count(b.rn)/count(*),3) as p
from cte a left join cte b on a.user_id=b.user_id and datediff(b.date,a.date)=1 
and b.rn-a.rn=1 
where a.rn=1

#方法2  正确的做辅助列的方法，不过这种方法唯一的缺点就是使用了两个distinct，占用资源大，不适合大数据
with cte as (
select user_id,date,min(date)over(partition by user_id) as min_date
from login
group by user_id,date  )
select round(count(distinct b.user_id)/count(distinct a.user_id),3)   as p
from cte a left join cte b on a.user_id=b.user_id and datediff(b.date,a.min_date)=1

发表于 2023-10-16 09:36:12 回复(0)

Mysql

薛定谔式偶遇

select
    round(sum(tt.non) / count(*), 3) as p
from
    (select
            lo.user_id,
            if (( select
                    lg.user_id
                from
                    login as lg
                where
                    lg.user_id = lo.user_id
                    and lg.date = DATE_ADD(min(lo.date), INTERVAL 1 DAY)
            ), 1, 0) as non
        from
            login as lo
        group by
            lo.user_id
    ) as tt;

发表于 2023-10-08 17:07:27 回复(0)

Mysql

牛客771002145号

select round(sum(sub)/count(distinct user_id),3) as p

from

(select user_id,if((date-min(date)over(partition by user_id order by date))=1,1,0) as sub

from login) as t2

发表于 2023-09-25 12:15:07 回复(0)

Mysql

dolen

# 请你写出一个sql语句查询新登录用户次日成功的留存率，
# 即第1天登陆之后，第2天再次登陆的概率,保存小数点后面3位(3位之后的四舍五入)

# 注意代码，平时还要多联系，否则总容易写错。
with tab_new_user as (
select 
    user_id,
    date,
    min(date)over(partition by user_id) as min_user_dt
    from login
)

select 
round(count(distinct if(datediff(date,min_user_dt) =1,user_id,null))/count(distinct if(datediff(date,min_user_dt) =0,user_id,null)),3) as rate
from tab_new_user t1
where datediff(date,min_user_dt) in (0,1)
order by 1
;

发表于 2023-09-19 11:08:47 回复(0)

Mysql

牛客403181168号

MySQL：

select round(sum(

case

when l2.user_id = l1.user_id and l1.date = date_add(l2.m_date, interval 1 day)

then 1 else 0

end)

/count(distinct l1.user_id),3) as p

from login as l1,

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

发表于 2023-09-06 16:44:29 回复(0)

Mysql

Ye_d

SELECT ROUND(COUNT(l.date) / COUNT(tl.date), 3) AS p
FROM login l
RIGHT JOIN (  # 先找出所有新用户第一天登录的记录
    SELECT user_id, MIN(date) AS date, DATE_ADD(MIN(date), INTERVAL 1 DAY) AS next_day
    FROM login
    GROUP BY user_id
) AS tl ON l.user_id = tl.user_id AND l.date = tl.next_day;
# 用表t1的用户id和新用户第一天登录时间的后一天（第二天登录时间，next_day
# 与原始表的用户id和登陆时间date进行右连接
# 如果原始登录记录中没有新用户第二天登录的记录则l.date为空
# 因此，COUNT(l.date)是新用户第二天没有登录的人数
# COUNT(tl.date)是所有新用户人数，相除即得留存率。

发表于 2023-08-19 17:08:13 回复(0)

Mysql

粗心的小猫想开了

select
    round(count(t2.date) / count(*), 3) p
from
    login t1
    left join login t2 on datediff (t2.date, t1.date) = 1
    and t1.user_id = t2.user_id
where
    (t1.user_id, t1.date) in (
        select
            user_id,
            min(date) min_date
        from
            login
        group by
            user_id
    )

发表于 2023-05-28 11:50:09 回复(0)

Mysql

Osusapi

RIGHT JOIN
加上 COUNT(id) / COUNT(*)
简洁明了

SELECT ROUND(COUNT(l.user_id)/COUNT(*),3) AS P
FROM login l RIGHT JOIN(
    SELECT user_id, MIN(date) AS first_login FROM login GROUP BY user_id
) AS t
ON l.user_id=t.user_id AND l.date+0=t.first_login+1;

发表于 2023-04-09 21:39:00 回复(0)

Mysql

爱吃烤肠的钝角不要葱花

select
    round(
        (
            select
                count(1)
            from
                (
                    select
                        c.user_id,
                        mt
                    from
                        (
                            select
                                user_id,
                                min(date) mt
                            from
                                (
                                    select
                                        id,
                                        user_id,
                                        date,
                                        row_number() over (
                                            partition by
                                                user_id
                                            order by
                                                date
                                        ) rn
                                    from
                                        login
                                ) b
                            where
                                rn > 1
                            group by
                                user_id
                        ) c
                        join (
                            select
                                user_id,
                                min(date) mt1
                            from
                                login
                            group by
                                user_id
                        ) d on c.user_id = d.user_id
                    where
                        DATE_ADD(mt1, INTERVAL 1 DAY) = mt
                ) e
        ) / (
            select
                count(1)
            from
                (
                    select
                        min(date)
                    from
                        login
                    group by
                        user_id
                ) a
        ),
        3
    ) p
from
    dual;

发表于 2023-03-24 02:29:06 回复(0)