Liverpool Project

3 hidden cells

DataFrameas

df

variable

SELECT STAGE, TEAM_NAME_HOME AS home_team, TEAM_NAME_AWAY AS away_team, TEAM_HOME_SCORE AS home_goals, TEAM_AWAY_SCORE AS away_goals
FROM SOCCER.TBL_UEFA_2022
WHERE TEAM_NAME_HOME = 'Liverpool' 
   OR TEAM_NAME_AWAY = 'Liverpool'
UNION ALL
SELECT STAGE, TEAM_NAME_HOME AS home_team, TEAM_NAME_AWAY AS away_team, TEAM_HOME_SCORE AS home_goals, TEAM_AWAY_SCORE AS away_goals
FROM SOCCER.TBL_UEFA_2021
WHERE TEAM_NAME_HOME = 'Liverpool' 
   OR TEAM_NAME_AWAY = 'Liverpool'
UNION ALL
SELECT STAGE, TEAM_NAME_HOME AS home_team, TEAM_NAME_AWAY AS away_team, TEAM_HOME_SCORE AS home_score, TEAM_AWAY_SCORE AS away_goals
FROM SOCCER.TBL_UEFA_2020
WHERE TEAM_NAME_HOME = 'Liverpool' 
   OR TEAM_NAME_AWAY = 'Liverpool'
	ORDER BY CASE 
            WHEN home_team = 'Liverpool' THEN home_goals 
            ELSE away_goals 
         END, STAGE;

1 hidden cell

DataFrameas

df1

variable

SELECT *
FROM SOCCER.TBL_UEFA_2021
WHERE STAGE = ' Final ';

1 hidden cell

DataFrameas

df2

variable

SELECT 
    TEAM_NAME_HOME AS home_team, 
    TEAM_NAME_AWAY AS away_team, 
    DUELS_WON_HOME AS home_duels_won, 
    DUELS_WON_AWAY AS away_duels_won,
    CASE 
        WHEN TEAM_NAME_HOME = 'Liverpool' AND TEAM_HOME_SCORE > TEAM_AWAY_SCORE THEN 'Liverpool Won'
        WHEN TEAM_NAME_AWAY = 'Liverpool' AND TEAM_AWAY_SCORE > TEAM_HOME_SCORE THEN 'Liverpool Won'
        ELSE 'Liverpool Did Not Win'
    END AS liverpool_result
FROM 
    SOCCER.TBL_UEFA_2022
WHERE 
    TEAM_NAME_HOME = 'Liverpool' 
    OR TEAM_NAME_AWAY = 'Liverpool'

UNION ALL

SELECT 
    TEAM_NAME_HOME AS home_team, 
    TEAM_NAME_AWAY AS away_team, 
    DUELS_WON_HOME AS home_duels_won, 
    DUELS_WON_AWAY AS away_duels_won,
    CASE 
        WHEN TEAM_NAME_HOME = 'Liverpool' AND TEAM_HOME_SCORE > TEAM_AWAY_SCORE THEN 'Liverpool Won'
        WHEN TEAM_NAME_AWAY = 'Liverpool' AND TEAM_AWAY_SCORE > TEAM_HOME_SCORE THEN 'Liverpool Won'
        ELSE 'Liverpool Did Not Win'
    END AS liverpool_result
FROM 
    SOCCER.TBL_UEFA_2021
WHERE 
    TEAM_NAME_HOME = 'Liverpool' 
    OR TEAM_NAME_AWAY = 'Liverpool'

UNION ALL

SELECT 
    TEAM_NAME_HOME AS home_team, 
    TEAM_NAME_AWAY AS away_team, 
    DUELS_WON_HOME AS home_duels_won, 
    DUELS_WON_AWAY AS away_duels_won,
    CASE 
        WHEN TEAM_NAME_HOME = 'Liverpool' AND TEAM_HOME_SCORE > TEAM_AWAY_SCORE THEN 'Liverpool Won'
        WHEN TEAM_NAME_AWAY = 'Liverpool' AND TEAM_AWAY_SCORE > TEAM_HOME_SCORE THEN 'Liverpool Won'
        ELSE 'Liverpool Did Not Win'
    END AS liverpool_result
FROM 
    SOCCER.TBL_UEFA_2020
WHERE 
    TEAM_NAME_HOME = 'Liverpool' 
    OR TEAM_NAME_AWAY = 'Liverpool'

ORDER BY 
    liverpool_result DESC, 
    home_duels_won DESC, 
    away_duels_won DESC;

5 hidden cells