SQL 面试题：每日活跃用户的 1日、3日、7日留存率怎么算？

SQL 面试题每日活跃用户的 1日、3日、7日留存率怎么算前段时间面试 的时候遇到一道留存率 SQL 题。题目本身不算特别难但我当时一看到“留存率”第一反应就是MIN(active_date)OVER(PARTITIONBYuser_id)然后那叫一个体直口快开始一顿窗口函数操作自我感觉还挺优雅。结果炫技炫错了 面试官要算的是每日活跃用户留存而我写出来的其实更接近新增用户留存两个都叫留存但最大的区别只有一个分母不是同一批人。这道题也算给大家提了个醒SQL 面试有时候不是函数不会而是业务口径先理解错了。一、题目有一张用户活跃表active_user active_date BIGINT -- 活跃日期例如 20260101 user_id BIGINT -- 用户 ID要求统计2026-01-01 2026-01-08 每天的活跃用户数以及对应的 1 日、3 日、7 日留存率。留存率定义某天活跃且 N 天后仍然活跃的用户数 / 当天活跃用户数最终结果类似日期 活跃用户数 1日留存率 3日留存率 7日留存率 20260108 X1 20260107 X2 Y1% 20260106 X3 Y2% 20260105 X4 Y3% Z1% 20260104 X5 Y4% Z2% ... 20260101 X8 Y7% Z4% W1%比如 01-08 没有次日留存因为题目的观察窗口只到 01-08。同理越靠后的日期3 日、7 日留存也可能暂时无法计算。二、我当时为什么会写错我当时直接想到了MIN(active_date)OVER(PARTITIONBYuser_id)这个 SQL 本身当然没问题。问题是MIN(active_date)找的是这个用户第一次活跃的日期。比如用户 A 的活跃记录是20260101 20260105 20260106那么MIN(active_date)得到的永远是20260101但当我们统计20260105的日活留存时用户 A 明明应该属于01-05 当天的活跃用户应该被算进 01-05 的分母。如果按照首次活跃日期去分组A 会一直被归到 01-01 那批用户里。这样 01-05 的分母就错了。所以MIN(active_date)OVER(...)更适合计算新增用户 / 首次活跃用户的留存而不是这道题要求的每日全量活跃用户留存三、正确思路其实很简单假设 01-01 有 4 个活跃用户A B C D01-02 还有A C那么1日留存率 2 / 4 50%如果到了 01-04只剩 A 还活跃3日留存率 1 / 4 25%所以这道题根本不用先找什么“首次活跃日期”。只需要拿当天活跃的用户去找 1 天、3 天、7 天后这个用户还在不在。这也是后来我按照面试官提示写出来的思路。四、LEFT JOIN 解法面试现场其实直接写 3 次LEFT JOIN就够了非常直观SELECTa.active_dateAS日期,-- 当天活跃用户数也就是分母COUNT(DISTINCTa.user_id)AS活跃用户数,-- 1 日留存COUNT(DISTINCTb1.user_id)*1.0/COUNT(DISTINCTa.user_id)AS1日留存率,-- 3 日留存COUNT(DISTINCTb3.user_id)*1.0/COUNT(DISTINCTa.user_id)AS3日留存率,-- 7 日留存COUNT(DISTINCTb7.user_id)*1.0/COUNT(DISTINCTa.user_id)AS7日留存率FROM(SELECTactive_date,user_idFROMactive_userWHEREactive_dateBETWEEN20260101AND20260108)aLEFTJOINactive_user b1ONa.user_idb1.user_idANDDATEDIFF(b1.active_date,a.active_date)1LEFTJOINactive_user b3ONa.user_idb3.user_idANDDATEDIFF(b3.active_date,a.active_date)3LEFTJOINactive_user b7ONa.user_idb7.user_idANDDATEDIFF(b7.active_date,a.active_date)7GROUPBYa.active_dateORDERBYa.active_date;核心其实就看两个地方。分母COUNT(DISTINCTa.user_id)代表当天所有活跃用户。分子COUNT(DISTINCTb1.user_id)代表当天活跃并且 1 天后又活跃的用户。3 日和 7 日完全一样。面试写到这里核心逻辑基本已经答出来了。注题目里的active_date是 BIGINT。实际在 Hive / Spark SQL 中需要根据具体引擎先转换成 DATE 再使用DATEDIFF。面试时如果默认日期字段可直接参与日期计算可以先重点写业务逻辑。另外如果严格按照题目“观察窗口截止 01-08”像 01-08 的次日留存应该展示为NULL而不是 0。这个可以再通过CASE WHEN补充处理属于边界展示问题不影响核心思路。五、这道题真正的坑分母是谁复盘下来这道题最重要的其实不是LEFT JOIN而是看到“留存率”三个字先确认分母到底是谁。1. 日活留存比如统计 01-05 的次日留存。分母是01-05 当天所有活跃用户不管这个用户是当天刚来的还是已经用了三年的老用户只要 01-05 活跃都算进去。分子是01-05 活跃并且 01-06 仍然活跃的用户所以日活留存更偏向衡量产品整体的用户粘性。SQL 思路也很直接a.user_idb.user_idANDDATEDIFF(b.active_date,a.active_date)N2. 新增用户留存如果题目换成统计每天新增用户的次日留存率。这时候分母就不是当天所有活跃用户了而是当天第一次出现的用户比如一个用户01-01 活跃 01-05 活跃 01-06 活跃他属于 01-01 的新增用户不属于 01-05 的新增用户。这时候我面试时想到的MIN(active_date)OVER(PARTITIONBYuser_id)反而就对了。六、如果题目真的问新增留存MIN 开窗就很好用假设题目变成求 2026-10-01 2026-10-07 期间新增用户的次日留存率。这时候可以先给每个用户找到首次活跃日期WITHfirst_loginAS(SELECTuser_id,active_date,MIN(active_date)OVER(PARTITIONBYuser_id)ASfirst_active_dateFROMactive_user)SELECTfirst_active_dateAS新增日期,COUNT(DISTINCTuser_id)AS新增用户数,COUNT(DISTINCTIF(DATEDIFF(active_date,first_active_date)1,user_id,NULL))AS次日留存人数,ROUND(COUNT(DISTINCTIF(DATEDIFF(active_date,first_active_date)1,user_id,NULL))*1.0/COUNT(DISTINCTuser_id),4)AS次日留存率FROMfirst_loginWHEREfirst_active_dateBETWEEN2026-10-01AND2026-10-07GROUPBYfirst_active_date;这时候MIN(active_date)OVER(PARTITIONBYuser_id)就是在给每个用户打一个标签你第一次出现是哪一天然后再用DATEDIFF(active_date,first_active_date)判断这个用户第一次出现后的第 1 天、第 3 天、第 7 天有没有再次活跃。所以MIN() OVER()并不是写错了。只是我把它用错题了 另外严格来说MIN(active_date)得到的是“首次活跃日期”。只有当数据覆盖了用户完整生命周期或者业务上约定首次活跃就等价于新增时才能把它直接当作“新增日期”。七、最后复盘这道题现在回头看其实挺简单。日活留存今天所有来过的人N 天后还有多少人回来用当天活跃用户去LEFT JOINN 天后的活跃记录。新增留存今天第一次来的人N 天后还有多少人回来先用MIN(active_date)OVER(PARTITIONBYuser_id)找到首次活跃日期再计算后续留存。所以以后再碰到留存题我估计不会再第一时间想窗口函数了而是会先思考这个留存率的分母是谁这次属于典型的窗口函数秀起来了答案也一起秀没了 不过面试里写错一次确实比自己刷十道类似的题记得牢。