--- title: Pair from-plays rate numerator and denominator play populations type: task status: cancelled description: >- Team and player from-plays rates divide a stat_countable numerator by a scrimmage denominator, so special-teams production inflates seven team columns without its plays reaching the denominator. Declare the play population once per measure and have both the numerator filter and the rate denominator read it. base_uri: user:task/league/data-views/fix-from-plays-rate-population-mismatch.md created_at: '2026-09-11T15:14:17.854Z' entity_id: 81d534d2-935c-4fc2-a9f5-ab13c4a320b5 finished_at: '2026-09-11T16:55:58.761Z' observations: - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ owner_identity_uri: █████████████████████████ public_read: true relations: - follows [[█████████████████████████-████-████-███████████-████-███████████████]] - relates [[█████████████████████-██████████-███████████-███-████████-█████████████]] - relates [[user:task/league/reduce-data-view-generation-latency.md]] tags: - user:tag/league-xo-football.md updated_at: '2026-09-11T16:55:58.761Z' visibility_analyzed_at: '2026-11-06T00:17:45.761Z' --- ## CLOSED 2026-09-11 — do not execute the plan below This task is Abandoned, and the plan in it is retained as a record rather than as work. **Executing it as written would undo shipped work**: its verify target of BUF `0.12253` is the widened-denominator answer that `409599811` refuted. Where each item went: - **Core population fix** — shipped under a different design. `409599811` narrowed the team numerator to scrimmage at both chokepoints (goldens `f9bc87237`); `8f94426fe` restored the three count-of-plays columns via a per-measure `play_type_set` opt-in (goldens `be5a33e4c`). - **team_pass_play / team_rush_play** — RULED and GATED by the `████████████████████████████████████████` lane, not open. A period is a denominator UNIT and does not reach into a column's population (`eb26e46c8`, gated `9d9e90e0b`, cross-named `4fffd4633`). The narrowing this plan proposed is refuted by live data. - **Cache invalidation** — never belonged to this task's design. It is an escalated operator decision owned by that same lane on [[█████████████████████-██████████-███████████-███-████████-█████████████]]. - **Knowledge-base pairing** — documented in `84e634fe8` and corrected in `2d55dcce8`, which removed a false claim that player and player_team count-of-plays variants exist. - **Player-side path** — the one survivor, re-filed as [[user:task/league/data-views/pair-player-from-plays-rate-populations.md]] with a corrected scope. It supersedes this task rather than duplicating it. Everything below this section is the historical record. ## SUPERSEDED IN FLIGHT — read this before the plan below **2026-09-11: the defect this plan targets was implemented by a sibling while this plan was under review.** Session `███████████████████████████████████████` (started 14:46 UTC, six minutes after the finding was recorded on [[█████████████████████-██████████-███████████-███-████████-█████████████]]) narrowed the team numerator to the scrimmage set at both chokepoints. The work is UNCOMMITTED in the shared league tree. It took the **simplest-plan shape, not the per-measure `play_population` framework the Design below proposes** — a nine-dimension review independently reached the same conclusion, that only 3 of 23 columns move materially, so a declaration framework for 3 exceptions was over-built. **The Design below is superseded on that point. Do not implement it as written.** The reconciliation lane owns deciding what, if anything, survives from it. Also superseded: the plan coins `play_population`, but `play_type_set` already names this exact concept with these exact three values (`apply-play-type-filter.mjs:33,39`). The sibling correctly used `play_type_set`. Do not introduce a second name. ### Review findings still live against the shipped approach These came from the nine-dimension review of this plan and were NOT all addressed by the sibling's implementation. Each needs checking against the in-flight diff. - **Count-column semantic collapse.** Narrowing the team factory to scrimmage makes `team_offensive_play_count_from_plays` and `team_play_count_from_plays` return identical values — the sibling's own code comment records this. But `team_play_count_from_plays` is catalogued in `libs-shared/data-view-fields-index.mjs` as "includes all plays (passes, rushes, punts, field goals, extra point attempts, two point conversions, kickoffs, and free kicks)". After the change that description is false and the two columns are duplicates. 2025 REG: 40,395 to 33,327. `team_drive_count_from_plays` (5,733 to 5,697) and `team_series_count_from_plays` (14,603 to 14,531) are the same class. These three want the stat_countable population while epa / win_percentage_added / expected_points_success_rate want scrimmage, so one factory-wide set cannot serve both. - **CTE-name collision.** `get_per_team_play_cte_table_name` (`per-team-play.mjs`) hashes team_unit, play_type, group_by, matchup_opponent_type, attribution and week — NOT the play population. If any per-column population variation lands, two columns in one view collide on one denominator CTE name and one silently gets the other's denominator. - **No cache invalidation.** `get-data-view-hash.mjs` folds no code version, and prior-season from-plays carries a 30-day TTL (`get-cache-info-for-fields-from-plays.mjs`). 154 of 208 saved views reference a `_from_plays` column, so post-deploy the same view serves pre- and post-fix numbers indistinguishably for weeks. This is also the only rollback lever. Needs an explicit purge or namespace bump as a deploy step. - **Period-imposed population is still unpaired.** `play_type` is a PERIOD input, not a measure input (`add_per_team_play_cte({play_type})`), so under `team_pass_play` / `team_rush_play` the denominator is PASS-only or RUSH-only while the numerator spans the whole declared set. Any claim that numerator and denominator "cannot disagree" is false for those two periods. - **The catalog CONV description is independently stale.** `stat_countable_play_types` excludes CONV, so the "two point conversions" clause was already wrong before this change. - **Untracked-file trap.** `test/data-views.team-play-denominator-play-types.spec.mjs` is UNTRACKED. `git commit -- ` silently carries no new file, so the gate can be left out of the very commit that claims it. Verified measurements reusable by the reconciliation lane: league-average EPA/play is ~0.000 by construction and reads **-0.02134** pre-fix against **-0.00004** post-fix on 2025 REG — a population-level oracle that fails whenever numerator and denominator disagree. BUF 2025 REG decomposes exactly: stat_countable EPA sum 92.557806994901 over 1,082 scrimmage plays reproduces the pre-fix 0.08554325969953881; scrimmage-only sum is 132.574365335906. ## Overview - Make every from-plays rate divide a numerator and a denominator drawn from the **same play population**, by declaring that population once per measure and having both readers consume the one declaration. - Today the numerator is filtered to `stat_countable_play_types` (PASS, RUSH, KOFF, PUNT, FGXP, FREE) while the rate denominator counts `scrimmage_play_types` (PASS, RUSH). Special-teams production enters the numerator without its plays entering the denominator. - Impact: seven team columns return wrong numbers to every data-view consumer, every saved view, and every agent-generated view. EPA per play is wrong for 27 of 32 teams and league-average EPA per play reads -0.02134 where the metric is ~0.000 by construction. - Ordering: follows [[█████████████████████████-████-████-███████████-████-███████████████]], which built the three-set vocabulary and deliberately preserved the scrimmage denominators. Relates [[█████████████████████-██████████-███████████-███-████████-█████████████]], whose denominator lane covers user-supplied WHERE filters failing to reach the denominator — a different defect at a different layer, reproducing only with filters present, where this one reproduces with `where: []`. ## Background Discovered while grading generation `e115f723-2b35-4369-b98e-6c57cad30cd9` (instruction: "show me epa/play for nfl teams in 2025"). The agent's `table_state` was correct on every axis; the registry returned the wrong numbers. **The two filter sites.** - Numerator, rate path: `libs-server/data-views-column-definitions/team-stats-from-plays-column-definitions.mjs:174` — `query.whereIn('nfl_plays.play_type', stat_countable_play_types)` inside `final_apply_filters`, reached from `libs-server/data-views/output-aggregator/build-period-cte.mjs:543`. - Numerator, season render: `libs-server/data-views/add-team-stats-play-by-play-with-statement.mjs:74-78`, same set. Both must move together or the season value and the rate stop agreeing. - Denominator: `libs-server/data-views/period-denominator/per-team-play.mjs:124-131`, narrowed to `scrimmage_play_types` in the no-`play_type` branch. **The vocabulary is sound; the pairing was never checked.** `libs-shared/constants/play-type-constants.mjs` documents all three sets and its header says "Countability is per stat family, not one play-level fact... Pick the set that matches the question; do not merge them." Both it and `libs-server/data-views/apply-play-type-filter.mjs:18-20` warn in one direction only — against *widening a denominator* to a countability set. Neither contemplates a countability numerator divided by an offensive-snap denominator. `separate-play-role-attribution-from-countability.md:294-295` reached these exact denominators and ruled they "keep their semantics exactly... folding them into a countability set would inflate every rate denominator with the special-teams population." That ruling is correct and this plan does not disturb it: the repair is to narrow the numerator, never to widen the denominator. **Blast radius.** 23 team from-plays `column_id`s over 7 rate periods. Seven are live — `team_expected_points_added_from_plays`, `team_win_percentage_added_from_plays`, `team_expected_points_success_rate_from_plays`, `team_play_count_from_plays`, `team_drive_count_from_plays`, `team_series_count_from_plays`, `team_series_conversion_rate_from_plays`. The remainder are structurally zero on special teams and numerically unaffected. `libs-server/data-views/period-denominator/per-player-play.mjs:62,88-94` reproduces the identical structure against the same `stat_countable` numerator. **A second, independent defect in the same columns.** `team_success_rate_from_plays` and `team_explosive_play_rate_from_plays` are ratio-shaped measures whose own internal `COUNT(*)` denominator is special-teams-inclusive while their numerator is structurally zero there. Both read roughly 21% low even with no rate period applied. This plan fixes it as a consequence of narrowing the population, but it is a distinct failure and is verified separately. **Industry anchor.** The nflfastR vignette and beginner's guide compute offensive EPA per play over `rush == 1 | pass == 1`, excluding punts, field goals, kickoffs and extra points. A charting supplier publishes EPA/Play beside EPA/Pass and EPA/Rush over the same universe. nflfastR prefers its `rush`/`pass` indicator columns over `play_type` because `play_type` codes penalty plays as `no_play`; that refinement is **not adoptable here** and is not in scope (see Assumptions). ### Assumptions - The rate numerator for team from-plays columns is filtered at `team-stats-from-plays-column-definitions.mjs:174`, not only at the with-statement chokepoint. Checked: read both sites; the with-statement is reached for the season render, the `apply_filters` hook for the rate numerator CTE. - `seas_type` defaults to `["REG"]` when unset, so the graded view was regular-season only and the defect is not a season-type artifact. Checked: `data-view-describe-column.mjs` reports `default_value: ["REG"]`. - League-average offensive EPA per play is ~0.000 by construction and is therefore a valid population-level oracle. Checked: fixed population returns -0.00004 against the current -0.02134 on 2025 REG. - `team_play_count_from_plays`, `team_drive_count_from_plays` and `team_series_count_from_plays` are intended to count ALL plays, not scrimmage plays, so a blanket narrowing would corrupt them. Checked: the catalog description of `team_play_count_from_plays` reads "includes all plays (passes, rushes, punts, field goals, extra point attempts, two point conversions, kickoffs, and free kicks)". Note that same description is already stale — `stat_countable_play_types` excludes CONV, so two-point conversions are not in fact counted. - Widening `scrimmage_play_types` to the nflfastR-canonical "pass or rush attempt including penalty no-plays" is NOT implementable on this schema. Checked: `is_passing_play` and `is_rushing_play` are populated on 0 of 11,426 `NOPL` rows in 2025 REG; only 28 carry `is_qb_dropback`. UNCHECKED whether an importer change could populate them; out of scope here. - Changing the population changes stored/exported values for affected columns, so published `league-data` exports become stale. UNCHECKED which exports carry these columns; the plan gates on measuring this before any republish. ## Design **One declaration, two readers.** Each entry in `stat_specs` gains an optional `play_population` naming one of the three sets. `final_apply_filters` reads it for the numerator instead of hardcoding `stat_countable_play_types`, and the per-team-play denominator CTE reads the same declaration instead of hardcoding `scrimmage_play_types`. After this change a numerator and its denominator cannot disagree, because there is only one place the population is stated. - Default is `scrimmage` — the population that makes a per-play rate mean what readers expect, and what the industry computes. - `team_play_count_from_plays`, `team_drive_count_from_plays`, `team_series_count_from_plays` declare `stat_countable`, preserving their all-plays intent. Their per-play rate then divides an all-plays numerator by an all-plays denominator and correctly returns 1.0 for the play count. - `team_offensive_play_count_from_plays` currently encodes the same idea as an inline `CASE WHEN play_type IN ('PASS','RUSH')` in its measure expression. That is the transitional-cruft form of this declaration; it becomes `SUM(1)` with `play_population: 'scrimmage'` and the inline CASE is deleted. - The denominator branch taking an explicit `play_type` (`team_pass_play`, `team_rush_play`) is unchanged — a per-unit rate declaring its own unit is the intended semantics, not the defect. - The same declaration and the same two readers are applied to the player-play path. The `scrimmage_play_types` / `stat_countable_play_types` / `non_nullified_play_types` membership is NOT changed. This plan changes only which set each measure points at, which keeps `████████████████████████████████████████████████`'s ruling intact and keeps the blast radius inside the data-view layer. **Naming.** `play_population` passes [[user:guideline/write/write-software.md]]: full words, searchable, and it names the distinguishing property (which plays the measure is computed over) rather than restating the type. Verified free — no occurrence anywhere in the repo. Its values reuse the existing set names already accepted by `apply_play_type_filter` (`scrimmage`, `stat_countable`, `non_nullified`), so no new closed set is coined. **Knowledge Base Impact.** - `libs-shared/constants/play-type-constants.mjs` and `libs-server/data-views/apply-play-type-filter.mjs` both carry the one-directional warning that missed this class; both need the pairing rule stated. - The `team_play_count_from_plays` catalog description is stale on CONV independently of this change and is corrected here. - Observations already recorded on [[█████████████████████-██████████-███████████-███-████████-█████████████]]; a `relates` edge to this task closes the loop. - No new domain entry document and no `ABOUT.md` change — this is a defect repair inside an existing subsystem. **Working tier.** No scratch slug needed. The fixture regeneration is in-repo and the measurements are single queries recorded in this body. **Shape.** Bounded enough for one task entity; no orchestrator. ## Notes - Fixture surface is large: roughly 143 files under `test/data-view-queries/` embed the scrimmage literal and roughly 138 embed the stat_countable literal, 58 of those naming a `team_*_from_plays` column. `scripts/update-data-view-snapshots.mjs` regenerates them. Cite the counts from a command rather than trusting these figures — they drift. - `separate-play-role-attribution-from-countability.md:299` records that the regenerator SKIPS template fixtures while reporting `template-skipped` in a line that reads like success. Hand-check those. - Deploys are human-gated. This plan lands code and fixtures; it does not deploy. ## Plan ### Core Implementation - [ ] **Declare the population on each team from-plays measure** - File: `libs-server/data-views-column-definitions/team-stats-from-plays-column-definitions.mjs` - Purpose: put the play population in one declared place per measure instead of two hardcoded filter sites that can disagree. - Changes: add an optional `play_population` to each `stat_specs` entry, defaulting to `scrimmage`; set `stat_countable` on `team_play_count_from_plays`, `team_drive_count_from_plays`, `team_series_count_from_plays`; replace the hardcoded `whereIn(..., stat_countable_play_types)` at `:174` with the declared value routed through `apply_play_type_filter`. - Verify: `NODE_ENV=production node scripts/data-view-preview-view.mjs` on the EPA-per-team-play state returns BUF `0.12253` (was `0.08554`); and `team_play_count_from_plays` per `team_play` returns exactly `1.0` for every team. - [ ] **Read the same declaration in the rate denominator** - File: `libs-server/data-views/period-denominator/per-team-play.mjs` - Purpose: close the mismatch structurally so a future measure cannot reintroduce it. - Changes: thread the column's `play_population` into the CTE builder and use it in the no-`play_type` branch at `:124-131` in place of the hardcoded `scrimmage_play_types`; leave the explicit-`play_type` branch untouched. - Verify: emitted SQL for a `scrimmage` measure is byte-identical to HEAD (`scripts/data-view-regression-check.mjs`); emitted SQL for `team_play_count_from_plays` now shows the stat_countable set in BOTH numerator and denominator. - [ ] **Move the season-render chokepoint onto the same declaration** - File: `libs-server/data-views/add-team-stats-play-by-play-with-statement.mjs` - Purpose: a season total and a rate over the same measure must describe the same plays. - Changes: replace the fixed `play_type_set: 'stat_countable'` at `:74-78` with the measure's declared population. - Verify: for each of the seven live columns, the plain season value equals the rate multiplied by the period count, asserted in a query over 2025 REG. - [ ] **Retire the inline population in `team_offensive_play_count_from_plays`** - File: `libs-server/data-views-column-definitions/team-stats-from-plays-column-definitions.mjs` - Purpose: remove the cruft form of the declaration this plan introduces; leave one way to express a population. - Changes: replace the `CASE WHEN play_type IN ('PASS','RUSH') THEN 1 ELSE 0 END` accumulator with `SUM(1)` plus `play_population: 'scrimmage'`. - Verify: the column's values are unchanged across 2023-2025 by a before/after query; no `CASE WHEN play_type` literal remains in the file. - [ ] **Apply the same pairing to the player-play path** - File: `libs-server/data-views/period-denominator/per-player-play.mjs`, `libs-server/data-views/add-player-stats-play-by-play-with-statement.mjs`, `libs-server/data-views-column-definitions/player-stats-from-plays-column-definitions.mjs` - Purpose: the identical structure exists there against the same `stat_countable` numerator, and the player EPA columns are the live class. - Changes: mirror the declaration and the two readers; enumerate the player columns non-zero on special teams before setting any non-default population. - Verify: player EPA per player-play changes and every structurally-zero player column is unmoved, both shown by a before/after query over 2025 REG. - Note: `per-player.mjs` (the action-denominator family) is already consistent at `:66-69` and is deliberately untouched. ### Knowledge Base Updates - [ ] **State the pairing rule where the one-directional warning lives** - Files: `libs-shared/constants/play-type-constants.mjs`, `libs-server/data-views/apply-play-type-filter.mjs` - Changes: record that a rate's numerator and denominator must name the same set, and that the failure is silent in both directions — not only when a denominator is widened. (no verify — documentation edit) - [ ] **Correct the stale `team_play_count_from_plays` description** - File: `libs-shared/data-view-fields-index.mjs` - Changes: the description claims two-point conversions are counted; `stat_countable_play_types` excludes CONV. (no verify — documentation edit) - [ ] **Add the `relates` edge to the owning vocabulary task** - Changes: `relates` to [[█████████████████████-██████████-███████████-███-████████-█████████████]], which already carries the finding observations. (no verify — entity edit) ### Testing and Validation - [ ] **Population-level oracle: league-average EPA per play returns to zero** - Purpose: catches the whole defect class in one number, independently of any single team's value. A per-team spot check cannot distinguish a corrected population from a shifted one; the league average can only sit at zero if the numerator and denominator describe the same plays. - Check: over 2025 REG, league-average `team_expected_points_added_from_plays` per `team_play` must move from -0.02134 to within 0.001 of 0.000. Reproduce with: `SELECT SUM(epa) FILTER (WHERE play_type IN ('PASS','RUSH')) / COUNT(*) FILTER (WHERE play_type IN ('PASS','RUSH')) FROM nfl_plays WHERE season_year=2025 AND season_type='REG' AND offense_nfl_team IS NOT NULL` - Failure mode caught: numerator and denominator drawn from different populations, in either direction. - [ ] **Prove the check can fail** - Purpose: a verify that cannot go red is not a verify. - Check: revert the numerator declaration alone and confirm the league-average assertion goes red at -0.02134, then restore. - [ ] **Confirm the ratio-shaped measures separately** - Purpose: `team_success_rate_from_plays` and `team_explosive_play_rate_from_plays` were ~21% low through their own internal `COUNT(*)`, which is a different failure from the rate-period mismatch and would otherwise be masked by it. - Check: both columns rise by the expected factor on 2025 REG with no rate period applied, and the numerator/denominator row counts for each are equal. - [ ] **Regenerate and hand-check the fixture surface** - Check: run `scripts/update-data-view-snapshots.mjs`; count the affected fixtures from a command rather than a stored number; confirm zero fixtures report `template-skipped` without being hand-reviewed, per the trap recorded on `separate-play-role-attribution-from-countability.md:299`. - [ ] **Full data-view suite green** - Check: `test/libs-server.data-view-queries.mjs` and `test/libs-server.data-view-queries-result-equivalence.mjs` pass, with the result-equivalence fixtures showing every structurally-zero column unmoved. - [ ] **Measure export staleness before any republish** - Purpose: the affected columns may be published in `league-data`, whose exports are replaced wholesale rather than amended. - Check: determine whether any export carries the seven affected columns; if so, record the finding and surface the republish as an operator decision rather than performing it.