f1_stats.ipynb

{
    "metadata": {
        "kernelspec": {
            "name": "SQL",
            "display_name": "SQL",
            "language": "sql"
        },
        "language_info": {
            "name": "sql",
            "version": ""
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "markdown",
            "source": [
                "# <p align =\"center\"><span style='color:navy'> **Formula 1 Stats** </span> </p>\r\n",
                "---\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "b0b49ab9-9d50-435d-a871-69cfedd30273"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "### <span style=\"color:dimgrey; font-family:WildWest;font-size:20px \"> I've created this notebook as an alternative to regular statistics </span>\n",
                "***\n",
                "\n",
                "- I wanted to move away from the AWS metrics we get on race day and look at answering ~awkward~ insightful queries on anything I can think of. As an alternative this notebook has been built using  [![Azure](https://badgen.net/badge/icon/azure?icon=azure&label)](https://azure.microsoft.com/) cloud services. \n",
                "\n",
                "- I've used the free Azure SQL Database and linked it using Azure Data Studio to upload all the CSV files and then query using this notebook. All the queries are done using `T-SQL`\n",
                "- I'll document them here, if people want to suggest more they are welcome.\n",
                "\n",
                "\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "36bab0d6-7288-440c-a486-fd575cb0a9c1"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "\n",
                "<p align=\"center\">\n",
                "  <img  src=\"file://D:\\git\\f1_stats\\images\\abed-ismail-yasMarina-unsplash_small.jpg\" width = 1000>\n",
                "</p>\n",
                "\n",
                "Photo by [Abed Ismail](https://unsplash.com/@abedismail?utm_source=unsplash&utm_medium=referral&utm_content=creditCopyText) on [Unsplash](https://unsplash.com/s/photos/formula-1?utm_source=unsplash&utm_medium=referral&utm_content=creditCopyText)\n",
                "\n",
                "\n",
                "\n",
                "##### The following Tables are part of the database\n",
                "\n",
                "\n",
                "|No  |          Tables           |\n",
                "|:--:|:--------------------:|\n",
                "|1.  |  Circuits                 | \n",
                "|2.  |  Constructor Results      | \n",
                "|3.  |  Constructor Standings    | \n",
                "|4.  |  Constructors             | \n",
                "|5.  |  Driver Standings         |   \n",
                "|6.  |  Drivers                  |   \n",
                "|7.  |  Lap Times                |  \n",
                "|8.  |  Pit Stops                |\n",
                "|9.  |  Qualifying               |\n",
                "|10. |  Races                    |\n",
                "|11. |  Results                  |\n",
                "|12. |  Seasons                  | \n",
                "|13. |  Status                   |\n",
                "\n",
                "\n",
                "\n",
                "This dataset has been created from the [Formula 1 World Championship (1950-2021)](https://www.kaggle.com/rohanrao/formula-1-world-championship-1950-2020) on Kaggle, the data was derived from the [Ergast API](http://ergast.com/mrd/) \n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "4874cf8d-a597-4c0c-ae8e-64aa81ba5eb2"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "##  <p align=\"center\">**1.** <span style='color:steelblue'>Who is the most sucessful driver in a season? </span></p>\r\n",
                "---\r\n",
                "\r\n",
                "- With an increasing amount of races in the F1 calendar the easiest way to compare efforts across the seasons is to use percentages\r\n",
                "- Although who's to say Alberto Ascari couldn't have kept up that 75% win ratio across a 20 race season?"
            ],
            "metadata": {
                "azdata_cell_guid": "15021c05-1fb0-4173-8e5c-0c44a94df157"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "\r\n",
                "SELECT TOP 10 ra.year as Season ,count(*) as Wins , CONCAT(forename, ' ', UPPER(surname)) as Driver, sum(count(*)) OVER(partition by ra.year) as Races, CONCAT(ROUND(CAST(count(*) as float)/sum(count(*)) OVER(partition by ra.year)*100,2),'%') as [Win Percentage]\r\n",
                "FROM [form].[results] re \r\n",
                "JOIN [form].[races] ra \r\n",
                "ON re.raceId = ra.raceId\r\n",
                "JOIN [form].[drivers] d\r\n",
                "ON re.driverId = d.driverId\r\n",
                "WHERE position = 1\r\n",
                "GROUP BY ra.year, CONCAT(forename, ' ', UPPER(surname))\r\n",
                "ORDER BY CAST(count(*) as float)/sum(count(*)) OVER(partition by ra.year)*100 DESC\r\n",
                "\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "138645f6-a6e0-4546-ad72-2bc24d3557f5",
                "tags": []
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(10 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.033"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 13,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "Season"
                                    },
                                    {
                                        "name": "Wins"
                                    },
                                    {
                                        "name": "Driver"
                                    },
                                    {
                                        "name": "Races"
                                    },
                                    {
                                        "name": "Win Percentage"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "1952",
                                    "1": "6",
                                    "2": "Alberto ASCARI",
                                    "3": "8",
                                    "4": "75%"
                                },
                                {
                                    "0": "2004",
                                    "1": "13",
                                    "2": "Michael SCHUMACHER",
                                    "3": "18",
                                    "4": "72.22%"
                                },
                                {
                                    "0": "1963",
                                    "1": "7",
                                    "2": "Jim CLARK",
                                    "3": "10",
                                    "4": "70%"
                                },
                                {
                                    "0": "2013",
                                    "1": "13",
                                    "2": "Sebastian VETTEL",
                                    "3": "19",
                                    "4": "68.42%"
                                },
                                {
                                    "0": "1954",
                                    "1": "6",
                                    "2": "Juan FANGIO",
                                    "3": "9",
                                    "4": "66.67%"
                                },
                                {
                                    "0": "2002",
                                    "1": "11",
                                    "2": "Michael SCHUMACHER",
                                    "3": "17",
                                    "4": "64.71%"
                                },
                                {
                                    "0": "2020",
                                    "1": "11",
                                    "2": "Lewis HAMILTON",
                                    "3": "17",
                                    "4": "64.71%"
                                },
                                {
                                    "0": "1965",
                                    "1": "6",
                                    "2": "Jim CLARK",
                                    "3": "10",
                                    "4": "60%"
                                },
                                {
                                    "0": "2011",
                                    "1": "11",
                                    "2": "Sebastian VETTEL",
                                    "3": "19",
                                    "4": "57.89%"
                                },
                                {
                                    "0": "2014",
                                    "1": "11",
                                    "2": "Lewis HAMILTON",
                                    "3": "19",
                                    "4": "57.89%"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>Season</th><th>Wins</th><th>Driver</th><th>Races</th><th>Win Percentage</th></tr><tr><td>1952</td><td>6</td><td>Alberto ASCARI</td><td>8</td><td>75%</td></tr><tr><td>2004</td><td>13</td><td>Michael SCHUMACHER</td><td>18</td><td>72.22%</td></tr><tr><td>1963</td><td>7</td><td>Jim CLARK</td><td>10</td><td>70%</td></tr><tr><td>2013</td><td>13</td><td>Sebastian VETTEL</td><td>19</td><td>68.42%</td></tr><tr><td>1954</td><td>6</td><td>Juan FANGIO</td><td>9</td><td>66.67%</td></tr><tr><td>2002</td><td>11</td><td>Michael SCHUMACHER</td><td>17</td><td>64.71%</td></tr><tr><td>2020</td><td>11</td><td>Lewis HAMILTON</td><td>17</td><td>64.71%</td></tr><tr><td>1965</td><td>6</td><td>Jim CLARK</td><td>10</td><td>60%</td></tr><tr><td>2011</td><td>11</td><td>Sebastian VETTEL</td><td>19</td><td>57.89%</td></tr><tr><td>2014</td><td>11</td><td>Lewis HAMILTON</td><td>19</td><td>57.89%</td></tr></table>"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 13
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Just for completeness....\r\n",
                "\r\n",
                "### Here's the most wins in a single season"
            ],
            "metadata": {
                "azdata_cell_guid": "af61a07a-9803-46bf-bea5-a9d7b81d0559"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT TOP 10 ra.year as Season ,count(*) as Wins , CONCAT(forename, ' ', UPPER(surname)) as Driver, sum(count(*)) OVER(partition by ra.year) as Races, CONCAT(ROUND(CAST(count(*) as float)/sum(count(*)) OVER(partition by ra.year)*100,2),'%') as [Win Percentage]\r\n",
                "FROM [form].[results] re \r\n",
                "JOIN [form].[races] ra \r\n",
                "ON re.raceId = ra.raceId\r\n",
                "JOIN [form].[drivers] d\r\n",
                "ON re.driverId = d.driverId\r\n",
                "WHERE position = 1\r\n",
                "GROUP BY ra.year, CONCAT(forename, ' ', UPPER(surname))\r\n",
                "ORDER BY count(*) DESC"
            ],
            "metadata": {
                "azdata_cell_guid": "56c08a6b-ffbc-4bdf-9abd-34bd58688fcc",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(10 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.032"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 15,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "Season"
                                    },
                                    {
                                        "name": "Wins"
                                    },
                                    {
                                        "name": "Driver"
                                    },
                                    {
                                        "name": "Races"
                                    },
                                    {
                                        "name": "Win Percentage"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "2004",
                                    "1": "13",
                                    "2": "Michael SCHUMACHER",
                                    "3": "18",
                                    "4": "72.22%"
                                },
                                {
                                    "0": "2013",
                                    "1": "13",
                                    "2": "Sebastian VETTEL",
                                    "3": "19",
                                    "4": "68.42%"
                                },
                                {
                                    "0": "2002",
                                    "1": "11",
                                    "2": "Michael SCHUMACHER",
                                    "3": "17",
                                    "4": "64.71%"
                                },
                                {
                                    "0": "2011",
                                    "1": "11",
                                    "2": "Sebastian VETTEL",
                                    "3": "19",
                                    "4": "57.89%"
                                },
                                {
                                    "0": "2014",
                                    "1": "11",
                                    "2": "Lewis HAMILTON",
                                    "3": "19",
                                    "4": "57.89%"
                                },
                                {
                                    "0": "2018",
                                    "1": "11",
                                    "2": "Lewis HAMILTON",
                                    "3": "21",
                                    "4": "52.38%"
                                },
                                {
                                    "0": "2019",
                                    "1": "11",
                                    "2": "Lewis HAMILTON",
                                    "3": "21",
                                    "4": "52.38%"
                                },
                                {
                                    "0": "2020",
                                    "1": "11",
                                    "2": "Lewis HAMILTON",
                                    "3": "17",
                                    "4": "64.71%"
                                },
                                {
                                    "0": "2015",
                                    "1": "10",
                                    "2": "Lewis HAMILTON",
                                    "3": "19",
                                    "4": "52.63%"
                                },
                                {
                                    "0": "2016",
                                    "1": "10",
                                    "2": "Lewis HAMILTON",
                                    "3": "21",
                                    "4": "47.62%"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>Season</th><th>Wins</th><th>Driver</th><th>Races</th><th>Win Percentage</th></tr><tr><td>2004</td><td>13</td><td>Michael SCHUMACHER</td><td>18</td><td>72.22%</td></tr><tr><td>2013</td><td>13</td><td>Sebastian VETTEL</td><td>19</td><td>68.42%</td></tr><tr><td>2002</td><td>11</td><td>Michael SCHUMACHER</td><td>17</td><td>64.71%</td></tr><tr><td>2011</td><td>11</td><td>Sebastian VETTEL</td><td>19</td><td>57.89%</td></tr><tr><td>2014</td><td>11</td><td>Lewis HAMILTON</td><td>19</td><td>57.89%</td></tr><tr><td>2018</td><td>11</td><td>Lewis HAMILTON</td><td>21</td><td>52.38%</td></tr><tr><td>2019</td><td>11</td><td>Lewis HAMILTON</td><td>21</td><td>52.38%</td></tr><tr><td>2020</td><td>11</td><td>Lewis HAMILTON</td><td>17</td><td>64.71%</td></tr><tr><td>2015</td><td>10</td><td>Lewis HAMILTON</td><td>19</td><td>52.63%</td></tr><tr><td>2016</td><td>10</td><td>Lewis HAMILTON</td><td>21</td><td>47.62%</td></tr></table>"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 15
        },
        {
            "cell_type": "markdown",
            "source": [
                "# <p align=\"center\">**2.** <span style='color:steelblue'>What about the most successful Constructor?</span> </p>\n",
                "\n",
                "  \n",
                "\n",
                "- ### probably an easy one for fans with McLaren winning 15 of the 16 in 1988, but did you expect Alfa Romeo to be in the Top 10?"
            ],
            "metadata": {
                "azdata_cell_guid": "36def6d7-b38b-4974-b54e-4514e6d70aa7"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "with cte as (SELECT  ra.year as Season ,count(*) as Wins , c.name as Constructor\r\n",
                "FROM [form].[results] re \r\n",
                "JOIN [form].[races] ra \r\n",
                "ON re.raceId = ra.raceId\r\n",
                "JOIN [form].[constructors] c\r\n",
                "ON re.constructorId = c.constructorId\r\n",
                "WHERE position = 1\r\n",
                "GROUP BY ra.year, c.name\r\n",
                ")\r\n",
                "\r\n",
                "SELECT TOP 10 Season, Wins, Constructor, sum(Wins) OVER(partition by Season) as Races, ROUND(CAST(Wins as float)/sum(Wins) OVER(partition by Season)*100,2) as [Win Percentage]\r\n",
                "FROM cte\r\n",
                "ORDER BY CAST(Wins as float)/sum(Wins) OVER(partition by Season)*100   DESC ; "
            ],
            "metadata": {
                "azdata_cell_guid": "fed1c7f5-8487-45ef-b447-f39e85cd2e2e",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(10 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.087"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 1,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "Season"
                                    },
                                    {
                                        "name": "Wins"
                                    },
                                    {
                                        "name": "Constructor"
                                    },
                                    {
                                        "name": "Races"
                                    },
                                    {
                                        "name": "Win Percentage"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "1988",
                                    "1": "15",
                                    "2": "McLaren",
                                    "3": "16",
                                    "4": "93.75"
                                },
                                {
                                    "0": "2016",
                                    "1": "19",
                                    "2": "Mercedes",
                                    "3": "21",
                                    "4": "90.48"
                                },
                                {
                                    "0": "2002",
                                    "1": "15",
                                    "2": "Ferrari",
                                    "3": "17",
                                    "4": "88.24"
                                },
                                {
                                    "0": "1952",
                                    "1": "7",
                                    "2": "Ferrari",
                                    "3": "8",
                                    "4": "87.5"
                                },
                                {
                                    "0": "1950",
                                    "1": "6",
                                    "2": "Alfa Romeo",
                                    "3": "7",
                                    "4": "85.71"
                                },
                                {
                                    "0": "2014",
                                    "1": "16",
                                    "2": "Mercedes",
                                    "3": "19",
                                    "4": "84.21"
                                },
                                {
                                    "0": "2015",
                                    "1": "16",
                                    "2": "Mercedes",
                                    "3": "19",
                                    "4": "84.21"
                                },
                                {
                                    "0": "2004",
                                    "1": "15",
                                    "2": "Ferrari",
                                    "3": "18",
                                    "4": "83.33"
                                },
                                {
                                    "0": "1953",
                                    "1": "7",
                                    "2": "Ferrari",
                                    "3": "9",
                                    "4": "77.78"
                                },
                                {
                                    "0": "2020",
                                    "1": "13",
                                    "2": "Mercedes",
                                    "3": "17",
                                    "4": "76.47"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>Season</th><th>Wins</th><th>Constructor</th><th>Races</th><th>Win Percentage</th></tr><tr><td>1988</td><td>15</td><td>McLaren</td><td>16</td><td>93.75</td></tr><tr><td>2016</td><td>19</td><td>Mercedes</td><td>21</td><td>90.48</td></tr><tr><td>2002</td><td>15</td><td>Ferrari</td><td>17</td><td>88.24</td></tr><tr><td>1952</td><td>7</td><td>Ferrari</td><td>8</td><td>87.5</td></tr><tr><td>1950</td><td>6</td><td>Alfa Romeo</td><td>7</td><td>85.71</td></tr><tr><td>2014</td><td>16</td><td>Mercedes</td><td>19</td><td>84.21</td></tr><tr><td>2015</td><td>16</td><td>Mercedes</td><td>19</td><td>84.21</td></tr><tr><td>2004</td><td>15</td><td>Ferrari</td><td>18</td><td>83.33</td></tr><tr><td>1953</td><td>7</td><td>Ferrari</td><td>9</td><td>77.78</td></tr><tr><td>2020</td><td>13</td><td>Mercedes</td><td>17</td><td>76.47</td></tr></table>"
                    },
                    "metadata": {
                        "azdata_chartOptions": {
                            "type": "table",
                            "dataDirection": "horizontal",
                            "columnsAsLabels": true,
                            "labelFirstColumn": false,
                            "legendPosition": "top"
                        }
                    }
                }
            ],
            "execution_count": 1
        },
        {
            "cell_type": "code",
            "source": [
                "with cte as (SELECT  ra.year as Season ,count(*) as Wins , c.name as Constructor\r\n",
                "FROM [form].[results] re \r\n",
                "JOIN [form].[races] ra \r\n",
                "ON re.raceId = ra.raceId\r\n",
                "JOIN [form].[constructors] c\r\n",
                "ON re.constructorId = c.constructorId\r\n",
                "WHERE position = 1\r\n",
                "GROUP BY ra.year, c.name\r\n",
                ")\r\n",
                "\r\n",
                "SELECT TOP 10 CONCAT(Constructor, ': ', Season), ROUND(CAST(Wins as float)/sum(Wins) OVER(partition by Season)*100,2) as [Win Percentage]\r\n",
                "FROM cte\r\n",
                "ORDER BY CAST(Wins as float)/sum(Wins) OVER(partition by Season)*100   DESC ; "
            ],
            "metadata": {
                "azdata_cell_guid": "cb6b1142-d2b9-4271-b073-4d3f61949725",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(10 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.037"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 14,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "(No column name)"
                                    },
                                    {
                                        "name": "Win Percentage"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "McLaren: 1988",
                                    "1": "93.75"
                                },
                                {
                                    "0": "Mercedes: 2016",
                                    "1": "90.48"
                                },
                                {
                                    "0": "Ferrari: 2002",
                                    "1": "88.24"
                                },
                                {
                                    "0": "Ferrari: 1952",
                                    "1": "87.5"
                                },
                                {
                                    "0": "Alfa Romeo: 1950",
                                    "1": "85.71"
                                },
                                {
                                    "0": "Mercedes: 2014",
                                    "1": "84.21"
                                },
                                {
                                    "0": "Mercedes: 2015",
                                    "1": "84.21"
                                },
                                {
                                    "0": "Ferrari: 2004",
                                    "1": "83.33"
                                },
                                {
                                    "0": "Ferrari: 1953",
                                    "1": "77.78"
                                },
                                {
                                    "0": "Mercedes: 2020",
                                    "1": "76.47"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>(No column name)</th><th>Win Percentage</th></tr><tr><td>McLaren: 1988</td><td>93.75</td></tr><tr><td>Mercedes: 2016</td><td>90.48</td></tr><tr><td>Ferrari: 2002</td><td>88.24</td></tr><tr><td>Ferrari: 1952</td><td>87.5</td></tr><tr><td>Alfa Romeo: 1950</td><td>85.71</td></tr><tr><td>Mercedes: 2014</td><td>84.21</td></tr><tr><td>Mercedes: 2015</td><td>84.21</td></tr><tr><td>Ferrari: 2004</td><td>83.33</td></tr><tr><td>Ferrari: 1953</td><td>77.78</td></tr><tr><td>Mercedes: 2020</td><td>76.47</td></tr></table>"
                    },
                    "metadata": {
                        "azdata_chartOptions": {
                            "type": "bar",
                            "dataDirection": "vertical",
                            "columnsAsLabels": true,
                            "labelFirstColumn": true,
                            "legendPosition": "right",
                            "yAxisLabel": "Percentage %",
                            "yAxisMin": 0,
                            "yAxisMax": 100,
                            "xAxisLabel": "F1 Constructors"
                        }
                    }
                }
            ],
            "execution_count": 14
        },
        {
            "cell_type": "markdown",
            "source": [
                "## <p align='center'>**3.** <span style='color:steelblue'>Championships thrown away on the final Race? </span></p>\n",
                "---\n",
                "\n",
                "- Which drivers left the championship leaders ruing a Silver? :)"
            ],
            "metadata": {
                "azdata_cell_guid": "f01dacf8-a9c3-4bfc-9a06-29cd8104e528"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "with pen as (SELECT ds.driverId, d.driverRef, year, points, ds.raceId, RANK() OVER(partition by year order by points DESC) AS pen_race_ranking\r\n",
                "FROM form.driver_standings as DS\r\n",
                "JOIN form.drivers AS d ON ds.driverId = d.driverId \r\n",
                "JOIN form.races AS r on r.raceid = ds.raceId\r\n",
                "WHERE ds.raceId IN (SELECT MAX(r1.raceId)-1 FROM form.races as r1 GROUP BY year)),\r\n",
                "--ORDER BY year DESC\r\n",
                "ult as (SELECT ds.driverId, d.driverRef, year, MAX(points) as final_points,  RANK() OVER(partition by year order by max(points) DESC) as final_standings\r\n",
                "FROM form.driver_standings as DS\r\n",
                "JOIN form.drivers AS d ON ds.driverId = d.driverId \r\n",
                "JOIN form.races AS r on r.raceid = ds.raceId\r\n",
                "\r\n",
                "GROUP BY ds.driverId, d.driverRef, year)\r\n",
                "--ORDER by year desc)\r\n",
                "\r\n",
                "SELECT p.year,UPPER(p.driverRef) as Driver,  pen_race_ranking as [Penultimate Championship Position], final_standings as [Final Standings]\r\n",
                "FROM pen AS p\r\n",
                "JOIN ult as u on p.driverRef= u.driverRef and p.year= u.year\r\n",
                "WHERE final_standings = 1 and pen_race_ranking !=1\r\n",
                "ORDER BY p.year DESC"
            ],
            "metadata": {
                "azdata_cell_guid": "13a5ae04-e54b-47de-becc-5f4a705cf05c",
                "tags": []
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Warning: Null value is eliminated by an aggregate or other SET operation."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(10 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.325"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 16,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "year"
                                    },
                                    {
                                        "name": "Driver"
                                    },
                                    {
                                        "name": "Penultimate Championship Position"
                                    },
                                    {
                                        "name": "Final Standings"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "2010",
                                    "1": "VETTEL",
                                    "2": "3",
                                    "3": "1"
                                },
                                {
                                    "0": "2007",
                                    "1": "RAIKKONEN",
                                    "2": "3",
                                    "3": "1"
                                },
                                {
                                    "0": "1999",
                                    "1": "HAKKINEN",
                                    "2": "2",
                                    "3": "1"
                                },
                                {
                                    "0": "1997",
                                    "1": "VILLENEUVE",
                                    "2": "2",
                                    "3": "1"
                                },
                                {
                                    "0": "1986",
                                    "1": "PROST",
                                    "2": "2",
                                    "3": "1"
                                },
                                {
                                    "0": "1983",
                                    "1": "PIQUET",
                                    "2": "2",
                                    "3": "1"
                                },
                                {
                                    "0": "1981",
                                    "1": "PIQUET",
                                    "2": "2",
                                    "3": "1"
                                },
                                {
                                    "0": "1976",
                                    "1": "HUNT",
                                    "2": "2",
                                    "3": "1"
                                },
                                {
                                    "0": "1964",
                                    "1": "SURTEES",
                                    "2": "2",
                                    "3": "1"
                                },
                                {
                                    "0": "1950",
                                    "1": "FARINA",
                                    "2": "3",
                                    "3": "1"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>year</th><th>Driver</th><th>Penultimate Championship Position</th><th>Final Standings</th></tr><tr><td>2010</td><td>VETTEL</td><td>3</td><td>1</td></tr><tr><td>2007</td><td>RAIKKONEN</td><td>3</td><td>1</td></tr><tr><td>1999</td><td>HAKKINEN</td><td>2</td><td>1</td></tr><tr><td>1997</td><td>VILLENEUVE</td><td>2</td><td>1</td></tr><tr><td>1986</td><td>PROST</td><td>2</td><td>1</td></tr><tr><td>1983</td><td>PIQUET</td><td>2</td><td>1</td></tr><tr><td>1981</td><td>PIQUET</td><td>2</td><td>1</td></tr><tr><td>1976</td><td>HUNT</td><td>2</td><td>1</td></tr><tr><td>1964</td><td>SURTEES</td><td>2</td><td>1</td></tr><tr><td>1950</td><td>FARINA</td><td>3</td><td>1</td></tr></table>"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 16
        },
        {
            "cell_type": "markdown",
            "source": [
                "### Not going to lie my favourite was the [2007 win](https://en.wikipedia.org/wiki/2007_Formula_One_World_Championship) for Kimi \r\n",
                "<p align=\"center\"> <img src = \"https://upload.wikimedia.org/wikipedia/commons/thumb/c/c9/Kimi_Raikkonen.jpg/170px-Kimi_Raikkonen.jpg\" > </p>"
            ],
            "metadata": {
                "azdata_cell_guid": "c9350061-e277-47f3-9508-49ca35012f33"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "## However one omission to that was the 1974 Championship"
            ],
            "metadata": {
                "azdata_cell_guid": "41deea34-ddef-4291-84e3-25e0c5882c73"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "with pen as (SELECT ds.driverId, d.driverRef,CONCAT(forename, ' ', UPPER(surname)) as [Driver], year, points, ds.raceId, RANK() OVER(partition by year order by points DESC) AS pen_race_ranking\r\n",
                "FROM form.driver_standings as DS\r\n",
                "JOIN form.drivers AS d ON ds.driverId = d.driverId \r\n",
                "JOIN form.races AS r on r.raceid = ds.raceId\r\n",
                "WHERE ds.raceId IN (SELECT MAX(r1.raceId)-1 FROM form.races as r1 GROUP BY year)),\r\n",
                "\r\n",
                "ult as (SELECT ds.driverId, d.driverRef, year, MAX(points) as final_points,  RANK() OVER(partition by year order by max(points) DESC) as final_standings\r\n",
                "FROM form.driver_standings as DS\r\n",
                "JOIN form.drivers AS d ON ds.driverId = d.driverId \r\n",
                "JOIN form.races AS r on r.raceid = ds.raceId\r\n",
                "\r\n",
                "GROUP BY ds.driverId, d.driverRef, year)\r\n",
                "\r\n",
                "\r\n",
                "SELECT Driver, p.year, pen_race_ranking, final_standings \r\n",
                "FROM pen AS p\r\n",
                "JOIN ult as u on p.driverRef= u.driverRef and p.year= u.year\r\n",
                "WHERE final_standings >1  and pen_race_ranking = 1 and p.year = 1974\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "d09bd3e3-7560-4473-9ed7-7c0b0108956c",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Warning: Null value is eliminated by an aggregate or other SET operation."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(1 row affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.085"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 12,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "Driver"
                                    },
                                    {
                                        "name": "year"
                                    },
                                    {
                                        "name": "pen_race_ranking"
                                    },
                                    {
                                        "name": "final_standings"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "Clay REGAZZONI",
                                    "1": "1974",
                                    "2": "1",
                                    "3": "2"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>Driver</th><th>year</th><th>pen_race_ranking</th><th>final_standings</th></tr><tr><td>Clay REGAZZONI</td><td>1974</td><td>1</td><td>2</td></tr></table>"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 12
        },
        {
            "cell_type": "markdown",
            "source": [
                "### <span style='color:#2874A6'>So why was that not included in the first query?</span>\n",
                "\n",
                "Delving a little deeper we see that it is due to this section of the code:\n",
                "\n",
                "```\n",
                "WHERE final_standings = 1 and pen_race_ranking !=1\n",
                "```\n",
                "\n",
                "- The error is not so much an error as a _unique_ situation\n",
                "\n",
                "For the 1974 Season **two drivers** went into the final season of the race tied on points"
            ],
            "metadata": {
                "azdata_cell_guid": "6e802ab5-93eb-4b20-895b-02b157dea5ac"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT TOP 2 CONCAT(forename, ' ', UPPER(surname)) as Driver, year as Season , points, RANK() OVER(partition by year order by points DESC) AS  [Penultimate Race Ranking]\r\n",
                "FROM form.driver_standings as DS\r\n",
                "JOIN form.drivers AS d ON ds.driverId = d.driverId \r\n",
                "JOIN form.races AS r on r.raceid = ds.raceId\r\n",
                "WHERE ds.raceId IN (SELECT MAX(r1.raceId)-1 FROM form.races as r1 GROUP BY year) and year = 1974\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "3b855679-eeed-45de-a0f8-de7a9ee6aba6",
                "tags": []
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(2 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.052"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 11,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "Driver"
                                    },
                                    {
                                        "name": "Season"
                                    },
                                    {
                                        "name": "points"
                                    },
                                    {
                                        "name": "Penultimate Race Ranking"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "Emerson FITTIPALDI",
                                    "1": "1974",
                                    "2": "52",
                                    "3": "1"
                                },
                                {
                                    "0": "Clay REGAZZONI",
                                    "1": "1974",
                                    "2": "52",
                                    "3": "1"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>Driver</th><th>Season</th><th>points</th><th>Penultimate Race Ranking</th></tr><tr><td>Emerson FITTIPALDI</td><td>1974</td><td>52</td><td>1</td></tr><tr><td>Clay REGAZZONI</td><td>1974</td><td>52</td><td>1</td></tr></table>"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 11
        },
        {
            "cell_type": "markdown",
            "source": [
                "Fittipaldi emerged victorious"
            ],
            "metadata": {
                "azdata_cell_guid": "de747ea2-915f-4f89-82dc-c9658fdf90d5"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "## <p align='center'>**4.** <span style='color:steelblue'> Winning from the back? </span></p>\r\n",
                "\r\n",
                "While they are several drivers that have recovered from dropping down the order. (Jenson Button in Canada 2011), have any done it after qualifying at the back?"
            ],
            "metadata": {
                "azdata_cell_guid": "471a11d2-eb79-4d98-a324-2faa7b4ff21a"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "with grid_pos as (SELECT ra.raceId, ra.name as Track, year, Max(grid)as last, Max(grid) - 1 as back_row\r\n",
                "FROM form.results r\r\n",
                "JOIN form.races ra ON r.raceId = ra.raceId\r\n",
                "\r\n",
                "GROUP BY ra.raceId,ra.name, year),\r\n",
                "pod AS (SELECT ra.raceid, r.driverId, driverRef, grid, position, year\r\n",
                "FROM form.results AS r\r\n",
                "JOIN form.races AS ra ON r.raceId = ra.raceId\r\n",
                "JOIN form.drivers AS dr ON dr.driverId = r.driverId\r\n",
                "WHERE position IN (1,2,3))\r\n",
                "\r\n",
                "SELECT g.Track, g.year, p.grid, UPPER(p.driverRef) as [Driver], position, count(*) over(partition by g.Track) as Times, count(*) over(partition by p.driverRef)\r\n",
                "FROM grid_pos AS g\r\n",
                "JOIN pod AS p ON p.grid = g.last OR p.grid = g.back_row \r\n",
                "WHERE g.raceId = p.raceId\r\n",
                "ORDER BY [year] DESC;"
            ],
            "metadata": {
                "azdata_cell_guid": "9c001c97-6c64-428a-9be0-f6fd41fa809b",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(17 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.067"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 8,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "Track"
                                    },
                                    {
                                        "name": "year"
                                    },
                                    {
                                        "name": "grid"
                                    },
                                    {
                                        "name": "Driver"
                                    },
                                    {
                                        "name": "position"
                                    },
                                    {
                                        "name": "Times"
                                    },
                                    {
                                        "name": "(No column name)"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "Brazilian Grand Prix",
                                    "1": "2019",
                                    "2": "20",
                                    "3": "SAINZ",
                                    "4": "3",
                                    "5": "1",
                                    "6": "1"
                                },
                                {
                                    "0": "German Grand Prix",
                                    "1": "2019",
                                    "2": "20",
                                    "3": "VETTEL",
                                    "4": "2",
                                    "5": "2",
                                    "6": "2"
                                },
                                {
                                    "0": "Belgian Grand Prix",
                                    "1": "2016",
                                    "2": "21",
                                    "3": "HAMILTON",
                                    "4": "3",
                                    "5": "1",
                                    "6": "2"
                                },
                                {
                                    "0": "Hungarian Grand Prix",
                                    "1": "2014",
                                    "2": "22",
                                    "3": "HAMILTON",
                                    "4": "3",
                                    "5": "1",
                                    "6": "2"
                                },
                                {
                                    "0": "Abu Dhabi Grand Prix",
                                    "1": "2012",
                                    "2": "24",
                                    "3": "VETTEL",
                                    "4": "3",
                                    "5": "1",
                                    "6": "2"
                                },
                                {
                                    "0": "Australian Grand Prix",
                                    "1": "2009",
                                    "2": "20",
                                    "3": "TRULLI",
                                    "4": "3",
                                    "5": "1",
                                    "6": "1"
                                },
                                {
                                    "0": "Bahrain Grand Prix",
                                    "1": "2006",
                                    "2": "22",
                                    "3": "RAIKKONEN",
                                    "4": "3",
                                    "5": "1",
                                    "6": "1"
                                },
                                {
                                    "0": "German Grand Prix",
                                    "1": "2005",
                                    "2": "20",
                                    "3": "MONTOYA",
                                    "4": "2",
                                    "5": "2",
                                    "6": "1"
                                },
                                {
                                    "0": "Canadian Grand Prix",
                                    "1": "2005",
                                    "2": "20",
                                    "3": "BARRICHELLO",
                                    "4": "3",
                                    "5": "1",
                                    "6": "1"
                                },
                                {
                                    "0": "United States Grand Prix West",
                                    "1": "1980",
                                    "2": "24",
                                    "3": "EMERSON_FITTIPALDI",
                                    "4": "3",
                                    "5": "1",
                                    "6": "1"
                                },
                                {
                                    "0": "Spanish Grand Prix",
                                    "1": "1970",
                                    "2": "16",
                                    "3": "MARIO_ANDRETTI",
                                    "4": "3",
                                    "5": "3",
                                    "6": "1"
                                },
                                {
                                    "0": "Spanish Grand Prix",
                                    "1": "1969",
                                    "2": "13",
                                    "3": "MCLAREN",
                                    "4": "2",
                                    "5": "3",
                                    "6": "1"
                                },
                                {
                                    "0": "Spanish Grand Prix",
                                    "1": "1968",
                                    "2": "13",
                                    "3": "REDMAN",
                                    "4": "3",
                                    "5": "3",
                                    "6": "1"
                                },
                                {
                                    "0": "French Grand Prix",
                                    "1": "1958",
                                    "2": "21",
                                    "3": "TRIPS",
                                    "4": "3",
                                    "5": "2",
                                    "6": "1"
                                },
                                {
                                    "0": "Indianapolis 500",
                                    "1": "1957",
                                    "2": "32",
                                    "3": "RATHMANN",
                                    "4": "2",
                                    "5": "1",
                                    "6": "1"
                                },
                                {
                                    "0": "Italian Grand Prix",
                                    "1": "1956",
                                    "2": "26",
                                    "3": "FLOCKHART",
                                    "4": "3",
                                    "5": "1",
                                    "6": "1"
                                },
                                {
                                    "0": "French Grand Prix",
                                    "1": "1950",
                                    "2": "18",
                                    "3": "WHITEHEAD",
                                    "4": "3",
                                    "5": "2",
                                    "6": "1"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>Track</th><th>year</th><th>grid</th><th>Driver</th><th>position</th><th>Times</th><th>(No column name)</th></tr><tr><td>Brazilian Grand Prix</td><td>2019</td><td>20</td><td>SAINZ</td><td>3</td><td>1</td><td>1</td></tr><tr><td>German Grand Prix</td><td>2019</td><td>20</td><td>VETTEL</td><td>2</td><td>2</td><td>2</td></tr><tr><td>Belgian Grand Prix</td><td>2016</td><td>21</td><td>HAMILTON</td><td>3</td><td>1</td><td>2</td></tr><tr><td>Hungarian Grand Prix</td><td>2014</td><td>22</td><td>HAMILTON</td><td>3</td><td>1</td><td>2</td></tr><tr><td>Abu Dhabi Grand Prix</td><td>2012</td><td>24</td><td>VETTEL</td><td>3</td><td>1</td><td>2</td></tr><tr><td>Australian Grand Prix</td><td>2009</td><td>20</td><td>TRULLI</td><td>3</td><td>1</td><td>1</td></tr><tr><td>Bahrain Grand Prix</td><td>2006</td><td>22</td><td>RAIKKONEN</td><td>3</td><td>1</td><td>1</td></tr><tr><td>German Grand Prix</td><td>2005</td><td>20</td><td>MONTOYA</td><td>2</td><td>2</td><td>1</td></tr><tr><td>Canadian Grand Prix</td><td>2005</td><td>20</td><td>BARRICHELLO</td><td>3</td><td>1</td><td>1</td></tr><tr><td>United States Grand Prix West</td><td>1980</td><td>24</td><td>EMERSON_FITTIPALDI</td><td>3</td><td>1</td><td>1</td></tr><tr><td>Spanish Grand Prix</td><td>1970</td><td>16</td><td>MARIO_ANDRETTI</td><td>3</td><td>3</td><td>1</td></tr><tr><td>Spanish Grand Prix</td><td>1969</td><td>13</td><td>MCLAREN</td><td>2</td><td>3</td><td>1</td></tr><tr><td>Spanish Grand Prix</td><td>1968</td><td>13</td><td>REDMAN</td><td>3</td><td>3</td><td>1</td></tr><tr><td>French Grand Prix</td><td>1958</td><td>21</td><td>TRIPS</td><td>3</td><td>2</td><td>1</td></tr><tr><td>Indianapolis 500</td><td>1957</td><td>32</td><td>RATHMANN</td><td>2</td><td>1</td><td>1</td></tr><tr><td>Italian Grand Prix</td><td>1956</td><td>26</td><td>FLOCKHART</td><td>3</td><td>1</td><td>1</td></tr><tr><td>French Grand Prix</td><td>1950</td><td>18</td><td>WHITEHEAD</td><td>3</td><td>2</td><td>1</td></tr></table>"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 8
        },
        {
            "cell_type": "markdown",
            "source": [
                "Evidently not\n",
                "\n",
                "  \n",
                "\n",
                "Which leads to the question, how far down the order can you qualify and expect to win?"
            ],
            "metadata": {
                "azdata_cell_guid": "0e93a911-f752-4fb4-b0ea-3bd9e0ec8f75"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "WITH driver as (SELECT re.raceId, grid, CONCAT(forename, ' ', UPPER(surname)) as [Driver], position, name, [year]\r\n",
                "FROM form.results AS re\r\n",
                "JOIN form.drivers AS dr ON re.driverId = dr.driverId\r\n",
                "JOIN form.races AS ra ON ra.raceId = re.raceId\r\n",
                "WHERE position = 1),\r\n",
                "grids as (SELECT max(grid) as bkRow, re.raceid, [year]\r\n",
                "FROM form.results AS re \r\n",
                "JOIN form.races AS ra ON re.raceid = ra.raceId\r\n",
                "GROUP BY re.raceId, [year])\r\n",
                "\r\n",
                "SELECT TOP 10 WITH TIES grid, Driver, position, g.[year], bkRow- grid as [Grid Places Ahead of Last]\r\n",
                "FROM driver AS d\r\n",
                "JOIN grids AS g on d.raceId = g.raceId and g.year = d.year\r\n",
                "ORDER BY grid DESC;"
            ],
            "metadata": {
                "azdata_cell_guid": "3bb9f6ac-8d13-48f1-bcb2-22adc5f25fc6",
                "tags": []
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(14 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.071"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 6,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "grid"
                                    },
                                    {
                                        "name": "Driver"
                                    },
                                    {
                                        "name": "position"
                                    },
                                    {
                                        "name": "year"
                                    },
                                    {
                                        "name": "Grid Places Ahead of Last"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "22",
                                    "1": "John WATSON",
                                    "2": "1",
                                    "3": "1983",
                                    "4": "4"
                                },
                                {
                                    "0": "19",
                                    "1": "Bill VUKOVICH",
                                    "2": "1",
                                    "3": "1954",
                                    "4": "14"
                                },
                                {
                                    "0": "18",
                                    "1": "Rubens BARRICHELLO",
                                    "2": "1",
                                    "3": "2000",
                                    "4": "4"
                                },
                                {
                                    "0": "17",
                                    "1": "Kimi RAIKKONEN",
                                    "2": "1",
                                    "3": "2005",
                                    "4": "3"
                                },
                                {
                                    "0": "17",
                                    "1": "John WATSON",
                                    "2": "1",
                                    "3": "1982",
                                    "4": "9"
                                },
                                {
                                    "0": "16",
                                    "1": "Jackie STEWART",
                                    "2": "1",
                                    "3": "1973",
                                    "4": "9"
                                },
                                {
                                    "0": "16",
                                    "1": "Michael SCHUMACHER",
                                    "2": "1",
                                    "3": "1995",
                                    "4": "8"
                                },
                                {
                                    "0": "15",
                                    "1": "Fernando ALONSO",
                                    "2": "1",
                                    "3": "2008",
                                    "4": "5"
                                },
                                {
                                    "0": "14",
                                    "1": "Jenson BUTTON",
                                    "2": "1",
                                    "3": "2006",
                                    "4": "8"
                                },
                                {
                                    "0": "14",
                                    "1": "Johnny HERBERT",
                                    "2": "1",
                                    "3": "1999",
                                    "4": "8"
                                },
                                {
                                    "0": "14",
                                    "1": "Olivier PANIS",
                                    "2": "1",
                                    "3": "1996",
                                    "4": "8"
                                },
                                {
                                    "0": "14",
                                    "1": "Alan JONES",
                                    "2": "1",
                                    "3": "1977",
                                    "4": "12"
                                },
                                {
                                    "0": "14",
                                    "1": "Bob SWEIKERT",
                                    "2": "1",
                                    "3": "1955",
                                    "4": "19"
                                },
                                {
                                    "0": "14",
                                    "1": "Lewis HAMILTON",
                                    "2": "1",
                                    "3": "2018",
                                    "4": "6"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>grid</th><th>Driver</th><th>position</th><th>year</th><th>Grid Places Ahead of Last</th></tr><tr><td>22</td><td>John WATSON</td><td>1</td><td>1983</td><td>4</td></tr><tr><td>19</td><td>Bill VUKOVICH</td><td>1</td><td>1954</td><td>14</td></tr><tr><td>18</td><td>Rubens BARRICHELLO</td><td>1</td><td>2000</td><td>4</td></tr><tr><td>17</td><td>Kimi RAIKKONEN</td><td>1</td><td>2005</td><td>3</td></tr><tr><td>17</td><td>John WATSON</td><td>1</td><td>1982</td><td>9</td></tr><tr><td>16</td><td>Jackie STEWART</td><td>1</td><td>1973</td><td>9</td></tr><tr><td>16</td><td>Michael SCHUMACHER</td><td>1</td><td>1995</td><td>8</td></tr><tr><td>15</td><td>Fernando ALONSO</td><td>1</td><td>2008</td><td>5</td></tr><tr><td>14</td><td>Jenson BUTTON</td><td>1</td><td>2006</td><td>8</td></tr><tr><td>14</td><td>Johnny HERBERT</td><td>1</td><td>1999</td><td>8</td></tr><tr><td>14</td><td>Olivier PANIS</td><td>1</td><td>1996</td><td>8</td></tr><tr><td>14</td><td>Alan JONES</td><td>1</td><td>1977</td><td>12</td></tr><tr><td>14</td><td>Bob SWEIKERT</td><td>1</td><td>1955</td><td>19</td></tr><tr><td>14</td><td>Lewis HAMILTON</td><td>1</td><td>2018</td><td>6</td></tr></table>"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 6
        },
        {
            "cell_type": "markdown",
            "source": [
                "## <p align='center'>*5.* <span style='color:steelblue'> Percentage Likelihood of Winning By Qualifying Position </span></p>\r\n",
                "\r\n",
                "- It's _all_ about the Saturday in F1"
            ],
            "metadata": {
                "azdata_cell_guid": "4f706275-c110-4307-a782-f2e783fd175d"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT grid as [Grid Position],\r\n",
                "\tROUND(CAST(count(*) AS FLOAT)/ (SELECT count(*) from form.results WHERE position = 1)*100,2) AS [Percentage Chance]\r\n",
                "FROM form.results\r\n",
                "WHERE position = 1\r\n",
                "GROUP BY grid\r\n",
                "ORDER by grid ASC;"
            ],
            "metadata": {
                "azdata_cell_guid": "716c88c0-1f0b-48fd-907e-bf5fcd8a3ff7"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(20 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.051"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 3,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "Grid Position"
                                    },
                                    {
                                        "name": "Percentage Chance"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "1",
                                    "1": "41.93"
                                },
                                {
                                    "0": "2",
                                    "1": "23.97"
                                },
                                {
                                    "0": "3",
                                    "1": "12.32"
                                },
                                {
                                    "0": "4",
                                    "1": "6.11"
                                },
                                {
                                    "0": "5",
                                    "1": "4.58"
                                },
                                {
                                    "0": "6",
                                    "1": "3.63"
                                },
                                {
                                    "0": "7",
                                    "1": "2.1"
                                },
                                {
                                    "0": "8",
                                    "1": "1.53"
                                },
                                {
                                    "0": "9",
                                    "1": "0.38"
                                },
                                {
                                    "0": "10",
                                    "1": "0.96"
                                },
                                {
                                    "0": "11",
                                    "1": "0.48"
                                },
                                {
                                    "0": "12",
                                    "1": "0.29"
                                },
                                {
                                    "0": "13",
                                    "1": "0.38"
                                },
                                {
                                    "0": "14",
                                    "1": "0.57"
                                },
                                {
                                    "0": "15",
                                    "1": "0.1"
                                },
                                {
                                    "0": "16",
                                    "1": "0.19"
                                },
                                {
                                    "0": "17",
                                    "1": "0.19"
                                },
                                {
                                    "0": "18",
                                    "1": "0.1"
                                },
                                {
                                    "0": "19",
                                    "1": "0.1"
                                },
                                {
                                    "0": "22",
                                    "1": "0.1"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>Grid Position</th><th>Percentage Chance</th></tr><tr><td>1</td><td>41.93</td></tr><tr><td>2</td><td>23.97</td></tr><tr><td>3</td><td>12.32</td></tr><tr><td>4</td><td>6.11</td></tr><tr><td>5</td><td>4.58</td></tr><tr><td>6</td><td>3.63</td></tr><tr><td>7</td><td>2.1</td></tr><tr><td>8</td><td>1.53</td></tr><tr><td>9</td><td>0.38</td></tr><tr><td>10</td><td>0.96</td></tr><tr><td>11</td><td>0.48</td></tr><tr><td>12</td><td>0.29</td></tr><tr><td>13</td><td>0.38</td></tr><tr><td>14</td><td>0.57</td></tr><tr><td>15</td><td>0.1</td></tr><tr><td>16</td><td>0.19</td></tr><tr><td>17</td><td>0.19</td></tr><tr><td>18</td><td>0.1</td></tr><tr><td>19</td><td>0.1</td></tr><tr><td>22</td><td>0.1</td></tr></table>"
                    },
                    "metadata": {
                        "azdata_chartOptions": {
                            "type": "line",
                            "dataDirection": "vertical",
                            "columnsAsLabels": true,
                            "labelFirstColumn": false,
                            "legendPosition": "right",
                            "yAxisLabel": "Percent",
                            "yAxisMin": 0,
                            "yAxisMax": 50,
                            "xAxisLabel": "Grid Postion",
                            "dataType": "number"
                        }
                    }
                }
            ],
            "execution_count": 3
        },
        {
            "cell_type": "markdown",
            "source": [
                "## <p align='center'><span style='color:steelblue'> *6.* The Effect of the Undercut </span></p>\n",
                "\n",
                "\n",
                "\n",
                "- One of the most talked about strategies in the modern era the _undercut_ has proved effective at 72 races since 2011 to 2020 (not including the current season)\n",
                "- Prior to this refuelling may have negated the effect\n",
                "- The result run is based purely on the initial pit stop"
            ],
            "metadata": {
                "azdata_cell_guid": "8198ba40-a0ff-4e39-acbc-720112d97f41"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "with [1st] as(SELECT re.raceId, [name], driverRef as [number1], lap, [year], [stop] \r\n",
                "FROM form.results AS re\r\n",
                "JOIN form.drivers AS dr on dr.driverId = re.driverId\r\n",
                "JOIN form.pit_stops AS p on p.driverId = re.driverId and p.raceId = re.raceId\r\n",
                "JOIN form.races AS ra ON ra.raceId = re.raceId\r\n",
                "WHERE position = 1 and stop=1),\r\n",
                "[2nd] as (SELECT re.raceId, driverRef as [number2], lap, [year], [stop] \r\n",
                "FROM form.results AS re\r\n",
                "JOIN form.drivers AS dr on dr.driverId = re.driverId\r\n",
                "JOIN form.pit_stops AS p on p.driverId = re.driverId and p.raceId = re.raceId\r\n",
                "JOIN form.races AS ra ON ra.raceId = re.raceId\r\n",
                "WHERE position = 2 and stop =1)\r\n",
                "\r\n",
                "SELECT  [1st].[name] as [Circuit], UPPER(number1) as [P1], [1st].[lap] AS [1:Pitted On], UPPER(number2) as [P2],  [2nd].[lap] AS [2:Pitted On], [1st].[year], count(*) OVER(partition by number1)\r\n",
                "FROM [1st]\r\n",
                "JOIN [2nd] ON [1st].raceId = [2nd].raceId and [1st].[stop] = [2nd].[stop]\r\n",
                "WHERE [1st].lap < [2nd].lap AND [1st].year != 2021\r\n",
                "ORDER BY [1st].[year] DESC"
            ],
            "metadata": {
                "azdata_cell_guid": "7cd8380c-1deb-4a57-bbd7-63e51a1a20f8",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(74 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.071"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 21,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "Circuit"
                                    },
                                    {
                                        "name": "P1"
                                    },
                                    {
                                        "name": "1:Pitted On"
                                    },
                                    {
                                        "name": "P2"
                                    },
                                    {
                                        "name": "2:Pitted On"
                                    },
                                    {
                                        "name": "year"
                                    },
                                    {
                                        "name": "(No column name)"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "Italian Grand Prix",
                                    "1": "GASLY",
                                    "2": "19",
                                    "3": "SAINZ",
                                    "4": "22",
                                    "5": "2020",
                                    "6": "1"
                                },
                                {
                                    "0": "Portuguese Grand Prix",
                                    "1": "HAMILTON",
                                    "2": "40",
                                    "3": "BOTTAS",
                                    "4": "41",
                                    "5": "2020",
                                    "6": "30"
                                },
                                {
                                    "0": "Turkish Grand Prix",
                                    "1": "HAMILTON",
                                    "2": "8",
                                    "3": "PEREZ",
                                    "4": "10",
                                    "5": "2020",
                                    "6": "30"
                                },
                                {
                                    "0": "Styrian Grand Prix",
                                    "1": "HAMILTON",
                                    "2": "27",
                                    "3": "BOTTAS",
                                    "4": "34",
                                    "5": "2020",
                                    "6": "30"
                                },
                                {
                                    "0": "Hungarian Grand Prix",
                                    "1": "HAMILTON",
                                    "2": "3",
                                    "3": "MAX_VERSTAPPEN",
                                    "4": "4",
                                    "5": "2020",
                                    "6": "30"
                                },
                                {
                                    "0": "Sakhir Grand Prix",
                                    "1": "PEREZ",
                                    "2": "1",
                                    "3": "OCON",
                                    "4": "41",
                                    "5": "2020",
                                    "6": "1"
                                },
                                {
                                    "0": "Belgian Grand Prix",
                                    "1": "LECLERC",
                                    "2": "21",
                                    "3": "HAMILTON",
                                    "4": "22",
                                    "5": "2019",
                                    "6": "2"
                                },
                                {
                                    "0": "Italian Grand Prix",
                                    "1": "LECLERC",
                                    "2": "20",
                                    "3": "BOTTAS",
                                    "4": "27",
                                    "5": "2019",
                                    "6": "2"
                                },
                                {
                                    "0": "Brazilian Grand Prix",
                                    "1": "MAX_VERSTAPPEN",
                                    "2": "21",
                                    "3": "GASLY",
                                    "4": "22",
                                    "5": "2019",
                                    "6": "2"
                                },
                                {
                                    "0": "Singapore Grand Prix",
                                    "1": "VETTEL",
                                    "2": "19",
                                    "3": "LECLERC",
                                    "4": "20",
                                    "5": "2019",
                                    "6": "15"
                                },
                                {
                                    "0": "Mexican Grand Prix",
                                    "1": "HAMILTON",
                                    "2": "23",
                                    "3": "VETTEL",
                                    "4": "37",
                                    "5": "2019",
                                    "6": "30"
                                },
                                {
                                    "0": "Azerbaijan Grand Prix",
                                    "1": "BOTTAS",
                                    "2": "12",
                                    "3": "HAMILTON",
                                    "4": "13",
                                    "5": "2019",
                                    "6": "4"
                                },
                                {
                                    "0": "United States Grand Prix",
                                    "1": "BOTTAS",
                                    "2": "14",
                                    "3": "HAMILTON",
                                    "4": "24",
                                    "5": "2019",
                                    "6": "4"
                                },
                                {
                                    "0": "Brazilian Grand Prix",
                                    "1": "HAMILTON",
                                    "2": "19",
                                    "3": "MAX_VERSTAPPEN",
                                    "4": "35",
                                    "5": "2018",
                                    "6": "30"
                                },
                                {
                                    "0": "Abu Dhabi Grand Prix",
                                    "1": "HAMILTON",
                                    "2": "7",
                                    "3": "VETTEL",
                                    "4": "15",
                                    "5": "2018",
                                    "6": "30"
                                },
                                {
                                    "0": "Hungarian Grand Prix",
                                    "1": "HAMILTON",
                                    "2": "25",
                                    "3": "VETTEL",
                                    "4": "39",
                                    "5": "2018",
                                    "6": "30"
                                },
                                {
                                    "0": "Singapore Grand Prix",
                                    "1": "HAMILTON",
                                    "2": "15",
                                    "3": "MAX_VERSTAPPEN",
                                    "4": "17",
                                    "5": "2018",
                                    "6": "30"
                                },
                                {
                                    "0": "British Grand Prix",
                                    "1": "VETTEL",
                                    "2": "20",
                                    "3": "HAMILTON",
                                    "4": "25",
                                    "5": "2018",
                                    "6": "15"
                                },
                                {
                                    "0": "Bahrain Grand Prix",
                                    "1": "VETTEL",
                                    "2": "18",
                                    "3": "BOTTAS",
                                    "4": "20",
                                    "5": "2018",
                                    "6": "15"
                                },
                                {
                                    "0": "Mexican Grand Prix",
                                    "1": "MAX_VERSTAPPEN",
                                    "2": "13",
                                    "3": "VETTEL",
                                    "4": "17",
                                    "5": "2018",
                                    "6": "2"
                                },
                                {
                                    "0": "United States Grand Prix",
                                    "1": "RAIKKONEN",
                                    "2": "21",
                                    "3": "MAX_VERSTAPPEN",
                                    "4": "22",
                                    "5": "2018",
                                    "6": "1"
                                },
                                {
                                    "0": "Chinese Grand Prix",
                                    "1": "RICCIARDO",
                                    "2": "17",
                                    "3": "BOTTAS",
                                    "4": "19",
                                    "5": "2018",
                                    "6": "3"
                                },
                                {
                                    "0": "Bahrain Grand Prix",
                                    "1": "VETTEL",
                                    "2": "10",
                                    "3": "HAMILTON",
                                    "4": "13",
                                    "5": "2017",
                                    "6": "15"
                                },
                                {
                                    "0": "Hungarian Grand Prix",
                                    "1": "VETTEL",
                                    "2": "32",
                                    "3": "RAIKKONEN",
                                    "4": "33",
                                    "5": "2017",
                                    "6": "15"
                                },
                                {
                                    "0": "British Grand Prix",
                                    "1": "HAMILTON",
                                    "2": "25",
                                    "3": "BOTTAS",
                                    "4": "32",
                                    "5": "2017",
                                    "6": "30"
                                },
                                {
                                    "0": "Belgian Grand Prix",
                                    "1": "HAMILTON",
                                    "2": "12",
                                    "3": "VETTEL",
                                    "4": "14",
                                    "5": "2017",
                                    "6": "30"
                                },
                                {
                                    "0": "Italian Grand Prix",
                                    "1": "HAMILTON",
                                    "2": "32",
                                    "3": "BOTTAS",
                                    "4": "33",
                                    "5": "2017",
                                    "6": "30"
                                },
                                {
                                    "0": "Russian Grand Prix",
                                    "1": "BOTTAS",
                                    "2": "27",
                                    "3": "VETTEL",
                                    "4": "34",
                                    "5": "2017",
                                    "6": "4"
                                },
                                {
                                    "0": "Abu Dhabi Grand Prix",
                                    "1": "BOTTAS",
                                    "2": "21",
                                    "3": "HAMILTON",
                                    "4": "24",
                                    "5": "2017",
                                    "6": "4"
                                },
                                {
                                    "0": "Hungarian Grand Prix",
                                    "1": "HAMILTON",
                                    "2": "16",
                                    "3": "ROSBERG",
                                    "4": "17",
                                    "5": "2016",
                                    "6": "30"
                                },
                                {
                                    "0": "Mexican Grand Prix",
                                    "1": "HAMILTON",
                                    "2": "17",
                                    "3": "ROSBERG",
                                    "4": "20",
                                    "5": "2016",
                                    "6": "30"
                                },
                                {
                                    "0": "Abu Dhabi Grand Prix",
                                    "1": "HAMILTON",
                                    "2": "7",
                                    "3": "ROSBERG",
                                    "4": "8",
                                    "5": "2016",
                                    "6": "30"
                                },
                                {
                                    "0": "Italian Grand Prix",
                                    "1": "ROSBERG",
                                    "2": "24",
                                    "3": "HAMILTON",
                                    "4": "25",
                                    "5": "2016",
                                    "6": "8"
                                },
                                {
                                    "0": "Australian Grand Prix",
                                    "1": "ROSBERG",
                                    "2": "12",
                                    "3": "HAMILTON",
                                    "4": "16",
                                    "5": "2016",
                                    "6": "8"
                                },
                                {
                                    "0": "Austrian Grand Prix",
                                    "1": "ROSBERG",
                                    "2": "33",
                                    "3": "HAMILTON",
                                    "4": "35",
                                    "5": "2015",
                                    "6": "8"
                                },
                                {
                                    "0": "Mexican Grand Prix",
                                    "1": "ROSBERG",
                                    "2": "26",
                                    "3": "HAMILTON",
                                    "4": "28",
                                    "5": "2015",
                                    "6": "8"
                                },
                                {
                                    "0": "Brazilian Grand Prix",
                                    "1": "ROSBERG",
                                    "2": "13",
                                    "3": "HAMILTON",
                                    "4": "14",
                                    "5": "2015",
                                    "6": "8"
                                },
                                {
                                    "0": "Abu Dhabi Grand Prix",
                                    "1": "ROSBERG",
                                    "2": "10",
                                    "3": "HAMILTON",
                                    "4": "11",
                                    "5": "2015",
                                    "6": "8"
                                },
                                {
                                    "0": "Australian Grand Prix",
                                    "1": "HAMILTON",
                                    "2": "25",
                                    "3": "ROSBERG",
                                    "4": "26",
                                    "5": "2015",
                                    "6": "30"
                                },
                                {
                                    "0": "Chinese Grand Prix",
                                    "1": "HAMILTON",
                                    "2": "14",
                                    "3": "ROSBERG",
                                    "4": "15",
                                    "5": "2015",
                                    "6": "30"
                                },
                                {
                                    "0": "Bahrain Grand Prix",
                                    "1": "HAMILTON",
                                    "2": "15",
                                    "3": "RAIKKONEN",
                                    "4": "17",
                                    "5": "2015",
                                    "6": "30"
                                },
                                {
                                    "0": "Canadian Grand Prix",
                                    "1": "HAMILTON",
                                    "2": "29",
                                    "3": "ROSBERG",
                                    "4": "30",
                                    "5": "2015",
                                    "6": "30"
                                },
                                {
                                    "0": "British Grand Prix",
                                    "1": "HAMILTON",
                                    "2": "19",
                                    "3": "ROSBERG",
                                    "4": "20",
                                    "5": "2015",
                                    "6": "30"
                                },
                                {
                                    "0": "United States Grand Prix",
                                    "1": "HAMILTON",
                                    "2": "18",
                                    "3": "ROSBERG",
                                    "4": "19",
                                    "5": "2015",
                                    "6": "30"
                                },
                                {
                                    "0": "Canadian Grand Prix",
                                    "1": "RICCIARDO",
                                    "2": "13",
                                    "3": "ROSBERG",
                                    "4": "18",
                                    "5": "2014",
                                    "6": "3"
                                },
                                {
                                    "0": "Hungarian Grand Prix",
                                    "1": "RICCIARDO",
                                    "2": "8",
                                    "3": "ALONSO",
                                    "4": "9",
                                    "5": "2014",
                                    "6": "3"
                                },
                                {
                                    "0": "Brazilian Grand Prix",
                                    "1": "ROSBERG",
                                    "2": "7",
                                    "3": "HAMILTON",
                                    "4": "8",
                                    "5": "2014",
                                    "6": "8"
                                },
                                {
                                    "0": "Austrian Grand Prix",
                                    "1": "ROSBERG",
                                    "2": "11",
                                    "3": "HAMILTON",
                                    "4": "13",
                                    "5": "2014",
                                    "6": "8"
                                },
                                {
                                    "0": "British Grand Prix",
                                    "1": "HAMILTON",
                                    "2": "24",
                                    "3": "BOTTAS",
                                    "4": "31",
                                    "5": "2014",
                                    "6": "30"
                                },
                                {
                                    "0": "Bahrain Grand Prix",
                                    "1": "HAMILTON",
                                    "2": "19",
                                    "3": "ROSBERG",
                                    "4": "21",
                                    "5": "2014",
                                    "6": "30"
                                },
                                {
                                    "0": "Spanish Grand Prix",
                                    "1": "HAMILTON",
                                    "2": "18",
                                    "3": "ROSBERG",
                                    "4": "21",
                                    "5": "2014",
                                    "6": "30"
                                },
                                {
                                    "0": "Abu Dhabi Grand Prix",
                                    "1": "HAMILTON",
                                    "2": "10",
                                    "3": "MASSA",
                                    "4": "13",
                                    "5": "2014",
                                    "6": "30"
                                },
                                {
                                    "0": "Spanish Grand Prix",
                                    "1": "ALONSO",
                                    "2": "9",
                                    "3": "RAIKKONEN",
                                    "4": "10",
                                    "5": "2013",
                                    "6": "3"
                                },
                                {
                                    "0": "Hungarian Grand Prix",
                                    "1": "HAMILTON",
                                    "2": "9",
                                    "3": "RAIKKONEN",
                                    "4": "13",
                                    "5": "2013",
                                    "6": "30"
                                },
                                {
                                    "0": "Italian Grand Prix",
                                    "1": "VETTEL",
                                    "2": "23",
                                    "3": "ALONSO",
                                    "4": "27",
                                    "5": "2013",
                                    "6": "15"
                                },
                                {
                                    "0": "Indian Grand Prix",
                                    "1": "VETTEL",
                                    "2": "2",
                                    "3": "ROSBERG",
                                    "4": "7",
                                    "5": "2013",
                                    "6": "15"
                                },
                                {
                                    "0": "United States Grand Prix",
                                    "1": "VETTEL",
                                    "2": "27",
                                    "3": "GROSJEAN",
                                    "4": "29",
                                    "5": "2013",
                                    "6": "15"
                                },
                                {
                                    "0": "Malaysian Grand Prix",
                                    "1": "VETTEL",
                                    "2": "5",
                                    "3": "WEBBER",
                                    "4": "7",
                                    "5": "2013",
                                    "6": "15"
                                },
                                {
                                    "0": "Bahrain Grand Prix",
                                    "1": "VETTEL",
                                    "2": "10",
                                    "3": "RAIKKONEN",
                                    "4": "16",
                                    "5": "2013",
                                    "6": "15"
                                },
                                {
                                    "0": "German Grand Prix",
                                    "1": "VETTEL",
                                    "2": "7",
                                    "3": "RAIKKONEN",
                                    "4": "8",
                                    "5": "2013",
                                    "6": "15"
                                },
                                {
                                    "0": "Singapore Grand Prix",
                                    "1": "VETTEL",
                                    "2": "10",
                                    "3": "BUTTON",
                                    "4": "14",
                                    "5": "2012",
                                    "6": "15"
                                },
                                {
                                    "0": "British Grand Prix",
                                    "1": "WEBBER",
                                    "2": "14",
                                    "3": "ALONSO",
                                    "4": "15",
                                    "5": "2012",
                                    "6": "1"
                                },
                                {
                                    "0": "Canadian Grand Prix",
                                    "1": "HAMILTON",
                                    "2": "17",
                                    "3": "GROSJEAN",
                                    "4": "21",
                                    "5": "2012",
                                    "6": "30"
                                },
                                {
                                    "0": "Hungarian Grand Prix",
                                    "1": "HAMILTON",
                                    "2": "18",
                                    "3": "RAIKKONEN",
                                    "4": "20",
                                    "5": "2012",
                                    "6": "30"
                                },
                                {
                                    "0": "Belgian Grand Prix",
                                    "1": "BUTTON",
                                    "2": "20",
                                    "3": "VETTEL",
                                    "4": "21",
                                    "5": "2012",
                                    "6": "3"
                                },
                                {
                                    "0": "German Grand Prix",
                                    "1": "ALONSO",
                                    "2": "18",
                                    "3": "BUTTON",
                                    "4": "19",
                                    "5": "2012",
                                    "6": "3"
                                },
                                {
                                    "0": "Italian Grand Prix",
                                    "1": "HAMILTON",
                                    "2": "23",
                                    "3": "PEREZ",
                                    "4": "29",
                                    "5": "2012",
                                    "6": "30"
                                },
                                {
                                    "0": "United States Grand Prix",
                                    "1": "HAMILTON",
                                    "2": "20",
                                    "3": "VETTEL",
                                    "4": "21",
                                    "5": "2012",
                                    "6": "30"
                                },
                                {
                                    "0": "British Grand Prix",
                                    "1": "ALONSO",
                                    "2": "12",
                                    "3": "VETTEL",
                                    "4": "13",
                                    "5": "2011",
                                    "6": "3"
                                },
                                {
                                    "0": "Canadian Grand Prix",
                                    "1": "BUTTON",
                                    "2": "8",
                                    "3": "VETTEL",
                                    "4": "20",
                                    "5": "2011",
                                    "6": "3"
                                },
                                {
                                    "0": "Hungarian Grand Prix",
                                    "1": "BUTTON",
                                    "2": "11",
                                    "3": "VETTEL",
                                    "4": "12",
                                    "5": "2011",
                                    "6": "3"
                                },
                                {
                                    "0": "Australian Grand Prix",
                                    "1": "VETTEL",
                                    "2": "14",
                                    "3": "HAMILTON",
                                    "4": "16",
                                    "5": "2011",
                                    "6": "15"
                                },
                                {
                                    "0": "Spanish Grand Prix",
                                    "1": "VETTEL",
                                    "2": "9",
                                    "3": "HAMILTON",
                                    "4": "11",
                                    "5": "2011",
                                    "6": "15"
                                },
                                {
                                    "0": "Monaco Grand Prix",
                                    "1": "VETTEL",
                                    "2": "16",
                                    "3": "ALONSO",
                                    "4": "17",
                                    "5": "2011",
                                    "6": "15"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>Circuit</th><th>P1</th><th>1:Pitted On</th><th>P2</th><th>2:Pitted On</th><th>year</th><th>(No column name)</th></tr><tr><td>Italian Grand Prix</td><td>GASLY</td><td>19</td><td>SAINZ</td><td>22</td><td>2020</td><td>1</td></tr><tr><td>Portuguese Grand Prix</td><td>HAMILTON</td><td>40</td><td>BOTTAS</td><td>41</td><td>2020</td><td>30</td></tr><tr><td>Turkish Grand Prix</td><td>HAMILTON</td><td>8</td><td>PEREZ</td><td>10</td><td>2020</td><td>30</td></tr><tr><td>Styrian Grand Prix</td><td>HAMILTON</td><td>27</td><td>BOTTAS</td><td>34</td><td>2020</td><td>30</td></tr><tr><td>Hungarian Grand Prix</td><td>HAMILTON</td><td>3</td><td>MAX_VERSTAPPEN</td><td>4</td><td>2020</td><td>30</td></tr><tr><td>Sakhir Grand Prix</td><td>PEREZ</td><td>1</td><td>OCON</td><td>41</td><td>2020</td><td>1</td></tr><tr><td>Belgian Grand Prix</td><td>LECLERC</td><td>21</td><td>HAMILTON</td><td>22</td><td>2019</td><td>2</td></tr><tr><td>Italian Grand Prix</td><td>LECLERC</td><td>20</td><td>BOTTAS</td><td>27</td><td>2019</td><td>2</td></tr><tr><td>Brazilian Grand Prix</td><td>MAX_VERSTAPPEN</td><td>21</td><td>GASLY</td><td>22</td><td>2019</td><td>2</td></tr><tr><td>Singapore Grand Prix</td><td>VETTEL</td><td>19</td><td>LECLERC</td><td>20</td><td>2019</td><td>15</td></tr><tr><td>Mexican Grand Prix</td><td>HAMILTON</td><td>23</td><td>VETTEL</td><td>37</td><td>2019</td><td>30</td></tr><tr><td>Azerbaijan Grand Prix</td><td>BOTTAS</td><td>12</td><td>HAMILTON</td><td>13</td><td>2019</td><td>4</td></tr><tr><td>United States Grand Prix</td><td>BOTTAS</td><td>14</td><td>HAMILTON</td><td>24</td><td>2019</td><td>4</td></tr><tr><td>Brazilian Grand Prix</td><td>HAMILTON</td><td>19</td><td>MAX_VERSTAPPEN</td><td>35</td><td>2018</td><td>30</td></tr><tr><td>Abu Dhabi Grand Prix</td><td>HAMILTON</td><td>7</td><td>VETTEL</td><td>15</td><td>2018</td><td>30</td></tr><tr><td>Hungarian Grand Prix</td><td>HAMILTON</td><td>25</td><td>VETTEL</td><td>39</td><td>2018</td><td>30</td></tr><tr><td>Singapore Grand Prix</td><td>HAMILTON</td><td>15</td><td>MAX_VERSTAPPEN</td><td>17</td><td>2018</td><td>30</td></tr><tr><td>British Grand Prix</td><td>VETTEL</td><td>20</td><td>HAMILTON</td><td>25</td><td>2018</td><td>15</td></tr><tr><td>Bahrain Grand Prix</td><td>VETTEL</td><td>18</td><td>BOTTAS</td><td>20</td><td>2018</td><td>15</td></tr><tr><td>Mexican Grand Prix</td><td>MAX_VERSTAPPEN</td><td>13</td><td>VETTEL</td><td>17</td><td>2018</td><td>2</td></tr><tr><td>United States Grand Prix</td><td>RAIKKONEN</td><td>21</td><td>MAX_VERSTAPPEN</td><td>22</td><td>2018</td><td>1</td></tr><tr><td>Chinese Grand Prix</td><td>RICCIARDO</td><td>17</td><td>BOTTAS</td><td>19</td><td>2018</td><td>3</td></tr><tr><td>Bahrain Grand Prix</td><td>VETTEL</td><td>10</td><td>HAMILTON</td><td>13</td><td>2017</td><td>15</td></tr><tr><td>Hungarian Grand Prix</td><td>VETTEL</td><td>32</td><td>RAIKKONEN</td><td>33</td><td>2017</td><td>15</td></tr><tr><td>British Grand Prix</td><td>HAMILTON</td><td>25</td><td>BOTTAS</td><td>32</td><td>2017</td><td>30</td></tr><tr><td>Belgian Grand Prix</td><td>HAMILTON</td><td>12</td><td>VETTEL</td><td>14</td><td>2017</td><td>30</td></tr><tr><td>Italian Grand Prix</td><td>HAMILTON</td><td>32</td><td>BOTTAS</td><td>33</td><td>2017</td><td>30</td></tr><tr><td>Russian Grand Prix</td><td>BOTTAS</td><td>27</td><td>VETTEL</td><td>34</td><td>2017</td><td>4</td></tr><tr><td>Abu Dhabi Grand Prix</td><td>BOTTAS</td><td>21</td><td>HAMILTON</td><td>24</td><td>2017</td><td>4</td></tr><tr><td>Hungarian Grand Prix</td><td>HAMILTON</td><td>16</td><td>ROSBERG</td><td>17</td><td>2016</td><td>30</td></tr><tr><td>Mexican Grand Prix</td><td>HAMILTON</td><td>17</td><td>ROSBERG</td><td>20</td><td>2016</td><td>30</td></tr><tr><td>Abu Dhabi Grand Prix</td><td>HAMILTON</td><td>7</td><td>ROSBERG</td><td>8</td><td>2016</td><td>30</td></tr><tr><td>Italian Grand Prix</td><td>ROSBERG</td><td>24</td><td>HAMILTON</td><td>25</td><td>2016</td><td>8</td></tr><tr><td>Australian Grand Prix</td><td>ROSBERG</td><td>12</td><td>HAMILTON</td><td>16</td><td>2016</td><td>8</td></tr><tr><td>Austrian Grand Prix</td><td>ROSBERG</td><td>33</td><td>HAMILTON</td><td>35</td><td>2015</td><td>8</td></tr><tr><td>Mexican Grand Prix</td><td>ROSBERG</td><td>26</td><td>HAMILTON</td><td>28</td><td>2015</td><td>8</td></tr><tr><td>Brazilian Grand Prix</td><td>ROSBERG</td><td>13</td><td>HAMILTON</td><td>14</td><td>2015</td><td>8</td></tr><tr><td>Abu Dhabi Grand Prix</td><td>ROSBERG</td><td>10</td><td>HAMILTON</td><td>11</td><td>2015</td><td>8</td></tr><tr><td>Australian Grand Prix</td><td>HAMILTON</td><td>25</td><td>ROSBERG</td><td>26</td><td>2015</td><td>30</td></tr><tr><td>Chinese Grand Prix</td><td>HAMILTON</td><td>14</td><td>ROSBERG</td><td>15</td><td>2015</td><td>30</td></tr><tr><td>Bahrain Grand Prix</td><td>HAMILTON</td><td>15</td><td>RAIKKONEN</td><td>17</td><td>2015</td><td>30</td></tr><tr><td>Canadian Grand Prix</td><td>HAMILTON</td><td>29</td><td>ROSBERG</td><td>30</td><td>2015</td><td>30</td></tr><tr><td>British Grand Prix</td><td>HAMILTON</td><td>19</td><td>ROSBERG</td><td>20</td><td>2015</td><td>30</td></tr><tr><td>United States Grand Prix</td><td>HAMILTON</td><td>18</td><td>ROSBERG</td><td>19</td><td>2015</td><td>30</td></tr><tr><td>Canadian Grand Prix</td><td>RICCIARDO</td><td>13</td><td>ROSBERG</td><td>18</td><td>2014</td><td>3</td></tr><tr><td>Hungarian Grand Prix</td><td>RICCIARDO</td><td>8</td><td>ALONSO</td><td>9</td><td>2014</td><td>3</td></tr><tr><td>Brazilian Grand Prix</td><td>ROSBERG</td><td>7</td><td>HAMILTON</td><td>8</td><td>2014</td><td>8</td></tr><tr><td>Austrian Grand Prix</td><td>ROSBERG</td><td>11</td><td>HAMILTON</td><td>13</td><td>2014</td><td>8</td></tr><tr><td>British Grand Prix</td><td>HAMILTON</td><td>24</td><td>BOTTAS</td><td>31</td><td>2014</td><td>30</td></tr><tr><td>Bahrain Grand Prix</td><td>HAMILTON</td><td>19</td><td>ROSBERG</td><td>21</td><td>2014</td><td>30</td></tr><tr><td>Spanish Grand Prix</td><td>HAMILTON</td><td>18</td><td>ROSBERG</td><td>21</td><td>2014</td><td>30</td></tr><tr><td>Abu Dhabi Grand Prix</td><td>HAMILTON</td><td>10</td><td>MASSA</td><td>13</td><td>2014</td><td>30</td></tr><tr><td>Spanish Grand Prix</td><td>ALONSO</td><td>9</td><td>RAIKKONEN</td><td>10</td><td>2013</td><td>3</td></tr><tr><td>Hungarian Grand Prix</td><td>HAMILTON</td><td>9</td><td>RAIKKONEN</td><td>13</td><td>2013</td><td>30</td></tr><tr><td>Italian Grand Prix</td><td>VETTEL</td><td>23</td><td>ALONSO</td><td>27</td><td>2013</td><td>15</td></tr><tr><td>Indian Grand Prix</td><td>VETTEL</td><td>2</td><td>ROSBERG</td><td>7</td><td>2013</td><td>15</td></tr><tr><td>United States Grand Prix</td><td>VETTEL</td><td>27</td><td>GROSJEAN</td><td>29</td><td>2013</td><td>15</td></tr><tr><td>Malaysian Grand Prix</td><td>VETTEL</td><td>5</td><td>WEBBER</td><td>7</td><td>2013</td><td>15</td></tr><tr><td>Bahrain Grand Prix</td><td>VETTEL</td><td>10</td><td>RAIKKONEN</td><td>16</td><td>2013</td><td>15</td></tr><tr><td>German Grand Prix</td><td>VETTEL</td><td>7</td><td>RAIKKONEN</td><td>8</td><td>2013</td><td>15</td></tr><tr><td>Singapore Grand Prix</td><td>VETTEL</td><td>10</td><td>BUTTON</td><td>14</td><td>2012</td><td>15</td></tr><tr><td>British Grand Prix</td><td>WEBBER</td><td>14</td><td>ALONSO</td><td>15</td><td>2012</td><td>1</td></tr><tr><td>Canadian Grand Prix</td><td>HAMILTON</td><td>17</td><td>GROSJEAN</td><td>21</td><td>2012</td><td>30</td></tr><tr><td>Hungarian Grand Prix</td><td>HAMILTON</td><td>18</td><td>RAIKKONEN</td><td>20</td><td>2012</td><td>30</td></tr><tr><td>Belgian Grand Prix</td><td>BUTTON</td><td>20</td><td>VETTEL</td><td>21</td><td>2012</td><td>3</td></tr><tr><td>German Grand Prix</td><td>ALONSO</td><td>18</td><td>BUTTON</td><td>19</td><td>2012</td><td>3</td></tr><tr><td>Italian Grand Prix</td><td>HAMILTON</td><td>23</td><td>PEREZ</td><td>29</td><td>2012</td><td>30</td></tr><tr><td>United States Grand Prix</td><td>HAMILTON</td><td>20</td><td>VETTEL</td><td>21</td><td>2012</td><td>30</td></tr><tr><td>British Grand Prix</td><td>ALONSO</td><td>12</td><td>VETTEL</td><td>13</td><td>2011</td><td>3</td></tr><tr><td>Canadian Grand Prix</td><td>BUTTON</td><td>8</td><td>VETTEL</td><td>20</td><td>2011</td><td>3</td></tr><tr><td>Hungarian Grand Prix</td><td>BUTTON</td><td>11</td><td>VETTEL</td><td>12</td><td>2011</td><td>3</td></tr><tr><td>Australian Grand Prix</td><td>VETTEL</td><td>14</td><td>HAMILTON</td><td>16</td><td>2011</td><td>15</td></tr><tr><td>Spanish Grand Prix</td><td>VETTEL</td><td>9</td><td>HAMILTON</td><td>11</td><td>2011</td><td>15</td></tr><tr><td>Monaco Grand Prix</td><td>VETTEL</td><td>16</td><td>ALONSO</td><td>17</td><td>2011</td><td>15</td></tr></table>"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 21
        },
        {
            "cell_type": "markdown",
            "source": [
                "## <p align='center'><span style='color:steelblue'> *7.* It's All About Consistency </span></p>\r\n",
                "\r\n",
                "- It's not necessarily the guy with the most wins that takes home the championship.\r\n",
                "- The following is list of all the times the driver with the most wins didn't have the most points\r\n",
                "- The query extracts where most wins did not equal the Drivers Championship, often this has led to drivers being tied on wins, but not on points."
            ],
            "metadata": {
                "azdata_cell_guid": "01a58c4a-bf89-41f5-9ab6-83afae80636b"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "with cte(driverRef, [year], Wins) as(SELECT driverRef, [year],\r\n",
                "\tSUM (CASE WHEN position = 1 THEN 1 ELSE 0 END)  as Wins\r\n",
                "FROM form.results AS re\r\n",
                "JOIN form.drivers AS dr ON dr.driverId = re.driverId\r\n",
                "JOIN form.races as ra ON ra.raceId = re.raceid\r\n",
                "WHERE  re.driverId IN  (SELECT driverId from form.results where position = 1 GROUP BY driverId)\r\n",
                "GROUP BY driverRef, [year]), \r\n",
                "cte2 as \r\n",
                "(SELECT * , max(wins) over(partition by [year]) as max_wins FROM cte),\r\n",
                "\r\n",
                "cte3 as(SELECT ds.points, driverRef,[year], \r\n",
                "\tRANK() OVER(partition by [year] order by ds.points DESC) as ranking\r\n",
                "FROM form.driver_standings as ds \r\n",
                "JOIN form.drivers as dr ON dr.driverId = ds.driverId\r\n",
                "JOIN form.races AS ra ON ra.raceId =ds.raceId\r\n",
                "join form.results as re ON re.driverId = dr.driverId\r\n",
                "GROUP BY ds.points, driverRef, [year])\r\n",
                "\r\n",
                "SELECT c.[year], UPPER(c.driverRef) as [Most Wins], c.Wins, UPPER(c2.driverRef) [f1 Champion], c4.Wins\r\n",
                "FROM (SELECT c1.[year], c1.driverRef, c1.Wins FROM cte2 AS c1 WHERE Wins=max_wins) as c\r\n",
                "JOIN (SELECT c3.driverRef, c3.[year] from cte3 AS c3 WHERE ranking = 1) as c2\r\n",
                "JOIN cte as C4 ON c4.[year] = c2.[year] and c4.driverRef = c2.driverRef\r\n",
                "ON c.[year] = c2.[year]\r\n",
                "WHERE c.driverRef != c2.driverRef\r\n",
                "ORDER BY c.[year] DESC"
            ],
            "metadata": {
                "azdata_cell_guid": "cdd13d43-9664-4dfc-9e86-cb0589f05714",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(29 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.771"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 7,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "year"
                                    },
                                    {
                                        "name": "Most Wins"
                                    },
                                    {
                                        "name": "Wins"
                                    },
                                    {
                                        "name": "f1 Champion"
                                    },
                                    {
                                        "name": "Wins"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "2016",
                                    "1": "HAMILTON",
                                    "2": "10",
                                    "3": "ROSBERG",
                                    "4": "9"
                                },
                                {
                                    "0": "2010",
                                    "1": "ALONSO",
                                    "2": "5",
                                    "3": "VETTEL",
                                    "4": "5"
                                },
                                {
                                    "0": "2008",
                                    "1": "MASSA",
                                    "2": "6",
                                    "3": "HAMILTON",
                                    "4": "5"
                                },
                                {
                                    "0": "2006",
                                    "1": "MICHAEL_SCHUMACHER",
                                    "2": "7",
                                    "3": "ALONSO",
                                    "4": "7"
                                },
                                {
                                    "0": "2005",
                                    "1": "RAIKKONEN",
                                    "2": "7",
                                    "3": "ALONSO",
                                    "4": "7"
                                },
                                {
                                    "0": "1989",
                                    "1": "SENNA",
                                    "2": "6",
                                    "3": "PROST",
                                    "4": "4"
                                },
                                {
                                    "0": "1987",
                                    "1": "MANSELL",
                                    "2": "6",
                                    "3": "PIQUET",
                                    "4": "3"
                                },
                                {
                                    "0": "1986",
                                    "1": "MANSELL",
                                    "2": "5",
                                    "3": "PROST",
                                    "4": "4"
                                },
                                {
                                    "0": "1984",
                                    "1": "PROST",
                                    "2": "7",
                                    "3": "LAUDA",
                                    "4": "5"
                                },
                                {
                                    "0": "1983",
                                    "1": "PROST",
                                    "2": "4",
                                    "3": "PIQUET",
                                    "4": "3"
                                },
                                {
                                    "0": "1982",
                                    "1": "ARNOUX",
                                    "2": "2",
                                    "3": "KEKE_ROSBERG",
                                    "4": "1"
                                },
                                {
                                    "0": "1982",
                                    "1": "LAUDA",
                                    "2": "2",
                                    "3": "KEKE_ROSBERG",
                                    "4": "1"
                                },
                                {
                                    "0": "1982",
                                    "1": "PIRONI",
                                    "2": "2",
                                    "3": "KEKE_ROSBERG",
                                    "4": "1"
                                },
                                {
                                    "0": "1982",
                                    "1": "PROST",
                                    "2": "2",
                                    "3": "KEKE_ROSBERG",
                                    "4": "1"
                                },
                                {
                                    "0": "1982",
                                    "1": "WATSON",
                                    "2": "2",
                                    "3": "KEKE_ROSBERG",
                                    "4": "1"
                                },
                                {
                                    "0": "1981",
                                    "1": "PROST",
                                    "2": "3",
                                    "3": "PIQUET",
                                    "4": "3"
                                },
                                {
                                    "0": "1979",
                                    "1": "JONES",
                                    "2": "4",
                                    "3": "SCHECKTER",
                                    "4": "3"
                                },
                                {
                                    "0": "1977",
                                    "1": "MARIO_ANDRETTI",
                                    "2": "4",
                                    "3": "LAUDA",
                                    "4": "3"
                                },
                                {
                                    "0": "1974",
                                    "1": "PETERSON",
                                    "2": "3",
                                    "3": "EMERSON_FITTIPALDI",
                                    "4": "3"
                                },
                                {
                                    "0": "1974",
                                    "1": "REUTEMANN",
                                    "2": "3",
                                    "3": "EMERSON_FITTIPALDI",
                                    "4": "3"
                                },
                                {
                                    "0": "1968",
                                    "1": "STEWART",
                                    "2": "3",
                                    "3": "HILL",
                                    "4": "3"
                                },
                                {
                                    "0": "1967",
                                    "1": "CLARK",
                                    "2": "4",
                                    "3": "HULME",
                                    "4": "2"
                                },
                                {
                                    "0": "1964",
                                    "1": "CLARK",
                                    "2": "3",
                                    "3": "SURTEES",
                                    "4": "2"
                                },
                                {
                                    "0": "1961",
                                    "1": "MOSS",
                                    "2": "2",
                                    "3": "PHIL_HILL",
                                    "4": "2"
                                },
                                {
                                    "0": "1961",
                                    "1": "TRIPS",
                                    "2": "2",
                                    "3": "PHIL_HILL",
                                    "4": "2"
                                },
                                {
                                    "0": "1959",
                                    "1": "BROOKS",
                                    "2": "2",
                                    "3": "JACK_BRABHAM",
                                    "4": "2"
                                },
                                {
                                    "0": "1959",
                                    "1": "MOSS",
                                    "2": "2",
                                    "3": "JACK_BRABHAM",
                                    "4": "2"
                                },
                                {
                                    "0": "1958",
                                    "1": "MOSS",
                                    "2": "4",
                                    "3": "HAWTHORN",
                                    "4": "1"
                                },
                                {
                                    "0": "1950",
                                    "1": "FANGIO",
                                    "2": "3",
                                    "3": "FARINA",
                                    "4": "3"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>year</th><th>Most Wins</th><th>Wins</th><th>f1 Champion</th><th>Wins</th></tr><tr><td>2016</td><td>HAMILTON</td><td>10</td><td>ROSBERG</td><td>9</td></tr><tr><td>2010</td><td>ALONSO</td><td>5</td><td>VETTEL</td><td>5</td></tr><tr><td>2008</td><td>MASSA</td><td>6</td><td>HAMILTON</td><td>5</td></tr><tr><td>2006</td><td>MICHAEL_SCHUMACHER</td><td>7</td><td>ALONSO</td><td>7</td></tr><tr><td>2005</td><td>RAIKKONEN</td><td>7</td><td>ALONSO</td><td>7</td></tr><tr><td>1989</td><td>SENNA</td><td>6</td><td>PROST</td><td>4</td></tr><tr><td>1987</td><td>MANSELL</td><td>6</td><td>PIQUET</td><td>3</td></tr><tr><td>1986</td><td>MANSELL</td><td>5</td><td>PROST</td><td>4</td></tr><tr><td>1984</td><td>PROST</td><td>7</td><td>LAUDA</td><td>5</td></tr><tr><td>1983</td><td>PROST</td><td>4</td><td>PIQUET</td><td>3</td></tr><tr><td>1982</td><td>ARNOUX</td><td>2</td><td>KEKE_ROSBERG</td><td>1</td></tr><tr><td>1982</td><td>LAUDA</td><td>2</td><td>KEKE_ROSBERG</td><td>1</td></tr><tr><td>1982</td><td>PIRONI</td><td>2</td><td>KEKE_ROSBERG</td><td>1</td></tr><tr><td>1982</td><td>PROST</td><td>2</td><td>KEKE_ROSBERG</td><td>1</td></tr><tr><td>1982</td><td>WATSON</td><td>2</td><td>KEKE_ROSBERG</td><td>1</td></tr><tr><td>1981</td><td>PROST</td><td>3</td><td>PIQUET</td><td>3</td></tr><tr><td>1979</td><td>JONES</td><td>4</td><td>SCHECKTER</td><td>3</td></tr><tr><td>1977</td><td>MARIO_ANDRETTI</td><td>4</td><td>LAUDA</td><td>3</td></tr><tr><td>1974</td><td>PETERSON</td><td>3</td><td>EMERSON_FITTIPALDI</td><td>3</td></tr><tr><td>1974</td><td>REUTEMANN</td><td>3</td><td>EMERSON_FITTIPALDI</td><td>3</td></tr><tr><td>1968</td><td>STEWART</td><td>3</td><td>HILL</td><td>3</td></tr><tr><td>1967</td><td>CLARK</td><td>4</td><td>HULME</td><td>2</td></tr><tr><td>1964</td><td>CLARK</td><td>3</td><td>SURTEES</td><td>2</td></tr><tr><td>1961</td><td>MOSS</td><td>2</td><td>PHIL_HILL</td><td>2</td></tr><tr><td>1961</td><td>TRIPS</td><td>2</td><td>PHIL_HILL</td><td>2</td></tr><tr><td>1959</td><td>BROOKS</td><td>2</td><td>JACK_BRABHAM</td><td>2</td></tr><tr><td>1959</td><td>MOSS</td><td>2</td><td>JACK_BRABHAM</td><td>2</td></tr><tr><td>1958</td><td>MOSS</td><td>4</td><td>HAWTHORN</td><td>1</td></tr><tr><td>1950</td><td>FANGIO</td><td>3</td><td>FARINA</td><td>3</td></tr></table>"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 7
        },
        {
            "cell_type": "markdown",
            "source": [
                "## <p align='center'><span style='color:steelblue'> *8.* The Lack of Consistency </span></p>\r\n",
                "\r\n",
                "- A list of drivers that, no matter how many times they tried, never made it to the podium"
            ],
            "metadata": {
                "azdata_cell_guid": "72586a3f-b2cf-4b53-b83d-4a53aadad64f"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT TOP 10 CONCAT(dr.forename,' ', UPPER(dr.driverRef)), count(driverRef) as [Races] \r\n",
                "FROM form.drivers as dr\r\n",
                "JOIN form.results AS r ON r.driverId = dr.driverId\r\n",
                "WHERE  dr.driverRef NOT IN (SELECT dr1.driverRef FROM form.drivers as dr1 JOIN form.results as r1 ON r1.driverId = dr1.driverId\r\n",
                "WHERE position IN  (1,2,3))\r\n",
                "--JOIN form.driver_standings AS ds ON dr.driverId = ds.driverId\r\n",
                "--WHERE position NOT IN (1,2,3)\r\n",
                "GROUP BY CONCAT(dr.forename,' ', UPPER(dr.driverRef))\r\n",
                "ORDER BY count(*) DESC;"
            ],
            "metadata": {
                "azdata_cell_guid": "071e7e9a-550d-4313-9847-fe68859e28ab",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(10 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.044"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 12,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "(No column name)"
                                    },
                                    {
                                        "name": "Races"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "Nico HULKENBERG",
                                    "1": "182"
                                },
                                {
                                    "0": "Adrian SUTIL",
                                    "1": "128"
                                },
                                {
                                    "0": "Pierluigi MARTINI",
                                    "1": "123"
                                },
                                {
                                    "0": "Philippe ALLIOT",
                                    "1": "115"
                                },
                                {
                                    "0": "Piercarlo GHINZANI",
                                    "1": "111"
                                },
                                {
                                    "0": "Pedro DINIZ",
                                    "1": "99"
                                },
                                {
                                    "0": "Marcus ERICSSON",
                                    "1": "97"
                                },
                                {
                                    "0": "Ukyo KATAYAMA",
                                    "1": "97"
                                },
                                {
                                    "0": "Marc SURER",
                                    "1": "88"
                                },
                                {
                                    "0": "Jonathan PALMER",
                                    "1": "88"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>(No column name)</th><th>Races</th></tr><tr><td>Nico HULKENBERG</td><td>182</td></tr><tr><td>Adrian SUTIL</td><td>128</td></tr><tr><td>Pierluigi MARTINI</td><td>123</td></tr><tr><td>Philippe ALLIOT</td><td>115</td></tr><tr><td>Piercarlo GHINZANI</td><td>111</td></tr><tr><td>Pedro DINIZ</td><td>99</td></tr><tr><td>Marcus ERICSSON</td><td>97</td></tr><tr><td>Ukyo KATAYAMA</td><td>97</td></tr><tr><td>Marc SURER</td><td>88</td></tr><tr><td>Jonathan PALMER</td><td>88</td></tr></table>"
                    },
                    "metadata": {
                        "azdata_chartOptions": {
                            "type": "bar",
                            "dataDirection": "vertical",
                            "columnsAsLabels": true,
                            "labelFirstColumn": true,
                            "legendPosition": "top",
                            "yAxisLabel": "No. Of Races",
                            "yAxisMin": 60,
                            "yAxisMax": 200,
                            "xAxisLabel": "Drivers"
                        }
                    }
                }
            ],
            "execution_count": 12
        },
        {
            "cell_type": "markdown",
            "source": [
                "## <p align='center'><span style='color:steelblue'> *9.* Championships by Country; including most chamionships by a driver </span></p>\r\n",
                "\r\n",
                "- Formula 1 for all it's global reach is dominated by European drivers with the spread of championships breaking down like this:\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "691f707e-d0c1-4792-853b-3efd3a4a7366"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT DISTINCT nationality as Nationality, count(*) OVER(partition by nationality) as [Championships by Country]\r\n",
                "FROM form.drivers as dr \r\n",
                "JOIN form.driver_standings as st \r\n",
                "ON dr.driverId = st.driverId\r\n",
                "JOIN (SELECT max(raceId) as final, [year]\r\n",
                "FROM form.races GROUP BY [year]) as ra\r\n",
                "ON st.raceId = ra.final\r\n",
                "WHERE position = 1\r\n",
                "ORDER BY [Championships by Country] DESC;"
            ],
            "metadata": {
                "azdata_cell_guid": "d3de8f21-4416-4899-a739-0e426ff8c522",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(14 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.096"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {
                        "azdata_chartOptions": {
                            "type": "doughnut",
                            "dataDirection": "vertical",
                            "columnsAsLabels": true,
                            "labelFirstColumn": false,
                            "legendPosition": "right"
                        }
                    },
                    "execution_count": 2,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "Nationality"
                                    },
                                    {
                                        "name": "Championships by Country"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "British",
                                    "1": "20"
                                },
                                {
                                    "0": "German",
                                    "1": "12"
                                },
                                {
                                    "0": "Brazilian",
                                    "1": "8"
                                },
                                {
                                    "0": "Argentine",
                                    "1": "5"
                                },
                                {
                                    "0": "Australian",
                                    "1": "4"
                                },
                                {
                                    "0": "Austrian",
                                    "1": "4"
                                },
                                {
                                    "0": "Finnish",
                                    "1": "4"
                                },
                                {
                                    "0": "French",
                                    "1": "4"
                                },
                                {
                                    "0": "Italian",
                                    "1": "3"
                                },
                                {
                                    "0": "American",
                                    "1": "2"
                                },
                                {
                                    "0": "Spanish",
                                    "1": "2"
                                },
                                {
                                    "0": "Canadian",
                                    "1": "1"
                                },
                                {
                                    "0": "New Zealander",
                                    "1": "1"
                                },
                                {
                                    "0": "South African",
                                    "1": "1"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>Nationality</th><th>Championships by Country</th></tr>",
                            "<tr><td>British</td><td>20</td></tr>",
                            "<tr><td>German</td><td>12</td></tr>",
                            "<tr><td>Brazilian</td><td>8</td></tr>",
                            "<tr><td>Argentine</td><td>5</td></tr>",
                            "<tr><td>Australian</td><td>4</td></tr>",
                            "<tr><td>Austrian</td><td>4</td></tr>",
                            "<tr><td>Finnish</td><td>4</td></tr>",
                            "<tr><td>French</td><td>4</td></tr>",
                            "<tr><td>Italian</td><td>3</td></tr>",
                            "<tr><td>American</td><td>2</td></tr>",
                            "<tr><td>Spanish</td><td>2</td></tr>",
                            "<tr><td>Canadian</td><td>1</td></tr>",
                            "<tr><td>New Zealander</td><td>1</td></tr>",
                            "<tr><td>South African</td><td>1</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 2
        },
        {
            "cell_type": "code",
            "source": [
                "with countries as (SELECT DISTINCT nationality, count(*) OVER(partition by nationality) as [Championships by Country]\r\n",
                "FROM form.drivers as dr \r\n",
                "JOIN form.driver_standings as st \r\n",
                "ON dr.driverId = st.driverId\r\n",
                "JOIN (SELECT max(raceId) as final, [year]\r\n",
                "FROM form.races GROUP BY [year]) as ra\r\n",
                "ON st.raceId = ra.final\r\n",
                "WHERE position = 1),\r\n",
                "\r\n",
                "\r\n",
                "ranked as (SELECT DISTINCT CONCAT(forename,' ',UPPER(surname)) as Driver, \r\n",
                "\tcount(*) as [Championships], nationality,  \r\n",
                "\tRANK() OVER(partition by nationality order by COUNT(*) DESC) as [Rank]\r\n",
                "FROM form.drivers as dr \r\n",
                "JOIN form.driver_standings as st \r\n",
                "ON dr.driverId = st.driverId\r\n",
                "JOIN (SELECT max(raceId) as final, [year]\r\n",
                "FROM form.races GROUP BY [year]) as ra\r\n",
                "ON st.raceId = ra.final\r\n",
                "WHERE position = 1\r\n",
                "GROUP BY CONCAT(forename,' ',UPPER(surname)), nationality)\r\n",
                "\r\n",
                "SELECT c.nationality as Nationality, c.[Championships by Country], Driver as [Most Successful Driver], [Championships]\r\n",
                "from countries as c\r\n",
                "JOIN ranked as r\r\n",
                "ON c.nationality = r.nationality\r\n",
                "WHERE [Rank] =1\r\n",
                "ORDER BY [Championships by Country] DESC;"
            ],
            "metadata": {
                "azdata_cell_guid": "a802dac9-7339-42a0-b643-8a6f7bf1e10e",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(16 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.057"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "metadata": {
                        "azdata_chartOptions": {
                            "type": "table",
                            "dataDirection": "horizontal",
                            "columnsAsLabels": true,
                            "labelFirstColumn": false,
                            "legendPosition": "top"
                        }
                    },
                    "execution_count": 5,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "Nationality"
                                    },
                                    {
                                        "name": "Championships by Country"
                                    },
                                    {
                                        "name": "Most Successful Driver"
                                    },
                                    {
                                        "name": "Championships"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "British",
                                    "1": "20",
                                    "2": "Lewis HAMILTON",
                                    "3": "7"
                                },
                                {
                                    "0": "German",
                                    "1": "12",
                                    "2": "Michael SCHUMACHER",
                                    "3": "7"
                                },
                                {
                                    "0": "Brazilian",
                                    "1": "8",
                                    "2": "Ayrton SENNA",
                                    "3": "3"
                                },
                                {
                                    "0": "Brazilian",
                                    "1": "8",
                                    "2": "Nelson PIQUET",
                                    "3": "3"
                                },
                                {
                                    "0": "Argentine",
                                    "1": "5",
                                    "2": "Juan FANGIO",
                                    "3": "5"
                                },
                                {
                                    "0": "Australian",
                                    "1": "4",
                                    "2": "Jack BRABHAM",
                                    "3": "3"
                                },
                                {
                                    "0": "Austrian",
                                    "1": "4",
                                    "2": "Niki LAUDA",
                                    "3": "3"
                                },
                                {
                                    "0": "Finnish",
                                    "1": "4",
                                    "2": "Mika HAKKINEN",
                                    "3": "2"
                                },
                                {
                                    "0": "French",
                                    "1": "4",
                                    "2": "Alain PROST",
                                    "3": "4"
                                },
                                {
                                    "0": "Italian",
                                    "1": "3",
                                    "2": "Alberto ASCARI",
                                    "3": "2"
                                },
                                {
                                    "0": "American",
                                    "1": "2",
                                    "2": "Mario ANDRETTI",
                                    "3": "1"
                                },
                                {
                                    "0": "American",
                                    "1": "2",
                                    "2": "Phil HILL",
                                    "3": "1"
                                },
                                {
                                    "0": "Spanish",
                                    "1": "2",
                                    "2": "Fernando ALONSO",
                                    "3": "2"
                                },
                                {
                                    "0": "Canadian",
                                    "1": "1",
                                    "2": "Jacques VILLENEUVE",
                                    "3": "1"
                                },
                                {
                                    "0": "New Zealander",
                                    "1": "1",
                                    "2": "Denny HULME",
                                    "3": "1"
                                },
                                {
                                    "0": "South African",
                                    "1": "1",
                                    "2": "Jody SCHECKTER",
                                    "3": "1"
                                }
                            ]
                        },
                        "text/html": [
                            "<table>",
                            "<tr><th>Nationality</th><th>Championships by Country</th><th>Most Successful Driver</th><th>Championships</th></tr>",
                            "<tr><td>British</td><td>20</td><td>Lewis HAMILTON</td><td>7</td></tr>",
                            "<tr><td>German</td><td>12</td><td>Michael SCHUMACHER</td><td>7</td></tr>",
                            "<tr><td>Brazilian</td><td>8</td><td>Ayrton SENNA</td><td>3</td></tr>",
                            "<tr><td>Brazilian</td><td>8</td><td>Nelson PIQUET</td><td>3</td></tr>",
                            "<tr><td>Argentine</td><td>5</td><td>Juan FANGIO</td><td>5</td></tr>",
                            "<tr><td>Australian</td><td>4</td><td>Jack BRABHAM</td><td>3</td></tr>",
                            "<tr><td>Austrian</td><td>4</td><td>Niki LAUDA</td><td>3</td></tr>",
                            "<tr><td>Finnish</td><td>4</td><td>Mika HAKKINEN</td><td>2</td></tr>",
                            "<tr><td>French</td><td>4</td><td>Alain PROST</td><td>4</td></tr>",
                            "<tr><td>Italian</td><td>3</td><td>Alberto ASCARI</td><td>2</td></tr>",
                            "<tr><td>American</td><td>2</td><td>Mario ANDRETTI</td><td>1</td></tr>",
                            "<tr><td>American</td><td>2</td><td>Phil HILL</td><td>1</td></tr>",
                            "<tr><td>Spanish</td><td>2</td><td>Fernando ALONSO</td><td>2</td></tr>",
                            "<tr><td>Canadian</td><td>1</td><td>Jacques VILLENEUVE</td><td>1</td></tr>",
                            "<tr><td>New Zealander</td><td>1</td><td>Denny HULME</td><td>1</td></tr>",
                            "<tr><td>South African</td><td>1</td><td>Jody SCHECKTER</td><td>1</td></tr>",
                            "</table>"
                        ]
                    }
                }
            ],
            "execution_count": 5
        }
    ]
}