1WITH cr7_stats as 
2(SELECT p.name as player, t.name as team, s.season_label as season, c.name as competition,
3  m.date as match_date, home_teams.name as home_team, away_teams.name as away_team, 
4  (EXTRACT(YEAR FROM match_date) - 1985) AS age,
5  mps.*
6FROM @blt.ultimate_soccer_dataset.match_player_stats mps
7     INNER JOIN @blt.ultimate_soccer_dataset.matches m on (mps.match_id = m.match_id)
8     INNER JOIN @blt.ultimate_soccer_dataset.players p on (mps.player_id = p.player_id)
9     INNER JOIN @blt.ultimate_soccer_dataset.teams t on (mps.team_id = t.team_id)
10     INNER JOIN @blt.ultimate_soccer_dataset.seasons s on (m.season_id = s.season_id)
11     INNER JOIN @blt.ultimate_soccer_dataset.competitions c on (s.competition_id = c.competition_id)
12     INNER JOIN @blt.ultimate_soccer_dataset.teams home_teams ON (m.home_team_id = home_teams.team_id)
13     INNER JOIN @blt.ultimate_soccer_dataset.teams away_teams ON (m.away_team_id = away_teams.team_id)
14WHERE p.player_id = '5b578ccf-496a-5afb-bea2-c7ece1b2c05b' -- Cristiano Ronaldo
15      and year(m.date) >= 2015
16      and mps.minutes_played > 0      
17)
18
19SELECT age, 
20  COUNT(*) AS matches, SUM(goals_scored) as goals, ROUND(goals/matches, 2) AS goals_per_match,
21  SUM(minutes_played) as mins_played, ROUND(goals/mins_played*90, 2) as goals_per_90_mins,
22  list(DISTINCT team) FILTER (team <> 'Portugal') AS clubs
23FROM cr7_stats
24GROUP BY age
25ORDER BY age
26