WITH valid_matches AS (
  SELECT match_id
  FROM "@blt.ultimate_soccer_dataset.matches"
  WHERE year(date) between 2021 and 2024
),

goals AS (
  SELECT
    player_id,
    COUNT(*) AS goals
  FROM "@blt.ultimate_soccer_dataset.goal_events"
  WHERE match_id IN (SELECT match_id FROM valid_matches)
  GROUP BY player_id
),

player_stats AS (
  SELECT
    player_id,
    SUM(minutes_played) AS total_minutes,
    SUM(shots_total) AS total_shots
  FROM "@blt.ultimate_soccer_dataset.match_player_stats"
  WHERE match_id IN (SELECT match_id FROM valid_matches)
  GROUP BY player_id
)

SELECT
  ps.player_id,
  p.name,
  ps.total_minutes,
  ps.total_shots,
  COALESCE(g.goals, 0) AS goals,
  ROUND(COALESCE(g.goals, 0) / NULLIF(ps.total_shots, 0), 4) AS goals_per_shot
FROM player_stats ps
LEFT JOIN goals g ON ps.player_id = g.player_id
LEFT JOIN @blt.ultimate_soccer_dataset.players p ON ps.player_id = p.player_id
WHERE ps.total_minutes > 900
ORDER BY goals_per_shot DESC;