You’re part of a group

Switch to your group space and start collaborating with your teammates.

You’re part of a group

Switch to your group space and start collaborating with your teammates.














Sign up
Workspace
Patrick Groepper/

Project: Producing Soccer Insights for a Sports Media Agency

0
Beta
Spinner

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 team homeFLOAT
POSSESSION_AWAYBall possession for the team awayFLOAT
TOTAL_SHOTS_HOMEMethod of transport usedNUMBER
TOTAL_SHOTS_AWAYMillions of journeys, measured in decimalsNUMBER
SHOTS_ON_TARGET_HOMETotal shot for team homeFLOAT
SHOTS_ON_TARGET_AWAYTotal shot for team awayFLOAT
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
LOCATIONMillions of journeys, measured in decimalsVARCHAR(50)
Note that in Snowflake all databases, tables, and columns are upper case by default.
Unknown integration
DataFrameavailable as
TEAM_HOME_WITH_MOST_GOALS
variable
-- Home team with the highest average goals from 2020 --
SELECT 
	TEAM_NAME_HOME, 
	AVG(TEAM_HOME_SCORE) AS AVG_HOME_SCORE
FROM SOCCER.TBL_UEFA_2020
GROUP BY TEAM_NAME_HOME
ORDER BY AVG_HOME_SCORE DESC
LIMIT 5;
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.

Over the course of the 2020 UEFA season Manchester United had the highest average home score.

Unknown integration
DataFrameavailable as
TEAM_WITH_MAJORITY_POSSESSION
variable
-- Number of games in which each team finished with a higher possession in 2021 --
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 5;
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.

Liverpool had more games than anyone else in which they finished with a higher possession rate than their opponents regardless of being at home or away in the 2021 season.

Unknown integration
DataFrameavailable as
TEAM_WON_DUEL_LOST_GAME_STAGE_WISE
variable
-- Teams by matchday in which they won more duels but did not win the game in 2022 --
SELECT 
	STAGE,
	CASE
		WHEN DUELS_WON_HOME > DUELS_WON_AWAY AND TEAM_HOME_SCORE < TEAM_AWAY_SCORE THEN TEAM_NAME_HOME
		WHEN DUELS_WON_AWAY > DUELS_WON_HOME AND TEAM_AWAY_SCORE < TEAM_HOME_SCORE THEN TEAM_NAME_AWAY
		ELSE NULL
		END AS TEAM_LOST
FROM SOCCER.TBL_UEFA_2022
WHERE TEAM_LOST IS NOT NULL;
	
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
  • AI Chat
  • Code