WEEK 03

需求变绕了：子查询、CTE 与章法

商品越上越多，公司上线了类目体系（categories 表进场）；运营的需求也从「统计一下」变成了「找出买过 A 又没买过 B 的用户」这种绕来绕去的活。你开始需要把复杂需求拆开、有章法地写的本事。
目标：拿到一个绕的需求，能拆成几步、有章法地写出来，而不是硬凑。
0 / 7 天
剧情 商品要归类了。类目是「电子 > 手机 > 配件」这样的树，存在 parent_id 里。第一个需求：按一级类目看销售。
练 · 65 min
  1. 跑 seed.sql「W3」段：建 categories 并回填 products.category_id
  2. 自连接输出「一级类目名 | 二级类目名」全表
  3. 按一级类目统计 GMV（order_items -> products -> categories 连两次）
  4. 给定一个二级类目，查出它的父类目
  5. 想想：为什么 products 不直接冗余一级类目 id？写两句利弊进 notes.md
过关按一级类目的 GMV 报表跑通，连接链能画出来。
专注视图 ->
剧情 老板：「找出消费高于平均水平的那批人，我给他们发券。」平均值本身就要一条查询来算--你第一次需要查询里套查询。
练 · 70 min
  1. 用标量子查询输出「每个用户消费额 − 全站平均消费额」
  2. 用派生表先按用户聚合订单数，再连接 users 输出
  3. 用 WHERE id IN (子查询) 查有过已支付记录的订单
  4. 写一个返回多行的标量子查询，记录报错信息
  5. 同一需求分别用派生表和 CTE 写，对比可读性
  6. 月报续集：商品 GMV 的 TOP10 及其占总 GMV 的比例（D13 缺的工具今天到货）
  7. 月报续集：复购用户数（下单 ≥ 2 次的用户）及复购率
过关能说出标量子查询在什么情况下会被执行 N 次（相关子查询）。
专注视图 ->
剧情 同行群里的事故通报：有人用 NOT IN 子查询查「没下过单的用户」，上线后返回空集，被运营当成故障投诉。你要彻底搞懂，别踩同一个坑。
练 · 60 min
  1. 用 IN、EXISTS、JOIN 三种写法查「下过单的用户」，对比 EXPLAIN
  2. 在子查询列里制造 NULL，复现 NOT IN 返回空集
  3. 把第 2 题改成 NOT EXISTS，验证结果正确
  4. 再用 LEFT JOIN ... WHERE b.id IS NULL 写第三遍
  5. 在 12 万行明细上跑三种反连接写法，做一张耗时对比表
过关手上有三写法耗时对比表；能完整讲出 NOT IN 遇 NULL 的推导过程。
专注视图 ->
剧情 老板在类目管理页点了「电子」，想看它下面所有层级类目的汇总销售。层级不固定，普通 JOIN 搞不定。
练 · 60 min
  1. 查每个分类的完整路径（如「电子 > 手机 > 配件」）
  2. 给定「电子」，查它的全部子孙分类及其销售汇总
  3. 用递归 CTE 生成 2026 全年日期序列（不用 generate_series）
  4. 算出每个分类所在的层级深度
  5. 自建 employees 表做组织架构下钻，输出每人的汇报链
过关不看资料写出一个递归 CTE，并逐段解释初始项和递归项各做什么。
专注视图 ->
剧情 运营的原话：「近 3 个月，每月新客的 GMV 和老客的 GMV 分开算。」你决定以后拿到需求先拆步骤再动手，用 CTE 把每一步写成能读的段落。
练 · 65 min
  1. D13 的最后一笔欠账：「每月新增用户数与当月下单用户数」--两段各自聚合，再对齐成一张表
  2. 写一个三级 CTE：清洗 -> 聚合 -> 排名
  3. 同一查询加与不加 MATERIALIZED，对比执行计划差异
  4. 「近 3 个月每月新客 GMV 与老客 GMV」：先写五步拆解，再写 SQL
  5. 写一个被引用两次的 CTE，观察是否被计算两次
过关能说清 PG 里 CTE 什么时候会被物化、物化意味着什么代价。
专注视图 ->
剧情 运营要做「每个类目热销 TOP3」看板。你上次用整表聚合再过滤的写法，数据一大就慢；这次学个新武器。
练 · 65 min
  1. 用 LATERAL 查每个用户最近 3 笔订单
  2. 用 LATERAL 查每个一级类目销量前 3 的商品
  3. 用 ROW_NUMBER 把第 2 题再写一遍（预习下周窗口函数语法）
  4. 用 DISTINCT ON 写 N=1 的版本
  5. 三种写法都跑 EXPLAIN ANALYZE，记录耗时做成对比表
过关能说出 LATERAL 在「分组多、每组取少量」场景下为什么可能更快。
专注视图 ->
剧情 三周了。本周测评改成白板模式--纸上手写、不许运行，模拟面试现场。
测评 · 110 min
  1. 60 分钟纸上手写 6 题（不许运行、不许查文档）--模拟白板环节
  2. 上机逐题验证，统计有几题一次通过
  3. 把写错的地方标红，归入 mistakes.md
  4. 对着镜子讲一遍其中最难那题的解题思路
过关6 题中至少 4 题手写版本能直接跑通。
专注视图 ->