M5-006Hard会话与序列 · Sessions & sequences
如何计算每个用户的“最长连续登录天数”?
How do you compute each user's longest consecutive-day streak?
💡 考场高分一句话答案:
经典 gaps-and-islands:日期减去行号得到一个分组键,同一段连续日期会得到相同的键,再按组计数。
Gaps and islands: subtract the row number from the date to get a group key. Consecutive dates share the same key, then count within each group.
🧭 Playbook 核心提示 / Actionable Calibration:
同一模板的其他应用:连续达标天数、连续亏损月数、连续 N 天无事故、订阅连续续费期数。只要是“连续区间”问题,都是这个套路。
📌 核心考察要点 (Key Points)
- 核心技巧:DATE_SUB(dt, INTERVAL ROW_NUMBER() OVER (PARTITION BY user ORDER BY dt) DAY)
- 原理:连续日期的 dt 与行号同步增长,差值恒定;断档后差值跳变
- 先 DISTINCT:同一天多次登录要去重,否则行号会错
- 取最长:按 (user, grp) 计数后取 MAX
- Key trick: DATE_SUB(dt, INTERVAL ROW_NUMBER() OVER (PARTITION BY user ORDER BY dt) DAY)
- Why it works: for consecutive dates, dt and the row number advance together so the difference is constant; a gap shifts it
- DISTINCT first: multiple logins in one day would corrupt the row numbering
- Longest: group by (user, grp), count, then take MAX
🔍 大厂面试官连环追问 (Follow-ups)
- 如何找出“连续登录 ≥ 7 天”的用户?
- 如果按周(而非按天)判断连续,怎么写?
嫌字多看太累?进入苏格拉底式对话推演实验室
零打字负担,以大厂考官真实困境切入,通过点选直觉选项暴露思维盲区,手把手推导 Why。
⚡
Test muscle memory on this topic via 3-Layer Flashcards
Active Recall: Mentally formulate Prompt ➔ Math Derivation ➔ Benchmark Implementation in 3 minutes.