Project: Producing Soccer Insights for a Sports Media Agency

The UEFA Champions League, often called the Champions League, is a preeminent annual soccer competition that captivates fans worldwide. Established in 1955 as the European Champion Clubs' Cup, it evolved into the UEFA Champions League in 1992, broadening its appeal. The modern format features 32 top-tier club teams selected based on their domestic league performance, adding to the intrigue.

This electrifying event transcends sports, becoming a celebration of unity, culture, and national pride. Fans, draped in their countries' colors, create an electric atmosphere, making the tournament as much about the spectators as the players.Financially, the Champions League is a lifeline for clubs, boosting revenues and offering transformative opportunities. Nevertheless, it sparks debates about wealth disparities in European soccer.

The Champions League is synonymous with historic rivalries, underdog triumphs, and individual brilliance. For players, it represents a career pinnacle, while for fans, it's a cultural phenomenon. The iconic anthem and rituals enrich the soccer experience. In 200 words, the UEFA Champions League is the epitome of European soccer excellence, offering unforgettable moments, financial rewards, and a unique cultural impact, with 32 top clubs adding to its allure.

Schema name: `SOCCER`

Table Name(s): `TBL_UEFA_2020` | `TBL_UEFA_2021` | `TBL_UEFA_2022`

Note : All three tables have same column names and data types

Column	Definition	Data type
`STAGE`	Stage of the March	`VARCHAR(50)`
`DATE`	When the match occurred.	`DATE`
`PENS`	Did the match end with penalty	`VARCHAR(50)`
`PENS_HOME_SCORE`	In case of penalty, score by home team	`VARCHAR(50)`
`PENS_AWAY_SCORE`	In case of penalty, score by away team	`VARCHAR(50)`
`TEAM_NAME_HOME`	Team home name	`VARCHAR(50)`
`TEAM_NAME_AWAY`	Team away name	`VARCHAR(50)`
`TEAM_HOME_SCORE`	Team home score	`NUMBER`
`TEAM_AWAY_SCORE`	Team away score	`NUMBER`
`POSSESSION_HOME`	Ball possession for the home team	`FLOAT`
`POSSESSION_AWAY`	Ball possession for the away team	`FLOAT`
`TOTAL_SHOTS_HOME`	Number of shots by the home team	`NUMBER`
`TOTAL_SHOTS_AWAY`	Number of shots by the away team	`NUMBER`
`SHOTS_ON_TARGET_HOME`	Total shot for home team	`FLOAT`
`SHOTS_ON_TARGET_AWAY`	Total shot for away team	`FLOAT`
`DUELS_WON_HOME`	duel win possession of ball - for home team	`NUMBER`
`DUELS_WON_AWAY`	duel win possession of ball - for away team	`NUMBER`
`PREDICTION_TEAM_HOME_WIN`	Probability of home team to win	`FLOAT`
`PREDICTION_DRAW`	Probability of draw	`FLOAT`
`PREDICTION_TEAM_AWAY_WIN`	Probability of away team to win	`FLOAT`
`LOCATION`	Stadium where the match was held	`VARCHAR(50)`
Note that in Snowflake all databases, tables, and columns are upper case by default.

You will execute SQL queries to answer three questions, as listed in the instructions.

DataFrameas

TEAM_HOME_WITH_MOST_GOALS

variable

-- TEAM_HOME_WITH_MOST_GOALS

-- Output the name of the home team and their score           
SELECT TEAM_NAME_HOME, TEAM_HOME_SCORE
-- Select UEFA Champions League 2020-21
FROM SOCCER.TBL_UEFA_2020
-- Descending on TEAM_HOME_SCORE and limiting to the top 3 teams
ORDER BY TEAM_HOME_SCORE DESC -- Sort by Team home score in descending order
LIMIT 3; -- Limit the result to the top 3 teams

DataFrameas

TEAM_WITH_MAJORITY_POSSESSION

variable

-- TEAM_WITH_MAJORITY_POSSESSION

-- Query to find the team with the maximum number of games having majority possession
SELECT 
    TEAM_NAME,                     -- Name of the team with majority possession
    COUNT(*) AS GAME_COUNT         -- Number of games the team had majority possession
FROM (
    -- Subquery for home teams with majority possession
    SELECT 
        TEAM_NAME_HOME AS TEAM_NAME
    FROM 
        SOCCER.TBL_UEFA_2021
    WHERE 
        POSSESSION_HOME > POSSESSION_AWAY   -- Home team had more possession

    UNION ALL

    -- Subquery for away teams with majority possession
    SELECT 
        TEAM_NAME_AWAY AS TEAM_NAME
    FROM 
        SOCCER.TBL_UEFA_2021
    WHERE 
        POSSESSION_AWAY > POSSESSION_HOME   -- Away team had more possession
) AS MAJORITY_POSSESSION_GAMES
GROUP BY 
    TEAM_NAME
ORDER BY 
    GAME_COUNT DESC  -- Sort by game count in descending order
LIMIT 1;            -- Only return the team with the most games having majority possession

DataFrameas

TEAM_WON_DUEL_LOST_GAME_STAGE_WISE

variable

-- TEAM_WON_DUEL_LOST_GAME_STAGE_WISE

-- Find the teams that won the duel but lost the game in UEFA Champions League 2022-23, stage wise
SELECT 
    STAGE,                              -- The stage of the match
    TEAM_NAME_HOME AS TEAM_LOST         -- Home team that won the duel but lost the game
FROM 
    SOCCER.TBL_UEFA_2022                -- Using data from the 2022-23 UEFA Champions League
WHERE 
    DUELS_WON_HOME > DUELS_WON_AWAY     -- Home team won more duels than away team
    AND TEAM_HOME_SCORE < TEAM_AWAY_SCORE  -- But home team lost the game (home score < away score)
    
UNION ALL

SELECT 
    STAGE,                              -- The stage of the match
    TEAM_NAME_AWAY AS TEAM_LOST         -- Away team that won the duel but lost the game
FROM 
    SOCCER.TBL_UEFA_2022                -- Using data from the 2022-23 UEFA Champions League
WHERE 
    DUELS_WON_AWAY > DUELS_WON_HOME     -- Away team won more duels than home team
    AND TEAM_AWAY_SCORE < TEAM_HOME_SCORE  -- But away team lost the game (away score < home score)
ORDER BY 
    STAGE;                             -- Order the result by the match stage

Project: Producing Soccer Insights for a Sports Media Agency

Table Name(s): TBL_UEFA_2020 | TBL_UEFA_2021 | TBL_UEFA_2022

Note : All three tables have same column names and data types

Table Name(s): `TBL_UEFA_2020` | `TBL_UEFA_2021` | `TBL_UEFA_2022`