首页 > 试题广场 >

查询连续登陆的用户

[编程题]查询连续登陆的用户
  • 热度指数：61073 时间限制：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
算法知识视频讲解
添加回答
Mysql
jojozyh头像 jojozyh 
# 去重，按用户分组登录日期排名；筛选条件register_tb.user_id
WITH t AS (
    SELECT 
        DISTINCT user_id, 
        DATE(log_time) AS log_dt, 
        DENSE_RANK() OVER (PARTITION BY user_id ORDER BY DATE(log_time)) AS r
    FROM login_tb 
    WHERE user_id IN (SELECT user_id FROM register_tb)
)
# 按用户，连续登陆分组；筛选条件连续登陆至少三天
SELECT DISTINCT user_id
FROM t
GROUP BY user_id, log_dt - INTERVAL r DAY
HAVING COUNT(*) > 2

发表于 2026-03-12 09:54:38 回复(0)
Mysql
牛客274149438号头像 牛客274149438号
# 首先去重，每个用户每天只保留一条登陆记录
with t1 as (
    select distinct l.user_id, date(log_time) date_time from register_tb r
    inner join login_tb l
    on r.user_id = l.user_id
),
# 为每个用户的登录日期按顺序编号
t2 as (
    select user_id, date_time,
    row_number() over(partition by user_id order by date_time) rn,
    date_sub(date_time, interval
    (row_number() over(partition by user_id order by date_time)) day) diff_date
    from t1
    order by user_id
),
# 计算日期和序号的差值，并按差值分组
t3 as (
    select user_id, count(*) from t2
    group by user_id, diff_date
    having count(*) >= 3
)
# 提取ID，并按照ID去重
select distinct user_id from t3 order by user_id;
发表于 2026-03-06 14:26:41 回复(0)
Mysql
最喜欢冬天的沸羊羊很活泼头像 最喜欢冬天的沸羊羊很活泼
with
distinct_day as(
    select user_id,date(log_time) as date
    from login_tb
    group by user_id,date(log_time)
),
count_day as (
    select r.user_id,d.date,
    row_number() over(partition by r.user_id order by d.date asc) as rn

    from register_tb as r join distinct_day as d
    on r.user_id=d.user_id
),
day_long as (
    select user_id,date,
    date_sub(date,interval rn day) as origin
    from count_day
)
select user_id
from
(
    select user_id,count(*) as consistent_days
    from day_long
    group by user_id,origin
)as final_table
where consistent_days>=3;

发表于 2025-12-07 14:42:24 回复(0)
Mysql
在创作的饭团头像 在创作的饭团
/* 一、核心逻辑梳理
限定新注册用户：仅保留 register_tb 中 reg_time 为 2022-02-08 的用户（排除非当日注册用户）；
提取有效登录记录：关联登录表，仅保留「注册后」的登录记录（避免注册前的无效登录），且按用户 + 登录日期去重（题目说明忽略单日多次登录）；
生成连续登录分组标识：对每个用户的登录日期排序后，用「登录日期 - 排序序号」得到固定值（连续日期的该值相同，中断则变化）；
统计连续登录天数：按用户 + 分组标识统计每组的登录天数，筛选出「最大连续登录天数≥3」的用户；
排序输出：按 user_id 升序排列。。*/

WITH
-- 步骤1：筛选2022-02-08新注册用户（仅保留当日注册的user_id和注册时间）
new_users AS (
    SELECT
        user_id,
        reg_time
    FROM register_tb
    WHERE DATE(reg_time) = '2022-02-08'
),
-- 步骤2：关联登录表，获取新用户注册后的登录记录（去重单日登录）
user_login AS (
    SELECT DISTINCT
        lu.user_id,
        DATE(lu.log_time) AS login_date  -- 提取登录日期（忽略时间）此处是考点之一
    FROM login_tb lu
    JOIN new_users nu ON lu.user_id = nu.user_id
    WHERE lu.log_time >= nu.reg_time  -- 仅保留注册后的登录记录
),
-- 步骤3：对每个用户的登录日期排序，生成连续分组标识
login_ranked AS (
    SELECT
        user_id,
        login_date,
        -- 核心：连续日期的分组标识相同，中断则变化（MySQL 写法）
        DATE_SUB(
            login_date,
            INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date ASC) DAY
        ) AS continuous_group
    FROM user_login
),
-- 步骤4：先统计每个用户+每个分组的连续天数（第一层聚合）
continuous_days_per_group AS (
    SELECT
        user_id,
        continuous_group,
        COUNT(*) AS consecutive_days  -- 每个分组的连续登录天数
    FROM login_ranked
    GROUP BY user_id, continuous_group
),
-- 步骤5：按用户分组，取最大连续天数（第二层聚合）
max_continuous_days AS (
    SELECT
        user_id,
        MAX(consecutive_days) AS max_days  -- 每个用户的最大连续登录天数
    FROM continuous_days_per_group
    GROUP BY user_id
)
-- 步骤6：筛选最大连续登录≥3天的用户，升序排序
SELECT user_id
FROM max_continuous_days
WHERE max_days >= 3
ORDER BY user_id ASC;
发表于 2025-11-14 11:11:51 回复(0)
Mysql
有礼貌的土拨鼠希望被offer砸中头像 有礼貌的土拨鼠希望被offer砸中
SELECT user_id
FROM (
    SELECT user_id, COUNT(*) as c1
    FROM (
        SELECT
            user_id,
            DATE_SUB(login_date, INTERVAL rn DAY) as r1
        FROM (
            SELECT
                user_id,
                DATE(log_time) as login_date,
                ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY DATE(log_time) ASC) as rn
            FROM login_tb
            GROUP BY user_id, DATE(log_time)
        ) t0
    ) t1
    GROUP BY user_id, r1
    HAVING COUNT(*) > 2
) t2
WHERE user_id IN(SELECT user_id FROM register_tb)
GROUP BY user_id;
发表于 2025-11-08 21:40:30 回复(0)
Mysql
王小和头像 王小和
SELECT distinct t1.user_id
FROM
(SELECT lt.user_id,
date(lt.log_time) as date_1,
row_number()over(partition by lt.user_id order by date(lt.log_time)) as rn
FROM login_tb lt
JOIN register_tb as rt
ON lt.user_id=rt.user_id
WHERE date(rt.reg_time)="2022-02-08"
GROUP BY lt.user_id,date(lt.log_time))as t1
GROUP BY t1.user_id,date_sub(t1.date_1,interval t1.rn day)
HAVING COUNT(*)>=3
ORDER BY t1.user_id;
发表于 2025-10-30 20:36:33 回复(0)
Mysql
像初雪一样遇见你头像 像初雪一样遇见你 
with t1 as (
select x.user_id,date(log_time) as dt,
row_number() over(partition by user_id order by date(log_time)) as rk
from login_tb x
join register_tb y
using(user_id)
),t2 as (
select user_id,date_sub(dt,interval rk day) as diff
from t1
)
select user_id
from t2 
group by user_id,diff
having count(*)>=3
order by 1

发表于 2025-10-16 12:30:50 回复(0)
Mysql
一路畅行的珂头像 一路畅行的珂 
SELECT user_id
FROM (
    SELECT 
        user_id,
        grp,
        COUNT(*) AS consecutive_days
    FROM (
        SELECT 
            user_id,
            log_date,
            DATE_SUB(log_date, INTERVAL rn DAY) AS grp
        FROM (
            SELECT 
                user_id,
                log_date,
                ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY log_date) AS rn
            FROM (
                SELECT DISTINCT 
                    l.user_id, 
                    DATE(l.log_time) AS log_date
                FROM login_tb l
                JOIN register_tb r ON l.user_id = r.user_id
                WHERE DATE(r.reg_time) = '2022-02-08'
            ) t
        ) t1
    ) t2
    GROUP BY user_id, grp
    HAVING consecutive_days >= 3
) t3
ORDER BY user_id;


发表于 2025-09-06 21:28:17 回复(0)
Mysql
Maga_头像 Maga_
#连续登录不少于3天的新注册用户，输出user_id升序
#为每个用户的登录日期按时间顺序编号
with log_in_rn as (
    select l.user_id, date(l.log_time) date,
    row_number() over(partition by l.user_id order by date(l.log_time)) rn
    from register_tb r
    join login_tb l
    using(user_id)
    group by l.user_id, date(l.log_time)
)
    select user_id
    from log_in_rn
    group by user_id, date_sub(date, interval rn day)#日期-编号，连续日期具有相同的结果，因此分组(very wonderful)
    having count(*) >= 3 #总计相同结果数量，也就是每个分组连续天数
    order by user_id

发表于 2025-08-15 17:28:16 回复(0)
Mysql
拥抱变化_赵头像 拥抱变化_赵
select user_id
from(
select rt.user_id,
row_number() over(partition by lt.user_id order by log_time) as 日期排序,
date_sub(log_time, interval row_number() over(partition by lt.user_id order by log_time) day) as 初始日期 from register_tb as rt
join login_tb as lt
on rt.user_id = lt.user_id
) t
group by user_id
having max(日期排序) - min(日期排序)+1  >= 3
order by user_id
发表于 2025-07-13 15:51:05 回复(0)
Mysql
牛客896519114号头像 牛客896519114号
SELECT user_id
FROM
(SELECT user_id,
    DAY(lt.log_time) - row_number() OVER(PARTITION BY user_id ORDER BY DAY(lt.log_time)) AS timelist
FROM login_tb lt
WHERE lt.user_id in (SELECT register_tb.user_id FROM register_tb)) sub
GROUP BY user_id
HAVING COUNT(timelist) >= 3


发表于 2025-05-12 03:12:14 回复(0)
Mysql
牛客211296859号头像 牛客211296859号
SELECT user_id
FROM
    (
    SELECT
        t1.user_id,
        date_format( DATE_SUB( t2.log_time, INTERVAL row_number() over ( PARTITION BY user_id ORDER BY log_time ) DAY ), '%Y-%m-%d' ) AS rrn
    FROM
        register_tb t1
    JOIN login_tb t2 ON t1.user_id = t2.user_id
    ) t3
    group by user_id,rrn
    having count(*) >=3
    order by user_id;
发表于 2025-05-04 17:17:20 回复(0)
Mysql
牛客775456359号头像 牛客775456359号 
select
    user_id
from
    (
        select
            user_id,
            date_sub (log_date, interval rk day) dt
        from
            (
                select
                    user_id,
                    date (log_time) log_date,
                    row_number() over (
                        partition by
                            user_id
                        order by
                            log_time
                    ) rk
                from
                    login_tb
                where
                    user_id in (
                        select
                            user_id
                        from
                            register_tb
                    )
                group by
                    user_id,
                    log_time
            ) t1
        group by
            user_id,
            dt
        having
            count(*) > 2
    ) t2
order by
    user_id;

发表于 2025-04-29 21:24:57 回复(0)
Mysql
兮贼歪头像 兮贼歪
select
    user_id
from(
    select
        rt.user_id,
        date(log_time) log_date,
        lead(date(log_time),1) over(partition by rt.user_id order by log_time) date1,
        lead(date(log_time),2) over(partition by rt.user_id order by log_time) date2
    from register_tb rt
    left join login_tb lt using(user_id)
    order by rt.user_id, log_date
) a
where log_date is not null and date1 is not null and date2 is not null and date1 = log_date + 1 and date2 = date1 + 1
order by user_id
发表于 2025-03-23 10:28:47 回复(0)
Mysql
求你们别卷了的灰太狼很喜欢溜溜球头像 求你们别卷了的灰太狼很喜欢溜溜球 
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

发表于 2025-03-20 14:18:56 回复(0)
Mysql
牛客854235923号头像 牛客854235923号 
select user_id
from
(select
    user_id,
    count(*) as cnt
from
    (
        select
            user_id,
            date(log_time) logindate,
            date_sub(date(log_time),
            interval cast(ank as signed) day)  begingday
        from
            (
                select
                    r.user_id,
                    l.log_time,
                    row_number() over (
                        partition by
                            (r.user_id)
                        order by
                            l.log_time asc
                    ) ank
                from
                    register_tb r,
                    login_tb l
                where
                    r.user_id = l.user_id
                group by
                    r.user_id,l.log_time
            ) a
    ) b
group by
    user_id,
    begingday
having
    cnt >= 3)c


发表于 2025-03-05 21:32:39 回复(0)
Mysql
牛客62490438号头像 牛客62490438号
select
user_id
from register_tb
where user_id in
    (
    select
    user_id
    from
        (
        select
        user_id
        ,date(log_time) lt
        ,date(log_time) - interval row_number()over(partition by user_id order by date(log_time)) day n
        from login_tb
        ) a
    group by user_id,a.n
    having count(distinct lt) >= 3
    )
发表于 2025-02-18 13:42:26 回复(0)
提交观点

问题信息

SQL
难度：
27条回答 3294浏览

热门推荐

相关试题

查询连续登陆的用户

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

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