Baselight
WITH messi_ronaldo_match_player_stats as 
(SELECT p.name as player, STRPTIME(p.date_of_birth, '%Y-%m-%d') as date_of_birth, t.name as team, s.season_label as season, c.name as competition, m.date as match_date, home_teams.name as home_team, away_teams.name as away_team, mps.*
FROM @blt.ultimate_soccer_dataset.match_player_stats mps
     INNER JOIN @blt.ultimate_soccer_dataset.matches m on (mps.match_id = m.match_id)
     INNER JOIN @blt.ultimate_soccer_dataset.players p on (mps.player_id = p.player_id)
     INNER JOIN @blt.ultimate_soccer_dataset.teams t on (mps.team_id = t.team_id)
     INNER JOIN @blt.ultimate_soccer_dataset.seasons s on (m.season_id = s.season_id)
     INNER JOIN @blt.ultimate_soccer_dataset.competitions c on (s.competition_id = c.competition_id)
     INNER JOIN @blt.ultimate_soccer_dataset.teams home_teams ON (m.home_team_id = home_teams.team_id)
     INNER JOIN @blt.ultimate_soccer_dataset.teams away_teams ON (m.away_team_id = away_teams.team_id)
WHERE p.player_id IN ('5446de4b-66b8-5627-990a-49e14dbe4e6d', -- L. Messi
                      '5b578ccf-496a-5afb-bea2-c7ece1b2c05b') -- Cristiano Ronaldo 
      and year(m.date) >= 2015
)
SELECT
  CASE
    WHEN (MONTH(match_date) > MONTH(date_of_birth)) 
          OR (MONTH(match_date) = MONTH(date_of_birth) AND DAY(match_date) >= DAY(date_of_birth))
    THEN YEAR(match_date) - YEAR(date_of_birth)
    ELSE YEAR(match_date) - YEAR(date_of_birth) - 1
  END AS age,
  COUNT(CASE WHEN player='Cristiano Ronaldo' THEN 1 END) AS matches_ronaldo,
  COUNT(CASE WHEN player='L. Messi' THEN 1 END) AS matches_messi,
  COUNT(*) FILTER (WHERE goals_scored > 0 AND player='Cristiano Ronaldo') AS matches_with_goals_ronaldo,
  COUNT(*) FILTER (WHERE goals_scored > 0 AND player='L. Messi') AS matches_with_goals_messi,
  ROUND(100*matches_with_goals_ronaldo / matches_ronaldo) as scoring_rate_ronaldo,
  ROUND(100*matches_with_goals_messi / matches_messi) as scoring_rate_messi
FROM messi_ronaldo_match_player_stats
WHERE minutes_played > 0 and age >=30 and age <= 40
GROUP BY age
ORDER BY age
agematches_ronaldomatches_messimatches_with_goals_ronaldomatches_with_goals_messiscoring_rate_ronaldoscoring_rate_messi
30284814245050
31485425345263
32503727165443
33436426356055
34494731136328
35455226275852
36572527124748
37443512162746
3843028065NaN
3945027060NaN
4017011065NaN

Share link

Anyone who has the link will be able to view this.