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