UEFA_Data_Analysis.sql

USE ROLE analytics1;

USE WAREHOUSE FOOTBALL_ANALYSIS;

USE DATABASE UEFA;

USE SCHEMA CHAMPIONS_LEAGUE;

SHOW TABLES IN SCHEMA UEFA.CHAMPIONS_LEAGUE;

SELECT * FROM DATA_2020;

-- Find the top 3 teams which scored highest goals while playing at their home ground in UEFA Champions League 2020-21. 
SELECT TEAM_NAME_HOME, TEAM_HOME_SCORE
FROM DATA_2020
ORDER BY TEAM_HOME_SCORE DESC 
LIMIT 3; 

-- Find the team with majority possession for maximum number of times during UEFA Champions League 2021-22.
SELECT
    CASE 
		WHEN POSSESSION_HOME > POSSESSION_AWAY THEN TEAM_NAME_HOME
		WHEN POSSESSION_AWAY > POSSESSION_HOME THEN TEAM_NAME_AWAY
		ELSE NULL END AS TEAM_NAME,
	COUNT(*) AS GAME_COUNT
FROM DATA_2021
GROUP BY TEAM_NAME
ORDER BY GAME_COUNT DESC
LIMIT 1;

-- Find the list of teams for each stage of the game, which won the duel in a match but still ended up losing the game in UEFA Championship 2022-23.
SELECT STAGE,
	CASE
        WHEN DUELS_WON_HOME > DUELS_WON_AWAY AND TEAM_HOME_SCORE < TEAM_AWAY_SCORE THEN TEAM_NAME_HOME
        WHEN DUELS_WON_AWAY > DUELS_WON_HOME AND TEAM_AWAY_SCORE < TEAM_HOME_SCORE THEN TEAM_NAME_AWAY
        ELSE NULL
        END AS TEAM_LOST    
FROM DATA_2022
WHERE TEAM_LOST IS NOT NULL;

-- What is the average possession for the home team in Final stages across all years?
WITH all_data AS (
    SELECT STAGE, POSSESSION_HOME
    FROM UEFA.CHAMPIONS_LEAGUE.DATA_2020
    UNION ALL
    SELECT STAGE, POSSESSION_HOME
    FROM UEFA.CHAMPIONS_LEAGUE.DATA_2021
    UNION ALL
    SELECT STAGE, POSSESSION_HOME
    FROM UEFA.CHAMPIONS_LEAGUE.DATA_2022
)
SELECT STAGE, AVG(POSSESSION_HOME) AS AVG_POSSESSION_HOME
FROM all_data
WHERE STAGE LIKE '%finals%'
GROUP BY STAGE
ORDER BY AVG_POSSESSION_HOME DESC;

-- What is the win percentage for home teams when they have more possession than away teams?
WITH all_data AS (
    SELECT TEAM_NAME_HOME, TEAM_NAME_AWAY, POSSESSION_HOME, POSSESSION_AWAY, TEAM_HOME_SCORE, TEAM_AWAY_SCORE
    FROM UEFA.CHAMPIONS_LEAGUE.DATA_2020
    UNION ALL
    SELECT TEAM_NAME_HOME, TEAM_NAME_AWAY, POSSESSION_HOME, POSSESSION_AWAY, TEAM_HOME_SCORE, TEAM_AWAY_SCORE
    FROM UEFA.CHAMPIONS_LEAGUE.DATA_2021
    UNION ALL
    SELECT TEAM_NAME_HOME, TEAM_NAME_AWAY, POSSESSION_HOME, POSSESSION_AWAY, TEAM_HOME_SCORE, TEAM_AWAY_SCORE
    FROM UEFA.CHAMPIONS_LEAGUE.DATA_2022
)
SELECT 
    ROUND(
        (SUM(CASE WHEN TEAM_HOME_SCORE > TEAM_AWAY_SCORE AND POSSESSION_HOME > POSSESSION_AWAY THEN 1 ELSE 0 END) * 100.0) 
        / COUNT(*), 2
    ) AS WIN_PERCENTAGE
FROM all_data;

-- Which team has the highest shot accuracy (shots on target / total shots) across all years?
WITH all_data AS (
    SELECT TEAM_NAME_HOME AS TEAM, SHOTS_ON_TARGET_HOME AS SHOTS_ON_TARGET, TOTAL_SHOTS_HOME AS TOTAL_SHOTS
    FROM UEFA.CHAMPIONS_LEAGUE.DATA_2020
    UNION ALL
    SELECT TEAM_NAME_AWAY AS TEAM, SHOTS_ON_TARGET_AWAY AS SHOTS_ON_TARGET, TOTAL_SHOTS_AWAY AS TOTAL_SHOTS
    FROM UEFA.CHAMPIONS_LEAGUE.DATA_2020
    UNION ALL
    SELECT TEAM_NAME_HOME AS TEAM, SHOTS_ON_TARGET_HOME AS SHOTS_ON_TARGET, TOTAL_SHOTS_HOME AS TOTAL_SHOTS
    FROM UEFA.CHAMPIONS_LEAGUE.DATA_2021
    UNION ALL
    SELECT TEAM_NAME_AWAY AS TEAM, SHOTS_ON_TARGET_AWAY AS SHOTS_ON_TARGET, TOTAL_SHOTS_AWAY AS TOTAL_SHOTS
    FROM UEFA.CHAMPIONS_LEAGUE.DATA_2021
    UNION ALL
    SELECT TEAM_NAME_HOME AS TEAM, SHOTS_ON_TARGET_HOME AS SHOTS_ON_TARGET, TOTAL_SHOTS_HOME AS TOTAL_SHOTS
    FROM UEFA.CHAMPIONS_LEAGUE.DATA_2022
    UNION ALL
    SELECT TEAM_NAME_AWAY AS TEAM, SHOTS_ON_TARGET_AWAY AS SHOTS_ON_TARGET, TOTAL_SHOTS_AWAY AS TOTAL_SHOTS
    FROM UEFA.CHAMPIONS_LEAGUE.DATA_2022
)
SELECT 
    TEAM, 
    ROUND(SUM(SHOTS_ON_TARGET) / SUM(TOTAL_SHOTS) * 100, 2) AS SHOT_ACCURACY_PERCENT
FROM all_data
GROUP BY TEAM
ORDER BY SHOT_ACCURACY_PERCENT DESC
LIMIT 10;

-- Find the teams with the highest number of duels won in Final stages across all years.
WITH all_data AS (
    SELECT TEAM_NAME_HOME AS TEAM, DUELS_WON_HOME AS DUELS_WON
    FROM UEFA.CHAMPIONS_LEAGUE.DATA_2020
    WHERE STAGE LIKE '%final%'
    UNION ALL
    SELECT TEAM_NAME_AWAY AS TEAM, DUELS_WON_AWAY AS DUELS_WON
    FROM UEFA.CHAMPIONS_LEAGUE.DATA_2020
    WHERE STAGE LIKE '%final%'
    UNION ALL
    SELECT TEAM_NAME_HOME AS TEAM, DUELS_WON_HOME AS DUELS_WON
    FROM UEFA.CHAMPIONS_LEAGUE.DATA_2021
    WHERE STAGE LIKE '%final%'
    UNION ALL
    SELECT TEAM_NAME_AWAY AS TEAM, DUELS_WON_AWAY AS DUELS_WON
    FROM UEFA.CHAMPIONS_LEAGUE.DATA_2021
    WHERE STAGE LIKE '%final%'
    UNION ALL
    SELECT TEAM_NAME_HOME AS TEAM, DUELS_WON_HOME AS DUELS_WON
    FROM UEFA.CHAMPIONS_LEAGUE.DATA_2022
    WHERE STAGE LIKE '%final%'
    UNION ALL
    SELECT TEAM_NAME_AWAY AS TEAM, DUELS_WON_AWAY AS DUELS_WON
    FROM UEFA.CHAMPIONS_LEAGUE.DATA_2022
    WHERE STAGE LIKE '%final%'
)
SELECT TEAM, SUM(DUELS_WON) AS TOTAL_DUELS_WON
FROM all_data
GROUP BY TEAM
ORDER BY TOTAL_DUELS_WON DESC
LIMIT 10;

-- Which stage had the highest average number of total shots by both teams combined?
WITH all_data AS (
    SELECT STAGE, TOTAL_SHOTS_HOME, TOTAL_SHOTS_AWAY
    FROM UEFA.CHAMPIONS_LEAGUE.DATA_2020
    UNION ALL
    SELECT STAGE, TOTAL_SHOTS_HOME, TOTAL_SHOTS_AWAY
    FROM UEFA.CHAMPIONS_LEAGUE.DATA_2021
    UNION ALL
    SELECT STAGE, TOTAL_SHOTS_HOME, TOTAL_SHOTS_AWAY
    FROM UEFA.CHAMPIONS_LEAGUE.DATA_2022
)
SELECT 
    STAGE, 
    ROUND(AVG(TOTAL_SHOTS_HOME + TOTAL_SHOTS_AWAY), 2) AS AVG_TOTAL_SHOTS
FROM all_data
GROUP BY STAGE
ORDER BY AVG_TOTAL_SHOTS DESC;

-- Which location has hosted the most matches across all years?
WITH all_data AS (
    SELECT LOCATION
    FROM UEFA.CHAMPIONS_LEAGUE.DATA_2020
    UNION ALL
    SELECT LOCATION
    FROM UEFA.CHAMPIONS_LEAGUE.DATA_2021
    UNION ALL
    SELECT LOCATION
    FROM UEFA.CHAMPIONS_LEAGUE.DATA_2022
)
SELECT LOCATION, COUNT(*) AS MATCH_COUNT
FROM all_data
GROUP BY LOCATION
ORDER BY MATCH_COUNT DESC
LIMIT 5;

-- Which teams have the most wins across all years (both home and away wins combined)?
WITH all_data AS (
    SELECT TEAM_NAME_HOME AS TEAM, TEAM_HOME_SCORE AS HOME_SCORE, TEAM_AWAY_SCORE AS AWAY_SCORE
    FROM UEFA.CHAMPIONS_LEAGUE.DATA_2020
    UNION ALL
    SELECT TEAM_NAME_AWAY AS TEAM, TEAM_AWAY_SCORE AS HOME_SCORE, TEAM_HOME_SCORE AS AWAY_SCORE
    FROM UEFA.CHAMPIONS_LEAGUE.DATA_2020
    UNION ALL
    SELECT TEAM_NAME_HOME AS TEAM, TEAM_HOME_SCORE AS HOME_SCORE, TEAM_AWAY_SCORE AS AWAY_SCORE
    FROM UEFA.CHAMPIONS_LEAGUE.DATA_2021
    UNION ALL
    SELECT TEAM_NAME_AWAY AS TEAM, TEAM_AWAY_SCORE AS HOME_SCORE, TEAM_HOME_SCORE AS AWAY_SCORE
    FROM UEFA.CHAMPIONS_LEAGUE.DATA_2021
    UNION ALL
    SELECT TEAM_NAME_HOME AS TEAM, TEAM_HOME_SCORE AS HOME_SCORE, TEAM_AWAY_SCORE AS AWAY_SCORE
    FROM UEFA.CHAMPIONS_LEAGUE.DATA_2022
    UNION ALL
    SELECT TEAM_NAME_AWAY AS TEAM, TEAM_AWAY_SCORE AS HOME_SCORE, TEAM_HOME_SCORE AS AWAY_SCORE
    FROM UEFA.CHAMPIONS_LEAGUE.DATA_2022
)
SELECT TEAM, COUNT(*) AS WINS
FROM all_data
WHERE HOME_SCORE > AWAY_SCORE
GROUP BY TEAM
ORDER BY WINS DESC
LIMIT 10;

-- Find the top 5 matches with the highest total shots combined (both teams).
WITH all_data AS (
    SELECT DATE, TEAM_NAME_HOME, TEAM_NAME_AWAY, TOTAL_SHOTS_HOME, TOTAL_SHOTS_AWAY
    FROM UEFA.CHAMPIONS_LEAGUE.DATA_2020
    UNION ALL
    SELECT DATE, TEAM_NAME_HOME, TEAM_NAME_AWAY, TOTAL_SHOTS_HOME, TOTAL_SHOTS_AWAY
    FROM UEFA.CHAMPIONS_LEAGUE.DATA_2021
    UNION ALL
    SELECT DATE, TEAM_NAME_HOME, TEAM_NAME_AWAY, TOTAL_SHOTS_HOME, TOTAL_SHOTS_AWAY
    FROM UEFA.CHAMPIONS_LEAGUE.DATA_2022
)
SELECT 
    DATE, 
    TEAM_NAME_HOME, 
    TEAM_NAME_AWAY, 
    TOTAL_SHOTS_HOME, 
    TOTAL_SHOTS_AWAY, 
    (TOTAL_SHOTS_HOME + TOTAL_SHOTS_AWAY) AS TOTAL_SHOTS
FROM all_data
ORDER BY TOTAL_SHOTS DESC
LIMIT 5;

-- Find the team with the most home wins across all years.
SELECT TEAM_NAME_HOME AS TEAM, COUNT(*) AS HOME_WINS
FROM UEFA.CHAMPIONS_LEAGUE.DATA_2020
WHERE TEAM_HOME_SCORE > TEAM_AWAY_SCORE
GROUP BY TEAM_NAME_HOME
UNION ALL
SELECT TEAM_NAME_HOME AS TEAM, COUNT(*) AS HOME_WINS
FROM UEFA.CHAMPIONS_LEAGUE.DATA_2021
WHERE TEAM_HOME_SCORE > TEAM_AWAY_SCORE
GROUP BY TEAM_NAME_HOME
UNION ALL
SELECT TEAM_NAME_HOME AS TEAM, COUNT(*) AS HOME_WINS
FROM UEFA.CHAMPIONS_LEAGUE.DATA_2022
WHERE TEAM_HOME_SCORE > TEAM_AWAY_SCORE
GROUP BY TEAM_NAME_HOME
ORDER BY HOME_WINS DESC
LIMIT 10;