首页 > 试题广场 >

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

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

热度指数：133408 时间限制：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语句查询每个日期登录新用户个数，并且查询结果按照日期升序排序，上面的例子查询结果如下:

date	new
2020-10-12	3
2020-10-13	0
2020-10-14	1
2020-10-15	0

查询结果表明:
2020-10-12，有3个新用户(user_id为2，3，1)登录
2020-10-13，没有新用户登录
2020-10-14，有1个新用户(user_id为4)登录
2020-10-15，没有新用户登录

示例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|3
2020-10-13|0
2020-10-14|1
2020-10-15|0

算法知识视频讲解

Mysql

SiKiYue

select date, new
from
(select date, count(*) as 'new'
from login
where (user_id,date) in
(select user_id, min(date)
from login
group by user_id)
group by date
union all
select date, 0 as 'new'
from login
where date not in
(select distinct min(date)
from login
group by user_id)
group by date) as t
order by date

发表于 2021-10-10 22:52:40 回复(0)

别看了，我菜逼

思路是窗口函数获取登录的次数，最早就是首次登录，然后分组对rank=1的求和即可。

select a.date,
sum(case when rank=1 then 1 else 0 end) new
from 
(select date, row_number() over(partition by user_id order by date) rank
from login) a
group by date;

发表于 2020-08-30 00:21:20 回复(41)

明月可掇

select date,sum(t) from
(
    select date,
    case when (user_id,date) in 
    (select user_id,min(date) from login group by user_id)
    then 1
    else 0
    end as t
    from login
)
group by date
order by date asc

不得不说，case语句真好用😋😋😋

发表于 2020-09-21 16:17:16 回复(18)

SunburstRun

这个题目做过前面的题的话，应该可以比较容易就知道，

select l1.date,count(distinct l1.user_id)
from login l1
group by l1.date;

这样可以得到每个日期里面，用户登录的数目，比较简单，所以在加一个where判断条件就能从这每个日期里面，用户登录的数目取出哪些是新用户，如下：

select l1.date,count(distinct l1.user_id)
from login l1
where l1.date =
(select min(date) from login where user_id=l1.user_id)
group by l1.date;

当这个日期，正好是这个用户登录的最小日期，而且用户id相同时，那么肯定就是这个日期登录的新用户，执行的用例的话，得到的结果应该如下:

2020-10-12|3
2020-10-14|1

但是这样并不能通过用例，因为这样的话，2020-10-13没有新用户登录，应该输出为0的，这个语句却没有输出。但是login表的日期是完整的，所以我们考虑将login表当主表，上面查出来的表左连接到主表，顺序输出，并使用ifnull语句将null变成0，最后再加上一个order by语句，就可以得到题目想要的结果了:

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 order by login.date

借鉴下他人的mysql使用窗口函数的写法:(mysql8.0里面rank是关键字，不能直接使用)

select a.date,
sum(case when t_rank=1 then 1 else 0 end) new
from 
(select date, row_number() over(partition by user_id order by date) t_rank
from login) a
group by date;

编辑于 2020-11-05 16:13:36 回复(12)

画船听雨眠_zj

在每一个date分组中，有数条记录

这些记录中，只有满足用户的date是首次（min）的，才被记录

select date,sum(case 
when (user_id,date) in (select user_id,min(date) from login group by user_id)
               then 1
               else 0
               end) as new
from login
group by date
order by date

发表于 2021-01-18 23:10:52 回复(4)

Mysql

牛客184739658号

-- 方法一：普通解法
select date
,count(first_date) new
from login l
left join
(
    select
    user_id
    ,min(date) first_date
    from login
    group by user_id
) a
on l.user_id = a.user_id
and l.date = a.first_date
group by date

-- 方法二：窗口函数
select date
,sum(case when r = 1 then 1 else 0 end)
from
(
    select *
    ,rank()over(partition by user_id order by date) r
    from login
) a
group by date
order by date

-- 方法三：机灵法
select date,sum(t) from
(
    select date
    ,case when (user_id,date) in 
    (select user_id,min(date) from login group by user_id)
    then 1
    else 0
    end as t
    from login
) a
group by date
order by date

发表于 2021-07-21 19:55:37 回复(1)

qstalking

#明确问题：登录的当前日期=该用户所有登录日期的最小值
#方法一：计数神器——sum+case方法

#【易错点】group by 后面不需要加distinct

select distinct date
        ,sum(case when (user_id,date) in 
    (select user_id,min(date)from login group by user_id)
    then 1 else 0 end)
from login
group by date
order by date;

发表于 2021-01-26 11:32:18 回复(3)

yeewingho

select a.all_date as date, ifnull(count(b.user_id),0) as new
from
    (select distinct(date) as all_date
    from login) as a
left join
    (select user_id, min(date) as reg_date
    from login
    group by user_id) as b 
on a.all_date = b.reg_date
group by a.all_date

发表于 2020-09-09 21:02:04 回复(1)

Mysql

再坚持

select a.date,

count(case when rn = 1 then 1 else null end) new

from (select *
,row_number()over(partition by user_id order by date) rn
from login
)a
group by a.date

发表于 2022-03-30 11:14:02 回复(0)

Mysql

牛客875451317号

采用count函数进行重复情况的提取

select date,count(
case when (user_id,date) in
(select distinct user_id,min(date) from login group by user_id) then 1 else null end ) 
from login 
group by date 
order by date

发表于 2021-08-23 15:12:28 回复(0)

Mysql

健谈的托尼在人才库

select l.date ,count(a.user_id) as new from login l left join
(select user_id ,min(date) as date from login
group by user_id
)a
on a.user_id = l.user_id
and a.date = l.date
group by l.date

发表于 2021-08-16 10:26:22 回复(0)

牛客812589688号

查询新用户的思路是，当使用user_id和date进行自身的左连接，并要求右表的登录日期小于左表，那么返回的表格中，右表date为空值的即为新用户

sqlite：

SELECT a.date, sum(case when ref is null then 1 else 0 end) from 
(select l1.user_id,l1.date,l2.date as ref from login as l1 left join login as l2 on l1.user_id=l2.user_id and l1.date>l2.date) as a
group by a.date order by a.date;

MySQL中可以使用isnull函数：

SELECT a.date, sum(isnull(a.ref)) from
(select l1.user_id,l1.date,l2.date as ref from login as l1 left join login as l2 on l1.user_id=l2.user_id and l1.date>l2.date) as a
group by a.date order by a.date;

发表于 2020-10-29 22:21:56 回复(0)

斯曦巍峨

select l.date,(case when  new is null then 0 else new end)as new from 
(
	select distinct date from login
) l 
left join 
(
	select date,count(*) as new
	from (
		select user_id as u_d,min(date) as date
		from login
		group by user_id
	)temp
	group by date
)t
on l.date = t.date;

发表于 2020-08-24 17:02:08 回复(0)

Mysql

一个好懒的人

使用窗口函数

加入第一次登录日期的列

建立临时表以供查询

with temp as
(
select
user_id,
date,
min(date) over(partition by user_id) as first_in
from login
)

然后对日期分组

第一次登录日期等于日期去重计数

（去重是防止有用户在首次登录日时有当天多次登录的情况）

select
date,
count(distinct if(date=first_in,user_id,null)) as new
from temp
group by date
order by date

完整代码：

with temp as
(
select
user_id,
date,
min(date) over(partition by user_id) as first_in
from login
)

select
date,
count(distinct if(date=first_in,user_id,null)) as new
from temp
group by date
order by date

发表于 2022-06-24 21:59:57 回复(0)

Mysql

买飞飞也用券

select date,count(user_id) new from login
where (date,user_id) in  (select min(date),user_id from login group by user_id )
group by date
union
select date,0 as new from login
where date not in (select min(date) from login group by user_id )
order by date

第一部分是新用户统计，第二部分是没有新用户的统计

发表于 2022-06-03 16:46:53 回复(0)

Mysql

牛客249669743号

select date,
sum(case when (user_id,date) in (select user_id,min(date) from login group by user_id)
then 1 else 0 end)
from login
group by date
order by date;

发表于 2022-04-15 21:38:26 回复(0)

Mysql

Isolated_Island

最简洁答案

SELECT L1.date, SUM(L2.id IS NULL) AS new  
FROM login AS L1 
LEFT JOIN login AS L2 
ON L1.user_id = L2.user_id 
AND L1.date > L2.date 
GROUP BY L1.date 
ORDER BY L1.date ASC;

发表于 2022-04-13 15:05:13 回复(3)

木木子同学

思路是建了个表

select U.date, count(distinct a.user_id)as new

from login as U
left join (select user_id,min(date) as first_logtime from login group by user_id) as a
on U.date=a.first_logtime

group by U.date

发表于 2021-12-27 15:02:52 回复(0)

Mysql

牛客592465033号

select date,sum(case when r_num=1 then 1 else 0 end) new
from
    (
    select date,
    row_number() over(partition by user_id order by date) as r_num 
    from login
    ) t
group by date
order by date

发表于 2021-11-04 16:16:01 回复(1)

Mysql

牛客168328177号

# select date
# ,count(*) new
# from (
#     select user_id
#     ,min(date) date
#     from login
#     group by user_id) newtable
# group by date
# order by date;
# # 这种方法无法获得新增用户为0的日期
#########################################
select date
,sum(case when (user_id,date) in (select user_id,min(date)
                                 from login
                                 group by user_id)
     then 1 else 0 end)
from login
group by date
order by date asc;

发表于 2021-10-22 14:19:43 回复(0)