--- title: Extend the Expected Points & Opportunity view on the players page type: task status: completed description: >- Decide which further columns the Expected Points & Opportunity view on the league players page should carry, and add the ones that earn their place. The view shipped 2026-09-24 with six columns and per-position shading; four rank columns sit unused on the payload, excluded for a stated sort-order reason. base_uri: user:task/league/extend-expected-points-and-opportunity-view.md created_at: '2026-09-24T23:38:18.021Z' entity_id: 38bf312d-0799-4e9d-9d19-df0e0d8dc515 finished_at: '2026-09-24T23:49:51.635Z' observations: - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ owner_identity_uri: █████████████████████████ public_read: true tags: - user:tag/league-xo-football.md - user:tag/software-task.md updated_at: '2026-09-25T02:19:12.637Z' visibility_analyzed_at: '2026-11-05T23:49:13.932Z' --- ## Overview Decide which further columns the Expected Points & Opportunity view on `/leagues/:lid/players` should carry, and add the ones that earn their place. The view shipped 2026-09-24 with six columns and per-position percentile shading. It was scoped to what was already on the `/api/players` payload and unambiguous to read; several candidates were left out for stated reasons rather than overlooked, and the payload carries more than the view shows. ## What exists now The view lives in `app/core/players/default-players-views.js`. Its fields are declared in `app/core/player-fields.js`, and its shading comes from `app/core/players/expected-points-percentiles.mjs`, which computes per-position breakpoints in the browser over the players already in the store and writes them to the percentiles slice under `PLAYER_SEASON_EXPECTED_POINTS_`. Six columns, all flat scalars selected onto the player row by `libs-server/get-players.mjs` from `scoring_format_player_seasonlogs`: | header | column | | --- | --- | | `xFP` | `expected_points_from_plays` | | `xFP/G` | `expected_points_per_game_from_plays` | | `FP` | `seasonlog_points` | | `FPOE` | `points_over_expected_from_plays` | | `xFP%` | `expected_points_share_from_plays` | | `xFP% PCTL` | `expected_points_share_percentile_from_plays` | ## Candidates already on the payload `get_players.mjs` selects four rank columns the view does not render — `expected_points_rank_from_plays`, `expected_points_position_rank_from_plays`, `expected_points_per_game_rank_from_plays`, `expected_points_per_game_position_rank_from_plays` — plus `expected_points_share_position_rank_from_plays`. They were excluded for a specific reason, not by oversight. Ranks are ascending-better, and `descendingComparator` in `app/core/players/selectors.js` puts nulls first on an ascending sort, so clicking a rank column to sort it the useful direction leads with the unranked. Shipping them well means changing shared sort behavior, which is its own piece of work. `PercentileMetric` already supports `invert_order` for lower-is-better fields, so the shading half is solved. ## Candidates needing new work The available-opportunity atom — `nfl_player_route_target_probability` and its four `available_expected_*` columns, 2025 onward — is a genuinely different measure from realized opportunity: what a player was available for rather than what they got. It is owned by `user:task/league/add-available-opportunity-to-utilization-components.md`; coordinate rather than duplicate. Route share and snap share are named in that same task. ## Constraints any addition inherits - Every column in this view is joined at `current_season.year` and the league's `scoring_format_id`, so none of them responds to the page's year, week, quarter or down filters. That is why the view is separate from the three `stats.` views. A column that does not share this grain does not belong here. - The share denominator excludes the passing role, so a team's shares sum to 100 across its touch competitors rather than the whole offense. A quarterback and a running back are not on one scale. - The percentile and position-rank columns are null below the qualifying floor and at every position outside RB, WR and TE. - `test/app.percentile-field-consumer-resolution.spec.mjs` grades this view's percentile requests against the computed vocabulary. A new shaded column must be added to `EXPECTED_POINTS_PERCENTILE_FIELDS` or the spec reports it unserved. ## Plan - [x] Decide which of the four rank columns, if any, earn a place, and whether the sort-null behavior is fixed here or deferred - [x] Review the rest of the `scoring_format_player_seasonlogs` and payload surface for columns worth adding - [x] Coordinate with the available-opportunity task before adding anything from that family - [x] Add the columns that pass, with shading and header tooltips - [x] Browser-verify the view per `█████████████████████████████████████████` ## Outcome Two columns added in `e823c11d5`: `FP/G` (`points_per_game`) and `G` (`games_played`). | header | column | shaded | | --- | --- | --- | | `G` | `games_played` | no | | `FP/G` | `points_per_game` | yes | **FP/G** completes the pairing the view was missing. It carried xFP beside xFP/G but FP with no rate, so comparing earned output to the rate of opportunity meant doing the division by eye. The column was already selected onto the payload and simply unrendered. **G** leads the view. Both per-game columns divide by it and nothing on the page said so; through the opening weeks of 2026 that denominator is 1 for 698 players and 2 for 4620, which is when a per-game rate is least safe to read and looks no different from a settled one. It is the only unshaded column in the view — availability is not performance, and a ladder over it would assert something the view does not measure. ### Second pass: the role and usage columns Four more added in `47e15cba4`, answering what a player's opportunity CONSISTED of rather than what it was worth. | header | column | source | shaded | | --- | --- | --- | --- | | `TGT%` | `team_target_share` | `player_receiving_seasonlogs` | yes | | `ROUTE%` | `route_share` | `player_receiving_seasonlogs` | yes | | `RUSH%` | `rush_share` | `player_rushing_seasonlogs` | yes | | `aDOT` | `average_route_depth` | `player_receiving_seasonlogs` | no | These COUNT opportunity where the expected-points columns VALUE it, and the gap between the two is the read: a 44 percent target share against a 30 percent expected-points share is a role built on cheap targets. `TGT%` and `ROUTE%` are a pair — alone, a small target share cannot separate a player who is off the field from one who is on it and ignored. `aDOT` is unshaded for the reason `G` is: depth describes a role rather than ranking one. **The two share scales disagree in the database**, and this is the defect the pass nearly shipped. `route_share` is `numeric(5,2)` and already a percent (1.33 to 95.89 on 2026), while `team_target_share` and `rush_share` are `numeric(5,4)` fractions (0.0130 to 0.4400 and 0.0143 to 0.8491). Rendered as stored, a row would have read `TGT% 0.44` beside `ROUTE% 95.89` and `xFP% 38.02` — three scales under two identical-looking headers. The fractions are scaled in the SELECT, not at render: the percentile ladder is computed on whatever the payload carries, so normalizing at render would rank on the raw fraction while painting the scaled one, and would also force `player_value_path` off the bare column name the consumer spec requires it to match. **Both joins pin `season_type = 'REG'`**, which is load-bearing rather than tidy. Each table carries a POST row per player, and unpinned the join returns 751 rows for 592 players on 2025 against exactly 592 pinned. REG is also what makes these agree with the columns beside them: `scoring_format_player_seasonlogs` is regular-season only, measured on 2025 where its 17-game players have exactly 17 REG gamelogs each and 169 POST gamelogs it never counts. That also confirms `G` is a REG count. **Excluded deliberately.** `weighted_opportunity` and `weighted_opportunity_rating` restate `xFP%` less well, and `███████████████████████` names the former as the metric it exists to retire (four buckets, PPR-only, a red-zone cliff at the 20) — shipping a column another lane is deprecating would be a mistake. Red-zone volume lives in two tables on two scales and needs a derivation rather than a column. Nothing was taken from the available-opportunity family: that atom is blocked on its model, and its route population carries a known 16.4 percent contamination. ### Fourth pass: the situational columns came back out, and became data views `RZ`, `GL` and `2MIN` were removed in `072c29e20`. `SNP` stayed. Five situational data views landed instead in `9fdef920e`. **Why they were the wrong shape here, not merely surplus.** Every column in this view is frozen at the season, while the players page carries yardline, quarter and down filters that move the `stats.` views beside it. The yardline control is a continuous 0-100 range slider, so a reader can set it to 0-20 directly. A column NAMING a situation the reader can filter on is therefore the sharpest form of the frozen-column failure this view's separation exists to prevent: drag the slider and the stats columns respond while `RZ` sits still, under a header saying the same word as the control that just moved. `SNP` is not that. It names no filterable situation, so it reads like `G` and the shares — a season descriptor of the role, not a claim about a slice the reader can re-cut. The snap join narrowed to that one column and was renamed `libs-server/join-player-snaps.mjs`; its header keeps the list of situational columns still sitting in `player_gamelogs` for whoever wants them. **The five views**, in `app/core/data-views/default-data-views.js`, each carrying the same eight-column opportunity line filtered to its slice: | view | filter | | --- | --- | | Red Zone Opportunity | `yard_line_100: [0, 20]` | | Goal Line Opportunity | `yard_line_100: [0, 5]` | | Two Minute Offense | `seconds_remaining_half: [0, 120]` | | Short Yardage | `yards_to_go: [1, 2]` | | Late Down Usage | `down_number: [3, 4]` | The column line spans the rush and receiving roles rather than splitting by position, because the interesting comparison inside the five is exactly the one between a back's carries and a tight end's targets. **A correction I had to make mid-pass.** `072c29e20`'s message claims a red-zone view cannot be built on today's params — that the field-position param is `yard_line_number` and the 0-100 one is an unimplemented TODO. Both halves are false. `yard_line_100` has been a live RANGE param since 2026-09-06 with `Redzone`, `Inside 10`, `Inside 5` and `Between 20s` presets. I missed it with a grep whose character class excluded digits, so the name could not match, then wrote the absence up as a finding. The 39.8 percent contamination figure is real but describes `yard_line_number`, which no correct view would use — it is the reason to prefer `yard_line_100`, and the spec now pins that choice. ### Third pass: situational snap usage Four more in `1cf4437a9`, under a new `USAGE` group. The view now reads in three bands — EXPECTED POINTS, OPPORTUNITY, USAGE. | header | column | meaning | | --- | --- | --- | | `SNP` | `offense_snaps` | total offensive snaps | | `RZ` | `redzone_snaps` | snaps inside the opponent 20 | | `GL` | `goal_line_snaps` | snaps inside the opponent 5 | | `2MIN` | `two_minute_snaps` | snaps under two minutes in a half | The shares size a role; these locate it. Kittle on 2026 reads 55 snaps with 19 inside the 20 and 8 inside the 5 — a scoring-position specialist that every earlier column reported as a small role, indistinguishable from a player simply not used. **Counts, not shares, and that is a data limit.** The team total a share would divide by is not stored. `player_gamelogs` carries the player's count beside a `_percentage` that is his share of the team for that GAME, and the two ways to recover the team total — `count / percentage`, and the per-team-game MAX across players — disagree on 620 of 2412 rows for 2026, by up to 8 snaps. A denominator two methods disagree about on a quarter of rows is not one to publish against, so these stay counts and the reader normalizes against `G`. **Rolled up from `player_gamelogs`** in `libs-server/join-situational-snaps.mjs`, because no season-grain table carries the situational splits. **Partition pruning is load-bearing**: the table is RANGE-partitioned on `season_year`, and bounding the season only through the `nfl_games` join reads every partition back to 2000 — 152ms over ~930k rows against 9.5ms over the single 2026 partition for the same answer. The join names the partition column directly and keeps `nfl_games` only to exclude the postseason. **Four-minute offense was asked for and is NOT here, because the schema cannot express it honestly.** It is the leading team killing clock; `snaps_leading` and `snaps_under_five_minutes` both exist but their intersection does not. `snaps_under_five_minutes` alone is a strict superset of `2MIN` (verified: zero rows where the two-minute count exceeds it) carrying about 2.6 more snaps a game, and would have shipped under a label it does not earn. Short-yardage (`snaps_neutral_short`) and passing-down (`snaps_neutral_late_down`) are out for a different reason: both are defined on neutral game script only, so they would sit in a row of unfiltered counts describing a different population. Still available and unused if a later pass wants them: `snaps_leading` / `snaps_trailing` (game script), `snaps_no_huddle` (pace), `snaps_pass` / `snaps_rush`, the per-quarter splits, and `endzone_targets` / `rush_attempts_goal_line` for a targets-and-carries cut of the goal-line question rather than a snaps cut. ### The rank columns stay out Unchanged from the reason they were excluded at ship: `descendingComparator` leads an ascending sort with nulls, so clicking a rank to sort it the useful direction opens on a wall of unranked players. Fixing that changes shared sort behavior for every ascending sort on the page and deserves its own scope. `xFP% PCTL` plus the per-position shading already answer the standing question the ranks would answer, so the gap they leave is small. ### `G` needs an API deploy before it populates `games_played` was not previously selected by `libs-server/get-players.mjs`. The key is ABSENT from the deployed payload rather than present-and-null — checked against the live `xo.football` response — so the column renders empty until the API ships. `FP/G` paints today, since its column was already served. ### Verification - The view was driven in a browser and seen painted, which also retires the never-browser-verified loose end on the six columns that shipped first. Shading is alive; `xFP% PCTL` is correctly blank for QBs. - Seeding the omission this change most invites — the shaded column absent from `EXPECTED_POINTS_PERCENTILE_FIELDS` — turned `test/app.percentile-field-consumer-resolution.spec.mjs` red on two assertions naming the column, and restoring it turned them green. - `points_per_game` is exactly `points / games_played` to one decimal, with zero disagreeing rows out of 7490 for 2026; zero-game players carry NULL rather than a divide artifact. - The percentile pass matches Postgres `percentile_disc` on the new field and on `expected_points_from_plays`, confirming the new column behaves identically to the six already shipped. Note this corrects the inherited description of the function as diffed against `percentile_cont`, which interpolates and diverges at p75. - `games_played` was confirmed to receive no ladder, and a null-position player was confirmed to mint no percentile key.