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
SOCCERTable Name(s): TBL_UEFA_2020 | TBL_UEFA_2021 | TBL_UEFA_2022
TBL_UEFA_2020 | TBL_UEFA_2021 | TBL_UEFA_2022Note : 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.
Find the top 3 teams which scored highest goals while playing at their home ground in UEFA Champions League 2020-21.
The output should contain two columns: TEAM_NAME_HOME and TEAM_HOME_SCORE arranged in descending order of TEAM_HOME_SCORE. Save the query as TEAM_HOME_WITH_MOST_GOALS.
-- TEAM_HOME_WITH_MOST_GOALS
SELECT TEAM_NAME_HOME, TEAM_HOME_SCORE
FROM SOCCER.TBL_UEFA_2020
ORDER BY TEAM_HOME_SCORE DESC
LIMIT 3;| index | TEAM_NAME_HOME | TEAM_HOME_SCORE |
|---|---|---|
| 0 | PSG | 5 |
| 1 | Manchester United | 5 |
| 2 | Barcelona | 5 |
Find the team with majority possession for maximum number of times during UEFA Champions League 2021-22.
The result should include two columns: TEAM_NAME and GAME_COUNT which is number of times the team had majority possession while playing soccer game. Save this query as TEAM_WITH_MAJORITY_POSSESSION
-- TEAM_WITH_MAJORITY_POSSESSION
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 SOCCER.TBL_UEFA_2021
GROUP BY TEAM_NAME
ORDER BY GAME_COUNT DESC
LIMIT 1;| index | TEAM_NAME | GAME_COUNT |
|---|---|---|
| 0 | Liverpool | 9 |
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.
The output should contain two columns: STAGE and TEAM_LOST. Save the query as TEAM_WON_DUEL_LOST_GAME_STAGE_WISE.
-- TEAM_WON_DUEL_LOST_GAME_STAGE_WISE
SELECT STAGE,
CASE
WHEN DUELS_WON_AWAY > DUELS_WON_HOME AND TEAM_AWAY_SCORE < TEAM_HOME_SCORE THEN TEAM_NAME_AWAY
WHEN DUELS_WON_HOME > DUELS_WON_AWAY AND TEAM_HOME_SCORE < TEAM_AWAY_SCORE THEN TEAM_NAME_HOME
END AS TEAM_LOST
FROM SOCCER.TBL_UEFA_2022
WHERE TEAM_LOST IS NOT NULL;| index | STAGE | TEAM_LOST |
|---|---|---|
| 0 | Group stage: Matchday 1 | Chelsea |
| 1 | Group stage: Matchday 1 | København |
| 2 | Group stage: Matchday 1 | Juventus |
| 3 | Group stage: Matchday 1 | Maccabi Haifa |
| 4 | Group stage: Matchday 1 | Rangers |
| 5 | Group stage: Matchday 1 | Liverpool |
| 6 | Group stage: Matchday 1 | Porto |
| 7 | Group stage: Matchday 1 | Bayer Leverkusen |
| 8 | Group stage: Matchday 1 | Viktoria Plze? |
| 9 | Group stage: Matchday 1 | Marseille |
| 10 | Group stage: Matchday 2 | Ajax |
| 11 | Group stage: Matchday 2 | Atletico Madrid |
| 12 | Group stage: Matchday 2 | Barcelona |
| 13 | Group stage: Matchday 2 | Tottenham Hotspur |
| 14 | Group stage: Matchday 2 | RB Leipzig |
| 15 | Group stage: Matchday 3 | Rangers |
| 16 | Group stage: Matchday 3 | Atletico Madrid |
| 17 | Group stage: Matchday 3 | Bayer Leverkusen |
| 18 | Group stage: Matchday 3 | Viktoria Plze? |
| 19 | Group stage: Matchday 3 | Barcelona |
| 20 | Group stage: Matchday 3 | Sporting CP |
| 21 | Group stage: Matchday 3 | Dinamo Zagreb |
| 22 | Group stage: Matchday 3 | AC Milan |
| 23 | Group stage: Matchday 3 | Celtic |
| 24 | Group stage: Matchday 3 | København |
| 25 | Group stage: Matchday 3 | Maccabi Haifa |
| 26 | Group stage: Matchday 4 | Juventus |
| 27 | Group stage: Matchday 4 | Ajax |
| 28 | Group stage: Matchday 4 | Eintracht Frankfurt |
| 29 | Group stage: Matchday 5 | Real Madrid |
| 30 | Group stage: Matchday 5 | København |
| 31 | Group stage: Matchday 5 | Juventus |
| 32 | Group stage: Matchday 5 | Maccabi Haifa |
| 33 | Group stage: Matchday 5 | Rangers |
| 34 | Group stage: Matchday 5 | Viktoria Plze? |
| 35 | Group stage: Matchday 5 | Marseille |
| 36 | Group stage: Matchday 6 | Napoli |
| 37 | Group stage: Matchday 6 | Atletico Madrid |
| 38 | Group stage: Matchday 6 | Internazionale |
| 39 | Group stage: Matchday 6 | Salzburg |
| 40 | Group stage: Matchday 6 | Dinamo Zagreb |
| 41 | Group stage: Matchday 6 | Celtic |
| 42 | Group stage: Matchday 6 | Sevilla |
| 43 | Round of 16 first leg | Chelsea |
| 44 | Round of 16 first leg | Tottenham |
| 45 | Round of 16 first leg | FC Porto |
| 46 | Round of 16 second leg | Paris Saint-Germain |
| 47 | Round of 16 second leg | Dortmund |
| 48 | Round of 16 second leg | Eintracht Frankfurt |
| 49 | Round of 16 second leg | Liverpool |
| 50 | Round of 16 second leg | Leipzig |
| 51 | Quarter-finals first leg | Napoli |
| 52 | Quarter-finals first leg | Chelsea |
| 53 | Semi-finals second leg | Real Madrid |
| 54 | Semi-finals second leg | Milan |
| 55 | Final | Inter |