校招算法笔面试 | SQL笔试面试编程题-查询连续登陆的用户

原创已于 2025-04-23 09:53:12 修改 · 320 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#算法 #面试 #sql #算法SQL #算法SQL面试

于 2025-04-23 09:52:41 首次发布

牛客SQL题单题解专栏收录该内容

131 篇文章

订阅专栏

题目## 题目

题目链接

1. 确定总体问题

我们需要找出在注册后连续登录不少于3天的用户，并输出他们的用户ID，并按用户ID升序排序。

2. 分析关键问题

提取登录日期：从登录信息中提取每个用户的登录日期。
编号登录记录：为每个用户的登录日期编号，以便识别连续登录的天数。
识别连续登录：通过计算日期差异识别连续登录的天数。
过滤和排序：筛选出连续登录不少于3天的用户，并按用户ID升序排序。

3. 解决每个关键问题的代码及讲解

步骤1：提取登录日期

我们使用WITH子句创建一个临时表login_dates，提取每个用户的登录日期：

WITH login_dates AS (
    SELECT DISTINCT user_id, DATE(log_time) AS login_date
    FROM login_tb
    WHERE user_id IN (SELECT user_id FROM register_tb)
)

SELECT DISTINCT user_id, DATE(log_time) AS login_date：提取每个用户的唯一登录日期，DISTINCT去重，DATE用于提取日期。
WHERE user_id IN (SELECT user_id FROM register_tb)：只选择注册用户的登录记录。

步骤2：编号登录记录

我们为每个用户的登录日期编号，以便识别连续登录的天数：

numbered_logins AS (
    SELECT user_id, login_date,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
    FROM login_dates
)

ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn：为每个用户的登录日期按时间顺序编号。
PARTITION BY user_id 按照user_id分区
ORDER BY login_date 按照login_date排序

步骤3：识别连续登录

通过计算日期差异识别连续登录的天数：

grouped_logins AS (
    SELECT user_id, COUNT(*) AS consecutive_days
    FROM numbered_logins
    GROUP BY user_id, DATE_SUB(login_date, INTERVAL rn DAY)
)

DATE_SUB(login_date, INTERVAL rn DAY)：通过减去编号，连续的日期将具有相同的结果，从而可以分组。
COUNT(*) AS consecutive_days：计算每个分组的连续天数。

DATE_SUB处理前后样例如下

login_date（处理前）	rn	→	login_date（处理后）
2023-01-02	1	→	2023-01-01
2023-01-03	2	→	2023-01-01
2023-01-05	3	→	2023-01-02

步骤4：过滤和排序

筛选出连续登录不少于3天的用户，并按用户ID升序排序：

SELECT user_id
FROM grouped_logins
WHERE consecutive_days >= 3
ORDER BY user_id;

WHERE consecutive_days >= 3：筛选出连续登录不少于3天的用户。
ORDER BY user_id：按用户ID升序排序。

完整代码

WITH login_dates AS (
    SELECT DISTINCT user_id, DATE(log_time) AS login_date
    FROM login_tb
    WHERE user_id IN (SELECT user_id FROM register_tb)
),
numbered_logins AS (
    SELECT user_id, login_date,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
    FROM login_dates
),
grouped_logins AS (
    SELECT user_id, COUNT(*) AS consecutive_days
    FROM numbered_logins
    GROUP BY user_id, DATE_SUB(login_date, INTERVAL rn DAY)
)
SELECT user_id
FROM grouped_logins
WHERE consecutive_days >= 3
ORDER BY user_id;

题目链接

这道题目要求我们查询在2022年6月12日入住并连续入住多晚的客户信息。我们需要输出客户ID、房间号、房间类型和连续入住天数，并按连续入住天数升序、房间号升序、客户ID降序排序。下面是这个SQL查询的思路和实现步骤。

1. 确定总体问题

我们需要找出在2022年6月12日入住并连续入住至少两晚的客户信息。

2. 分析关键问题

连接表：我们需要将checkin_tb和guestroom_tb表连接起来，以便获取每个入住记录的房间类型。
计算入住天数：使用DATEDIFF函数计算入住天数。
过滤数据：只选择在2022年6月12日入住并连续入住至少两晚的记录。
排序输出：按连续入住天数升序、房间号升序、客户ID降序排序。

3. 解决每个关键问题的代码及讲解

步骤1：连接表

我们使用JOIN将checkin_tb和guestroom_tb表连接起来：

FROM
    checkin_tb ct
JOIN 
    guestroom_tb gt ON gt.room_id = ct.room_id

ON gt.room_id = ct.room_id：通过房间号进行连接获取每个入住记录对应的房间类型。

步骤2：计算入住天数

我们使用DATEDIFF函数计算入住天数：

DATEDIFF(ct.checkout_time, ct.checkin_time) AS days

DATEDIFF(ct.checkout_time, ct.checkin_time)：前后两个时间做差来计算入住天数。

步骤3：过滤数据

我们使用WHERE子句来过滤出符合条件的记录：

WHERE
   DATE(ct.checkin_time) = '2022-06-12' AND DATEDIFF(ct.checkout_time, ct.checkin_time) >= 2

DATE(ct.checkin_time) = '2022-06-12'：选择在2022年6月12日入住的记录。
DATEDIFF(ct.checkout_time, ct.checkin_time) >= 2：选择连续入住至少两晚的记录（注意不要在where直接使用days>= 2）。

步骤4：排序输出

我们使用ORDER BY按要求排序输出结果：

ORDER BY
    days ASC, ct.room_id ASC, ct.user_id DESC

days ASC：按连续入住天数升序排序。
ct.room_id ASC：按房间号升序排序。
ct.user_id DESC：按客户ID降序排序。

完整代码

SELECT
    ct.user_id,
    ct.room_id,
    gt.room_type,
    DATEDIFF(ct.checkout_time, ct.checkin_time) AS days
FROM
    checkin_tb ct
JOIN 
    guestroom_tb gt ON gt.room_id = ct.room_id
WHERE
   DATE(ct.checkin_time) = '2022-06-12' AND DATEDIFF(ct.checkout_time, ct.checkin_time) >= 2
ORDER BY
    days ASC, ct.room_id ASC, ct.user_id DESC