SELECT 
    c.name as competition_name,
    c.type,
    COUNT(m.match_id) as total_matches,
    COUNT(mps.match_id) as matches_with_player_stats,
    COUNT(DISTINCT ms.match_id) as matches_with_team_stats,
    SUM(CASE WHEN ms.type IN ('field_goals_total', 'fgm') THEN ms.value ELSE 0 END) as total_field_goals,
    STRING_AGG(DISTINCT s.season_label, ', ' ORDER BY s.season_label DESC) as seasons_available
FROM @blt.ultimate_basketball_dataset.competitions c
LEFT JOIN @blt.ultimate_basketball_dataset.seasons s ON c.competition_id = s.competition_id
LEFT JOIN @blt.ultimate_basketball_dataset.matches m ON s.season_id = m.season_id  
LEFT JOIN @blt.ultimate_basketball_dataset.match_player_stats mps ON m.match_id = mps.match_id
LEFT JOIN @blt.ultimate_basketball_dataset.match_stats ms ON m.match_id = ms.match_id
WHERE c.name LIKE '%NBA%'
GROUP BY c.name, c.type
ORDER BY COUNT(m.match_id) DESC, c.name