1SELECT
2  p.player_id,
3  p.name AS player_name,
4  p.full_name,
5  p.date_of_birth,
6  p.country_of_birth,
7  p.city_of_birth,
8  p.country_of_citizenship,
9  p.positions,
10  p.height_cm,
11  p.weight_kg,
12  c.competition_id,
13  c.name AS competition_name,
14  c.country AS competition_country,
15  c.type AS competition_type,
16  s.season_id,
17  s.season_label,
18  COUNT(DISTINCT mps.match_id) AS matches_played,
19  SUM(CASE WHEN CAST(mps.minutes_played AS DOUBLE) > 0 THEN 1 ELSE 0 END) AS matches_with_minutes,
20  ROUND(AVG(CAST(mps.minutes_played AS DOUBLE)), 2) AS avg_minutes_per_match,
21  SUM(CAST(mps.minutes_played AS DOUBLE)) AS total_minutes,
22  ROUND(AVG(TRY_CAST(mps.rating AS DOUBLE)), 2) AS avg_rating,
23  ROUND(AVG(CAST(mps.shots_total AS DOUBLE)), 2) AS avg_shots_total,
24  ROUND(AVG(CAST(mps.shots_on_target AS DOUBLE)), 2) AS avg_shots_on_target,
25  ROUND(AVG(CAST(mps.goals_scored AS DOUBLE)), 2) AS avg_goals_scored,
26  SUM(CAST(mps.goals_scored AS DOUBLE)) AS total_goals_scored,
27  ROUND(AVG(CAST(mps.goals_assists AS DOUBLE)), 2) AS avg_assists,
28  SUM(CAST(mps.goals_assists AS DOUBLE)) AS total_assists,
29  ROUND(AVG(CAST(mps.goals_conceded AS DOUBLE)), 2) AS avg_goals_conceded,
30  SUM(CAST(mps.goals_conceded AS DOUBLE)) AS total_goals_conceded,
31  ROUND(AVG(CAST(mps.saves AS DOUBLE)), 2) AS avg_saves,
32  SUM(CAST(mps.saves AS DOUBLE)) AS total_saves,
33  ROUND(AVG(CAST(mps.passes_total AS DOUBLE)), 2) AS avg_passes_total,
34  SUM(CAST(mps.passes_total AS DOUBLE)) AS total_passes_total,
35  ROUND(AVG(CAST(mps.passes_key AS DOUBLE)), 2) AS avg_key_passes,
36  SUM(CAST(mps.passes_key AS DOUBLE)) AS total_key_passes,
37  ROUND(AVG(CAST(mps.tackles_total AS DOUBLE)), 2) AS avg_tackles_total,
38  SUM(CAST(mps.tackles_total AS DOUBLE)) AS total_tackles_total,
39  ROUND(AVG(CAST(mps.tackles_blocks AS DOUBLE)), 2) AS avg_tackles_blocks,
40  SUM(CAST(mps.tackles_blocks AS DOUBLE)) AS total_tackles_blocks,
41  ROUND(AVG(CAST(mps.tackles_interceptions AS DOUBLE)), 2) AS avg_tackles_interceptions,
42  SUM(CAST(mps.tackles_interceptions AS DOUBLE)) AS total_tackles_interceptions,
43  ROUND(AVG(CAST(mps.duels_total AS DOUBLE)), 2) AS avg_duels_total,
44  SUM(CAST(mps.duels_total AS DOUBLE)) AS total_duels_total,
45  ROUND(AVG(CAST(mps.duels_won AS DOUBLE)), 2) AS avg_duels_won,
46  SUM(CAST(mps.duels_won AS DOUBLE)) AS total_duels_won,
47  ROUND(AVG(CAST(mps.dribbles_attempts AS DOUBLE)), 2) AS avg_dribbles_attempts,
48  SUM(CAST(mps.dribbles_attempts AS DOUBLE)) AS total_dribbles_attempts,
49  ROUND(AVG(CAST(mps.dribbles_success AS DOUBLE)), 2) AS avg_dribbles_success,
50  SUM(CAST(mps.dribbles_success AS DOUBLE)) AS total_dribbles_success,
51  ROUND(AVG(CAST(mps.dribbled_past AS DOUBLE)), 2) AS avg_dribbled_past,
52  SUM(CAST(mps.dribbled_past AS DOUBLE)) AS total_dribbled_past,
53  ROUND(AVG(CAST(mps.fouls_drawn AS DOUBLE)), 2) AS avg_fouls_drawn,
54  SUM(CAST(mps.fouls_drawn AS DOUBLE)) AS total_fouls_drawn,
55  ROUND(AVG(CAST(mps.fouls_committed AS DOUBLE)), 2) AS avg_fouls_committed,
56  SUM(CAST(mps.fouls_committed AS DOUBLE)) AS total_fouls_committed,
57  SUM(CAST(mps.yellow_cards AS DOUBLE)) AS total_yellow_cards,
58  SUM(CAST(mps.red_cards AS DOUBLE)) AS total_red_cards,
59  ROUND(AVG(CAST(mps.penalties_won AS DOUBLE)), 2) AS avg_penalties_won,
60  SUM(CAST(mps.penalties_won AS DOUBLE)) AS total_penalties_won,
61  ROUND(AVG(CAST(mps.penalties_committed AS DOUBLE)), 2) AS avg_penalties_committed,
62  SUM(CAST(mps.penalties_committed AS DOUBLE)) AS total_penalties_committed,
63  SUM(CAST(mps.penalties_scored AS DOUBLE)) AS total_penalties_scored,
64  SUM(CAST(mps.penalties_missed AS DOUBLE)) AS total_penalties_missed,
65  ROUND(AVG(CAST(mps.penalties_saved AS DOUBLE)), 2) AS avg_penalties_saved,
66  SUM(CAST(mps.penalties_saved AS DOUBLE)) AS total_penalties_saved,
67  ROUND(AVG(CAST(mps.offsides AS DOUBLE)), 2) AS avg_offsides,
68  SUM(CAST(mps.offsides AS DOUBLE)) AS total_offsides,
69  COUNT(DISTINCT CASE WHEN mps.is_captain THEN mps.match_id END) AS captain_appearances,
70  COUNT(DISTINCT CASE WHEN mps.is_substitute THEN mps.match_id END) AS substitute_appearances
71FROM "@blt.ultimate_soccer_dataset.match_player_stats" mps
72JOIN "@blt.ultimate_soccer_dataset.matches" m ON mps.match_id = m.match_id
73JOIN "@blt.ultimate_soccer_dataset.seasons" s ON m.season_id = s.season_id
74JOIN "@blt.ultimate_soccer_dataset.competitions" c ON s.competition_id = c.competition_id
75JOIN "@blt.ultimate_soccer_dataset.players" p ON mps.player_id = p.player_id
76GROUP BY
77  p.player_id,
78  p.name,
79  p.full_name,
80  p.date_of_birth,
81  p.country_of_birth,
82  p.city_of_birth,
83  p.country_of_citizenship,
84  p.positions,
85  p.height_cm,
86  p.weight_kg,
87  c.competition_id,
88  c.name,
89  c.country,
90  c.type,
91  s.season_id,
92  s.season_label
93ORDER BY c.country, s.season_label DESC, p.name
94