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_HOME_WITH_MOST_GOALS
variable

SELECT TEAM_NAME_HOME,TEAM_HOME_SCORE
FROM SOCCER.TBL_UEFA_2020
ORDER BY TEAM_HOME_SCORE DESC
LIMIT 3;
Spinner
DataFrameas
TEAM_WITH_MAJORITY_POSSESSION
variable
-- TEAM_WITH_MAJORITY_POSSESSION
WITH POSSESSION_COUNT AS (
    SELECT TEAM_NAME_HOME AS TEAM_NAME
    FROM SOCCER.TBL_UEFA_2021
    WHERE POSSESSION_HOME > POSSESSION_AWAY
    UNION ALL
    SELECT TEAM_NAME_AWAY AS TEAM_NAME
    FROM SOCCER.TBL_UEFA_2021
    WHERE POSSESSION_AWAY > POSSESSION_HOME
)
SELECT TEAM_NAME, COUNT(*) AS GAME_COUNT
FROM POSSESSION_COUNT
GROUP BY TEAM_NAME
ORDER BY GAME_COUNT DESC
LIMIT 1;
Spinner
DataFrameas
TEAM_WON_DUEL_LOST_GAME_STAGE_WISE
variable
-- TEAM_WON_DUEL_LOST_GAME_STAGE_WISE
WITH DUEL_LOSS AS (
    
    SELECT STAGE,
           TEAM_NAME_HOME AS TEAM_LOST
    FROM SOCCER.TBL_UEFA_2022
    WHERE DUELS_WON_HOME > DUELS_WON_AWAY
      AND TEAM_HOME_SCORE < TEAM_AWAY_SCORE

    UNION ALL
    
    SELECT STAGE,
           TEAM_NAME_AWAY AS TEAM_LOST
    FROM SOCCER.TBL_UEFA_2022
    WHERE DUELS_WON_AWAY > DUELS_WON_HOME
      AND TEAM_AWAY_SCORE < TEAM_HOME_SCORE
)
SELECT STAGE, TEAM_LOST
FROM DUEL_LOSS
ORDER BY STAGE, TEAM_LOST;