M5-017Hard会话与序列 · Sessions & sequences

如何取每个用户最近的两个事件?

How do you get each user's two most recent events?
💡 考场高分一句话答案:

用 ROW_NUMBER 编号后取 rn<=2,再按用户聚合成两列;或用 ARRAY_AGG 排序后取前两个元素。

ROW_NUMBER ordered by time descending, keep rn ≤ 2, then pivot into columns with conditional aggregation; or ARRAY_AGG with a limit.

📌 核心考察要点 (Key Points)

  • 方式一:ROW_NUMBER 后 WHERE rn <= 2,再 PIVOT 成两列
  • 方式二:ARRAY_AGG(STRUCT(...) ORDER BY ts DESC LIMIT 2)
  • 用途:分析“最后一次行为 → 流失”、事件序列模式
  • 注意:同一时间戳的多个事件需要次级排序键
  • Approach one: ROW_NUMBER then WHERE rn <= 2, then MAX(IF(rn = 1, ...)) to pivot
  • Approach two: ARRAY_AGG(STRUCT(...) ORDER BY ts DESC LIMIT 2)
  • Use: last action before churn, event-sequence patterns
  • Note: events sharing a timestamp need a secondary sort key

🔍 大厂面试官连环追问 (Follow-ups)

  • 如何统计“最后事件是 X 的用户数”?
  • ARRAY_AGG 方式怎么写?

嫌字多看太累?进入苏格拉底式对话推演实验室

零打字负担,以大厂考官真实困境切入,通过点选直觉选项暴露思维盲区,手把手推导 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➔