首页 > 试题广场 >

查询连续登陆的用户

[编程题]查询连续登陆的用户
  • 热度指数：55199 时间限制：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
算法知识视频讲解
添加回答
Sqlite
风流倜傥三金老师头像 风流倜傥三金老师
WITH
t1 AS (     -- 清洗数据，确保是2月8日注册用户，把登录时间转化为登录日期并去重确保无一天多次登录记录
SELECT
    DISTINCT a.user_id,DATE(a.log_time) AS day
FROM login_tb AS a
INNER JOIN register_tb AS b
    ON a.user_id = b.user_id
    AND b.reg_time >= '2022-02-08 00:00:00' AND b.reg_time < '2022-02-09 00:00:00'
),

t2 AS (     -- 用窗口函数对连续登录记录进行排名，用以分组
SELECT
    user_id,day,
    ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY day) AS rk
FROM t1
),

t3 AS (     -- 对每位用户的每次连续登录记录进行分组
SELECT
    user_id,
    DATE_SUB(day,INTERVAL (rk - 1) DAY) AS fz
FROM t2
),

t4 AS (     -- 计算每位用户每次登录的天数
SELECT
    user_id,
    COUNT(*) AS lx
FROM t3
GROUP BY user_id,fz
)

SELECT      -- 主查询，显示最大连续登录不少于3天的用户ID
    user_id
FROM t4
WHERE lx >= 3;
发表于 2025-11-23 17:08:29 回复(0)
Sqlite
Naruse头像 Naruse
参考41题阿里求最长连续登陆天数的写法，利用连续登陆时日期减排名的结果相同，计算连续登陆天数 
select user_id
from
(
select user_id,count(start_time) count_time
from
(
select l.user_id,log_time,
date_format(date_sub(log_time,interval(dense_rank() over(partition by l.user_id order by log_time))day),'%Y-%m-%d') start_time 
from login_tb as l
inner join register_tb as r on l.user_id = r.user_id
) as t1
group by user_id,start_time
) as t2
where count_time >= 3
order by user_id

发表于 2025-07-21 15:58:38 回复(0)
Sqlite
牛客484876145号头像 牛客484876145号
with
    t1 as(
        select
            user_id,
            date(reg_time) dt,
            reg_port port
        from register_tb
        union
        select
            user_id,
            date(log_time) dt,
            log_port port
        from login_tb
),
    t2 as(
        select
            user_id,
            dt,
            dense_rank() over(partition by user_id order by dt) rk
        from t1
),
    t3 as(
        select
            user_id,
            date_sub(dt,INTERVAL rk day) diff_date
        from t2
        where rk != 1
)
select
    user_id
from t3
group by user_id,diff_date
having count(diff_date) >= 3
发表于 2025-07-09 15:45:54 回复(0)
Sqlite
谦虚的布拉德offer多多头像 谦虚的布拉德offer多多
WITH
    t1 AS (
        SELECT
            log_id,
            lt.user_id,
            DATE_FORMAT (log_time, '%Y-%m-%d') AS log_time,
            log_port,
            DATE_FORMAT (reg_time, '%Y-%m-%d') AS reg_time,
            reg_port
        FROM
            login_tb AS lt
            LEFT JOIN register_tb AS rt ON lt.user_id = rt.user_id
        WHERE
            reg_time IS NOT NULL
        ORDER BY
            user_id,
            log_time
    ), t2 AS(
        SELECT
            *,
            lag(log_time,1,0) OVER(PARTITION BY user_id ORDER BY log_time) AS lag_time
        FROM
            t1
    ), t3 AS(
        SELECT
            *,
            SUM(IF(DATEDIFF(log_time,lag_time) <= 1,0,1)) OVER (PARTITION BY user_id ORDER BY log_time) AS partition_tag
        FROM
            t2
    ) , t4 AS(
        SELECT
            user_id,
            partition_tag,
            DATEDIFF(MAX(log_time),MIN(log_time))+1 AS consec_day
        FROM
            t3
        GROUP BY
            user_id,
            partition_tag

    ) , t5 AS(
        SELECT
            user_id,
            MAX(consec_day) AS max_consec_day
        FROM
            t4
        GROUP BY
            user_id
    )


SELECT
    user_id
FROM
    t5
WHERE
    max_consec_day >= 3
ORDER BY
    user_id

发表于 2025-06-08 16:39:29 回复(0)
Sqlite
彭检头像 彭检 
with r1 as (select rtb.user_id, date(ltb.log_time) dt from register_tb rtb join login_tb ltb using (user_id) where date(rtb.reg_time) = '2022-02-08'  group by rtb.user_id, dt order by rtb.user_id, dt),
     r2 as (select r1.user_id,
                   r1.dt,
                   dt - row_number() over (partition by user_id) same_date from r1) select r2.user_id from r2 group by user_id having count(1) >= 3;
发表于 2025-01-29 15:11:53 回复(0)
Sqlite
dolen头像 dolen 
select
   t4.user_id
from (
select 
    t3.user_id,
    date_sub( date(log_time) , interval rn day ) as sub_date,
    count(1) cnt
from (
    select 
    t1.user_id,
    t1.log_time,
    row_number()over(partition by user_id order by  t1.log_time) rn 
    from login_tb t1 
    inner join register_tb t2 on t1.user_id = t2.user_id
) t3
group by  t3.user_id, date_sub( date(log_time) , interval rn day ) 
) t4
where t4.cnt >=3
group by  t4.user_id

发表于 2024-11-07 16:17:06 回复(0)
Sqlite
阿斯特拉_头像 阿斯特拉_
with t1 as
(select user_id, date(log_time),
row_number() over(partition by user_id order by date(log_time)) rnk,
date(log_time) - row_number() over(partition by user_id order by date(log_time)) cnt
from login_tb
where user_id in (select user_id from register_tb)
order by user_id)

select user_id
from t1
group by user_id
having count(cnt) >= 3
发表于 2024-10-21 18:22:25 回复(0)
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)
Sqlite
呼呼呼噜头像 呼呼呼噜
select
    user_id
from(
    select
    t2.user_id,
    subdate(DATE(t1.log_time),dense_rank() over(partition by t2.user_id order by DATE(t1.log_time))) as first_date
from login_tb as t1
join register_tb as t2 on t1.user_id=t2.user_id
)as t3
group by user_id,first_date
having count(*)>=3;
发表于 2024-09-13 09:15:29 回复(0)
Sqlite
风度翩翩的中国人很喜欢走神头像 风度翩翩的中国人很喜欢走神
select
user_id
from register_tb
where user_id in(
select
user_id
from (
select
user_id,row_number()over(partition by user_id order by log_time)-date_format(log_time,'%d') d
from login_tb
)t
group by user_id,d
having count(*)>=3)
发表于 2024-08-25 16:53:40 回复(0)
Sqlite
祖拜尔实习生头像 祖拜尔实习生 
select uid
from(
    select r.user_id as uid
        , substr(r.reg_time,1,10) as rt
        , substr(l.log_time,1,10) as lt
        , rank() over(partition by l.user_id order by l.log_time) as rk
    from login_tb l right join register_tb r
        on l.user_id = r.user_id
)t
group by uid, lt - interval rk day
having count(*) >= 3


发表于 2024-08-21 15:57:12 回复(0)
提交观点

问题信息

SQL
难度：
13条回答 2877浏览

热门推荐

相关试题

查询连续登陆的用户

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

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