Skip to content

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

ColumnDefinitionData type
STAGEStage of the MarchVARCHAR(50)
DATEWhen the match occurred.DATE
PENSDid the match end with penaltyVARCHAR(50)
PENS_HOME_SCOREIn case of penalty, score by home teamVARCHAR(50)
PENS_AWAY_SCOREIn case of penalty, score by away teamVARCHAR(50)
TEAM_NAME_HOMETeam home nameVARCHAR(50)
TEAM_NAME_AWAYTeam away nameVARCHAR(50)
TEAM_HOME_SCORETeam home scoreNUMBER
TEAM_AWAY_SCORETeam away scoreNUMBER
POSSESSION_HOMEBall possession for the home teamFLOAT
POSSESSION_AWAYBall possession for the away teamFLOAT
TOTAL_SHOTS_HOMENumber of shots by the home teamNUMBER
TOTAL_SHOTS_AWAYNumber of shots by the away teamNUMBER
SHOTS_ON_TARGET_HOMETotal shot for home teamFLOAT
SHOTS_ON_TARGET_AWAYTotal shot for away teamFLOAT
DUELS_WON_HOMEduel win possession of ball - for home teamNUMBER
DUELS_WON_AWAYduel win possession of ball - for away teamNUMBER
PREDICTION_TEAM_HOME_WINProbability of home team to winFLOAT
PREDICTION_DRAWProbability of drawFLOAT
PREDICTION_TEAM_AWAY_WINProbability of away team to winFLOAT
LOCATIONStadium where the match was heldVARCHAR(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.

Spinner
DataFrameas
TEAM_WON_DUEL_LOST_GAME_STAGE_WISE
variable
-- TEAM_WON_DUEL_LOST_GAME_STAGE_WISE
SELECT
	stage,
	CASE
		WHEN duels_won_home > duels_won_away AND team_home_score < team_away_score THEN team_name_home
		WHEN duels_won_home < duels_won_away AND team_home_score > team_away_score THEN team_name_away
		END AS team_lost

FROM SOCCER.TBL_UEFA_2022

WHERE team_lost IS NOT NULL
Spinner
DataFrameas
TEAM_HOME_WITH_MOST_GOALS
variable
-- TEAM_HOME_WITH_MOST_GOALS
WITH TEAM_HOME_WITH_MOST_GOALS AS (
	SELECT TEAM_NAME_HOME, TEAM_HOME_SCORE
	FROM SOCCER.TBL_UEFA_2020
)

SELECT
	TEAM_NAME_HOME,
	MAX(TEAM_HOME_SCORE) AS TEAM_HOME_SCORE

FROM TEAM_HOME_WITH_MOST_GOALS

GROUP BY TEAM_NAME_HOME

ORDER BY TEAM_HOME_SCORE DESC

LIMIT 3
Spinner
DataFrameas
TEAM_WITH_MAJORITY_POSSESSION
variable
-- TEAM_WITH_MAJORITY_POSSESSION
WITH home_team_possession AS (
	SELECT
		team_name_home as team_name,

	CASE
		WHEN possession_home > possession_away THEN '1' ELSE '0'
		END AS possession_wins

	FROM SOCCER.TBL_UEFA_2021
),

away_team_possession AS (
	SELECT
		team_name_away as team_name,

	CASE
		WHEN possession_home < possession_away THEN '1' ELSE '0'
		END AS possession_wins

	FROM SOCCER.TBL_UEFA_2021
),

possession_teams AS (
	SELECT *
	FROM home_team_possession
	UNION ALL
	SELECT *
	FROM away_team_possession
)

SELECT
	team_name,
	SUM(possession_wins) AS game_count
FROM possession_teams
GROUP BY team_name
ORDER BY game_count DESC
LIMIT 1