WITH top_scorers AS (
SELECT
p.name AS player,
t.name AS team,
s.season_label AS season,
c.name AS competition,
SUM(mps.goals_scored) AS goals_scored
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
WHERE YEAR(s.start_date) >= 2016
AND c.name IN ('Premier League', 'Primeira Liga')
GROUP BY player, team, season, competition
ORDER BY goals_scored DESC
LIMIT 5
)
SELECT
ts.player,
ts.team,
ts.season,
ts.competition,
ts.goals_scored,
mv.market_value_of_club_in_millions AS "club market value (M)",
mv.average_market_value_of_players_in_millions AS avg_player_value_m
FROM top_scorers ts
LEFT JOIN "@kaggle.sanjeetsinghnaik_football_club_market_value_2021.club" mv
ON LOWER(
REPLACE(
REPLACE(ts.team, 'Benfica', 'SL Benfica'),
'Liverpool', 'Liverpool FC'
)
) = LOWER(mv.club_name)
ORDER BY
ts.goals_scored