SQL_Intermediate_at-DataCamp.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Intermediate SQL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## CASE statements\n",
    "Contains a WHEN, THEN, and ELSE statement, finished with END\n",
    "```SQL\n",
    " CASE WHEN x = 1 THEN \"a\" \n",
    "     WHEN x = 2 THEN \"b\"\n",
    "     ELSE \"c\" END AS new_column\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Basic CASE statements\n",
    "The European Soccer Database contains data about matches from 11 European countries from the 2011-2015 match seasons. In total, you will be working with data from 12,800 matches!\n",
    "\n",
    "You'll notice that in the match table, there are two columns called `hometeam_id` and `awayteam_id`. Both ID's can join to the teamapiid column in the team table, but you cannot perform a join on both columns at the same time.\n",
    "\n",
    "You can use a CASE statement to identify which teams played in a match. However, your first step is to determine which IDs correspond to the FC Schalke 04 and FC Bayern Munich teams by querying the team table. You will then use these IDs to examine matches played by these teams in the match table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**match**\n",
    "\n",
    "`\n",
    "id\tcountry_id\tseason\tstage\tdate\thometeam_id\tawayteam_id\thome_goal\taway_goal\n",
    "757 \t 1\t     2011/2012\t 1\t2011-07-29\t1773\t     8635\t       2\t          1\n",
    "758\t  1\t     2011/2012\t 1\t2011-07-30\t9998\t     9985\t       1\t          1\n",
    "759\t  1    \t 2011/2012\t 1\t2011-07-30\t9987\t     9993\t       3\t          1\n",
    ".............................`\n",
    "\n",
    "\n",
    "**team**\n",
    "\n",
    "`\n",
    "id\tteam_api_id\tteam_long_name\tteam_short_name\n",
    "1\t  9987\t        KRC Genk\t         GEN\n",
    "2\t  9993\t        Beerschot AC\t     BAC\n",
    "3\t  10000\t       SV Zulte-Waregem\t ZUL\n",
    ".............................\n",
    "`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "   **Instructions**\n",
    "   \n",
    "**1.1**- Create a query that gives you the team IDs for FC Schalke 04 and FC Bayern Munich.\n",
    "\n",
    "```SQL\n",
    "SELECT\n",
    "\t-- Select the team long name and team API id\n",
    "\tteam_long_name, team_api_id\n",
    "\t\n",
    "FROM team \n",
    "-- Only include FC Schalke 04 and FC Bayern Munich\n",
    "WHERE team_long_name IN ('FC Schalke 04', 'FC Bayern Munich');\n",
    "\n",
    "```\n",
    "`#OUTPUT\n",
    "team_long_name\t      team_api_id\n",
    "FC Bayern Munich\t     9823\n",
    "FC Schalke 04\t        10189\n",
    ".............................`\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "**2.1** Create a CASE statement that identifies whether a match played in Germany included Bayern Munich, Schalke 04, or neither.<br>\n",
    "**2.2** .Count the id column to determine the number of matches played by each team.<br>\n",
    "**2.3** Group the query by the CASE statement.\n",
    "\n",
    "\n",
    "```SQL\n",
    "-- Identify if a match included Bayern Munich, Schalke 04, or neither\n",
    "SELECT \n",
    "\tCASE WHEN hometeam_id = 10189 OR awayteam_id = 10189 THEN 'FC Schalke 04'\n",
    "        WHEN  hometeam_id = 9823  OR awayteam_id = 9823 THEN 'FC Bayern Munich'\n",
    "         ELSE 'Other' END AS team_name,\n",
    "\tCOUNT(id) AS matches\n",
    "FROM match\n",
    "WHERE country_id = 7809\n",
    "-- Group by the CASE statement\n",
    "GROUP BY  \n",
    "\tCASE WHEN hometeam_id = 10189 OR awayteam_id = 10189 THEN 'FC Schalke 04'\n",
    "        WHEN  hometeam_id = 9823  OR awayteam_id = 9823 THEN 'FC Bayern Munich'\n",
    "         ELSE 'Other' END\n",
    "```\n",
    "`OUTPUT\n",
    "team_name\t          matches\n",
    "Other\t                960\n",
    "FC Schalke 04\t        136\n",
    "FC Bayern Munich     \t128\n",
    ".............................`\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### CASE statements comparing column values\n",
    "\n",
    " **QUERY:** Querying a list of all matches in the 2011/2012 season where Barcelona was the home team. You will do this using a CASE statement that compares the values of two columns to create a new group -- wins, losses, and ties. The match table contains two columns -- home team ID, and away team ID, to join with the team table. Since you can only join team to one column at a time, you will create two queries in this and the next exercise to examine Barcelona's performance.\n",
    "\n",
    "```SQL\n",
    "SELECT m.date, t.team_long_name AS opponent\n",
    "    CASE WHEN m.home_goal >  m.away_goal THEN 'Barcelone Win'\n",
    "         WHEN m.home_goal < m.away_goal THEN 'Barcelona Loss :('\n",
    "         ELSE 'Tie' END AS outcome\n",
    "\n",
    "FROM match AS m\n",
    "LEFT JOIN team AS t\n",
    "ON m.awayteam_id = t.team_api_id\n",
    "-- Filter for 2012/2013 and Barcelona as the home team\n",
    "WHERE m.season = '2011/2012' AND m.hometeam_id = 8634\n",
    "-- Order by the date, with most recent first\n",
    "ORDER BY m.date ASC; \n",
    "\n",
    "```\n",
    "`OUTPUT\n",
    "2012-04-10\tGetafe CF\t Barcelona win!\n",
    "2012-04-21\tReal Madrid   CFBarcelona loss :(\n",
    "2012-05-02\tMálaga CF\t Barcelona win!\n",
    "2012-05-05\tRCD           Espanyol\n",
    ".............................`\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```SQL\n",
    "-- Select matches where Barcelona was the away team\n",
    "SELECT  \n",
    "\tm.date,\n",
    "\tt.team_long_name AS opponent,\n",
    "\tCASE WHEN m.home_goal < m.away_goal THEN 'Barcelona win!'\n",
    "         WHEN m.home_goal > m.away_goal THEN 'Barcelona loss :(' \n",
    "         ELSE 'Tie' END AS outcome\n",
    "FROM match AS m\n",
    "-- Join the team table to match\n",
    "LEFT JOIN team AS t ON m.hometeam_id = t.team_api_id\n",
    "WHERE  m.season = '2011/2012' AND m.awayteam_id = 8634\n",
    "ORDER BY m.date;\n",
    "```\n",
    "`OUTPUT\n",
    "2011-09-10\tReal Sociedad\t             Tie\n",
    "2011-09-21\tValencia CF\t               Tie\n",
    "2011-10-02\tReal Sporting de Gijón\t    Barcelona win!\n",
    ".............................`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### CASE WHEN things to look out for\n",
    " **QUERY:**  Barcelona and Real Madrid have been rival teams for more than 80 years. Matches between these two teams are given the name El Clásico (The Classic). In this exercise, you will query a list of matches played between these two rivals.\n",
    "\n",
    "\n",
    "```SQL\n",
    "SELECT \n",
    "\tdate,\n",
    "\tCASE WHEN hometeam_id = 8634 THEN 'FC Barcelona' \n",
    "         ELSE 'Real Madrid CF' END as home,\n",
    "\tCASE WHEN awayteam_id = 8634 THEN 'FC Barcelona' \n",
    "         ELSE 'Real Madrid CF' END as away,\n",
    "\t-- Identify all 5 possible match outcomes\n",
    "\tCASE WHEN home_goal > away_goal AND hometeam_id = 8634 THEN 'Barcelona win!'\n",
    "          WHEN home_goal > away_goal AND hometeam_id = 8633 THEN 'Real Madrid win!'\n",
    "          WHEN home_goal < away_goal AND awayteam_id = 8634 THEN 'Barcelona win!'\n",
    "          WHEN home_goal < away_goal AND awayteam_id = 8633 THEN 'Real Madrid win!'\n",
    "          ELSE 'Tie!' END AS outcome\n",
    "FROM match\n",
    "WHERE (awayteam_id = 8634 OR hometeam_id = 8634)\n",
    "      AND (awayteam_id = 8633 OR hometeam_id = 8633);\n",
    "```\n",
    "`OUTPUT\n",
    "date\t      home\t           away\t          outcome\n",
    "2011-12-10\tReal Madrid CF\tFC Barcelona\t  Barcelona win!\n",
    "2012-04-21\tFC Barcelona\t  Real Madrid CF\tReal Madrid win!\n",
    "2013-03-02\tReal Madrid CF\tFC Barcelona\t  Real Madrid win!\n",
    "2012-10-07\tFC Barcelona\t  Real Madrid CF\tTie!\n",
    "..........................................................`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will use the match and `country` table which showes below.\n",
    "\n",
    "`country`<br>\n",
    "`id\t    name\n",
    "1\t     Belgium\n",
    "1729\t  England\n",
    "4769\t  France\n",
    "7809\t  Germany\n",
    "10257\t Italy\n",
    "13274\t Netherlands\n",
    "15722\t Poland\n",
    ".............................`\n",
    "<br>\n",
    "<br>\n",
    "`match` <br>\n",
    "`id\tcountry_id\tseason\t  stage\t    date\thometeam_id\tawayteam_id\thome_goal\taway_goal\n",
    "757\t1\t       2011/2012\t   1\t  2011-07-29\t1773\t     8635\t          2\t       1\n",
    "758\t1\t       2011/2012\t   1\t  2011-07-30\t9998\t     9985\t          1\t       1\n",
    "759\t1\t       2011/2012\t   1\t  2011-07-30\t9987\t     9993\t          3\t       1\n",
    "760\t1\t       2011/2012\t   1\t  2011-07-30\t9991\t     9984\t          0\t       1\n",
    "761\t1\t       2011/2012\t   1\t  2011-07-30\t9994\t     10000\t         0\t       0\n",
    "762\t1\t       2011/2012\t   1\t  2011-07-30\t8571\t     9989\t          1\t       1\n",
    ".......................................................................................`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " **QUERY:** You will examine the number of matches played in 3 seasons within each country listed in the database. This is much easier to explore with each season's matches in separate columns. Using the country and match table, count the number of matches played in each country during the 2013, 2014, and 2015 seasons\n",
    "```SQL\n",
    "SELECT \n",
    "\tc.name AS country,\n",
    "    -- Count all games from the 2013, 2014, and 2015 seasons\n",
    "\tCOUNT(CASE WHEN m.season = '2012/2013' THEN m.id END) AS matches_2012_2013,\n",
    "\tCOUNT(CASE WHEN m.season = '2013/2014' THEN m.id END) AS matches_2013_2014,\n",
    "\tCOUNT(CASE WHEN m.season = '2014/2015' THEN m.id END) AS matches_2014_2015\n",
    "-- Join the match table onto the country table\n",
    "FROM country AS c\n",
    "LEFT JOIN match AS m\n",
    "ON c.id = m.country_id\n",
    "GROUP BY c.name;\n",
    "\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " **QUERY:**  You will use the country and match table to sum the number of matches won by the home team in each country during the 2013, 2014, and 2015 soccer seasons.\n",
    "```SQL\n",
    "SELECT \n",
    "\tc.name AS country,\n",
    "    -- Sum the total records in each season where the home team won\n",
    "\tSUM(CASE WHEN m.season = '2012/2013' AND m.home_goal > m.away_goal \n",
    "        THEN 1 ELSE 0 END) AS matches_2012_2013,\n",
    " \tSUM(CASE WHEN m.season = '2013/2014' AND m.home_goal > away_goal\n",
    "        THEN 1 ELSE 0 END) AS matches_2013_2014,\n",
    "\tSUM(CASE WHEN m.season = '2014/2015' AND home_goal > away_goal\n",
    "\t    THEN 1 ELSE 0 END) AS matches_2014_2015\n",
    "FROM country AS c\n",
    "LEFT JOIN match AS m\n",
    "ON c.id = m.country_id\n",
    "GROUP BY c.name;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "  **QUERY:** You will examine data about the number and percentage of wins, loses, and ties in each country.\n",
    "```SQL\n",
    "SELECT \n",
    "\tc.name AS country,\n",
    "    -- Round the percentage of tied games to 2 decimal points\n",
    "\tROUND(AVG(CASE WHEN m.season='2013/2014' AND m.home_goal = m.away_goal THEN 1\n",
    "\t\t\t WHEN m.season='2013/2014' AND m.home_goal != m.away_goal THEN 0\n",
    "\t\t\t END)*100,2) AS ties_2014,\n",
    "\tROUND(AVG(CASE WHEN m.season='2014/2015' AND m.home_goal = m.away_goal THEN 1\n",
    "\t\t\t WHEN m.season='2014/2015' AND m.home_goal != m.away_goal THEN 0\n",
    "\t\t\t END)*100,2) AS ties_2015\n",
    "FROM country AS c\n",
    "LEFT JOIN match AS m\n",
    "ON c.id = m.country_id\n",
    "GROUP BY c.name;\n",
    "\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Short and Simple Subqueries\n",
    "\n",
    "### Filtering using scalar subqueries\n",
    "\n",
    " **QUERY:** You will generate a list of teams whose total goals scored (for both teams in total) is more than 3 times the average for games in the 2013/2014 season.\n",
    " \n",
    " ```SQL\n",
    " SELECT \n",
    "\t-- Select the date, home team and away team ID/goals scored\n",
    "    date,\n",
    "\thometeam_id,\n",
    "\tawayteam_id,\n",
    "\thome_goal,\n",
    "\taway_goal\n",
    "FROM  match\n",
    "WHERE \n",
    "\tseason = '2013/2014'\n",
    "-- Filter for matches where total goals exceeds 3x the average\n",
    "\tAND (home_goal + away_goal) >\n",
    "        (SELECT 3 * AVG(home_goal + away_goal)\n",
    "         FROM match); \n",
    " \n",
    " ```\n",
    " `OUTPUT \n",
    " date\thometeam_id\tawayteam_id\thome_goal\taway_goal\n",
    "2013-12-14\t8456\t     9825\t        6\t      3\n",
    "2014-03-22\t8344\t     8650\t        3\t      6\n",
    "2013-10-30\t8633\t     8302\t        7\t      3\n",
    ".............................`\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Filtering using a subquery with a list\n",
    "**QUERY** You will generate a list of teams that do not have any home matches listed in the database.\n",
    "```SQL\n",
    "SELECT \n",
    "\t-- Select the team long and short names\n",
    "\tteam_long_name,\n",
    "\tteam_short_name\n",
    "FROM team\n",
    "WHERE \n",
    "\t-- Filter for teams that are NOT IN the subquery\n",
    "\tteam_api_id NOT IN\n",
    "     (SELECT DISTINCT hometeam_id  FROM match);\n",
    "```\n",
    "`OUTPUT\n",
    "team_long_name\tteam_short_name\n",
    "FCV Dender EH\t DEN\n",
    "KSV Roeselare\t ROS\n",
    "Tubize\t        TUB\n",
    ".............................`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Filtering with more complex subquery conditions\n",
    "**QUERY**  Based on the single subquery created, you aren't able to determine if the 52 teams listed played any matches at all. In order to do this, you would need to construct a second subquery in the WHERE clause also eliminating teams whose ID's were in a list of away games.\n",
    "```SQL\n",
    "SELECT\n",
    "\t-- Select the team long and short names\n",
    "\tteam_long_name,\n",
    "\tteam_short_name\n",
    "FROM team\n",
    "WHERE \n",
    "\t-- Filter by the 2 subquery lists of home and away team IDs\n",
    "\tteam_api_id NOT IN\n",
    "   \t\t(SELECT DISTINCT hometeam_id FROM match)\n",
    "    AND team_api_id NOT IN\n",
    "  \t    (SELECT DISTINCT awayteam_id FROM match);\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Subqueries in FROM\n",
    "\n",
    " **QUERY** you will be creating a table of values in a subquery to include in a main query, and join it to a table from the database.\n",
    "```SQL\n",
    "SELECT\n",
    "\t-- Select country name, season, and the count of match IDs\n",
    "    c.name AS country,\n",
    "    COUNT(m.id) AS matches\n",
    "FROM country AS c\n",
    "-- Inner join the subquery onto country\n",
    "-- Select the country id and match id columns\n",
    "INNER  JOIN (SELECT country_id , id \n",
    "           FROM match\n",
    "           -- Filter the subquery by matches with 10 or more goals\n",
    "           WHERE (home_goal + away_goal) >= 10) AS m\n",
    "ON c.id = m.country_id\n",
    "GROUP BY c.name;\n",
    "```\n",
    "`OUTPUT\n",
    "country\t    matches\n",
    "Netherlands\t    1\n",
    "Spain\t          4\n",
    "Germany            1\n",
    "England            3`\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**QUERY** In the previous exercise, you found that England, Netherlands, Germany and Spain were the only countries that had matches in the database where 10 or more goals were scored overall. Let's find out some more details about those matches -- when they were played, during which seasons, and how many of the goals were home vs. away goals.\n",
    "```SQL\n",
    "SELECT\n",
    "\t-- Select country, date, home, and away goals from the subquery\n",
    "    country,\n",
    "    date,\n",
    "    home_goal,\n",
    "    away_goal\n",
    "FROM\n",
    "\t-- Select country name, date, and total goals in the subquery\n",
    "\t(SELECT c.name AS country, \n",
    "     \t    m.date, \n",
    "     \t\tm.home_goal, \n",
    "     \t\tm.away_goal,\n",
    "           (m.home_goal + m.away_goal) AS total_goals\n",
    "    FROM match AS m\n",
    "    LEFT JOIN country AS c\n",
    "    ON m.country_id = c.id) AS subq\n",
    "-- Filter by total goals scored in the main query\n",
    "WHERE total_goals >= 10;\n",
    "```\n",
    "`OUTPUT\n",
    "country\t    date\t   home_goal\taway_goal\n",
    "England \t   2011-08-28\t8\t     2\n",
    "England\t    2012-12-29\t7\t     3\n",
    "England\t    2013-05-19\t5\t     5\n",
    "Germany\t    2013-03-30\t9\t     2\n",
    "Netherlands\t2011-11-06\t6\t     4\n",
    "Spain\t      2013-10-30\t7\t     3\n",
    "Spain\t      2015-04-05\t9\t     1\n",
    "Spain\t      2015-05-23\t7\t     3`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following table shows the example of league table.\n",
    "\n",
    "`league \n",
    "id\t  country_id\tname\n",
    "1\t     1\t    Belgium Jupiler League\n",
    "1729\t 1729\t  England Premier League\n",
    "4769\t 4769\t  France Ligue 1\n",
    "7809\t 7809\t  Germany 1. Bundesliga\n",
    "10257\t10257\t Italy Serie A\n",
    "13274\t13274\t Netherlands Eredivisie\n",
    "15722\t15722\t Poland Ekstraklasa\n",
    "17642\t17642\t Portugal Liga ZON Sagres\n",
    "..........................`\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "### Subqueries in SELECT\n",
    "**QUERY** You will construct a query that calculates the average number of goals per match in each country's league.\n",
    "\n",
    "```SQL\n",
    "SELECT \n",
    "\tl.name AS league,\n",
    "    -- Select and round the league's total goals\n",
    "    ROUND(AVG(m.home_goal + m.away_goal), 2) AS avg_goals,\n",
    "    -- Select & round the average total goals for the season\n",
    "    (SELECT ROUND(AVG(home_goal + away_goal), 2) \n",
    "     FROM match\n",
    "     WHERE season = '2013/2014') AS overall_avg\n",
    "FROM league AS l\n",
    "LEFT JOIN match AS m\n",
    "ON l.country_id = m.country_id\n",
    "-- Filter for the 2013/2014 season\n",
    "WHERE season = '2013/2014'\n",
    "GROUP BY l.name;\n",
    "\n",
    "```\n",
    "`OUTPUT\n",
    "league\t                    avg_goals\toverall_avg\n",
    "Switzerland Super League\t   2.89\t    2.77\n",
    "Poland Ekstraklasa\t         2.64\t    2.77\n",
    "Netherlands Eredivisie\t     3.20\t    2.77`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**NOTE: When writing queries in SELECT, it's important to remember that filtering the main query does not filter the subquery -- and vice versa.**\n",
    "**QUERY** In the previous exercise, you created a column to compare each league's average total goals to the overall average goals in the 2013/2014 season. In this exercise, you will add a column that directly compares these values by subtracting the overall average from the subquery.\n",
    "```SQL\n",
    "SELECT\n",
    "\t-- Select the league name and average goals scored\n",
    "\tname AS league,\n",
    "\tROUND(AVG(m.home_goal + m.away_goal),2) AS avg_goals,\n",
    "    -- Subtract the overall average from the league average\n",
    "\tROUND(AVG(m.home_goal + m.away_goal) -\n",
    "\t\t(SELECT AVG(home_goal + away_goal)\n",
    "\t\t FROM match \n",
    "         WHERE season = '2013/2014'),2) AS diff\n",
    "FROM league AS l\n",
    "LEFT JOIN match AS m\n",
    "ON l.country_id = m.country_id\n",
    "-- Only include 2013/2014 results\n",
    "WHERE season = '2013/2014'\n",
    "GROUP BY l.name;\n",
    "```\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Subqueries everywhere!\n",
    "**QUERY** You will build a query that contains three subqueries -- one in the SELECT clause, one in the FROM clause, and one in the WHERE clause. The final query will extract data assessing how the average home and away goals scored changes across match stages. Do teams score more or less as the stakes get higher?\n",
    "\n",
    "```sQL\n",
    "SELECT \n",
    "\t-- Select the stage and average goals for each stage\n",
    "m.stage,\n",
    "    ROUND(AVG(m.home_goal + m.away_goal),2) AS avg_goals,\n",
    "    -- Select the average overall goals for the 2012/2013 season\n",
    "    ROUND((SELECT AVG(home_goal + away_goal) \n",
    "           FROM match \n",
    "           WHERE season = '2012/2013'),2) AS overall\n",
    "FROM match AS m\n",
    "WHERE season = '2012/2013'\n",
    "GROUP BY m.stage;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**QUERY** you will turn the main query into a subquery to extract a list of stages where the average home goals in a stage, is higher than the overall average for home goals in a match.\n",
    "\n",
    "```SQL\n",
    "SELECT \n",
    "\t-- Select the stage and average goals from the subquery\n",
    "\ts.stage,\n",
    "\tROUND(s.avg_goals,2) AS avg_goals\n",
    "FROM \n",
    "\t-- Select the stage and average goals in 2013/2014\n",
    "\t(SELECT\n",
    "\t\t stage,\n",
    "         AVG(home_goal + away_goal) AS avg_goals\n",
    "\t FROM match\n",
    "\t WHERE season = '2012/2013'\n",
    "\t GROUP BY stage) AS s\n",
    "WHERE \n",
    "\t-- Filter the main query using the subquery\n",
    "\ts.avg_goals > (SELECT AVG(home_goal + away_goal) \n",
    "                    FROM match WHERE season = '2012/2013');```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**QUERY** In the previous exercise, you added a subquery to the FROM statement and selected the stages where the number of average goals in a stage exceeded the overall average number of goals in the 2012/2013 match season. In this final step, you will add a subquery in SELECT to compare the average number of goals scored in each stage to the total.\n",
    "```SQL\n",
    "SELECT \n",
    "\t-- Select the stage and average goals from s\n",
    "\ts.stage,\n",
    "    ROUND(s.avg_goals,2) AS avg_goal,\n",
    "    -- Select the overall average for 2012/2013\n",
    "    (SELECT AVG(home_goal + away_goal) FROM match WHERE season = '2012/2013') AS overall_avg\n",
    "FROM \n",
    "\t-- Select the stage and average goals in 2012/2013 from match\n",
    "\t(SELECT\n",
    "\t\t stage,\n",
    "         AVG(home_goal + away_goal) AS avg_goals\n",
    "\t FROM match\n",
    "\t WHERE season = '2012/2013'\n",
    "\t GROUP BY stage) AS s\n",
    "WHERE \n",
    "\t-- Filter the main query using the subquery\n",
    "\ts.avg_goals > (SELECT AVg(home_goal+ away_goal) \n",
    "                    FROM match WHERE season = '2012/2013');```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Basic Correlated Subqueries\n",
    "Correlated subqueries are subqueries that reference one or more columns in the main query. Correlated subqueries depend on information in the main query to run, and thus, cannot be executed on their own.\n",
    "\n",
    "**QUERY** In this exercise, you will practice using correlated subqueries to examine matches with scores that are extreme outliers for each country -- above 3 times the average score!\n",
    "\n",
    "```SQL\n",
    "SELECT \n",
    "\t-- Select country ID, date, home, and away goals from match\n",
    "\tmain.country_id,\n",
    "    main.date,\n",
    "    main.home_goal, \n",
    "    main.away_goal\n",
    "FROM match AS main\n",
    "WHERE \n",
    "\t-- Filter the main query by the subquery\n",
    "\t(home_goal + away_goal) > \n",
    "        (SELECT AVG((sub.home_goal + sub.away_goal) * 3)\n",
    "         FROM match AS sub\n",
    "         -- Join the main query to the subquery in WHERE\n",
    "         WHERE main.country_id = sub.country_id);\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**QUERY**  In this exercise, you're going to add an additional column for matching to answer the question -- what was the highest scoring match for each country, in each season?\n",
    "```SQL\n",
    "SELECT \n",
    "\t-- Select country ID, season, home, and away goals from match\n",
    "\tmain.country_id,\n",
    "    main.date,\n",
    "    main.home_goal,\n",
    "    main.away_goal\n",
    "FROM match AS main\n",
    "WHERE \n",
    "\t-- Filter for matches with the highest number of goals scored\n",
    "\t(home_goal + away_goal) =\n",
    "        (SELECT MAX(sub.home_goal + sub.away_goal)\n",
    "         FROM match AS sub\n",
    "         WHERE main.country_id = sub.country_id\n",
    "               AND main.season = sub.season);\n",
    "\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Nested Subqueries\n",
    "\n",
    "**QUERY** In this exercise, you will practice creating a nested subquery to examine the highest total number of goals in each season, overall, and during July across all seasons.\n",
    "```SQL\n",
    "SELECT\n",
    "\t-- Select the season and max goals scored in a match\n",
    "\tseason,\n",
    "    MAX(home_goal + away_goal) AS max_goals,\n",
    "    -- Select the overall max goals scored in a match\n",
    "   (SELECT MAX(home_goal + away_goal) FROM match) AS overall_max,\n",
    "   -- Select the max number of goals scored in any match in July\n",
    "   (SELECT MAX(home_goal + away_goal) \n",
    "    FROM match\n",
    "    WHERE id IN (\n",
    "          SELECT id FROM match WHERE EXTRACT(MONTH FROM date) = 07)) AS july_max\n",
    "FROM match\n",
    "GROUP BY season;\n",
    "```\n",
    "\n",
    "**QUERY**In this exercise, you will practice creating some more complicated mathematical computations. Your task is to answer a two-part question with a nested subquery in the FROMstatement: What is the average monthly maximum of goals in each season, and which season has the highest monthly max average?\n",
    "```SQL\n",
    "SELECT\n",
    "\t-- Select the season and max home, away, total averages\n",
    "    season,\n",
    "    MAX(sub2.avg_max_home) AS max_home_avg,\n",
    "    MAX(sub2.avg_max_away) AS max_away_avg,\n",
    "    MAX(sub2.total_goals) AS max_total_avg\n",
    "FROM\n",
    "\t-- Select the season, average max and average total goals\n",
    "\t(SELECT \n",
    "     \t sub1.season,\n",
    "     \t AVG(sub1.max_home) AS avg_max_home, \n",
    "     \t AVG(sub1.max_away) AS avg_max_away,\n",
    "         AVG(sub1.max_home + sub1.max_away) AS total_goals\n",
    "    FROM\n",
    "     \t-- Select the season, month, and max of home/away goals\n",
    "     \t(SELECT\n",
    "        \tseason,\n",
    "        \tEXTRACT(MONTH FROM m.date) AS month,\n",
    "        \tMAX(m.home_goal) AS max_home,\n",
    "        \tMAX(m.away_goal) AS max_away\n",
    "         FROM match AS m\n",
    "         GROUP BY season, EXTRACT(MONTH FROM m.date)) AS sub1\n",
    "    GROUP BY season) AS sub2\n",
    "GROUP BY season;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "you generated a list of countries and the number of matches in each country with more than 10 goals overall. The query in that exercise was listed in the FROM statement. In this exercise, you will set up a similar exercise using a CTE.\n",
    "\n",
    "```SQL\n",
    "-- Set up your CTE\n",
    "WITH list AS (\n",
    "    SELECT \n",
    "  \t\tcountry_id, \n",
    "  \t\tid\n",
    "    FROM match\n",
    "    WHERE (home_goal + away_goal) >= 10)\n",
    "-- Select league and count of matches from the CTE\n",
    "SELECT\n",
    "    l.name AS league,\n",
    "    COUNT(list.id) AS matches\n",
    "FROM league AS l\n",
    "-- Join the CTE to the league table\n",
    "LEFT JOIN list ON l.id = list.country_id\n",
    "GROUP BY l.name;\n",
    "```\n",
    "**QUERY** In the previous exercise, you modified a task completed in chapter 2. In this exercise, you will expand on the following exercise looking at details about these matches with very high scores. Just like a subquery in FROM, you can join tables inside a CTE.\n",
    "```SQL\n",
    "-- Set up your CTE\n",
    "with list AS (\n",
    "  -- Select the league name, date, home, and away goals\n",
    "    SELECT \n",
    "  \t\tl.name AS league, \n",
    "     \tm.date, \n",
    "  \t\tm.home_goal, \n",
    "  \t\tm.away_goal,\n",
    "       (m.home_goal + m.away_goal) AS total_goals\n",
    "    FROM match AS m\n",
    "    LEFT JOIN league as l ON m.country_id = l.id)\n",
    "-- Select the league, name, date, home and away goals from the CTE\n",
    "SELECT  league, date, home_goal, away_goal\n",
    "FROM list\n",
    "-- Filter by total goals\n",
    "WHERE total_goals >=10;```\n",
    "\n",
    "**QUERY**  CTEs can help you keep track of the logical flow of your work.\n",
    "```SQL\n",
    "-- Set up your CTE\n",
    "with match_list AS (\n",
    "    SELECT \n",
    "  \t\tcountry_id,\n",
    "  \t   (home_goal + away_goal) AS goals\n",
    "    FROM match\n",
    "  \t-- Create a list of match IDs to filter data in the CTE\n",
    "    WHERE id IN (\n",
    "       SELECT id\n",
    "       FROM match \n",
    "       WHERE season = '2013/2014' AND EXTRACT(MONTH FROM date) = 08))\n",
    "-- Select the league name and average of goals in the CTE\n",
    "SELECT \n",
    "\tl.name,\n",
    "    AVG(match_list.goals)\n",
    "FROM league AS l\n",
    "-- Join the CTE onto the league table\n",
    "LEFT JOIN match_list ON l.id = match_list.country_id\n",
    "GROUP BY l.name;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**QUERY** In the following exercises, you will solve one of the key problems with extracting meaningful information from the European Soccer Database\n",
    "```SQL\n",
    "-- Set up your CTE\n",
    "with home AS (\n",
    " \t-- Select the match id and team long name\n",
    "    SELECT\n",
    "  \t\tm.id, \n",
    "  \t\tt.team_long_name \n",
    "    FROM match AS m\n",
    "    LEFT JOIN team AS t ON m.hometeam_id = t.team_api_id)\n",
    "-- Select the id, team name, and home goals\n",
    "SELECT \n",
    "\thome.id,\n",
    "    home.team_long_name AS hometeam,\n",
    "    m.home_goal\n",
    "FROM match AS m\n",
    "-- Left join the CTE onto the match table\n",
    "LEFT JOIN home ON m.id = home.id\n",
    "WHERE m.season = '2014/2015';\n",
    "```\n",
    "**QUERY** In this second exercise, you will set up the second CTE joining the away team ID to the team table. This will allow you to create a data set with the away team names included in match records.\n",
    "```SQL\n",
    "-- Set up your CTE\n",
    "with away AS (\n",
    " \t-- Select the match id and team long name\n",
    "    SELECT\n",
    "  \t\tm.id, \n",
    "  \t\tt.team_long_name\n",
    "    FROM match AS m\n",
    "    LEFT JOIN team AS t ON m.awayteam_id = t.team_api_id)\n",
    "-- Select the id, team name, and home goals\n",
    "SELECT \n",
    "\tm.id,\n",
    "    away.team_long_name AS awayteam,\n",
    "    m.away_goal\n",
    "FROM match AS m\n",
    "-- Left join the CTE onto the match table\n",
    "LEFT JOIN away ON m.id = away.id\n",
    "WHERE m.season = '2014/2015';\n",
    "```\n",
    "**QUERY** You've now created both CTEs matching the team ID to the home and away team names. Your next step is to combine them and then SELECT them into a main query, giving you a more informative data set.\n",
    "```SQL\n",
    "-- Create your home team CTE\n",
    "with home AS (\n",
    "    -- Select the match id and team long name\n",
    "    SELECT m.id, t.team_long_name\n",
    "    FROM match AS m\n",
    "    LEFT JOIN team AS t ON m.hometeam_id = t.team_api_id),\n",
    "-- Create your away team CTE. \n",
    "away AS (\n",
    "    -- Select the match id and team long name\n",
    "    SELECT m.id, t.team_long_name\n",
    "    FROM match AS m\n",
    "    LEFT JOIN team AS t ON m.awayteam_id = t.team_api_id)\n",
    "-- Select the stage, date, home and away teams\n",
    "SELECT \n",
    "\tm.stage,\n",
    "    m.date,\n",
    "    home.team_long_name AS home_team,\n",
    "    away.team_long_name AS away_team,\n",
    "    m.home_goal,\n",
    "    m.away_goal\n",
    "FROM match AS m\n",
    "-- Join the CTEs onto the match table\n",
    "LEFT JOIN home ON m.id = home.id\n",
    "LEFT JOIN away ON m.id = away.id\n",
    "WHERE m.season = '2014/2015';\n",
    "```\n",
    "`OUTPUT\n",
    "stage\tdate\t   home_team\t             away_team\t          home_goal\taway_goal\n",
    "1\t2014-07-25\t  Standard de Liège\t     Sporting Charleroi\t    3\t       0\n",
    "1\t2014-07-26\t  Waasland-Beveren\t      Club Brugge KV\t        0\t       2\n",
    "1\t2014-07-26\t  KSV Cercle Brugge\t     KAA Gent\t              0\t       0\n",
    "1\t2014-07-26\t  Lierse SK\t             KV Oostende\t           2\t       0\n",
    "1\t2014-07-26\t  KVC Westerlo\t          Sporting Lokeren\t      1\t       0`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### The match is OVER\n",
    "The OVER() clause allows you to pass an aggregate function down a data set, similar to subqueries in SELECT. The OVER() clause offers significant benefits over subqueries in select -- namely, your queries will run faster, and the OVER() clause has a wide range of additional functions and clauses you can include with it that we will cover later on in this chapter.\n",
    "\n",
    "**QUERY** In this exercise, you will revise some queries from previous chapters using the OVER() clause.\n",
    "\n",
    "```SQL\n",
    "SELECT \n",
    "\t-- Select the id, country name, season, home, and away goals\n",
    "\tm.id, \n",
    "    c.name AS country, \n",
    "    m.season,\n",
    "\tm.home_goal,\n",
    "\tm.away_goal,\n",
    "    -- Use a window to include the aggregate average in each row\n",
    "\tAVG(m.home_goal + m.away_goal) OVER() AS overall_avg\n",
    "FROM match AS m\n",
    "LEFT JOIN country AS c ON m.country_id = c.id;\n",
    "```\n",
    "\n",
    "#### What's OVER here?\n",
    "Window functions allow you to create a RANK of information according to any variable you want to use to sort your data. When setting this up, you will need to specify what column/calculation you want to use to calculate your rank. This is done by including an ORDER BY clause inside the OVER() clause. Below is an example:\n",
    "```SQL\n",
    "SELECT \n",
    "    id,\n",
    "    RANK() OVER(ORDER BY home_goal) AS rank\n",
    "FROM match;```\n",
    "\n",
    "**QUERY**In this exercise, you will create a data set of ranked matches according to which leagues, on average, score the most goals in a match.\n",
    "```SQL\n",
    "SELECT \n",
    "\t-- Select the league name and average goals scored\n",
    "\tl.name AS league,\n",
    "    AVG(m.home_goal + m.away_goal) AS avg_goals,\n",
    "    -- Rank each league according to the average goals\n",
    "    RANK() OVER(ORDER BY AVG(m.home_goal + m.away_goal)) AS league_rank\n",
    "FROM league AS l\n",
    "LEFT JOIN match AS m \n",
    "ON l.id = m.country_id\n",
    "WHERE m.season = '2011/2012'\n",
    "GROUP BY l.name\n",
    "-- Order the query by the rank you created\n",
    "ORDER BY league_rank;\n",
    "\n",
    "```\n",
    "`OUTPUT\n",
    "league\t                   avg_goals\t        league_rank\n",
    "Poland Ekstraklasa\t       2.1958333333333333\t1\n",
    "France Ligue 1\t           2.5157894736842105\t2\n",
    "Italy Serie A\t            2.5837988826815642\t3\n",
    "Switzerland Super League     2.6234567901234568\t4\n",
    "Scotland Premier League\t  2.6359649122807018\t5\n",
    "Portugal Liga ZON Sagres     2.6416666666666667\t6`\n",
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**QUERY** \n",
    "* Select the league name and average goals scored\n",
    "* Rank leagues in descending order by average goals\n",
    "* Order the query by the rank you created\n",
    "```SQL\n",
    "SELECT \n",
    "\t-- Select the league name and average goals scored\n",
    "\tl.name AS league,\n",
    "    AVG(m.home_goal + m.away_goal) AS avg_goals,\n",
    "    -- Rank leagues in descending order by average goals\n",
    "    RANK() OVER(ORDER BY AVG(m.home_goal + m.away_goal) DESC) AS league_rank\n",
    "FROM league AS l\n",
    "LEFT JOIN match AS m \n",
    "ON l.id = m.country_id\n",
    "WHERE m.season = '2011/2012'\n",
    "GROUP BY l.name\n",
    "-- Order the query by the rank you created\n",
    "ORDER BY league_rank;```\n",
    "`OUTPUT\n",
    "league\t                        avg_goals\t       league_rank\n",
    "Netherlands Eredivisie\t        3.2581699346405229\t1\n",
    "Belgium Jupiler League\t        2.8791666666666667\t2\n",
    "Germany 1. Bundesliga\t         2.8594771241830065\t3\n",
    "England Premier League\t        2.8052631578947368\t4\n",
    "Spain LIGA BBVA\t               2.7631578947368421\t5\n",
    "Portugal Liga ZON Sagres\t      2.6416666666666667\t6\n",
    "Scotland Premier League\t       2.6359649122807018\t7\n",
    "Switzerland Super League\t      2.6234567901234568\t8\n",
    "Italy Serie A\t                 2.5837988826815642\t9\n",
    "France Ligue 1              \t  2.5157894736842105\t10\n",
    "Poland Ekstraklasa\t            2.1958333333333333\t11`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### PARTITION BY a column\n",
    "The PARTITION BY clause allows you to calculate separate \"windows\" based on columns you want to divide your results. For example, you can create a single column that calculates an overall average of goals scored for each season.\n",
    "\n",
    "**QUERY** In this exercise, you will be creating a data set of games played by Legia Warszawa, the top ranked team in Poland, and comparing their individual game performance to the overall average for that season. Where do you see the most outliers? Are more Legia Warszawa outliers home or away games?\n",
    "```SQL\n",
    "SELECT\n",
    "\t-- Select the date, season, home, and away goals\n",
    "\tdate,\n",
    "\tseason,\n",
    "\thome_goal,\n",
    "\taway_goal,\n",
    "\tCASE WHEN hometeam_id = 8673 THEN 'home' \n",
    "\t\t ELSE 'away' END AS warsaw_location,\n",
    "    -- Calculate the average goals scored partitioned by season\n",
    "    AVG(home_goal) OVER(PARTITION BY season)AS season_homeavg,\n",
    "    AVG(away_goal) OVER(PARTITION BY season)AS season_awayavg\n",
    "FROM match\n",
    "-- Filter the data set for Legia Warszawa matches only\n",
    "WHERE hometeam_id = 8673 OR awayteam_id = 8673\n",
    "ORDER BY (home_goal + away_goal) DESC;```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### PARTITION BY multiple columns\n",
    "The PARTITION BY clause can be used to break out window averages by multiple data points (columns). You can even calculate the information you want to use to partition your data! For example, you can calculate average goals scored by season and by country, or by the calendar year (taken from the date column).\n",
    "\n",
    "**QUERY** In this exercise, you will calculate the average number home and away goals scored Legia Warszawa, and their opponents, partitioned by the month in each season.\n",
    "```SQL\n",
    "SELECT \n",
    "\t-- Select the date, season, home, and away goals\n",
    "\tdate,\n",
    "\tseason,\n",
    "\thome_goal,\n",
    "\taway_goal,\n",
    "\tCASE WHEN hometeam_id = 8673 THEN 'home' \n",
    "         ELSE 'away' END AS warsaw_location,\n",
    "\t-- Calculate average goals partitioned by season and month\n",
    "    AVG(home_goal) OVER(PARTITION BY season, \n",
    "         \tEXTRACT(MONTH FROM date)) AS season_mo_home,\n",
    "    AVG(away_goal) OVER(PARTITION BY season, \n",
    "            EXTRACT(MONTH FROM date)) AS season_mo_away\n",
    "FROM match\n",
    "WHERE hometeam_id = 8673 OR awayteam_id = 8673\n",
    "ORDER BY (home_goal + away_goal) DESC;\n",
    "\n",
    "```\n",
    "\n",
    "\n",
    "#### Slide to the left\n",
    "Sliding windows allow you to create running calculations between any two points in a window using functions such as PRECEDING, FOLLOWING, and CURRENT ROW. You can calculate running counts, sums, averages, and other aggregate functions between any two points you specify in the data set.\n",
    "\n",
    "PRECEDING AND FOLLOWING are used to specify the number of rows before or after the current row that you want to include in a calculation. UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING include every row since begining or the end of the date in your calculation. CURRENT ROW is stop your calculation.\n",
    "\n",
    "**QUERY** In this exercise, you will expand on the examples discussed in the video, calculating the running total of goals scored by the FC Utrecht when they were the home team during the 2011/2012 season. Do they score more goals at the end of the season as the home or away team?\n",
    "```SQL\n",
    "SELECT \n",
    "\t-- Select the date, home goal, and away goals\n",
    "\tdate,\n",
    "    home_goal,\n",
    "    away_goal,\n",
    "    -- Create a running total and running average of home goals\n",
    "    SUM(home_goal) OVER(ORDER BY date \n",
    "         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,\n",
    "    AVG(home_goal) OVER(ORDER BY date \n",
    "         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_avg\n",
    "FROM match\n",
    "WHERE \n",
    "\thometeam_id = 9908 \n",
    "\tAND season = '2011/2012';\n",
    "\n",
    "```\n",
    "\n",
    "#### Slide to the right\n",
    "Now let's see how FC Utrecht performs when they're the away team. You'll notice that the total for the season is at the bottom of the data set you queried. Depending on your results, this could be pretty long, and scrolling down is not very helpful.\n",
    "\n",
    "**QUERY** In this exercise you will slightly modify the query from the previous exercise by sorting the data set in reverse order and calculating a backward running total from the CURRENT ROW to the end of the data set (earliest record).\n",
    "\n",
    "```SQL\n",
    "SELECT \n",
    "\t-- Select the date, home goal, and away goals\n",
    "\tdate,\n",
    "    home_goal,\n",
    "    away_goal,\n",
    "    -- Create a running total and running average of home goals\n",
    "    SUM(home_goal) OVER(ORDER BY date DESC\n",
    "         ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS running_total,\n",
    "    AVG(home_goal) OVER(ORDER BY date DESC\n",
    "         ROWS BETWEEN  CURRENT ROW AND UNBOUNDED FOLLOWING) AS running_avg\n",
    "FROM match\n",
    "WHERE awayteam_id = 9908 AND season = '2011/2012';\n",
    "```\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Setting up the home team CTE\n",
    "In this course, we've covered ways in which you can use CASE statements, subqueries, common table expressions, and window functions in your queries to structure a data set that best meets your needs.**For this exercise, you will be using all of these concepts to generate a list of matches in which Manchester United was defeated during the 2014/2015 English Premier League season**.\n",
    "\n",
    "**QUERY** Your first task is to create the first query that filters for matches where Manchester United played as the home team. This will become a common table expression in a later exercise.\n",
    "```SQL\n",
    "SELECT \n",
    "\t-- Select the match id and team long name\n",
    "\tm.id, \n",
    "    t.team_long_name,\n",
    "    -- Identify matches as home/away wins or ties\n",
    "\tCASE WHEN m.home_goal > m.away_goal THEN'Home Team Win'\n",
    "\t\t WHEN m.home_goal < m.away_goal THEN'Away Team Win' \n",
    "         ELSE 'Tie' END AS outcome\n",
    "FROM match AS m\n",
    "-- Left join team on the home team ID and team API id\n",
    "LEFT JOIN team AS t \n",
    "ON m.hometeam_id = t.team_api_id\n",
    "WHERE \n",
    "\t-- Filter for 2014/2015 and Manchester United as the home team\n",
    "\tm.season = '2014/2015'\n",
    "\tAND t.team_long_name = 'Manchester United';\n",
    "\n",
    "```\n",
    "**In this exercise, you will set up the subquery for the AWAY team, just as you did in the previous exercise.**\n",
    "```SQL\n",
    "SELECT \n",
    "\t-- Select the match id and team long name\n",
    "\tm.id, \n",
    "    t.team_long_name,\n",
    "    -- Identify matches as home/away wins or ties\n",
    "\tCASE WHEN m.home_goal > m.away_goal THEN 'Home Team Win'\n",
    "\t\t WHEN m.home_goal < m.away_goal THEN 'Away Team Win' \n",
    "         ELSE 'Tie' END AS outcome\n",
    "FROM match AS m\n",
    "-- Left join team on the away team ID and team API id \n",
    "LEFT JOIN team AS t \n",
    "ON m.awayteam_id = t.team_api_id\n",
    "WHERE \n",
    "\t-- Filter for 2014/2015 and Manchester United as the home team\n",
    "\tm.season = '2014/2015'\n",
    "\tAND t.team_long_name = 'Manchester United';\n",
    "```\n",
    "**QUERY** Putting the CTEs together\n",
    "Your two previous queries are now set up as subqueries in the current statement as common table expressions (CTEs). Continue building the query to extract all matches where Manchester United was defeated by another team.\n",
    "```SQL\n",
    "-- Set up the home team CTE\n",
    "WITH home AS (\n",
    "  SELECT m.id, t.team_long_name,\n",
    "\t     CASE WHEN m.home_goal > m.away_goal THEN 'Home Team Win'\n",
    "\t\t      WHEN m.home_goal < m.away_goal THEN 'Away Team Win' \n",
    "              ELSE 'Tie' END AS outcome\n",
    "  FROM match AS m\n",
    "  LEFT JOIN team AS t ON m.hometeam_id = t.team_api_id),\n",
    "-- Set up the away team CTE\n",
    "away AS (\n",
    "  SELECT m.id, t.team_long_name,\n",
    "\t     CASE WHEN m.home_goal > m.away_goal THEN 'Home Team Win'\n",
    "\t\t      WHEN m.home_goal < m.away_goal THEN 'Away Team Win' \n",
    "              ELSE 'Tie' END AS outcome\n",
    "  FROM match AS m\n",
    "  LEFT JOIN team AS t ON m.awayteam_id = t.team_api_id)\n",
    "-- Select team names from the CTEs and the date and goals from match\n",
    "SELECT DISTINCT\n",
    "    date,\n",
    "    home.team_long_name AS home_team,\n",
    "    away.team_long_name AS away_team,\n",
    "    m.home_goal,\n",
    "    m.away_goal\n",
    "-- Join the CTEs onto the match table\n",
    "FROM match AS m\n",
    "LEFT JOIN home ON m.id = home.id\n",
    "LEFT JOIN away ON m.id = away.id\n",
    "WHERE m.season = '2014/2015'\n",
    "      AND (home.team_long_name = 'Manchester United' OR away.team_long_name = 'Manchester United');\n",
    "   ```\n",
    "      \n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**QUERY** Add a window function\n",
    "You have one final step to complete! Add a window function to the main query that ranks the matches by date.\n",
    "\n",
    "```SQL\n",
    "-- Set up the home team CTE\n",
    "WITH home AS (\n",
    "  SELECT m.id, t.team_long_name,\n",
    "\t     CASE WHEN m.home_goal > m.away_goal THEN 'Home Team Win'\n",
    "\t\t      WHEN m.home_goal < m.away_goal THEN 'Away Team Win'\n",
    "\t\t      ELSE 'Tie' END AS outcome\n",
    "  FROM match AS m\n",
    "  LEFT JOIN team AS t ON m.hometeam_id = t.team_api_id),\n",
    "-- Set up the away team CTE\n",
    "away AS (\n",
    "  SELECT m.id, t.team_long_name,\n",
    "\t   CASE WHEN m.home_goal > m.away_goal THEN 'Home Team Win'\n",
    "\t\t     WHEN m.home_goal < m.away_goal THEN 'Away Team Win' \n",
    "             ELSe 'Tie' END AS outcome\n",
    "  FROM match AS m\n",
    "  LEFT JOIN team AS t ON m.awayteam_id = t.team_api_id)\n",
    "-- Select columns and and rank the matches by date\n",
    "SELECT DISTINCT\n",
    "    m.date,\n",
    "    home.team_long_name AS home_team,\n",
    "    away.team_long_name AS away_team,\n",
    "    m.home_goal, m.away_goal,\n",
    "    RANK() OVER(ORDER BY date) as date_rank\n",
    "-- Join the CTEs onto the match table\n",
    "FROM match AS m\n",
    "LEFT JOIN home ON m.id = home.id\n",
    "LEFT JOIN away ON m.id = away.id\n",
    "WHERE m.season = '2014/2015'\n",
    "      AND ((home.team_long_name = 'Manchester United' AND home.outcome = 'Away Team Win')\n",
    "      OR (away.team_long_name = 'Manchester United' AND away.outcome = 'Home Team Win'));\n",
    "      ```\n",
    "`OUTPUT  \n",
    "date\t      home_team\t        away_team\t     home_goal\taway_goal\tdate_rank\n",
    "2014-08-16\tManchester United\tSwansea City\t     1\t     2\t          1\n",
    "2014-09-21\tLeicester City\t   Manchester United\t5\t     3\t          2\n",
    "2014-11-02\tManchester City      Manchester United\t1\t     0\t          3\n",
    "2015-01-11\tManchester United\tSouthampton\t      0\t     1\t          4\n",
    "2015-02-21\tSwansea City\t     Manchester United\t2\t     1      \t    5`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}