---
title: Add Per-Player Touch and Opportunity Rate Types
type: task
status: completed
description: >-
  Add per_player_touch (rush + reception) and per_player_opportunity (pass attempt excluding sacks +
  rush + target) as offensive-player rate-type denominators in the data-views system.
base_uri: user:task/league/add-per-player-touch-and-opportunity-rate-types.md
created_at: '2026-06-04T18:16:19.299Z'
entity_id: 0a557c46-2817-4ac7-a04f-ebf4fcb5d99c
finished_at: '2026-06-04T18:41:14.654Z'
observations:
  - ████████████████████████████████████████████████
owner_identity_uri: █████████████████████████
priority: Medium
public_read: true
started_at: '2026-06-04T18:34:13.086Z'
tags:
  - user:tag/league-xo-football.md
updated_at: '2026-06-04T18:41:14.654Z'
visibility_analyzed_at: '2026-11-05T23:48:13.974Z'
---

# Implementation Plan: Add Per-Player Touch and Opportunity Rate Types

## Overview

- Add two new offensive-player rate-type denominators: `per_player_touch` (rush attempts + receptions) and `per_player_opportunity` (pass attempts excluding sacks + rush attempts + targets).
- Both extend the existing per-player rate-type plugin (denominator-only CTE; numerator handled by the standard `aggregator_rate` path).
- Impact: data-views users get two new rate-type values in the offensive-player rate-type dropdown, usable on any offensive-player measure column to express per-touch / per-opportunity rates. No existing behavior changes.
- Ordering: standalone enhancement. Does not depend on other in-flight work and does not block downstream tasks.

## Background

- Existing per-player rate types are implemented in `libs-server/data-views/rate-type/rate-type-per-player.mjs`. The `add_per_player_cte` function builds a denominator CTE against `nfl_plays`, dispatching on a `stat_type` string (`rush_attempt`, `pass_attempt`, `target`, `reception`). Each case sets the pid-bearing column and the `COUNT`/`SUM(CASE …)` expression, then groups by that pid.
- Period-to-plugin registration happens in `libs-server/data-views/output-aggregator-registry.mjs` via `register(period, 'rate', adapt(plugin_per_player, { stat_type }))`.
- Token-to-period mapping lives in `libs-shared/data-views-output-tokens.mjs` (`RATE_TYPE_TO_OUTPUT`).
- UI dropdown values are mirrored in `libs-shared/rate-type-column-param.mjs` (`offensive_player_rate_type_param`) and `libs-shared/output-column-param.mjs`.
- The two new rate types differ from existing ones in that the denominator counts events that can originate from more than one role column on the same play. A `CROSS JOIN LATERAL (VALUES …) AS t(pid)` expansion converts each play into one row per active contributing role, then groups by `t.pid`.
- Existing data-view query tests use a JSON-fixture pattern under `test/data-view-queries/*.json`, executed by `test/libs-server.data-view-queries.mjs` via `load_data_view_test_queries_sync` and `compare_queries`. New rate types are tested by adding fixtures.

### Assumptions

- PostgreSQL 13+ supports `CROSS JOIN LATERAL (VALUES (…)) AS t(pid)` with `CASE` expressions inside the `VALUES`. _Checked: this construct is standard since PostgreSQL 9.3._
- For any single play, the rusher `bc_pid`, passer `psr_pid`, and target `trg_pid` columns reference at most one player each, and a single play in `nfl_plays` cannot produce the same `pid` from two different role columns. The LATERAL expansion therefore does not double-count a player on one play. _Checked against existing rate types: `per_player_pass_attempt` and `per_player_target` rely on the same single-pid-per-role invariant._
- `apply_play_by_play_column_params_to_query` (used by `add_per_player_cte` after the switch) applies WHERE clauses against `nfl_plays.<col>`-qualified columns only, never against `t.*`, so adding a LATERAL with alias `t` does not introduce conflicts. _Checked: function inspected in `libs-server/apply-play-by-play-column-params-to-query.mjs`._
- `cte_query.crossJoin(db.raw('LATERAL (…) AS t(pid)'))` is the knex pattern that produces the desired SQL with the installed knex (pg dialect). _Checked: knex `crossJoin` accepts `db.raw` for arbitrary right-hand sources._
- The `MATERIALIZED` wrapping at `withMaterialized(rate_type_table_name, cte_query)` continues to apply unchanged; partition pruning by `year` remains effective because year predicates are still pushed at construction time via `apply_play_by_play_column_params_to_query`.

## Design

- New `stat_type` cases in `add_per_player_cte` for `touch` and `opportunity`.
- New period registrations in the output-aggregator registry binding each to `plugin_per_player` with the corresponding `stat_type`.
- New token entries in `RATE_TYPE_TO_OUTPUT` mapping `per_player_touch` → `player_touch` and `per_player_opportunity` → `player_opportunity`.
- New `{ value, label }` entries appended to `offensive_player_rate_type_param.values` and its mirror in `output-column-param.mjs`.

### SQL shape (generated denominator CTE)

For `touch`:

```sql
SELECT t.pid, COUNT(*) AS rate_type_total_count
FROM nfl_plays
CROSS JOIN LATERAL (VALUES
  (nfl_plays.bc_pid),
  (CASE WHEN nfl_plays.comp = true THEN nfl_plays.trg_pid END)
) AS t(pid)
WHERE t.pid IS NOT NULL
  AND NOT play_type = 'NOPL'
  AND <play-level filters>
GROUP BY t.pid
```

For `opportunity`:

```sql
SELECT t.pid, COUNT(*) AS rate_type_total_count
FROM nfl_plays
CROSS JOIN LATERAL (VALUES
  (CASE WHEN nfl_plays.sk IS NULL OR nfl_plays.sk = false THEN nfl_plays.psr_pid END),
  (nfl_plays.bc_pid),
  (nfl_plays.trg_pid)
) AS t(pid)
WHERE t.pid IS NOT NULL
  AND NOT play_type = 'NOPL'
  AND <play-level filters>
GROUP BY t.pid
```

The sack exclusion on the passer leg mirrors the existing `per_player_pass_attempt` case.

### Knex implementation pattern

In `add_per_player_cte`, for the new cases, replace the existing `select('… as pid')` + `groupBy('pid_col')` pair with:

- `cte_query.crossJoin(db.raw('LATERAL (VALUES (…), (…)) AS t(pid)'))` — introduces the LATERAL expansion and the `t` alias.
- `cte_query.whereRaw('t.pid IS NOT NULL')` — drops empty role rows from the expansion.
- `cte_query.select('t.pid as pid')` and `cte_query.groupBy('t.pid')` — group on the emitted pid.
- `count_expression = 'COUNT(*)'` — each remaining row is exactly one contributing event.

### Knowledge Base Impact

No new domain entries, ABOUT.md updates, tags, observations, or guidelines. The rate-type system is self-documenting via the registry, the token map, and the param-value lists — all of which gain symmetric entries in this plan. No relations added on the task entity (no upstream/downstream entity to link).

## Notes

- `per_player_touch` denominator for QBs is intentionally tiny (only scrambles count, since a QB only appears as `bc_pid` on rushing plays). Same convention as `per_player_reception` applied to non-receivers — analysts apply the rate where it is meaningful.
- The LATERAL VALUES form materializes one row per non-null role per play before grouping. With the existing play-level filters and the `NOT play_type = 'NOPL'` guard, this stays cheap; partition pruning on `year` is preserved.
- No scratch slug needed; all artifacts are durable source files in the league repo.

## Plan

Top-level heading is `## Plan` per [[user:guideline/write/write-task.md#canonical-plan-section-heading]].

### Core Implementation

- [x] **Extend the `switch (stat_type)` block in `add_per_player_cte`**
  - File: `repository/active/league/libs-server/data-views/rate-type/rate-type-per-player.mjs`
  - Purpose: register denominator SQL for the two new stat types.
  - Changes:
    - Add `case 'touch':` that calls `cte_query.crossJoin(db.raw("LATERAL (VALUES (nfl_plays.bc_pid), (CASE WHEN nfl_plays.comp = true THEN nfl_plays.trg_pid END)) AS t(pid)"))`, `cte_query.whereRaw('t.pid IS NOT NULL')`, `cte_query.select('t.pid as pid')`, `cte_query.groupBy('t.pid')`, and leaves `count_expression` at its `'COUNT(*)'` default.
    - Add `case 'opportunity':` that calls `cte_query.crossJoin(db.raw("LATERAL (VALUES (CASE WHEN nfl_plays.sk IS NULL OR nfl_plays.sk = false THEN nfl_plays.psr_pid END), (nfl_plays.bc_pid), (nfl_plays.trg_pid)) AS t(pid)"))`, same `whereRaw` / `select` / `groupBy` / count default.
  - Verify: `yarn test --reporter min test/libs-server.data-view-queries.mjs` passes with the two new fixtures from the Testing & Validation group.

- [x] **Register the two new periods in the aggregator registry**
  - File: `repository/active/league/libs-server/data-views/output-aggregator-registry.mjs`
  - Purpose: bind each new period to `plugin_per_player` with its dispatch `stat_type`.
  - Changes:
    - After the existing `player_reception` registration, add `register('player_touch', 'rate', adapt(plugin_per_player, { stat_type: 'touch' }))`.
    - Add `register('player_opportunity', 'rate', adapt(plugin_per_player, { stat_type: 'opportunity' }))`.
  - Verify: `yarn test --reporter min test/libs-server.data-view-queries.mjs` resolves both periods without throwing `No output aggregator for …`.

- [x] **Add token-to-period entries in `RATE_TYPE_TO_OUTPUT`**
  - File: `repository/active/league/libs-shared/data-views-output-tokens.mjs`
  - Purpose: map the new rate-type tokens to their periods.
  - Changes:
    - Add `per_player_touch: { period: 'player_touch', aggregation: 'rate' }`.
    - Add `per_player_opportunity: { period: 'player_opportunity', aggregation: 'rate' }`.
  - Verify: `node -e "import('./libs-shared/data-views-output-tokens.mjs').then(m => console.log(m.translate_rate_type_to_output('per_player_touch'), m.translate_rate_type_to_output('per_player_opportunity')))"` from the league repo root prints non-null objects.

- [x] **Append UI dropdown values in the rate-type column param**
  - File: `repository/active/league/libs-shared/rate-type-column-param.mjs`
  - Purpose: expose the new tokens in the offensive-player rate-type dropdown.
  - Changes:
    - Append `{ value: 'per_player_touch', label: 'Per Player Touch' }` to `offensive_player_rate_type_param.values`.
    - Append `{ value: 'per_player_opportunity', label: 'Per Player Opportunity' }` to the same array.
  - Verify (no verify — mechanical step; covered by SQL fixture tests below).

- [x] **Mirror the same values in the output-column param**
  - File: `repository/active/league/libs-shared/output-column-param.mjs`
  - Purpose: keep the output-column param dropdown in sync.
  - Changes:
    - Append the same two `{ value, label }` entries to the corresponding offensive-player values list (matching the existing block at lines around 86).
  - Verify (no verify — mechanical step; identical entries to previous step).

### Testing & Validation

- [x] **Add a SQL fixture for `per_player_touch`**
  - File: `repository/active/league/test/data-view-queries/per-player-touch-rate-type.json` (new)
  - Purpose: lock down the generated SQL shape for the new rate type.
  - Changes: build a fixture in the same shape as existing `data-view-queries/*.json` files, with a table_state containing one offensive-player measure column (e.g., `player_fantasy_points_from_plays` with a known scoring format) and `rate_type: ['per_player_touch']`. Expected SQL contains a `CROSS JOIN LATERAL (VALUES (nfl_plays.bc_pid), (CASE WHEN nfl_plays.comp = true THEN nfl_plays.trg_pid END)) AS t(pid)` clause and `GROUP BY t.pid` in the denominator CTE.
  - Verify: `yarn test --reporter min test/libs-server.data-view-queries.mjs --grep "per-player-touch-rate-type"` passes.

- [x] **Add a SQL fixture for `per_player_opportunity`**
  - File: `repository/active/league/test/data-view-queries/per-player-opportunity-rate-type.json` (new)
  - Purpose: lock down the generated SQL shape for the new rate type.
  - Changes: parallel fixture to the touch one, with `rate_type: ['per_player_opportunity']`. Expected SQL contains the three-leg `CROSS JOIN LATERAL` with the sack-excluding `CASE` on `psr_pid`.
  - Verify: `yarn test --reporter min test/libs-server.data-view-queries.mjs --grep "per-player-opportunity-rate-type"` passes.

- [x] **Spot-check denominators against production data**
  - Purpose: confirm the LATERAL expansion produces the intended event counts.
  - Changes: pick one RB and one WR with substantial 2025 REG usage. Execute the generated denominator CTE via `mcp__db__query_league` for each rate type.
  - Verify: for `per_player_touch`, denominator equals `(rush attempts) + (receptions)` for that player in 2025 REG (cross-checked by running independent COUNT queries on `nfl_plays` with `bc_pid = '<pid>'` and `trg_pid = '<pid>' AND comp = true`). For `per_player_opportunity`, denominator equals `(pass attempts excluding sacks for QB) + (rush attempts) + (targets, caught or not)` for the chosen player. Variance > 0 indicates a bug in the LATERAL or filter logic.