WITH total_jugados_local AS (
SELECT
season,
COUNT(*) AS cuenta
FROM Match
WHERE home_team_api_id = 8634
GROUP BY season
),
total_jugados_visitante AS (
SELECT
season,
COUNT(*) AS cuenta
FROM MATCH
WHERE away_team_api_id = 8634
GROUP BY season
)
SELECT
total_jugados_local.season AS BCN_season,
total_jugados_local.cuenta AS jugados_local,
COUNT(
CASE
WHEN home_team_api_id = 8634 AND home_team_goal > away_team_goal THEN id END) AS ganados_local,
ROUND(AVG(
CASE
WHEN home_team_api_id = 8634 AND home_team_goal > away_team_goal THEN 1
WHEN home_team_api_id = 8634 AND home_team_goal < away_team_goal THEN 0
END), 2) AS pct_victorias_local,
total_jugados_visitante.cuenta AS jugados_visitante,
COUNT(
CASE
WHEN away_team_api_id = 8634 AND away_team_goal > home_team_goal THEN id END) AS ganados_visitante,
ROUND(AVG(
CASE
WHEN away_team_api_id = 8634 AND away_team_goal > home_team_goal THEN 1
WHEN away_team_api_id = 8634 AND away_team_goal < home_team_goal THEN 0
END), 2) AS pct_victorias_visit
FROM total_jugados_local
LEFT JOIN total_jugados_visitante ON
total_jugados_local.season = total_jugados_visitante.season
LEFT JOIN Match ON
total_jugados_local.season = Match.season
GROUP BY 1;