---
title: Emit the offset-range percentile through one lateral instead of five correlated scans
type: task
status: planned
description: >-
  The offset-range percentile is correct and exceeds the 30s statement timeout: render_percentile
  repeats the value three times and the position twice, so each outer row drives five correlated
  scans of a materialized CTE against a 3.0s one-scan baseline.
base_uri: user:task/league/serve-the-offset-range-percentile-in-one-scan.md
created_at: '2026-09-24T21:19:49.341Z'
entity_id: 0ed16e52-9395-4787-a6d4-f25a0a12c9b7
owner_identity_uri: █████████████████████████
public_read: true
tags:
  - user:tag/league-xo-football.md
  - user:tag/software-task.md
updated_at: '2026-09-24T21:19:49.341Z'
visibility_analyzed_at: '2026-11-05T23:51:49.122Z'
---

## Problem

`player_expected_fantasy_points_share_percentile_from_plays` now emits CORRECT SQL
in the `year_offset_range` shape and that SQL exceeds the 30-second statement
timeout, so the column errors on execution instead of returning.

This is the residue of
[[user:task/league/repair-expected-fantasy-points-percentile-sql.md]], which fixed
the validity and the correctness. Nothing here is a wrong number — the column
fails loudly.

## Measured 2026-09-24

| query | result |
|---|---|
| percentile column, `year_offset [0,1]`, all 27 years | timeout at 30s |
| percentile column, `year_offset [0,1]`, 2024-2025 only | timeout at 30s |
| `player_opportunity_share_from_plays`, same shape, all 27 years | **3.0s** |

**The year span is not the cause** — narrowing to two years did not help, which was
checked rather than assumed. The cause is the number of correlated scans.

`render_percentile` repeats `value_sql` three times (the `CASE WHEN`, the
`PARTITION BY` qualifier, the `ORDER BY`) and the subject position twice. Over an
offset range each of those is its own correlated subquery against a materialized
CTE with no index, so an outer row drives five scans where a non-percentile
combined measure drives one. The window also forces the whole population before
`LIMIT 500`.

## The fix

Compute the value and the position ONCE per outer row instead of five times.

`LEFT JOIN LATERAL (SELECT <combine> as value, <position> as position FROM t WHERE
<correlation>) x ON true`, with the percentile referencing `x.value` and
`x.position`. One scan per row, and the repetition inside `render_percentile`
becomes free because it references columns rather than re-running a subquery.

**Two things make this more than a one-line swap.**

`get_select_string` returns `{ select, group_by }` and has no join surface, so the
lateral cannot be emitted from where the percentile is applied today
(`libs-server/data-views/select-string.mjs`, the `recombine_accumulators` branch).
The existing route is the `register_ctes` hook, which DOES receive the `query`
object — `libs-server/get-data-view-results.mjs:1261` and `:1339`, with a working
precedent at `libs-server/data-views-column-definitions/player-adp-column-definitions.mjs:214`.

A lateral's columns are BARE COLUMNS OF A JOINED RELATION, which Postgres does not
treat as functionally dependent on the grouped player columns — a 42803 on the whole
statement if they are omitted from `GROUP BY`. `select-string.mjs` already names and
handles this case as `offset_range_reads_join_alias`; read that note before writing
the join.

## Plan

- [ ] Reproduce both timings above, and re-measure the 3.0s baseline the same day
  rather than quoting this table
- [ ] Emit the value and position through one lateral (or a registered CTE) per
  offset-range percentile column
- [ ] Handle the `GROUP BY` consequence via the existing
  `offset_range_reads_join_alias` path rather than a new mechanism
- [ ] Re-run `node db/gates/check-data-view-sql-validity.mjs` and assert the
  statement COUNT is unchanged at 4650, not merely that it passes
- [ ] Re-run the full `yarn test:isolated` — `select-string.mjs` is a clause
  emitter, so goldens move; see
  [[user:guideline/nfl/league/data-view-column-definition-test-selection.md]]
- [ ] Confirm the offset-range query returns inside the statement timeout, and
  report the time against the non-percentile baseline

## Verification

The column must RETURN, and the returned percentiles must be non-degenerate — more
than one distinct value across the qualified population. A query that completes by
answering NULL or 0 everywhere is the defect the predecessor task removed, so assert
the distribution rather than the exit status.

## Priority

Low. The gate tiers this column `catalog_only` — reachable only by hand-picking it
in the column browser — and it fails loudly rather than answering wrongly. The
shared measure contract is already correct; this is an emitter efficiency change.

## Found by

`repl-ef5faa34` on 2026-09-24, measuring the lift it had just shipped rather than
declaring it done on a green gate.