1WITH latest_matches as 
2(SELECT 
3  date::date as "Match Date",
4  (home_score + away_score) as "Total Goals",
5  m.competition_name as "Competition",
6  home_team_name as "Home Team",
7  away_team_name as "Away Team",
8  home_score as "Home Score",
9  away_score as "Away Score",
10  venue_name as "Venue",
11  c.country as "Country"
12FROM @blt.ultimate_soccer_dataset.matches m
13LEFT OUTER JOIN "@blt.ultimate_soccer_dataset.seasons" s 
14  ON m.season_id = s.season_id
15LEFT OUTER JOIN "@blt.ultimate_soccer_dataset.competitions" c 
16  ON s.competition_id = c.competition_id
17WHERE status = 'Match Finished' AND year(m.date) BETWEEN 2020 and 2024
18ORDER BY date DESC, m.competition_name
19)
20SELECT "Country"
21        , ROUND(1.0*SUM("Total Goals") / count(*),1) as "Avg Goals per Match"
22        , count(*) as "Total Matches"
23        , SUM("Total Goals") as "Total Goals"
24FROM latest_matches
25GROUP BY ALL
26ORDER BY "Avg Goals per Match" DESC
27