1-- Messi vs Ronaldo: goals by season (plus penalties)
2WITH stars AS (
3  SELECT DISTINCT
4         player_id,
5         CASE
6           WHEN LOWER(COALESCE(full_name, name)) LIKE '%messi%' THEN 'Lionel Messi'
7           WHEN LOWER(COALESCE(full_name, name)) LIKE 'cristiano ronaldo%' THEN 'Cristiano Ronaldo'
8         END AS player_name
9  FROM @blt.ultimate_soccer_dataset.players
10  WHERE LOWER(COALESCE(full_name, name)) LIKE '%messi%'
11     OR LOWER(COALESCE(full_name, name)) LIKE 'cristiano ronaldo%'
12),
13player_season_goals AS (
14  SELECT
15    st.player_name,
16    s.season_label,
17    s.start_date,
18    SUM(COALESCE(mps.goals_scored, 0))            AS goals,
19    SUM(COALESCE(mps.penalties_scored, 0))        AS penalties
20  FROM @blt.ultimate_soccer_dataset.match_player_stats mps
21  JOIN @blt.ultimate_soccer_dataset.matches m   ON mps.match_id = m.match_id
22  JOIN @blt.ultimate_soccer_dataset.seasons s   ON m.season_id   = s.season_id
23  JOIN stars st                                 ON mps.player_id = st.player_id
24  GROUP BY st.player_name, s.season_label, s.start_date
25)
26SELECT
27  player_name,
28  season_label,
29  goals,
30  penalties,
31  (goals - penalties) AS non_penalty_goals
32FROM player_season_goals
33ORDER BY start_date, player_name;