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

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

DataFrameas

TEAM_WITH_MAJORITY_POSSESSION

variable

-- TEAM_WITH_MAJORITY_POSSESSION

-- Majority Possession for number of times during UEFA 2021-2022

-- Create a temporary table using CTE
WITH majority_poss AS(
	SELECT 
-- Using case when to get whose the winning team
    CASE
        WHEN SOCCER.TBL_UEFA_2021.POSSESSION_HOME > SOCCER.TBL_UEFA_2021.POSSESSION_AWAY THEN SOCCER.TBL_UEFA_2021.TEAM_NAME_HOME
        WHEN SOCCER.TBL_UEFA_2021.POSSESSION_HOME < SOCCER.TBL_UEFA_2021.POSSESSION_AWAY THEN SOCCER.TBL_UEFA_2021.TEAM_NAME_AWAY
        ELSE NULL
    END AS TEAM_NAME
FROM SOCCER.TBL_UEFA_2021
WHERE SOCCER.TBL_UEFA_2021.POSSESSION_HOME <> SOCCER.TBL_UEFA_2021.POSSESSION_AWAY
)
-- Count the game played by the winning team
SELECT TEAM_NAME,
       COUNT(*) AS GAME_COUNT
FROM majority_poss
GROUP BY TEAM_NAME
ORDER BY GAME_COUNT DESC
LIMIT 1;

DataFrameas

TEAM_WON_DUEL_LOST_GAME_STAGE_WISE

variable

-- TEAM_WON_DUEL_LOST_GAME_STAGE_WISE

-- Create a temporary table using CTE
WITH temp AS(
	SELECT SOCCER.TBL_UEFA_2022.STAGE,
-- Using case when to flagged the winning team
	CASE
		WHEN SOCCER.TBL_UEFA_2022.TEAM_HOME_SCORE > SOCCER.TBL_UEFA_2022.TEAM_AWAY_SCORE THEN SOCCER.TBL_UEFA_2022.TEAM_NAME_AWAY
		WHEN SOCCER.TBL_UEFA_2022.TEAM_HOME_SCORE < SOCCER.TBL_UEFA_2022.TEAM_AWAY_SCORE THEN
SOCCER.TBL_UEFA_2022.TEAM_NAME_HOME
	    ELSE NULL
	END AS TEAM_LOST,
-- Using case when to flagged which team won the duel
	CASE
		WHEN SOCCER.TBL_UEFA_2022.DUELS_WON_HOME > SOCCER.TBL_UEFA_2022.DUELS_WON_AWAY THEN SOCCER.TBL_UEFA_2022.TEAM_NAME_HOME
		WHEN SOCCER.TBL_UEFA_2022.DUELS_WON_HOME < SOCCER.TBL_UEFA_2022.DUELS_WON_AWAY THEN SOCCER.TBL_UEFA_2022.TEAM_NAME_AWAY
	    ELSE NULL
	END AS DUEL_TEAM_WIN
FROM SOCCER.TBL_UEFA_2022
)
SELECT STAGE, TEAM_LOST
FROM temp
-- With condition losing team equal to duel winning team and is not null
WHERE TEAM_LOST = DUEL_TEAM_WIN AND TEAM_LOST IS NOT NULL

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`