🏟️ Report 5: Venue Usage Report

SELECT v.venue_name, v.city, v.state, v.capacity, COUNT(m.match_id) AS total_matches, SUM(m.result = 'Win') AS wins_at_venue, SUM(m.result = 'Loss') AS losses_at_venue, SUM(m.result = 'Draw') AS draws_at_venue, ROUND(AVG(m.team_score), 1) AS avg_team_score, ROUND(AVG(m.opponent_score), 1) AS avg_opp_score, MAX(m.match_date) AS last_match_date FROM venue v LEFT JOIN `match` m ON m.venue_id = v.venue_id GROUP BY v.venue_id ORDER BY total_matches DESC;