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.
🎯 Drill This Card Now➔