(SQL) Project: Producing Football Insights (Case: UEFA Champions League/UCL) for a Sports Media Agency [Updated Version]

-- Overview of the 20/21 Season
SELECT * FROM SOCCER.TBL_UEFA_2020

SELECT STAGE
FROM SOCCER.TBL_UEFA_2020
WHERE STAGE NOT LIKE 'Group stage: Matchday%'
GROUP BY STAGE
ORDER BY STAGE

-- Group Stage in UCL

SELECT STAGE
FROM SOCCER.TBL_UEFA_2022
WHERE STAGE LIKE '%Group stage: Matchday%' AND STAGE NOT LIKE 'Group stage: Matchday%'
GROUP BY STAGE
ORDER BY STAGE

-- KO Stage in UCL 

SELECT STAGE
FROM SOCCER.TBL_UEFA_2022
WHERE NOT (STAGE LIKE '%Group stage: Matchday%')
GROUP BY STAGE
ORDER BY STAGE

-- Participating Teams in UCL 20/21 Season

SELECT TEAM_NAME_HOME AS team_name 
FROM SOCCER.TBL_UEFA_2020
GROUP BY TEAM_NAME_HOME

UNION 

SELECT TEAM_NAME_AWAY AS team_name
FROM SOCCER.TBL_UEFA_2020
GROUP BY TEAM_NAME_AWAY

-- TEAM_HOME_WITH_MOST_GOALS
SELECT TEAM_NAME_HOME, TEAM_HOME_SCORE 
FROM SOCCER.TBL_UEFA_2020
ORDER BY TEAM_HOME_SCORE DESC
LIMIT 3

-- TOTAL_TEAM_WITH_MOST_GOALS AT HOME in 20/21 Season
SELECT TEAM_NAME_HOME, SUM(TEAM_HOME_SCORE) AS total_goals_home
FROM SOCCER.TBL_UEFA_2020
GROUP BY TEAM_NAME_HOME
ORDER BY total_goals_home DESC

-- TEAM_WITH_MOST_GOALS AT AWAY in 20/21 Season
SELECT TEAM_NAME_AWAY, SUM(TEAM_AWAY_SCORE) AS total_goals_away
FROM SOCCER.TBL_UEFA_2020
GROUP BY TEAM_NAME_AWAY
ORDER BY total_goals_away DESC

-- team with most goals scored during the 20/21 season UCL campaign

SELECT team_name, SUM(total_goals) AS total_goals
FROM (
    SELECT TEAM_NAME_HOME AS team_name, SUM(TEAM_HOME_SCORE) AS total_goals
    FROM SOCCER.TBL_UEFA_2020
    GROUP BY TEAM_NAME_HOME

    UNION ALL

    SELECT TEAM_NAME_AWAY AS team_name, SUM(TEAM_AWAY_SCORE) AS total_goals
    FROM SOCCER.TBL_UEFA_2020
    GROUP BY TEAM_NAME_AWAY
) AS combined_goals
GROUP BY team_name
ORDER BY total_goals DESC;

-- team with most goals conceded during the 20/21 season UCL campaign
SELECT team_name, SUM(total_goals) AS total_goals
FROM (
    SELECT TEAM_NAME_HOME AS team_name, SUM(TEAM_AWAY_SCORE) AS total_goals
    FROM SOCCER.TBL_UEFA_2020
    GROUP BY TEAM_NAME_HOME

    UNION ALL

    SELECT TEAM_NAME_AWAY AS team_name, SUM(TEAM_HOME_SCORE) AS total_goals
    FROM SOCCER.TBL_UEFA_2020
    GROUP BY TEAM_NAME_AWAY
) AS combined_goals
GROUP BY team_name
ORDER BY total_goals DESC;

-- The Champion of 20/21 UCL campaign overview throughout the season

SELECT DISTINCT STAGE AS stage, TEAM_NAME_HOME, TEAM_NAME_AWAY, TEAM_HOME_SCORE, TEAM_AWAY_SCORE 
FROM SOCCER.TBL_UEFA_2020
WHERE TEAM_NAME_HOME LIKE '%Chelsea%' OR TEAM_NAME_AWAY LIKE '%Chelsea%'
ORDER BY stage

-- team with most clean sheets during the 20/21 season UCL campaign
SELECT team_name, SUM(clean_sheets) AS total_clean_sheets
FROM (
    SELECT TEAM_NAME_HOME AS team_name, 
		SUM(CASE 
			WHEN TEAM_AWAY_SCORE = 0 AND TEAM_HOME_SCORE = 0 THEN 1 
			WHEN TEAM_AWAY_SCORE = 0 AND TEAM_HOME_SCORE > 0 THEN 1
			ELSE 0 END) AS clean_sheets
    FROM SOCCER.TBL_UEFA_2020
    GROUP BY stage, TEAM_NAME_HOME

    UNION ALL

    SELECT TEAM_NAME_AWAY AS team_name, 
		SUM(CASE 
			WHEN TEAM_AWAY_SCORE = 0 AND TEAM_HOME_SCORE = 0 THEN 1 
			WHEN TEAM_AWAY_SCORE > 0 AND TEAM_HOME_SCORE = 0 THEN 1
			ELSE 0 END) AS clean_sheets
    FROM SOCCER.TBL_UEFA_2020
    GROUP BY stage, TEAM_NAME_AWAY
) AS cleaned_sheets
GROUP BY team_name
ORDER BY total_clean_sheets DESC;