--- title: Design the same-column difference tier, and refuse the general one type: task status: draft description: >- Ruling on GitHub issue 212: build the same-column, param-divergent difference (the PPG vs PPG N+1 case) and refuse a general binary-operation column tier, because the issue's own headline example shows a generic subtraction produces sign-wrong figures over mismatched role sets. base_uri: user:task/league/design-same-column-difference-tier.md created_at: '2026-09-23T21:17:06.472Z' entity_id: f16c2f35-0717-4724-b7d5-4b4529246bb0 owner_identity_uri: █████████████████████████ priority: Low public_read: true relations: - relates [[user:task/league/add-points-over-expected-column.md]] - relates [[██████████████████████-████████-█████-████████]] - relates [[user:text/league/expected-stats-model.md]] tags: - user:tag/league-xo-football.md updated_at: '2026-09-23T21:18:49.748Z' visibility_analyzed_at: '2026-11-05T23:49:08.722Z' --- ## The ruling **Design the narrow case. Refuse the general tier.** GitHub issue 212 asks for a generic "subtract one column from another" parameter, by analogy to the rate stats. Build the same-column, param-divergent difference only. Do not build a general binary-operation or formula-column tier. This is a design ruling, not an implementation plan. Nothing is started. ## Why the issue's premise does not hold The issue says "same as the rate stats but instead of dividing one number by another you subtract one from another." That analogy is the thing to reject. `rate_type` — now the `output` param — picks a **denominator unit for ONE column**. It never introduces a second operand; it reinterprets a single measure against a chosen per-unit base. A difference needs **two column INSTANCES**, which is a tier of the data-view system that does not exist. Confirmed by grep across the tree: no `diff_type`, no derived-column, computed-column or formula-column machinery anywhere. So this is not a parameter. It is a new kind of column, and the cost is the design surface below rather than the subtraction itself. ## Why the headline example argues AGAINST the general feature The issue's first example, fantasy points over expected, is shipped — `player_points_over_expected_from_plays`, league `5bb94b66e`, live on both hosts. **It could not have been built as a generic subtraction, and that is the finding that should govern this issue.** The expected column scores passing, rushing and receiving only; the lateral and stat-sourced roles (kicking, returns, two-point conversions, fumbles lost) carry no fitted model and are dropped. The actual column scores all of them. Subtracting the two shipped columns therefore leaves every return touchdown, two-point conversion and lost fumble standing in the result as production over expectation — 192 of 699 2024 skill-position player-seasons, biased in both directions at once, so it neither cancels nor corrects by a constant. KaVontae Turpin's 2024 reads `-2.14` where the subtraction reads `+8.86`: the naive figure gets his **sign** wrong. The shipped column is correct because it takes the difference PER SCORING ROLE, over exactly the roles carrying a model. That role-matching is knowledge only the column author has. Nothing in a generic subtraction tier could have supplied it, and nothing in such a tier could detect its absence. **A general binary-operation tier would let any user construct exactly that invalid figure, at scale, with the system unable to flag it.** It industrialises the defect the shipped column exists to avoid. That is the argument against, and it comes from the issue's own headline example. The issue's second example points the same way: "WOs and FPTs DIFF" subtracts weighted opportunities from fantasy points — incommensurable units. That it appears as a peer example is evidence the general request is under-specified by the requester, not that it is wanted as stated. ## What IS worth building The third example, "PPG and PPG N+1 DIFF", is well-posed, and it is well-posed for a reason that generalises usefully: **the two operands are the SAME column, differing in exactly one param.** That single restriction dissolves most of the design surface: - Commensurability holds by construction — same measure, same unit, same scoring format. - Operand identity is trivial. There is one `column_id`; the operands are distinguished by their param tuple. - Role-set mismatch, the defect above, cannot arise. Both operands are built by the same builder over the same roles. Scope it to `year_offset` divergence first. That covers the requested example and is the only divergence with an established meaning in the system today. ## Open design questions — settle these before writing code 1. **Which params may diverge.** Start with exactly `year_offset`. Widening later is additive; starting wide is not retractable, because of the id permanence below. 2. **Null when one operand is absent.** N+1 does not exist for the current season. The difference must be NULL, and NULL must stay distinguishable from a real zero — a zero difference and a missing operand are opposite findings. Note the shipped column's own lesson here: the HAVING predicate had to move from `SUM(...) > 0` to `<> 0`, because on a signed measure the unsigned form deleted every under-performing player and the left join served NULL. A missing row reads as a missing player, not as a visible error. 3. **Meaning under an `output` aggregation.** Difference-of-rates and rate-of-differences are not the same quantity. Pin which one is served; do not let it emerge from the order the CTEs happen to compose in. 4. **The id scheme for a derived instance.** Column ids are PERMANENT — they persist in immutable short URLs. Whatever encodes "this column minus this column at offset N" into an id is a one-way decision, and it is the single hardest-to-reverse part of this work. 5. **Bonus and milestone terms stay out**, if any operand is an expected-bearing column. `E[1{Y >= 40}] != 1{E[Y] >= 40}` is a statistical limit rather than a scope choice. ## What to tell the issue Do not close 212 on the shipped column. It serves the headline example only, and the plan entity for the expected-stats model wrongly claimed otherwise — that claim has been corrected. Per house rule, nothing is posted to GitHub; this entity is the record.