1SELECT 
2    c.name as competition_name,
3    c.type,
4    COUNT(m.match_id) as total_matches,
5    COUNT(mps.match_id) as matches_with_player_stats,
6    COUNT(DISTINCT ms.match_id) as matches_with_team_stats,
7    SUM(CASE WHEN ms.type IN ('field_goals_total', 'fgm') THEN ms.value ELSE 0 END) as total_field_goals,
8    STRING_AGG(DISTINCT s.season_label, ', ' ORDER BY s.season_label DESC) as seasons_available
9FROM @blt.ultimate_basketball_dataset.competitions c
10LEFT JOIN @blt.ultimate_basketball_dataset.seasons s ON c.competition_id = s.competition_id
11LEFT JOIN @blt.ultimate_basketball_dataset.matches m ON s.season_id = m.season_id  
12LEFT JOIN @blt.ultimate_basketball_dataset.match_player_stats mps ON m.match_id = mps.match_id
13LEFT JOIN @blt.ultimate_basketball_dataset.match_stats ms ON m.match_id = ms.match_id
14WHERE c.name LIKE '%NBA%'
15GROUP BY c.name, c.type
16ORDER BY COUNT(m.match_id) DESC, c.name