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'
18ORDER BY date DESC, m.competition_name
19LIMIT 500 
20)
21SELECT "Country"
22        , ROUND(1.0*SUM("Total Goals") / count(*),1) as "Avg Goals per Match"
23        , count(*) as "Total Matches"
24        , SUM("Total Goals") as "Total Goals"
25FROM latest_matches
26GROUP BY ALL
27ORDER BY "Avg Goals per Match" DESC
28