1WITH top_scorers AS (
2    SELECT 
3        p.name AS player, 
4        t.name AS team, 
5        s.season_label AS season, 
6        c.name AS competition, 
7        SUM(mps.goals_scored) AS goals_scored
8    FROM "@blt.ultimate_soccer_dataset.match_player_stats" mps
9         INNER JOIN "@blt.ultimate_soccer_dataset.matches" m ON mps.match_id = m.match_id
10         INNER JOIN "@blt.ultimate_soccer_dataset.players" p ON mps.player_id = p.player_id
11         INNER JOIN "@blt.ultimate_soccer_dataset.teams" t ON mps.team_id = t.team_id
12         INNER JOIN "@blt.ultimate_soccer_dataset.seasons" s ON m.season_id = s.season_id
13         INNER JOIN "@blt.ultimate_soccer_dataset.competitions" c ON s.competition_id = c.competition_id
14    WHERE YEAR(s.start_date) >= 2016
15          AND c.name IN ('Premier League', 'Primeira Liga')
16    GROUP BY player, team, season, competition
17    ORDER BY goals_scored DESC
18    LIMIT 5
19)
20
21SELECT 
22    ts.player,
23    ts.team,
24    ts.season,
25    ts.competition,
26    ts.goals_scored,
27    mv.market_value_of_club_in_millions AS "club market value (M)",
28    mv.average_market_value_of_players_in_millions AS avg_player_value_m
29FROM top_scorers ts
30LEFT JOIN "@kaggle.sanjeetsinghnaik_football_club_market_value_2021.club" mv
31  ON LOWER(
32      REPLACE(
33          REPLACE(ts.team, 'Benfica', 'SL Benfica'),
34          'Liverpool', 'Liverpool FC'
35      )
36  ) = LOWER(mv.club_name)
37ORDER BY
38    ts.goals_scored