calculations.matches_6_team_stats_window

Adds team-level scoring and conceding statistics over a trailing 1-year window (31_536_000 seconds preceding, excluding the current match) for both the home and away team in each row. Inherits all columns from matches_5_score.

Lineage

flowchart LR
m5[/matches_5_score/] --> me[/calculations.matches_6_team_stats_window/]
me --> m7[/matches_7_league_stats_window/]
me --> mart[/mart.matches/]
click m5 "/calculations/matches_5_score/" "Open"
click m7 "/calculations/matches_7_league_stats_window/" "Open"
click mart "/mart/matches/" "Open"

Columns

Column	Description	Formula
* (matches_5_score)	All upstream columns.	passthrough
stats_goals_scored_avg_home	Mean goals scored by the home team in this league over the prior year (excl. current match).	round(coalesce(avg(score_home) over (partition by league_id, team_home_id order by toUnixTimestamp(event_date) RANGE BETWEEN 31536000 PRECEDING AND 1 PRECEDING), 0), 2)
stats_goals_scored_count_home	Number of prior matches contributing to the average.	count(score_home) over (… same window …)
stats_goals_conceded_avg_home	Mean goals conceded by the home team in this league over the prior year.	round(coalesce(avg(score_away) over (partition by league_id, team_home_id …), 0), 2)
stats_goals_conceded_count_home	Count for the conceded average.	count(score_away) over (…)
stats_goals_scored_avg_away	Mean goals scored by the away team.	round(coalesce(avg(score_away) over (partition by league_id, team_away_id …), 0), 2)
stats_goals_scored_count_away	Count for the away-team scored average.	count(score_away) over (…)
stats_goals_conceded_avg_away	Mean goals conceded by the away team.	round(coalesce(avg(score_home) over (partition by league_id, team_away_id …), 0), 2)
stats_goals_conceded_count_away	Count for the away-team conceded average.	count(score_home) over (…)

All windows: partition by league_id, team_*_id order by toUnixTimestamp(event_date) RANGE BETWEEN 31536000 PRECEDING AND 1 PRECEDING. The full historical dataset is loaded (no incremental filter) so the windows are correct on incremental runs; the incremental filter is applied only to the final SELECT.