M5-005Medium窗口函数 · Window functions🔥 大厂高频出现 1 次

如何计算周环比(WoW)增长率?

How do you compute week-over-week growth?
💡 考场高分一句话答案:

按周聚合成 CTE,再用 LAG 取上一周的值,用 SAFE_DIVIDE 算增长率。

Aggregate to weekly buckets in a CTE, then LAG to get the previous week and SAFE_DIVIDE for the growth rate.

📌 核心考察要点 (Key Points)

  • 周定义:DATE_TRUNC(d, WEEK(MONDAY)) 避免默认周日开始
  • LAG 的顺序:ORDER BY week 升序,LAG 取前一行
  • 首周为 NULL:增长率应为 NULL 而不是 0
  • 除零:用 SAFE_DIVIDE
  • Week definition: DATE_TRUNC(d, WEEK(MONDAY)); the default starts on Sunday and shifts everything
  • Ordering: ORDER BY week ascending so LAG reads the previous row
  • First week: the growth rate should be NULL, not zero
  • Division: always SAFE_DIVIDE

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

  • 如何计算同比(YoY)?
  • 如果某一周数据缺失,LAG 会取到两周前,怎么避免?

🎯 关联大厂考题实战训练 (1 题)

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

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