Snowflake SQL：多人项目预算如何分摊后求员工平均

题目重点：DE 题目说明一个项目多名员工、每人分得预算份额；用去重、项目团队人数和两级聚合解释 average budget 口径。

题意与假设

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

训练 schema：projects(id,budget)、assignments(employee_id,project_id)。每项目 budget 平均分给该项目不同员工；重复 assignment 是脏数据，先去重。
训练输出为每名员工跨自己参与项目的 budget_share 算术平均，忽略 budget 为 NULL 的项目；没有有效项目的员工不返回。
题目的 average budget per employee per project 也可能要求逐员工逐项目 share 而非再按员工平均；本文先展示中间 per_employee_project CTE，使两种输出仅差最后一层聚合。

解题路线

第一层 DISTINCT (employee_id,project_id)，否则重复行会同时抬高团队人数和该员工的权重。
第二层按 project_id 算 team_size，JOIN 项目 budget，算每个员工在该项目的 budget/team_size；若预算不应均分，必须给权重表与约束。
第三层按 employee_id AVG(budget_share)；若实际要求每员工每项目一行，直接 SELECT per_employee_project 即可。
用独立 Python 集合去重和浮点分摊对照随机 SQLite 数据；测试 1 人项目、重复 assignment、NULL budget、零预算和同员工多项目。

手动走一遍

P1 预算 120，A/B 两人各得 60；P2 预算 90，A 独自得 90。A 的跨项目平均份额为 (60+90)/2=75，B 为 60。若 A-P1 有两条 assignment，去重后团队人数仍为 2。

SQL 参考实现

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

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

-- Training interpretation: split each project's budget equally among distinct
-- assigned employees, then average a person's per-project shares.
WITH unique_assignments AS (
  SELECT DISTINCT employee_id, project_id FROM assignments
),
project_team_sizes AS (
  SELECT project_id, COUNT(*) AS team_size
  FROM unique_assignments
  GROUP BY project_id
),
per_employee_project AS (
  SELECT a.employee_id, a.project_id,
         1.0 * p.budget / t.team_size AS budget_share
  FROM unique_assignments AS a
  JOIN projects AS p ON p.id = a.project_id
  JOIN project_team_sizes AS t ON t.project_id = a.project_id
  WHERE p.budget IS NOT NULL
)
SELECT employee_id, AVG(budget_share) AS average_budget_share
FROM per_employee_project
GROUP BY employee_id
ORDER BY employee_id;

复杂度

A 条 assignment 去重通常需 O(A log A) 排序或期望 O(A) 哈希；项目团队计数与份额聚合再各扫描去重后的边。临时状态 O(A+P+E)，实际执行计划受索引、分区和数据库优化器影响。

易错点与追问

容易失分的边界

直接把总 project budget 记给每个参与员工，造成预算重复计量。
未去重 assignment，项目团队人数和员工平均都被重复行扭曲。
AVG(project.budget) 与 AVG(project.budget/team_size) 混淆。
把 NULL 预算当 0，改变有值项目的平均。
未确认题目最后要逐员工平均还是逐员工逐项目明细。

继续深挖

若每人贡献比例不同，如何校验权重和为 1 并分摊？
若预算按项目年份变化，如何做时态 join？
如何同时返回平均、总份额和参与项目数而不重复计算 CTE？