--- title: Fix data view duplicate rows from year offset expansion in get_year_range type: task status: completed description: >- Remove year_offset-based expansion from get_year_range() which creates phantom base_years entries causing duplicate player rows in year-split data views, and skip redundant LEFT JOINs for year_offset range columns that use correlated subselects base_uri: user:task/league/fix-data-view-year-offset-duplicate-rows.md created_at: '2026-03-12T20:44:15.220Z' entity_id: f4286130-6c36-4a67-802b-67a10a803962 finished_at: '2026-03-12T20:52:36.769Z' observations: [] owner_identity_uri: █████████████████████████ priority: High public_read: true relations: - relates [[██████████████████████████████-████-███████]] started_at: '2026-03-12T20:44:57.546Z' tags: - user:tag/league-xo-football.md updated_at: '2026-03-12T20:52:36.769Z' visibility_analyzed_at: '2026-11-05T23:49:20.965Z' --- # Implementation Plan: Fix Data View Duplicate Rows from Year Offset Expansion ## Overview - Fix duplicate rows in data views caused by `get_year_range()` expanding `base_years` using year_offset values from columns - Remove redundant LEFT JOINs for year_offset range columns that already use correlated subselects - Preserve existing single-year comparison column behavior (`single_year_param_set` pattern) ## Background - Data views with `splits: ["year"]` generate a `base_years` CTE via `get_year_range()` which cross-joins with all players to create `player_years` - `get_year_range()` collects year_offset min/max from all columns and expands base years by applying offsets (e.g., year [2024] with offset [1,30] becomes [2024,2025,2026] capped at current_season.year) - Non-offset columns with a single year param use a hardcoded join (`year = 2024`) instead of `year = player_years.year` -- this is intentional for comparison columns - The combination produces duplicate-looking rows: each player gets one row per expanded year, all showing the same hardcoded year data for non-offset columns - CTE year filters (WHERE year IN) are computed independently by `apply_play_by_play_column_params_to_query()`, so `base_years` changes do not affect CTE data availability - Year_offset range columns use correlated subselects in SELECT but also get a redundant LEFT JOIN from `add_clauses_for_table` - Reported URL: `https://xo.football/u/ea94c6e4f82ef725acbd58f6e340fa03` ## Design - Remove offset expansion from `get_year_range()`: return only explicitly specified years, eliminating phantom split rows - Skip JOIN for year_offset range columns when no WHERE/HAVING filter exists on that column group (the correlated subselect handles data access) - Keep JOIN when WHERE/HAVING filters exist (needed for INNER JOIN + HAVING to work) - Update affected test fixtures to reflect narrower base_years and removed redundant JOINs ## Notes - The `single_year_param_set` hardcoded join pattern appears in 7 locations across 6 files -- intentionally left unchanged as it serves valid comparison column use cases - The `apply_play_by_play_column_params_to_query()` function independently expands CTE year filters, so offset CTEs will still include the correct expanded year range in their WHERE clauses - Correlated subselects reference `player_years.year` and query the CTE directly -- they work correctly regardless of what years are in `base_years` - Run `yarn test --reporter min test/data-view-queries.spec.mjs` after changes to validate all 18+ test fixtures ## Plan ### Core: Remove offset expansion from `get_year_range()` - [x] **Simplify `get_year_range()` to exclude offset-based year expansion** - File: `libs-server/get-data-view-results.mjs` (lines 323-380) - Purpose: Stop `base_years` from including years derived from year_offset, which create phantom split rows with duplicated data - Changes: - Remove `min_offset` and `max_offset` tracking variables - Remove the `year_offset` extraction from `check_params()` - Remove the offset expansion loop that creates `all_years` from offsets - Return the deduplicated, sorted explicit years directly ### Core: Skip redundant JOIN for year_offset range columns without filters - [x] **Add condition to skip join_func when year_offset range column has no filters** - File: `libs-server/get-data-view-results.mjs`, in `add_clauses_for_table` (around line 936-961) - Purpose: Eliminate redundant LEFT JOIN that causes unnecessary row fan-out for year_offset range columns that use correlated subselects - Changes: - Before calling `join_func`, check if the column group has a year_offset range (length > 1, distinct values) AND has no where_clauses for that table - If both conditions are true, skip the `join_func` call -- the correlated subselect in SELECT handles data access independently - If where_clauses exist, proceed with the JOIN as before (needed for INNER JOIN + HAVING filters) ### Test Fixture Updates - [x] **Update `player-rush-yards-after-contact-per-attempt-from-plays-year-offset-range.json`** - File: `test/data-view-queries/player-rush-yards-after-contact-per-attempt-from-plays-year-offset-range.json` - Purpose: Reflect narrower base_years and removed redundant LEFT JOIN - Changes: - Update `base_years` from `[2018,...,2024]` to `[2018,2019,2020,2021]` (explicit years only) - Remove the LEFT JOIN for `tc362c7c66c0bc9281ff31ba0a15b34cc` (year_offset range CTE with no filters) - Remove `tc362c7c66c0bc9281ff31ba0a15b34cc` from GROUP BY if present - [x] **Update `adjust-specified-year-params-when-year-offset-is-specified.json`** - File: `test/data-view-queries/adjust-specified-year-params-when-year-offset-is-specified.json` - Purpose: Reflect narrower base_years from removing offset expansion - Changes: - Update `base_years` from `[2021,2022,2023]` to `[2022,2023]` (explicit years only, offset of -1 no longer expands) - [x] **Update `year-offset-range-with-where-filters.json`** - File: `test/data-view-queries/year-offset-range-with-where-filters.json` - Purpose: Verify JOIN is preserved when WHERE filters exist, update base_years - Changes: - Update `base_years` to contain only explicit years (remove offset-expanded years) - Confirm INNER JOIN for the year_offset range CTE is preserved (has WHERE filter) - [x] **Update `year-offset-range-with-rate-type-and-where-filter.json`** - File: `test/data-view-queries/year-offset-range-with-rate-type-and-where-filter.json` - Purpose: Same as above -- verify JOIN preserved with filters, update base_years - Changes: - Update `base_years` to contain only explicit years - Confirm JOIN and rate_type subselect patterns are preserved - [x] **Identify and update any additional affected fixtures** - Purpose: Catch any other test fixtures that include year_offset params - Changes: - Search all JSON fixtures in `test/data-view-queries/` for `year_offset` - Update base_years and JOIN expectations as appropriate for each - 7 total fixtures updated: the 4 above plus `create-a-query-with-an-n1-column.json`, `single-year-offset-with-rate-type.json`, and `query-wr-fantasy-stats-with-year-splits-and-sort-by-points-per-game-seasonlogs-careerlogs-plays-and-keeptradecut.json` ### Validation - [x] **Run data view query test suite** - Command: `yarn test:data-view-queries --reporter min` - Purpose: Verify all test fixtures pass with the changes - Result: All 117 tests passing - [x] **Manual verification with reported URL** - Command: `yarn debug:data-view /u/ea94c6e4f82ef725acbd58f6e340fa03` - Purpose: Verify the reported URL now generates a query with `base_years = [2024]` and produces one row per player instead of three - Result: Confirmed -- `base_years = [2024]`, no redundant LEFT JOIN for year_offset range CTE, one row per player