SQL 手机用户换机周期：两次激活间隔天数（小米面试题）

一、题目

根据用户的手机激活记录，计算每个用户的平均换机周期（天数）。

• 换机周期 = 同一用户相邻两次激活新手机之间的天数间隔
• 注意：只激活过一台手机的用户无法计算换机周期（无前序设备可对比），会被自动排除

假设有手机激活表 t4_phone_activation：

+----------+------------+------------+-------------------+
| user_id  | device_id  |   model    |  activation_time  |
+----------+------------+------------+-------------------+
| u01      | DV001      | Xiaomi 12  | 2022-03-15 10:00  |
| u01      | DV002      | Xiaomi 13  | 2024-05-20 14:00  |
| u01      | DV003      | Xiaomi 15  | 2025-06-01 09:00  |
| u02      | DV004      | Redmi K50  | 2023-01-10 11:00  |
| u02      | DV005      | Redmi K70  | 2024-08-15 16:00  |
| u03      | DV006      | Xiaomi 14  | 2024-02-20 08:00  |
| u04      | DV007      | Xiaomi 12  | 2022-06-01 10:00  |
| u04      | DV008      | Xiaomi 14  | 2024-01-15 12:00  |
+----------+------------+------------+-------------------+

二、思路分析

1. 使用 LEAD 窗口函数获取每个用户下一次激活时间；
2. 第二次及以上激活才存在"上一次激活"，第一条记录的换机周期为NULL（无前序设备）；
3. 按用户分组计算平均换机周期，排除NULL值。

维度	评分
题目难度	⭐️⭐️⭐️
题目清晰度	⭐️⭐️⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️⭐️

三、逐步推导

1.使用LEAD计算每次换机的天数间隔

用 lead 窗口函数获取每个用户下一次激活时间，计算两次激活的间隔天数。

执行SQL

select user_id,
       device_id,
       model,
       activation_time,
       lead(activation_time, 1) over (partition by user_id order by activation_time) as next_activation_time,
       datediff(lead(activation_time, 1) over (partition by user_id order by activation_time), activation_time) as replace_days
from t4_phone_activation

查询结果

+----------+------------+------------+-------------------+-----------------------+---------------+
| user_id  | device_id  |   model    |  activation_time  | next_activation_time  | replace_days  |
+----------+------------+------------+-------------------+-----------------------+---------------+
| u01      | DV001      | Xiaomi 12  | 2022-03-15 10:00  | 2024-05-20 14:00      | 797           |
| u01      | DV002      | Xiaomi 13  | 2024-05-20 14:00  | 2025-06-01 09:00      | 377           |
| u01      | DV003      | Xiaomi 15  | 2025-06-01 09:00  | NULL                  | NULL          |
| u02      | DV004      | Redmi K50  | 2023-01-10 11:00  | 2024-08-15 16:00      | 583           |
| u02      | DV005      | Redmi K70  | 2024-08-15 16:00  | NULL                  | NULL          |
| u03      | DV006      | Xiaomi 14  | 2024-02-20 08:00  | NULL                  | NULL          |
| u04      | DV007      | Xiaomi 12  | 2022-06-01 10:00  | 2024-01-15 12:00      | 593           |
| u04      | DV008      | Xiaomi 14  | 2024-01-15 12:00  | NULL                  | NULL          |
+----------+------------+------------+-------------------+-----------------------+---------------+
8 rows selected (0.979 seconds)(https://www.dwsql.com)

2.按用户计算平均换机周期

排除最新设备后，按用户计算平均换机天数。

执行SQL

select user_id,
       count(1) as activation_cnt,
       round(avg(replace_days), 0) as avg_replace_days
from (
    select user_id,
           datediff(lead(activation_time, 1) over (partition by user_id order by activation_time), activation_time) as replace_days
    from t4_phone_activation
) t
where replace_days is not null
group by user_id

查询结果

+----------+-----------------+-------------------+
| user_id  | activation_cnt  | avg_replace_days  |
+----------+-----------------+-------------------+
| u01      | 2               | 587.0             |
| u02      | 1               | 583.0             |
| u04      | 1               | 593.0             |
+----------+-----------------+-------------------+
3 rows selected (0.65 seconds)(https://www.dwsql.com)

四、常见坑点

坑1：LEAD vs LAG 方向混淆 — 换机周期需要从前一台设备看向后一台设备，必须用 LEAD 获取下一次激活时间。若误用 LAG 会得到上一次激活时间，结果是从后一台设备看向前一台，逻辑颠倒。

坑2：NULL 值自动跳过 — 每个用户的最新一条激活记录（最后一台设备）的 LEAD 返回 NULL，datediff 结果也为 NULL。好消息是 avg() 聚合函数自动跳过 NULL 值，无需手动过滤。但 count(1) 会统计所有行，所以子查询中仍需 where replace_days is not null 来正确统计有效换机次数。

坑3：datediff 参数顺序 — datediff(end, start) 返回正数天数。若写成 datediff(activation_time, lead(...)) 参数顺序颠倒，会得到负数。面试中务必确认参数顺序：后一个时间减前一个时间。

五、知识点总结

考点	说明
LEAD 窗口函数	获取同一分区内下一行的值，用于计算"到下一次事件"的间隔，如换机周期、复购间隔
DATEDIFF 日期差	datediff(end, start) 计算两个日期间隔天数，注意参数顺序决定正负
GROUP BY + 聚合函数	按用户分组计算平均换机周期，avg() 自动跳过 NULL 值
NULL 值处理	窗口函数 NULL 向下传递（datediff(null, x) = null），聚合函数自动跳过 NULL

六、建表语句和数据插入

点击展开 DDL & DML

-- 建表语句
create table t4_phone_activation (
    user_id         string comment '用户ID',
    device_id       string comment '设备ID',
    model           string comment '手机型号',
    activation_time string comment '激活时间'
) comment '手机激活记录表';

-- 插入数据
insert into t4_phone_activation(user_id, device_id, model, activation_time) values
('u01','DV001','Xiaomi 12','2022-03-15 10:00'),
('u01','DV002','Xiaomi 13','2024-05-20 14:00'),
('u01','DV003','Xiaomi 15','2025-06-01 09:00'),
('u02','DV004','Redmi K50','2023-01-10 11:00'),
('u02','DV005','Redmi K70','2024-08-15 16:00'),
('u03','DV006','Xiaomi 14','2024-02-20 08:00'),
('u04','DV007','Xiaomi 12','2022-06-01 10:00'),
('u04','DV008','Xiaomi 14','2024-01-15 12:00');

📱关注公众号

「数据仓库技术」文章同步更新，不错过每一篇干货

💬加群交流

备注「数据仓库技术」加入社群，每日一道大厂SQL真题