Snowflake SQL：参与超过 90 天已完成项目的员工

题目重点：DE 电面明确问 ever completed a project lasting more than 90 days；本文拆开项目时长、完成状态与员工去重，并用随机日期独立对照。

题意与假设

这道练习题保留了面经中值得训练的核心约束，并将需要澄清的条件单独列出。现场拿到变体时，先和面试官确认这些条件。

训练 schema：employees(id,name)、projects(id,start_date,end_date,status)、assignments(employee_id,project_id)。项目 status='completed' 表示已完成，ISO 日期按 UTC 日历日比较。
项目时长为 end_date−start_date 的整日差，严格大于 90；同一员工多项目或重复 assignment 只返回一次。若要求把首尾两天都计入，91/90 天边界要重算。
姓名可以重复，真实系统应返回员工 ID 加姓名；题目说 return name，本训练查询为方便展示只选 name，故同名员工会被合并，生产实现需改。

解题路线

先筛选 status 已完成且 end_date 存在、时长严格大于 90 天的 projects；日期差计算依数据库方言更换，SQLite 用 julianday。
用 assignments 连接 employees，只要员工参与一个合格项目即可；DISTINCT 消除重复分配行和多项目重复。
先决定是否在项目层预筛选以减少 join，再检查 projects(id)、assignments(project_id,employee_id) 索引和执行计划；对大表可用 EXISTS 避免宽 join 去重。
用独立 Python date 差值参考模型随机生成项目与重复 assignment，核 SQL 输出；测试恰好 90、91、未完成、NULL 结束和重复行。

手动走一遍

项目 P1 从 1 月 1 日到 4 月 1 日为 90 天，不满足 >90；P2 到 4 月 2 日为 91 天且 completed，满足。员工 A 对 P2 有两条 assignment，结果仍只有 A 一次；员工 B 只参与 P1 则不返回。

SQL 参考实现

下面的代码按上文假设实现，适合本地运行并追加自己的测试用例。

代码区可横向滚动；使用键盘时，先聚焦代码区再按方向键查看。

-- Training schema: employees(id,name), projects(id,start_date,end_date,status),
-- assignments(employee_id,project_id). Dates use ISO-8601 UTC calendar dates.
SELECT DISTINCT e.name
FROM employees AS e
JOIN assignments AS a ON a.employee_id = e.id
JOIN projects AS p ON p.id = a.project_id
WHERE p.status = 'completed'
  AND p.end_date IS NOT NULL
  AND julianday(p.end_date) - julianday(p.start_date) > 90
ORDER BY e.name;

复杂度

若顺序扫描 P 项目、A 分配、E 员工，通常 O(P+A+E) 读取再加去重排序 O(R log R)；索引或哈希计划会改变实际成本。SQL 优化器的执行顺序与数据分布相关，需看 EXPLAIN。结果 R 为符合条件的员工数。

易错点与追问

容易失分的边界

把 >=90 写成 >90 或反之而没测边界。
忽略完成状态，返回仍在进行中的长项目员工。
直接 join 后不去重，员工因多个项目出现多次。
按字符串字面比较日期或把 NULL end_date 当当前日期。
以姓名为唯一身份，误合并两个同名员工；训练查询应在真实数据里返回 ID。

继续深挖

若一名员工只参与项目后半程，90 天是项目总时长还是本人参与时长？
若 end_date 是 timestamp 且跨时区，按 90×24 小时还是日历天？
如何用 EXISTS 改写以减少 DISTINCT 成本？