考察点
连续登录和留存率是 SQL 面试的经典应用题，面试官想看你能不能把业务语言翻译成集合运算。追问方向：连续 N 天的分组键原理、7 日留存的口径、断档一天算不算连续、日期是 datetime 类型怎么处理。
参考答案
连续登录：日期减排名法
题目：找出连续登录 3 天以上的用户。核心思路分四步：
第一步：去重。一个用户一天可能登录多次，先按用户+日期去重：
select distinct user_id, date(login_time) as login_date from login_log
第二步：组内编号。给每个用户的登录日期排序编号：
row_number() over (partition by user_id order by login_date) as rn
第三步：构造连续分组键。这是整道题的题眼——date_sub(login_date, rn)。连续日期里，日期每加一天、排名也加一，两者之差是常数；一旦断档，差值就变了。比如用户 1 号、2 号、3 号、5 号登录，rn 是 1、2、3、4，日期减 rn 分别是 0 号、0 号、0 号、1 号——前三天差值相同，自然聚成一组。
第四步：分组统计：
select user_id, grp, count(*) as consecutive_days,
min(login_date) as start_date, max(login_date) as end_date
from (
select user_id, login_date,
date_sub(login_date, cast(rn as int)) as grp
from (
select user_id, login_date,
row_number() over (partition by user_id order by login_date) as rn
from (select distinct user_id, date(login_time) as login_date from login_log) d
) r
) g
group by user_id, grp
having count(*) >= 3;
这个模板还能扩展：求每个用户最长连续登录天数，就按用户取 max(consecutive_days)；求「连续 N 周」把日期换成周一对齐的周标识。同理可解「连续消费」「连续活跃」一切连续性问题。
另一种解法是 lag 自比较：lag(login_date) over (partition by user_id order by login_date) 和当前行日期差等于 1 则连续。标记断点后做累计求和也能分组，思路等价但写法更绕，日期减排名法更简洁，是主流答案。
留存率：cohort 思维
题目：求次日留存率、7 日留存率。留存的标准口径：某天首次活跃（或注册）的用户中，第 k 天还活跃的比例。核心是把用户按「首日」分群（cohort），再看每个群里第 k 天的回访情况。
第一步：求每个用户的首日：
select user_id, min(date(active_time)) as first_date
from active_log group by user_id
第二步：计算首日与活跃日的间隔：
select f.first_date,
datediff(date(a.active_time), f.first_date) as day_diff,
count(distinct a.user_id) as users
from active_log a
join first_day f on a.user_id = f.user_id
group by f.first_date, datediff(date(a.active_time), f.first_date)
第三步：透视成留存报表：
select first_date,
max(case when day_diff = 0 then users end) as new_users,
max(case when day_diff = 1 then users end) as retain_1d,
max(case when day_diff = 7 then users end) as retain_7d,
max(case when day_diff = 1 then users end) / max(case when day_diff = 0 then users end) as retain_1d_rate
from cohort_diff
group by first_date;
用 case when 行转列是留存报表的标准收尾。这个写法的本质是自连接——活跃表和首日历表按 user_id 关联，一条活跃记录会贡献到「间隔天数」对应的格子里。
两个口径细节
- n 日留存 vs n 日内留存：「次日留存」是第 1 天恰好活跃，「3 日内留存」是第 1-3 天任意一天活跃，SQL 分别是
day_diff = 1和day_diff between 1 and 3，面试时要问清口径。 - datetime 字段必须先转 date，否则同一用户一天多条记录、时间戳参与 datediff 全乱。第一步的 distinct/min 之前就要切好粒度。
性能提示
留存 SQL 是用户级自连接，大表上 shuffle 量不小。优化方向：先按日期范围过滤活跃表（算近 30 天留存不需要全量历史）；首日表可以物化成维度表每天增量维护，而不是每次现算 min。
可能的追问
- 断档一天也算连续（允许 1 天缺口）怎么改？ 把「日期差等于 1」放宽到「小于等于 2」不能直接套日期减排名法了，要用 lag 累计算断点：相邻日期间隔大于 2 记 1 否则记 0，再对这个标记做窗口累计求和作为分组键。
- 新用户留存和活跃用户回访率区别？ 新用户留存的 cohort 是「首日注册/激活」，回访率的分母是某天活跃的全部用户。分母表不同，join 逻辑一样，换个 cohort 定义即可复用模板。
- 周留存怎么算？ 把日期换成周（date_trunc 到周一或 yearweek 函数），间隔单位换成周，模板不变。关键是明确周对齐规则，不同公司的「周」起点可能不同。