横扫SQL面试——连续性登录问题

横扫SQL面试

📌 连续性登录问题

在互联网公司的SQL面试中，连续性问题堪称“必考之王”。💻🔍

用户连续登录7天送优惠券🌟，服务器连续报警3次触发熔断⚠️，图书馆连续3天人流破百开启限流⚡” …

既考察你对窗口函数的灵活运用，又考验你能否将业务场景抽象为数学模型。

博主总结一些经典题型，帮列位小伙伴拿下这类题目🤣 🤣 🤣 🤣

Tips：
暴力解法（如自连接、逐行遍历）在数据量小时勉强可用，但面对百万级📈数据时：

• 性能灾难：自连接时间复杂度达O(n²)，1万行数据需1亿次计算 🔥
• 逻辑漏洞：简单lag/lead无法处理连续多天的复杂中断

而真正的工业级解法，只需一行窗口函数 + 虚拟分组标记，就能以O(n)时间复杂度解决问题！ 🚀

🌟 连续问题通用解法框架

话不多说——直接上题：🎈🎈🎈🎈🎈🎈🎈

🎯1. 最长连续登录天数

你正在搭建一个用户活跃度的画像，其中一个与活跃度相关的特征是“最长连续登录天数”

计算用户在指定时间段内的最长连续登录天数。例如：统计用户2023年1月的最长连续登录记录。

预期结果🔑

博主按照解题框架 一步一步带大家看哈~🤣🤣🤣

步骤1：生成连续标记（CTE t1）🧩

为每个用户的登录日期生成序号，标记连续登录的潜在分组。

with t1 as (
    select
        user_id,
        fdate,
        -- 默认大家都是有基础的哈 窗口函数应该都会哈
        row_number() over(partition by user_id order by fdate) as rn
    from tb_dau
    where fdate between '2023-01-01' and '2023-01-31'
)

临时表 t1：✅

• rn 表示用户按日期排序后的登录次数序号。
• 连续日期的 rn 差值等于日期差值（例如：2023-01-02 是第2次登录，日期差为1天）。

步骤2：计算虚拟起始点（CTE t2）🧩

通过 date_sub(fdate, interval rn day) 将连续日期映射到同一虚拟起始点。

t2 as (
    select
        user_id,
        fdate,
        date_sub(fdate, interval rn day) as start_date
    from t1
)

临时表 t2：✅

• 连续日期的 start_date 相同（如1月1日和1月2日均映射到2022-12-31）。
• 非连续日期的 start_date 不同（如1月4日映射到2023-01-01）。

步骤3：统计连续天数（CTE t3）🧩

按用户和虚拟起始点分组，统计每组中的记录数（即连续天数）。

t3 as (
    select
        user_id,
        start_date,
        count(*) as cnt
    from t2
    group by user_id, start_date
)

临时表 t3：✅

• cnt 表示每个虚拟起始点对应的连续登录天数。
• 用户10000有两个连续区间：2天和1天。

最终结果取每个用户的最大连续天数。🧩

select
    user_id,
    max(cnt) as max_consec_days
from t3
group by user_id;

输出结果：✅

技术本质🧩

通过 date_sub(fdate, interval rn day)，将连续日期的差值抵消，映射到同一虚拟起始点：

• 连续日期：fdate - rn 恒定✨（如 1月1日-1天=12月31日，1月2日-2天=12月31日）。
• 非连续日期：fdate - rn 不同（如1月4日-3天=1月1日）。

将连续性问题转化为分组计数问题，时间复杂度仅为 O(n)。✨

完整代码 ~

-- 定义第一个公共表表达式 (CTE) t1，用于计算每个用户登录日期的排序
with t1 as (
    select
        user_id,  -- 用户ID
        fdate,    -- 登录日期
        row_number() over(partition by user_id order by fdate) as rn  -- 为每个用户的登录日期生成排序编号
    from tb_dau
    where fdate between '2023-01-01' and '2023-01-31'  -- 选择指定日期范围内的记录
), 

-- 定义第二个公共表表达式 (CTE) t2，用于计算每个登录日期的起始日期
t2 as (
    select
        user_id,  -- 用户ID
        fdate,    -- 登录日期
        date_sub(fdate, interval rn day) as start_date  -- 计算起始日期：将登录日期减去排序编号天数
    from t1
), 

-- 定义第三个公共表表达式 (CTE) t3，用于计算每个用户在相同起始日期下的连续登录天数
t3 as (
    select
        user_id,  -- 用户ID
        count(*) as cnt  -- 计算连续登录天数
    from t2
    group by user_id, start_date  -- 按用户和起始日期分组
)

-- 从 t3 表中选择用户ID和其最大连续登录天数
select
    user_id,  -- 用户ID
    max(cnt) as max_consec_days  -- 最大连续登录天数
from t3
group by user_id;  -- 按用户ID分组

🎯2. 连续出现的数字

从数字序列中找出至少连续出现3次的数字。例如：[1, 1, 1, 2, 2, 3] 中，1 连续出现3次。

这题同理~连续性问题解法框架：
1.💡 标记连续性：使用 row_number() 生成序号。
2. 🔍生成虚拟组：通过差值（如 id - rn）抵消连续增量。
3. 🛠️分组统计：按虚拟组聚合，筛选满足条件的结果。

步骤1：生成连续标记（CTE t1）🚀

为每个数字按 id 排序生成行号，标记连续出现的潜在分组。

with t1 as (
    select
        num,
        id,
        row_number() over(partition by num order by id) as rn
    from logs
)

临时表 t1：✅

• rn 表示相同数字（num）按 id 排序后的出现次数序号。
• 连续相同数字的 id 与 rn 的差值恒定（例如：num=1 时，id - rn = 0）。

步骤2：计算虚拟分组标记（CTE t2）🚀

通过 id - rn 生成分组标记 group_id，将连续相同数字映射到同一虚拟组。

t2 as (
    select
        num,
        id - rn as group_id
    from t1
)

临时表 t2：✅

• 连续相同数字的 group_id 相同（如 num=1 的3条记录均为 group_id=0）。
• 非连续或不同数字的 group_id 不同（如 num=2 和 num=3）。

步骤3：统计连续出现次数（最终查询）🚀

按 num 和 group_id 分组，筛选出出现次数≥3的组，并去重输出结果。

select distinct num as ConsecutiveNums
from t2
group by num, group_id
having count(*) >= 3;

分组统计结果：✅

最终输出：✅

技术本质🚀

通过 id - row_number()，将连续相同数字的差值抵消，映射到同一虚拟分组：

• 连续相同数字：id - rn 恒定🔥（如 num=1 时，id=1,2,3 → 1-1=0, 2-2=0, 3-3=0）。
• 非连续或不同数字：id - rn 不同（如 num=2 时，id=4,5 → 4-1=3, 5-2=3，但次数不足）。

如果题目要求连续出现4次，只需修改 having 条件：

having count(*) >= 4  -- 筛选连续出现4次的数字

🎯3. 新注册用户连续登录不少于3天

筛选出新注册用户在注册后至少连续登录3天的用户列表。例如：用户注册后连续登录了2023-01-01、01-02、01-03三天。

用户表 (tb_users)✅

登录表 (tb_login)✅

预期结果

步骤1：关联用户与登录数据（CTE login_sequence）✅

筛选注册后7天内的登录记录，并为每个用户的登录日期生成行号。

with login_sequence as (
    select
        u.user_id,
        l.login_date,
        -- 为每个用户的登录日期生成行号（按日期排序）
        row_number() over(
            partition by u.user_id 
            order by l.login_date
        ) as rn
    from tb_users u
    join tb_login l 
        on u.user_id = l.user_id
        and l.login_date between u.reg_date and u.reg_date + interval 7 day
)

临时表 login_sequence：✅

• rn 表示用户按登录日期排序后的连续次数。
• login_date between reg_date and reg_date + 7 day 限定注册后7天内的登录行为。

限定用户注册后7天内的登录行为，聚焦新用户关键活跃期，数据进入窗口函数前剔除无效数据，避免对全量数据排序。聚焦核心业务目标（如新用户激活率、首周留存率）

步骤2：生成虚拟分组标记（CTE consec_groups）🚀

计算 login_date - rn，将连续日期映射到同一虚拟起始点。

consec_groups as (
    select
        user_id,
        login_date,
        -- 计算虚拟分组标记（连续日期的差值为0）
        date_sub(login_date, interval rn day) as group_id
    from login_sequence
)

临时表 consec_groups：✅

• 连续登录的日期差值相同（如用户10001的3次登录均映射到 2022-12-31）。🚀🚀🚀
• 非连续登录的日期差值不同（如用户10002的2次登录映射到 2023-01-04）。

步骤3：统计连续登录天数（最终查询）

按用户和虚拟分组标记统计连续天数，筛选≥3天的用户。

distinct user_id 确保用户多次满足条件时只输出一次。

select distinct user_id
from consec_groups
group by user_id, group_id
having count(*) >= 3;

分组统计结果：✅

最终输出：✅

💡 关键逻辑

虚拟分组标记：
date_sub(login_date, interval rn day) 将连续日期映射到同一虚拟起始点，本质是公式：
连续天数 = 最大登录日期 - 最小登录日期 + 1 （若连续，则 login_date - rn 恒定）

🎯4. 图书馆高峰期检测

找出图书馆连续3天及以上人流量≥100的高峰时段。例如：2023-01-02至2023-01-04连续三天人流量达标。

预期结果

后面博主就不再啰嗦啦 大家可以发现 套路是不是都一样~🤣🤣🤣 “标记→分组→过滤”✅✅✅

with valid_days as (
    select 
        date,
        date - row_number() over(order by date) as grp
    from info
    where people >= 100
),
consec_groups as (
    select
        min(date) as start_date,
        max(date) as end_date,
        count(*) as consecutive_days
    from valid_days
    group by grp
    having count(*) >= 3
)
select * from consec_groups;

1. 筛选有效日期：过滤人流量≥100的天数。
2. 生成连续组标记：date - row_number() 将连续有效日期映射到同一组。
3. 统计连续时段：按组统计起止日期和持续天数。

🎯5. 用户指标检测

从订单表中筛选出连续三天及以上每天总下单金额均超过100元的用户。例如：用户A在2023-01-01至2023-01-03每天的总消费分别为120元、150元、110元，满足条件。

预期结果

-- 步骤1：按用户和日期汇总金额，过滤每天金额>100的记录
with daily_summary as (
    select
        id as user_id,
        dt,
        sum(amount) as total_amount
    from order_table
    group by user_id, dt
    having sum(amount) > 100
),
-- 步骤2：生成连续标记
sequence_marker as (
    select
        user_id,
        dt,
        date_sub(dt, interval row_number() over(partition by user_id order by dt) day) as grp
    from daily_summary
),
-- 步骤3：统计连续天数
consec_groups as (
    select
        user_id,
        grp,
        count(*) as consec_days,
        min(dt) as start_date,
        max(dt) as end_date
    from sequence_marker
    group by user_id, grp
    having count(*) >= 3  -- 连续3天及以上
)
-- 步骤4：输出结果
select distinct user_id
from consec_groups;

1. 按天汇总金额：group by user_id, dt 处理一天多笔订单。
2. 生成虚拟分组：date_sub(dt, interval row_number() day) 将连续日期映射到同一虚拟组。
3. 统计连续天数：筛选连续≥3天的用户。

🎯6. 用户最大连续缴费次数

计算每个用户的最长连续缴费天数。例如：用户U002在2023-01-03至2023-01-05连续缴费3天，结果为3。

预期结果

with payment_sequence as (
    select
        user_id,
        pay_date,
        date_sub(pay_date, interval row_number() over(partition by user_id order by pay_date) day) as grp
    from payment_log
),
consec_groups as (
    select
        user_id,
        grp,
        count(*) as consec_days
    from payment_sequence
    group by user_id, grp
)
select
    user_id,
    max(consec_days) as max_consec_days
from consec_groups
group by user_id;

1. 生成虚拟分组：date_sub(pay_date, interval row_number() day) 标记连续缴费序列。
2. 统计连续天数：按用户和虚拟组计算连续缴费次数。
3. 取最大值：max(consec_days) 获取每个用户的最大连续天数。

🧩 连续性问题的通用解法框架

这一套组合拳下来✅✅✅ 相信列位面试在遇到连续性登录问题 丝毫不慌了😂

后续还会继续更新奥 求关注 求订阅~🌟🌟🌟🌟