首页 > 试题广场 >

查询连续登陆的用户

[编程题]查询连续登陆的用户
  • 热度指数：54820 时间限制：C/C++ 1秒，其他语言2秒 空间限制：C/C++ 256M，其他语言512M
  • 算法知识视频讲解
某产品在2022年2月8日各端口用户注册信息及后几日登录信息如下：

用户注册信息表register_tb(user_id-用户id, reg_time-注册时间, reg_port-注册端口)
user_id reg_time reg_port
1101 2022-02-08 07:23:15 pc
1102 2022-02-08 09:12:22 app
1103 2022-02-08 09:35:45 m
1104 2022-02-08 09:41:01 app
1105 2022-02-08 12:01:01 app
1106 2022-02-08 17:22:13 app
1107 2022-02-08 18:26:21 pc
1108 2022-02-08 19:16:21 pc
1109 2022-02-08 19:56:21 pc

用户登录信息表login_tb(log_id-登录动作id,user_id-用户id, log_time-登录时间,  log_port-登录端口)
log_id user_id log_time log_port
101 1101 2022-02-09 07:24:15 pc
102 1102 2022-02-09 09:12:57 app
103 1003 2022-02-09 09:36:11 m
104 1102 2022-02-10 09:37:01 app
105 1104 2022-02-10 12:01:46 app
106 1106 2022-02-10 10:23:01 app
107 1003 2022-02-10 10:43:01 m
108 1102 2022-02-11 11:56:47 app
109 1104 2022-02-11 14:52:37 app
1010 1106 2022-02-11 16:56:27 app
1011 1003 2022-02-11 17:43:01 m
1012 1106 2022-02-12 10:56:17 app

问题：请查询连续登陆不少于3天的新注册用户？要求：输出user_id并升序排序。
注：登录表为单日随机一次登录数据，该题忽略单日多次登录情况。

示例输出如下：
user_id
1102
1106
解释：1102在9日、10日、11日，登陆了系统满足查询条件；1106在10日、11日、12日登录了系统满足查询条件。
示例1

输入

drop table if exists  `register_tb` ; 
CREATE TABLE `register_tb` (
`user_id` int(11) NOT NULL,
`reg_time` datetime NOT NULL,
`reg_port` varchar(8) NOT NULL,
PRIMARY KEY (`user_id`));
INSERT INTO register_tb VALUES(1101,'2022-02-08 07:23:15','pc');
INSERT INTO register_tb VALUES(1102,'2022-02-08 09:12:22','app');
INSERT INTO register_tb VALUES(1103,'2022-02-08 09:35:45','m');
INSERT INTO register_tb VALUES(1104,'2022-02-08 09:41:01','app');
INSERT INTO register_tb VALUES(1105,'2022-02-08 12:01:01','app');
INSERT INTO register_tb VALUES(1106,'2022-02-08 17:22:13','app');
INSERT INTO register_tb VALUES(1107,'2022-02-08 18:26:21','pc');
INSERT INTO register_tb VALUES(1108,'2022-02-08 19:16:21','pc');
INSERT INTO register_tb VALUES(1109,'2022-02-08 19:56:21','pc');

drop table if exists  `login_tb` ;   
CREATE TABLE `login_tb` (
`log_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`log_time` datetime NOT NULL,
`log_port` varchar(8) NOT NULL,
PRIMARY KEY (`log_id`));
INSERT INTO login_tb VALUES(101,1101,'2022-02-09 07:24:15','pc');
INSERT INTO login_tb VALUES(102,1102,'2022-02-09 09:12:57','app');
INSERT INTO login_tb VALUES(103,1003,'2022-02-09 09:36:11','m');
INSERT INTO login_tb VALUES(104,1102,'2022-02-10 09:37:01','app');
INSERT INTO login_tb VALUES(105,1104,'2022-02-10 12:01:46','app');
INSERT INTO login_tb VALUES(106,1106,'2022-02-10 10:23:01','app');
INSERT INTO login_tb VALUES(107,1003,'2022-02-10 10:43:01','m');
INSERT INTO login_tb VALUES(108,1102,'2022-02-11 11:56:47','app');
INSERT INTO login_tb VALUES(109,1104,'2022-02-11 14:52:37','app');
INSERT INTO login_tb VALUES(1010,1106,'2022-02-11 16:56:27','app');
INSERT INTO login_tb VALUES(1011,1003,'2022-02-11 17:43:01','m');
INSERT INTO login_tb VALUES(1012,1106,'2022-02-12 10:56:17','app');

输出

1102
1106
算法知识视频讲解
添加回答
牛客572350778号头像 牛客572350778号
1103不是也连续登录了3天吗 

发表于 2024-11-11 13:35:36 回复(11)
辞年小方头像 辞年小方
# 方案一 ：适用于连续次数比较少的情况

# select distinct user_id
# # ,log_time,adddate(log_time,1) ,adddate(log_time,2)
# from (
#     select user_id,date_format(log_time,"%Y-%m-%d") as log_time from login_tb
#     ) t0
# where user_id in (select user_id from register_tb) and
# (user_id,adddate(log_time,1)) in (select user_id,date_format(log_time,"%Y-%m-%d") as log_time from login_tb)
# and (user_id,adddate(log_time,2)) in (select user_id,date_format(log_time,"%Y-%m-%d") as log_time from login_tb)


# 方案二：窗口位移函数lead(列,偏移量,空的默认值) over() 适用于连续比较少的情况
# select distinct user_id from (
#     select user_id,date_format(log_time,"%Y-%m-%d") as log_time,
#     lead(date_format(log_time,"%Y-%m-%d"),1) over(partition by user_id) as log_time_1,
#     lead(date_format(log_time,"%Y-%m-%d"),2) over(partition by user_id) as log_time_2
#     from login_tb
#     where user_id in (select user_id from register_tb)
# ) t0
# where log_time_1 is not null and log_time_2 is not null

# 方案三：分组row_number差值count通用大法  适用于连续次数较多的情况

select user_id
from(
    select user_id,date_format(log_time,"%Y-%m-%d") as log_time,row_number() over(partition by user_id order by log_time) as rn
from login_tb
where user_id in (select user_id from register_tb)
) t0
group by user_id,subdate(log_time,rn)
having count(*)>=3




发表于 2024-09-21 19:43:35 回复(4)
Sqlite
一颗宏心头像 一颗宏心
# 使用窗口函数
select user_id
from (
    select l.user_id,rank() over(partition by l.user_id order by l.log_time) as rk
    from register_tb r inner join login_tb l
    on r.user_id = l.user_id
)t
where t.rk >=3

发表于 2024-09-19 22:02:21 回复(4)
dengtianbo头像 dengtianbo 
select
    user_id
from
    (
        select
            user_id,
            date_sub(date, interval ranks day) date_sub
        from
            (
                select
                    l.user_id,
                    date (log_time) date,
                    row_number() over (
                        partition by
                            l.user_id
                        order by
                            date (log_time)
                    ) ranks
                from
                    register_tb r
                    join login_tb l using (user_id)
                group by
                    user_id,
                    date (log_time)
            ) t
    ) t
group by
    user_id,
    date_sub
having
    count(*) >= 3

发表于 2024-08-27 15:02:31 回复(0)
热血的托尼反对画饼头像 热血的托尼反对画饼 
with start_log as (
select lt.user_id, log_time,
row_number()over(partition by lt.user_id order by log_time) start_time
from login_tb lt join register_tb rt using(user_id)
)
select start_log.user_id
from start_log
group by start_log.user_id
having count(start_time) = 3


发表于 2024-12-16 23:01:59 回复(0)
已转码的番石榴很喜欢修勾头像 已转码的番石榴很喜欢修勾
select a.user_id
from(
select r.user_id
,date_sub(left(log_time,10),interval rank()over(partition by r.user_id order by log_time) day) 日期差
from register_tb r join login_tb l
on r.user_id=l.user_id) a
group by a.user_id,日期差
having count(日期差)>=3
order by a.user_id
发表于 2024-10-12 21:06:20 回复(1)
牛客290590683号头像 牛客290590683号
想到了两种方法处理：
1.用偏移函数LEAD()OVER()和DATEDIFF()函数结合解决
WITH A AS (SELECT l.user_id,
      log_time,
            LEAD(log_time,1)OVER(PARTITION BY l.user_id ORDER BY log_time) AS log_time2,
            LEAD(log_time,2)OVER(PARTITION BY l.user_id ORDER BY log_time) AS log_time3
FROM login_tb AS l
INNER JOIN register_tb AS r
ON r.user_id = l.user_id)

SELECT user_id
FROM A
WHERE DATEDIFF(log_time2,log_time)= 1 AND DATEDIFF(log_time3,log_time2)= 1
ORDER BY user_id

2. 用排名函数和子循环
SELECT user_id FROM
(SELECT user_id,count(*) as 连续登录天数
FROM (SELECT l.user_id,
                log_time,
                DENSE_RANK()OVER(PARTITION BY l.user_id ORDER BY log_time) AS date_order,
                DATE_SUB(log_time,INTERVAL (DENSE_RANK()OVER(PARTITION BY l.user_id ORDER BY log_time)) DAY) AS fake_date
FROM login_tb l
INNER JOIN register_tb r
ON l.user_id = r.user_id ) AS t1
GROUP BY user_id,DATE_FORMAT(fake_date,'%Y-%m-%d')) AS t2
WHERE 连续登录天数 >=3
ORDER BY user_id 



发表于 2025-02-25 14:44:10 回复(0)
wenzo_头像 wenzo_
# LEAD() 是 SQL 中的一种窗口函数，它用于从当前行的某个列中获取后一行的值
# LEAD(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY order_column)
# column_name：你想要获取值的列。
# offset：向后多少行，默认值是 1，即获取当前行之后的一行。如果指定其他值，可以获取更远的行。
# OVER() 子句：定义窗口函数的分组和排序方式。
# PARTITION BY：可选项，用于将结果集分成多个分区，每个分区内独立执行窗口函数。
# ORDER BY：用于定义数据的排序顺序，从而决定 LEAD() 应该从哪一行开始查找。
SELECT
    user_id
    FROM (  # 创建一个新的table，用LEAD function 得出每组user_id(PARTITION BY)的log_time的后一天和后两天
        SELECT 
            user_id,
            DATE(log_time) AS log_time,
            LEAD(Date(log_time), 1) OVER (PARTITION BY user_id ORDER BY DATE(log_time)) AS next_day, # 以user_id分组，得出log_time之后1天
            LEAD(Date(log_time), 2) OVER (PARTITION BY user_id ORDER BY DATE(log_time)) AS next_day2 # 以user_id分组，得出log_time之后2天
        FROM login_tb
) AS consecutive_table 
JOIN register_tb r # 用新建table join register_tb，找目标为2022-02-08注册的用户
USING (user_id)
WHERE DATE(r.reg_time) = '2022-02-08' # 在2022-02-08日注册的用户
    AND DATEDIFF(next_day, log_time) = 1 # 登录时间第一天和第二天的差为1
    AND DATEDIFF(next_day2, next_day) = 1 # 登录时间第二天和第三天的差为1
ORDER BY user_id # 以order_id正序排序


发表于 2024-10-11 04:48:33 回复(1)
子在川上_头像 子在川上_
用递归的cte即可
with recursive
    cte as (
        select
            user_id,
            log_time,
            1 'n'
        from
            login_tb
        union all
        select
            l.user_id,
            l.log_time,
            n + 1
        from
            login_tb `l`
            join cte on (l.user_id = cte.user_id) and datediff(l.log_time,cte.log_time) = 1
    )
select
    c.user_id
from
    cte `c`
    join register_tb `r` on c.user_id = r.user_id
where
    n > 2;
发表于 2025-08-19 21:51:24 回复(0)
7code_dog头像 7code_dog 
select distinct user_id
from
(select user_id,
        lead(user_id,1) over(partition by user_id order by log_time) as p1,
        lead(user_id,2) over(partition by user_id order by log_time) as p2
from login_tb) as A
where (user_id in
(select user_id 
from register_tb))
and user_id=p1 and user_id=p2

发表于 2025-02-04 14:35:03 回复(0)
Bo_cchi头像 Bo_cchi 
select distinct user_id
  from
(
select user_id, date(log_time) - dense_rank()over(partition by user_id order by date(log_time)) r2
  from login_tb
  where user_id in (select user_id from register_tb)
) temp1
 group by user_id
 having count(r2) >= 3
 order by user_id

发表于 2024-08-10 21:35:57 回复(0)
苹果与狗与狗尾巴草头像 苹果与狗与狗尾巴草 
with a as (
    select log_time, user_id,
           date_sub(log_time, interval (row_number() over (partition by user_id order by log_time)) day) as start_day
    from login_tb
)
select distinct a.user_id
from a
join register_tb as rt
  on a.user_id = rt.user_id
group by a.user_id,a.start_day
having datediff(max(a.log_time), a.start_day) >= 3;

发表于 2025-12-09 17:16:55 回复(0)
牛客68852182号头像 牛客68852182号
with c as(
select
 a.user_id,
 concat(year(log_time),"-",month(log_time),"-",day(log_time)) as log_time,
 row_number() over(partition by user_id order by b.user_id asc,log_time asc) as time
from register_tb as a
left join login_tb as b on a.user_id=b.user_id
group by a.user_id,b.log_time
order by a.user_id)
select
 c.user_id
from c
group by user_id
having count(c.time) >=3

发表于 2025-10-17 23:21:15 回复(0)
牛客791071232号头像 牛客791071232号
SELECT r.user_id
FROM login_tb l
JOIN register_tb r ON l.user_id = r.user_id
JOIN login_tb l2 ON l.user_id=l2.user_id
    AND DATE(l2.log_time)=DATE_ADD(DATE(l.log_time),INTERVAL 1 DAY)
JOIN login_tb l3 ON l.user_id=l3.user_id
    AND DATE(l3.log_time)=DATE_ADD(DATE(l.log_time),INTERVAL 2 DAY)
GROUP BY r.user_id
ORDER BY r.user_id

发表于 2025-09-28 16:35:53 回复(0)
xiaomin1头像 xiaomin1 
select distinct
    l1.user_id
from
    login_tb l1
    JOIN login_tb l2 on l1.user_id = l2.user_id and date(l2.log_time) = date(l1.log_time) + INTERVAL 1 DAY
    JOIN login_tb l3 on l1.user_id = l3.user_id and date(l3.log_time) = date(l1.log_time) + INTERVAL 2 DAY
where
    l1.user_id in (
        select
            user_id
        from
            register_tb
        where
            date(reg_time) = '2022-02-08'
    )
order by 
    l1.user_id

发表于 2025-08-24 08:51:51 回复(0)
牛客600102492号头像 牛客600102492号 
SELECT
    DISTINCT l1.user_id
FROM login_tb l1
JOIN login_tb l2 ON DATE_FORMAT(l2.log_time, '%Y-%m-%d') = DATE_ADD(DATE_FORMAT(l1.log_time, '%Y-%m-%d'), INTERVAL 1 DAY) AND l1.user_id = l2.user_id
JOIN login_tb l3 ON DATE_FORMAT(l3.log_time, '%Y-%m-%d') = DATE_ADD(DATE_FORMAT(l1.log_time, '%Y-%m-%d'), INTERVAL 2 DAY) AND l1.user_id = l3.user_id
WHERE l1.user_id IN (
    SELECT user_id
    FROM register_tb
)

发表于 2025-08-18 09:05:03 回复(0)
牛客482350188号头像 牛客482350188号
发表于 2025-08-14 22:52:48 回复(0)
牛客923164085号头像 牛客923164085号
1003,1103,眼睛急转弯
发表于 2025-08-12 15:29:57 回复(0)
嘉子_头像 嘉子_
select
    b.user_id
from
    (
        select
            user_id,
            date_sub(date(log_time), interval ranking day) begin_date,
            count(log_time) con_days
        from
            (
                select
                    user_id,
                    log_time,
                    rank() over (
                        partition by
                            user_id
                        order by
                            log_time
                    ) ranking
                from
                    login_tb
            ) as a
        group by
            user_id,
            date_sub(date(log_time), interval ranking day)
    ) as b
    join register_tb on b.user_id = register_tb.user_id
where
    con_days >= 3


发表于 2025-07-30 17:02:05 回复(0)
Boost_头像 Boost_ 
with date_group as 
    (select 
        lt.user_id
        ,lt.log_date
        , DATE_SUB(lt.log_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY lt.user_id ORDER BY lt.log_date) DAY) AS grp
    from 
        (select distinct user_id,date(log_time) as log_date
        from login_tb) lt 
    left join 
        register_tb rt
    on 
        lt.user_id=rt.user_id
    where 
        date(rt.reg_time)='2022-02-08')


select
    user_id 
from 
    date_group
group by
    user_id,grp
having
    count(1)>=3;
运用date_sub函数给连续天数分组，注意给原登录表去重以及子查询重命名
发表于 2025-07-22 11:35:55 回复(0)
提交观点

问题信息

SQL
难度：
276条回答 2861浏览

热门推荐

相关试题

查询连续登陆的用户

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

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