queries.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#libraries that we need to use \n",
    "from sqlalchemy import create_engine\n",
    "import pgspecial\n",
    "#load sql\n",
    "%load_ext sql\n",
    "#we get access to the dabatabase \n",
    "%sql postgresql://postgres:arnold@localhost/partsunlimited"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Q1: What is the distribution of EV charging stations and EV cars across different geographic regions, and how does this relate to the popularity of EVs in those regions?"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "My EV population data is from Washington state website. If we had more data this query would of give us which regeions are leading electronic vehichles and in charging stations. This information can be very usefull for future investment. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/partsunlimited\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>region</th>\n",
       "            <th>vehicle_pop_sum</th>\n",
       "            <th>total_ev_charging_stations</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Midwest</td>\n",
       "            <td>23</td>\n",
       "            <td>5978</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Northeast</td>\n",
       "            <td>24</td>\n",
       "            <td>7908</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>125</td>\n",
       "            <td>11894</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>121769</td>\n",
       "            <td>20966</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>None</td>\n",
       "            <td>5</td>\n",
       "            <td>283</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Midwest', Decimal('23'), Decimal('5978')),\n",
       " ('Northeast', Decimal('24'), Decimal('7908')),\n",
       " ('South', Decimal('125'), Decimal('11894')),\n",
       " ('West', Decimal('121769'), Decimal('20966')),\n",
       " (None, Decimal('5'), Decimal('283'))]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT l.region, SUM(ef.vehicle_pop_quantity) AS vehicle_pop_sum, SUM(ef.ev_cs_quantity) AS total_ev_charging_stations\n",
    "FROM ev_charginging_facts ef\n",
    "JOIN ev_car_populaion ep ON ef.ev_car_pop_id = ep.ev_car_pop_id\n",
    "JOIN ev_charging_stations ecs ON ef.ev_cs_dim_id = ecs.ev_cs_dim_id\n",
    "JOIN location_dim l ON ef.location_id = l.location_id\n",
    "group by l.region;"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Q2: How has the popularity of different EV models and electric vehicle types varied by geographic region over time, and how can Parts Unlimited use this information to target their marketing and sales efforts more effectively?"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In here we can focus on years in between 2020 and 2022, we can use rollup to show totals of each year and display the totals at the end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/partsunlimited\n",
      "221 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>region</th>\n",
       "            <th>year</th>\n",
       "            <th>vehicle_model</th>\n",
       "            <th>vehicle_pop_sum</th>\n",
       "            <th>ev_cs_sum</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "            <td>57167</td>\n",
       "            <td>26378</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>BENTAYGA</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>2022</td>\n",
       "            <td>RAV4 PRIME</td>\n",
       "            <td>5</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>2022</td>\n",
       "            <td>I4</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>X5</td>\n",
       "            <td>576</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>SOUL EV</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>PRIUS PRIME</td>\n",
       "            <td>157</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>AVIATOR</td>\n",
       "            <td>37</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>CAYENNE</td>\n",
       "            <td>31</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>530E</td>\n",
       "            <td>3</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>MODEL Y</td>\n",
       "            <td>6616</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>X5</td>\n",
       "            <td>299</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Midwest</td>\n",
       "            <td>2021</td>\n",
       "            <td>MODEL 3</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>NIRO</td>\n",
       "            <td>75</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>ESCAPE</td>\n",
       "            <td>55</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>MUSTANG MACH-E</td>\n",
       "            <td>1083</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>2022</td>\n",
       "            <td>WRANGLER</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>GLC-CLASS</td>\n",
       "            <td>32</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>2021</td>\n",
       "            <td>None</td>\n",
       "            <td>0</td>\n",
       "            <td>3237</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>745E</td>\n",
       "            <td>7</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>530E</td>\n",
       "            <td>20</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>PACIFICA</td>\n",
       "            <td>507</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>330E</td>\n",
       "            <td>56</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>IONIQ</td>\n",
       "            <td>52</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>2020</td>\n",
       "            <td>MODEL Y</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>E-TRON</td>\n",
       "            <td>221</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>I-PACE</td>\n",
       "            <td>73</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>RANGE ROVER SPORT</td>\n",
       "            <td>20</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Northeast</td>\n",
       "            <td>2021</td>\n",
       "            <td>None</td>\n",
       "            <td>0</td>\n",
       "            <td>2484</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Northeast</td>\n",
       "            <td>2021</td>\n",
       "            <td>MODEL 3</td>\n",
       "            <td>2</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>TAYCAN</td>\n",
       "            <td>141</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Northeast</td>\n",
       "            <td>2021</td>\n",
       "            <td>WRANGLER</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>AVIATOR</td>\n",
       "            <td>46</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>2020</td>\n",
       "            <td>None</td>\n",
       "            <td>0</td>\n",
       "            <td>2199</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>MODEL X</td>\n",
       "            <td>891</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>PRIUS PRIME</td>\n",
       "            <td>215</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>XC90</td>\n",
       "            <td>220</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>None</td>\n",
       "            <td>2020</td>\n",
       "            <td>None</td>\n",
       "            <td>0</td>\n",
       "            <td>41</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>2022</td>\n",
       "            <td>330E</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>A8 E</td>\n",
       "            <td>3</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>Q5 E</td>\n",
       "            <td>282</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>MODEL S</td>\n",
       "            <td>715</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>CLARITY</td>\n",
       "            <td>16</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>E-TRON SPORTBACK</td>\n",
       "            <td>25</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>COUNTRYMAN</td>\n",
       "            <td>19</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Northeast</td>\n",
       "            <td>2020</td>\n",
       "            <td>MODEL 3</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>ID.4</td>\n",
       "            <td>660</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>XC90</td>\n",
       "            <td>59</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>I8</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>2022</td>\n",
       "            <td>BOLT EV</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>S90</td>\n",
       "            <td>3</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>C40</td>\n",
       "            <td>126</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>TUCSON</td>\n",
       "            <td>39</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>LEAF</td>\n",
       "            <td>533</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>2021</td>\n",
       "            <td>MUSTANG MACH-E</td>\n",
       "            <td>2</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>CORSAIR</td>\n",
       "            <td>61</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>E-TRON SPORTBACK</td>\n",
       "            <td>74</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>MODEL X</td>\n",
       "            <td>638</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>WRANGLER</td>\n",
       "            <td>489</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>F-150</td>\n",
       "            <td>279</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Midwest</td>\n",
       "            <td>2021</td>\n",
       "            <td>E-TRON</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>I4</td>\n",
       "            <td>117</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>PRIUS PRIME</td>\n",
       "            <td>318</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>CORSAIR</td>\n",
       "            <td>3</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>S60</td>\n",
       "            <td>36</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>None</td>\n",
       "            <td>2020</td>\n",
       "            <td>MODEL Y</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Midwest</td>\n",
       "            <td>2022</td>\n",
       "            <td>F-150</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>None</td>\n",
       "            <td>0</td>\n",
       "            <td>8862</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>2022</td>\n",
       "            <td>MODEL 3</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>2021</td>\n",
       "            <td>MODEL Y</td>\n",
       "            <td>11</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>SORENTO</td>\n",
       "            <td>348</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>PACIFICA</td>\n",
       "            <td>329</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>None</td>\n",
       "            <td>2021</td>\n",
       "            <td>None</td>\n",
       "            <td>0</td>\n",
       "            <td>81</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>2021</td>\n",
       "            <td>MODEL 3</td>\n",
       "            <td>7</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>V60</td>\n",
       "            <td>4</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Northeast</td>\n",
       "            <td>2020</td>\n",
       "            <td>PRIUS PRIME</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Northeast</td>\n",
       "            <td>2022</td>\n",
       "            <td>MODEL 3</td>\n",
       "            <td>2</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>EQB-CLASS</td>\n",
       "            <td>44</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>I3</td>\n",
       "            <td>54</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>PANAMERA</td>\n",
       "            <td>6</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Northeast</td>\n",
       "            <td>2022</td>\n",
       "            <td>MODEL Y</td>\n",
       "            <td>2</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>HARDTOP</td>\n",
       "            <td>132</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>2022</td>\n",
       "            <td>XC90</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Midwest</td>\n",
       "            <td>2021</td>\n",
       "            <td>NIRO</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>SANTA FE</td>\n",
       "            <td>68</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>AVIATOR</td>\n",
       "            <td>29</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>2022</td>\n",
       "            <td>S60</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>RAV4 PRIME</td>\n",
       "            <td>748</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>FLYING SPUR</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>XC40</td>\n",
       "            <td>237</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>PANAMERA</td>\n",
       "            <td>5</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>None</td>\n",
       "            <td>2022</td>\n",
       "            <td>MODEL Y</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>PACIFICA</td>\n",
       "            <td>281</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>XC40</td>\n",
       "            <td>247</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>R1T</td>\n",
       "            <td>864</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>KONA</td>\n",
       "            <td>154</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>2021</td>\n",
       "            <td>PACIFICA</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>2022</td>\n",
       "            <td>MODEL X</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>E-TRON GT</td>\n",
       "            <td>79</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>Q5</td>\n",
       "            <td>143</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>RAV4 PRIME</td>\n",
       "            <td>348</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>A7</td>\n",
       "            <td>10</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>OUTLANDER</td>\n",
       "            <td>95</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>2021</td>\n",
       "            <td>RAV4 PRIME</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>OPTIMA</td>\n",
       "            <td>12</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>2020</td>\n",
       "            <td>MODEL S</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>KONA ELECTRIC</td>\n",
       "            <td>158</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Midwest</td>\n",
       "            <td>2021</td>\n",
       "            <td>MODEL Y</td>\n",
       "            <td>3</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>MODEL Y</td>\n",
       "            <td>2363</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>BOLT EV</td>\n",
       "            <td>930</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>2022</td>\n",
       "            <td>X5</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>NIRO</td>\n",
       "            <td>509</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>OUTLANDER</td>\n",
       "            <td>9</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>MODEL 3</td>\n",
       "            <td>3583</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>None</td>\n",
       "            <td>2020</td>\n",
       "            <td>MODEL 3</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>HARDTOP</td>\n",
       "            <td>190</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Midwest</td>\n",
       "            <td>2022</td>\n",
       "            <td>LEAF</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>530E</td>\n",
       "            <td>25</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>BOLT EV</td>\n",
       "            <td>955</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>X3</td>\n",
       "            <td>78</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>FUSION</td>\n",
       "            <td>64</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>OUTLANDER</td>\n",
       "            <td>87</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>RS E-TRON GT</td>\n",
       "            <td>17</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>IONIQ</td>\n",
       "            <td>10</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>2022</td>\n",
       "            <td>KONA ELECTRIC</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>MUSTANG MACH-E</td>\n",
       "            <td>828</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>745LE</td>\n",
       "            <td>2</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>RANGE ROVER</td>\n",
       "            <td>3</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>V60</td>\n",
       "            <td>5</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>MODEL S</td>\n",
       "            <td>465</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>ESCAPE</td>\n",
       "            <td>221</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>2020</td>\n",
       "            <td>GLC-CLASS</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>XC60</td>\n",
       "            <td>235</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>GRAND CHEROKEE</td>\n",
       "            <td>181</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Midwest</td>\n",
       "            <td>2021</td>\n",
       "            <td>None</td>\n",
       "            <td>0</td>\n",
       "            <td>1829</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>EDV</td>\n",
       "            <td>220</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>WRANGLER</td>\n",
       "            <td>626</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>2022</td>\n",
       "            <td>MODEL Y</td>\n",
       "            <td>9</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>XC60</td>\n",
       "            <td>83</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Northeast</td>\n",
       "            <td>2021</td>\n",
       "            <td>RAV4 PRIME</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>IX</td>\n",
       "            <td>138</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>ID.4</td>\n",
       "            <td>982</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>IONIQ 5</td>\n",
       "            <td>397</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>2020</td>\n",
       "            <td>PACIFICA</td>\n",
       "            <td>2</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>RANGE ROVER SPORT</td>\n",
       "            <td>5</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>EV6</td>\n",
       "            <td>1046</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>X3</td>\n",
       "            <td>212</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>R1S</td>\n",
       "            <td>134</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>MODEL 3</td>\n",
       "            <td>3832</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>2020</td>\n",
       "            <td>BOLT EV</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>BENTAYGA</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>MODEL 3</td>\n",
       "            <td>5058</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>PS2</td>\n",
       "            <td>374</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>TRANSIT</td>\n",
       "            <td>122</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Midwest</td>\n",
       "            <td>2020</td>\n",
       "            <td>None</td>\n",
       "            <td>0</td>\n",
       "            <td>811</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>None</td>\n",
       "            <td>0</td>\n",
       "            <td>5067</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>PANAMERA</td>\n",
       "            <td>8</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>2022</td>\n",
       "            <td>NIRO</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>CAYENNE</td>\n",
       "            <td>26</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Northeast</td>\n",
       "            <td>2021</td>\n",
       "            <td>MODEL Y</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>2022</td>\n",
       "            <td>F-150</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>S60</td>\n",
       "            <td>8</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>TAYCAN</td>\n",
       "            <td>120</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>CAYENNE</td>\n",
       "            <td>24</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>EQS-CLASS SEDAN</td>\n",
       "            <td>97</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>330E</td>\n",
       "            <td>60</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>I3</td>\n",
       "            <td>42</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>I-PACE</td>\n",
       "            <td>6</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>E-TRON</td>\n",
       "            <td>177</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>2020</td>\n",
       "            <td>NIRO</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>MODEL S</td>\n",
       "            <td>384</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>BOLT EV</td>\n",
       "            <td>381</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>LUCID AIR</td>\n",
       "            <td>95</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>Q4</td>\n",
       "            <td>78</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>COUNTRYMAN</td>\n",
       "            <td>12</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>2021</td>\n",
       "            <td>330E</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Northeast</td>\n",
       "            <td>2020</td>\n",
       "            <td>None</td>\n",
       "            <td>0</td>\n",
       "            <td>1767</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>2021</td>\n",
       "            <td>ID.4</td>\n",
       "            <td>2</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>NX</td>\n",
       "            <td>40</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>2020</td>\n",
       "            <td>MODEL 3</td>\n",
       "            <td>4</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>I-PACE</td>\n",
       "            <td>19</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>MODEL X</td>\n",
       "            <td>164</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Midwest</td>\n",
       "            <td>2020</td>\n",
       "            <td>MODEL Y</td>\n",
       "            <td>2</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>XC90</td>\n",
       "            <td>150</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>LEAF</td>\n",
       "            <td>945</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>LEAF</td>\n",
       "            <td>589</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>E-TRON SPORTBACK</td>\n",
       "            <td>76</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>RANGE ROVER</td>\n",
       "            <td>10</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>IONIQ</td>\n",
       "            <td>26</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>S60</td>\n",
       "            <td>27</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>CLARITY</td>\n",
       "            <td>79</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>COUNTRYMAN</td>\n",
       "            <td>3</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Midwest</td>\n",
       "            <td>2020</td>\n",
       "            <td>MODEL 3</td>\n",
       "            <td>3</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>MODEL Y</td>\n",
       "            <td>7645</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>XC60</td>\n",
       "            <td>145</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>TAYCAN</td>\n",
       "            <td>177</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>Q5 E</td>\n",
       "            <td>196</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>PS2</td>\n",
       "            <td>98</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>NIRO</td>\n",
       "            <td>732</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>V60</td>\n",
       "            <td>3</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>KONA ELECTRIC</td>\n",
       "            <td>119</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Northeast</td>\n",
       "            <td>2021</td>\n",
       "            <td>None</td>\n",
       "            <td>5</td>\n",
       "            <td>2484</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>None</td>\n",
       "            <td>2022</td>\n",
       "            <td>None</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>None</td>\n",
       "            <td>27701</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Midwest</td>\n",
       "            <td>2022</td>\n",
       "            <td>None</td>\n",
       "            <td>2</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>None</td>\n",
       "            <td>10922</td>\n",
       "            <td>5067</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>None</td>\n",
       "            <td>2020</td>\n",
       "            <td>None</td>\n",
       "            <td>2</td>\n",
       "            <td>41</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Midwest</td>\n",
       "            <td>2021</td>\n",
       "            <td>None</td>\n",
       "            <td>6</td>\n",
       "            <td>1829</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>2021</td>\n",
       "            <td>None</td>\n",
       "            <td>25</td>\n",
       "            <td>3237</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Midwest</td>\n",
       "            <td>2020</td>\n",
       "            <td>None</td>\n",
       "            <td>5</td>\n",
       "            <td>811</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>None</td>\n",
       "            <td>18455</td>\n",
       "            <td>8862</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Northeast</td>\n",
       "            <td>2022</td>\n",
       "            <td>None</td>\n",
       "            <td>4</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>2022</td>\n",
       "            <td>None</td>\n",
       "            <td>26</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>2020</td>\n",
       "            <td>None</td>\n",
       "            <td>11</td>\n",
       "            <td>2199</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Northeast</td>\n",
       "            <td>2020</td>\n",
       "            <td>None</td>\n",
       "            <td>2</td>\n",
       "            <td>1767</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>None</td>\n",
       "            <td>2021</td>\n",
       "            <td>None</td>\n",
       "            <td>0</td>\n",
       "            <td>81</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "            <td>3</td>\n",
       "            <td>122</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "            <td>62</td>\n",
       "            <td>5436</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "            <td>57078</td>\n",
       "            <td>13929</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Midwest</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "            <td>13</td>\n",
       "            <td>2640</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Northeast</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "            <td>11</td>\n",
       "            <td>4251</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(None, None, None, Decimal('57167'), Decimal('26378')),\n",
       " ('West', 2021, 'BENTAYGA', Decimal('1'), Decimal('0')),\n",
       " ('South', 2022, 'RAV4 PRIME', Decimal('5'), Decimal('0')),\n",
       " ('South', 2022, 'I4', Decimal('1'), Decimal('0')),\n",
       " ('West', 2022, 'X5', Decimal('576'), Decimal('0')),\n",
       " ('West', 2020, 'SOUL EV', Decimal('1'), Decimal('0')),\n",
       " ('West', 2022, 'PRIUS PRIME', Decimal('157'), Decimal('0')),\n",
       " ('West', 2021, 'AVIATOR', Decimal('37'), Decimal('0')),\n",
       " ('West', 2022, 'CAYENNE', Decimal('31'), Decimal('0')),\n",
       " ('West', 2020, '530E', Decimal('3'), Decimal('0')),\n",
       " ('West', 2021, 'MODEL Y', Decimal('6616'), Decimal('0')),\n",
       " ('West', 2021, 'X5', Decimal('299'), Decimal('0')),\n",
       " ('Midwest', 2021, 'MODEL 3', Decimal('1'), Decimal('0')),\n",
       " ('West', 2021, 'NIRO', Decimal('75'), Decimal('0')),\n",
       " ('West', 2021, 'ESCAPE', Decimal('55'), Decimal('0')),\n",
       " ('West', 2022, 'MUSTANG MACH-E', Decimal('1083'), Decimal('0')),\n",
       " ('South', 2022, 'WRANGLER', Decimal('1'), Decimal('0')),\n",
       " ('West', 2020, 'GLC-CLASS', Decimal('32'), Decimal('0')),\n",
       " ('South', 2021, None, Decimal('0'), Decimal('3237')),\n",
       " ('West', 2020, '745E', Decimal('7'), Decimal('0')),\n",
       " ('West', 2021, '530E', Decimal('20'), Decimal('0')),\n",
       " ('West', 2021, 'PACIFICA', Decimal('507'), Decimal('0')),\n",
       " ('West', 2022, '330E', Decimal('56'), Decimal('0')),\n",
       " ('West', 2020, 'IONIQ', Decimal('52'), Decimal('0')),\n",
       " ('South', 2020, 'MODEL Y', Decimal('1'), Decimal('0')),\n",
       " ('West', 2022, 'E-TRON', Decimal('221'), Decimal('0')),\n",
       " ('West', 2020, 'I-PACE', Decimal('73'), Decimal('0')),\n",
       " ('West', 2020, 'RANGE ROVER SPORT', Decimal('20'), Decimal('0')),\n",
       " ('Northeast', 2021, None, Decimal('0'), Decimal('2484')),\n",
       " ('Northeast', 2021, 'MODEL 3', Decimal('2'), Decimal('0')),\n",
       " ('West', 2021, 'TAYCAN', Decimal('141'), Decimal('0')),\n",
       " ('Northeast', 2021, 'WRANGLER', Decimal('1'), Decimal('0')),\n",
       " ('West', 2022, 'AVIATOR', Decimal('46'), Decimal('0')),\n",
       " ('South', 2020, None, Decimal('0'), Decimal('2199')),\n",
       " ('West', 2022, 'MODEL X', Decimal('891'), Decimal('0')),\n",
       " ('West', 2021, 'PRIUS PRIME', Decimal('215'), Decimal('0')),\n",
       " ('West', 2022, 'XC90', Decimal('220'), Decimal('0')),\n",
       " (None, 2020, None, Decimal('0'), Decimal('41')),\n",
       " ('South', 2022, '330E', Decimal('1'), Decimal('0')),\n",
       " ('West', 2020, 'A8 E', Decimal('3'), Decimal('0')),\n",
       " ('West', 2021, 'Q5 E', Decimal('282'), Decimal('0')),\n",
       " ('West', 2022, 'MODEL S', Decimal('715'), Decimal('0')),\n",
       " ('West', 2021, 'CLARITY', Decimal('16'), Decimal('0')),\n",
       " ('West', 2020, 'E-TRON SPORTBACK', Decimal('25'), Decimal('0')),\n",
       " ('West', 2022, 'COUNTRYMAN', Decimal('19'), Decimal('0')),\n",
       " ('Northeast', 2020, 'MODEL 3', Decimal('1'), Decimal('0')),\n",
       " ('West', 2022, 'ID.4', Decimal('660'), Decimal('0')),\n",
       " ('West', 2020, 'XC90', Decimal('59'), Decimal('0')),\n",
       " ('West', 2020, 'I8', Decimal('1'), Decimal('0')),\n",
       " ('South', 2022, 'BOLT EV', Decimal('1'), Decimal('0')),\n",
       " ('West', 2020, 'S90', Decimal('3'), Decimal('0')),\n",
       " ('West', 2022, 'C40', Decimal('126'), Decimal('0')),\n",
       " ('West', 2022, 'TUCSON', Decimal('39'), Decimal('0')),\n",
       " ('West', 2021, 'LEAF', Decimal('533'), Decimal('0')),\n",
       " ('South', 2021, 'MUSTANG MACH-E', Decimal('2'), Decimal('0')),\n",
       " ('West', 2022, 'CORSAIR', Decimal('61'), Decimal('0')),\n",
       " ('West', 2022, 'E-TRON SPORTBACK', Decimal('74'), Decimal('0')),\n",
       " ('West', 2020, 'MODEL X', Decimal('638'), Decimal('0')),\n",
       " ('West', 2022, 'WRANGLER', Decimal('489'), Decimal('0')),\n",
       " ('West', 2022, 'F-150', Decimal('279'), Decimal('0')),\n",
       " ('Midwest', 2021, 'E-TRON', Decimal('1'), Decimal('0')),\n",
       " ('West', 2022, 'I4', Decimal('117'), Decimal('0')),\n",
       " ('West', 2020, 'PRIUS PRIME', Decimal('318'), Decimal('0')),\n",
       " ('West', 2021, 'CORSAIR', Decimal('3'), Decimal('0')),\n",
       " ('West', 2022, 'S60', Decimal('36'), Decimal('0')),\n",
       " (None, 2020, 'MODEL Y', Decimal('1'), Decimal('0')),\n",
       " ('Midwest', 2022, 'F-150', Decimal('1'), Decimal('0')),\n",
       " ('West', 2021, None, Decimal('0'), Decimal('8862')),\n",
       " ('South', 2022, 'MODEL 3', Decimal('1'), Decimal('0')),\n",
       " ('South', 2021, 'MODEL Y', Decimal('11'), Decimal('0')),\n",
       " ('West', 2022, 'SORENTO', Decimal('348'), Decimal('0')),\n",
       " ('West', 2022, 'PACIFICA', Decimal('329'), Decimal('0')),\n",
       " (None, 2021, None, Decimal('0'), Decimal('81')),\n",
       " ('South', 2021, 'MODEL 3', Decimal('7'), Decimal('0')),\n",
       " ('West', 2022, 'V60', Decimal('4'), Decimal('0')),\n",
       " ('Northeast', 2020, 'PRIUS PRIME', Decimal('1'), Decimal('0')),\n",
       " ('Northeast', 2022, 'MODEL 3', Decimal('2'), Decimal('0')),\n",
       " ('West', 2022, 'EQB-CLASS', Decimal('44'), Decimal('0')),\n",
       " ('West', 2020, 'I3', Decimal('54'), Decimal('0')),\n",
       " ('West', 2022, 'PANAMERA', Decimal('6'), Decimal('0')),\n",
       " ('Northeast', 2022, 'MODEL Y', Decimal('2'), Decimal('0')),\n",
       " ('West', 2021, 'HARDTOP', Decimal('132'), Decimal('0')),\n",
       " ('South', 2022, 'XC90', Decimal('1'), Decimal('0')),\n",
       " ('Midwest', 2021, 'NIRO', Decimal('1'), Decimal('0')),\n",
       " ('West', 2022, 'SANTA FE', Decimal('68'), Decimal('0')),\n",
       " ('West', 2020, 'AVIATOR', Decimal('29'), Decimal('0')),\n",
       " ('South', 2022, 'S60', Decimal('1'), Decimal('0')),\n",
       " ('West', 2021, 'RAV4 PRIME', Decimal('748'), Decimal('0')),\n",
       " ('West', 2022, 'FLYING SPUR', Decimal('1'), Decimal('0')),\n",
       " ('West', 2022, 'XC40', Decimal('237'), Decimal('0')),\n",
       " ('West', 2021, 'PANAMERA', Decimal('5'), Decimal('0')),\n",
       " (None, 2022, 'MODEL Y', Decimal('1'), Decimal('0')),\n",
       " ('West', 2020, 'PACIFICA', Decimal('281'), Decimal('0')),\n",
       " ('West', 2021, 'XC40', Decimal('247'), Decimal('0')),\n",
       " ('West', 2022, 'R1T', Decimal('864'), Decimal('0')),\n",
       " ('West', 2020, 'KONA', Decimal('154'), Decimal('0')),\n",
       " ('South', 2021, 'PACIFICA', Decimal('1'), Decimal('0')),\n",
       " ('South', 2022, 'MODEL X', Decimal('1'), Decimal('0')),\n",
       " ('West', 2022, 'E-TRON GT', Decimal('79'), Decimal('0')),\n",
       " ('West', 2022, 'Q5', Decimal('143'), Decimal('0')),\n",
       " ('West', 2022, 'RAV4 PRIME', Decimal('348'), Decimal('0')),\n",
       " ('West', 2021, 'A7', Decimal('10'), Decimal('0')),\n",
       " ('West', 2020, 'OUTLANDER', Decimal('95'), Decimal('0')),\n",
       " ('South', 2021, 'RAV4 PRIME', Decimal('1'), Decimal('0')),\n",
       " ('West', 2020, 'OPTIMA', Decimal('12'), Decimal('0')),\n",
       " ('South', 2020, 'MODEL S', Decimal('1'), Decimal('0')),\n",
       " ('West', 2021, 'KONA ELECTRIC', Decimal('158'), Decimal('0')),\n",
       " ('Midwest', 2021, 'MODEL Y', Decimal('3'), Decimal('0')),\n",
       " ('West', 2020, 'MODEL Y', Decimal('2363'), Decimal('0')),\n",
       " ('West', 2022, 'BOLT EV', Decimal('930'), Decimal('0')),\n",
       " ('South', 2022, 'X5', Decimal('1'), Decimal('0')),\n",
       " ('West', 2020, 'NIRO', Decimal('509'), Decimal('0')),\n",
       " ('West', 2021, 'OUTLANDER', Decimal('9'), Decimal('0')),\n",
       " ('West', 2020, 'MODEL 3', Decimal('3583'), Decimal('0')),\n",
       " (None, 2020, 'MODEL 3', Decimal('1'), Decimal('0')),\n",
       " ('West', 2022, 'HARDTOP', Decimal('190'), Decimal('0')),\n",
       " ('Midwest', 2022, 'LEAF', Decimal('1'), Decimal('0')),\n",
       " ('West', 2022, '530E', Decimal('25'), Decimal('0')),\n",
       " ('West', 2020, 'BOLT EV', Decimal('955'), Decimal('0')),\n",
       " ('West', 2020, 'X3', Decimal('78'), Decimal('0')),\n",
       " ('West', 2020, 'FUSION', Decimal('64'), Decimal('0')),\n",
       " ('West', 2022, 'OUTLANDER', Decimal('87'), Decimal('0')),\n",
       " ('West', 2022, 'RS E-TRON GT', Decimal('17'), Decimal('0')),\n",
       " ('West', 2022, 'IONIQ', Decimal('10'), Decimal('0')),\n",
       " ('South', 2022, 'KONA ELECTRIC', Decimal('1'), Decimal('0')),\n",
       " ('West', 2021, 'MUSTANG MACH-E', Decimal('828'), Decimal('0')),\n",
       " ('West', 2022, '745LE', Decimal('2'), Decimal('0')),\n",
       " ('West', 2021, 'RANGE ROVER', Decimal('3'), Decimal('0')),\n",
       " ('West', 2021, 'V60', Decimal('5'), Decimal('0')),\n",
       " ('West', 2021, 'MODEL S', Decimal('465'), Decimal('0')),\n",
       " ('West', 2022, 'ESCAPE', Decimal('221'), Decimal('0')),\n",
       " ('South', 2020, 'GLC-CLASS', Decimal('1'), Decimal('0')),\n",
       " ('West', 2022, 'XC60', Decimal('235'), Decimal('0')),\n",
       " ('West', 2022, 'GRAND CHEROKEE', Decimal('181'), Decimal('0')),\n",
       " ('Midwest', 2021, None, Decimal('0'), Decimal('1829')),\n",
       " ('West', 2022, 'EDV', Decimal('220'), Decimal('0')),\n",
       " ('West', 2021, 'WRANGLER', Decimal('626'), Decimal('0')),\n",
       " ('South', 2022, 'MODEL Y', Decimal('9'), Decimal('0')),\n",
       " ('West', 2020, 'XC60', Decimal('83'), Decimal('0')),\n",
       " ('Northeast', 2021, 'RAV4 PRIME', Decimal('1'), Decimal('0')),\n",
       " ('West', 2022, 'IX', Decimal('138'), Decimal('0')),\n",
       " ('West', 2021, 'ID.4', Decimal('982'), Decimal('0')),\n",
       " ('West', 2022, 'IONIQ 5', Decimal('397'), Decimal('0')),\n",
       " ('South', 2020, 'PACIFICA', Decimal('2'), Decimal('0')),\n",
       " ('West', 2021, 'RANGE ROVER SPORT', Decimal('5'), Decimal('0')),\n",
       " ('West', 2022, 'EV6', Decimal('1046'), Decimal('0')),\n",
       " ('West', 2021, 'X3', Decimal('212'), Decimal('0')),\n",
       " ('West', 2022, 'R1S', Decimal('134'), Decimal('0')),\n",
       " ('West', 2021, 'MODEL 3', Decimal('3832'), Decimal('0')),\n",
       " ('South', 2020, 'BOLT EV', Decimal('1'), Decimal('0')),\n",
       " ('West', 2020, 'BENTAYGA', Decimal('1'), Decimal('0')),\n",
       " ('West', 2022, 'MODEL 3', Decimal('5058'), Decimal('0')),\n",
       " ('West', 2022, 'PS2', Decimal('374'), Decimal('0')),\n",
       " ('West', 2022, 'TRANSIT', Decimal('122'), Decimal('0')),\n",
       " ('Midwest', 2020, None, Decimal('0'), Decimal('811')),\n",
       " ('West', 2020, None, Decimal('0'), Decimal('5067')),\n",
       " ('West', 2020, 'PANAMERA', Decimal('8'), Decimal('0')),\n",
       " ('South', 2022, 'NIRO', Decimal('1'), Decimal('0')),\n",
       " ('West', 2020, 'CAYENNE', Decimal('26'), Decimal('0')),\n",
       " ('Northeast', 2021, 'MODEL Y', Decimal('1'), Decimal('0')),\n",
       " ('South', 2022, 'F-150', Decimal('1'), Decimal('0')),\n",
       " ('West', 2020, 'S60', Decimal('8'), Decimal('0')),\n",
       " ('West', 2020, 'TAYCAN', Decimal('120'), Decimal('0')),\n",
       " ('West', 2021, 'CAYENNE', Decimal('24'), Decimal('0')),\n",
       " ('West', 2022, 'EQS-CLASS SEDAN', Decimal('97'), Decimal('0')),\n",
       " ('West', 2021, '330E', Decimal('60'), Decimal('0')),\n",
       " ('West', 2021, 'I3', Decimal('42'), Decimal('0')),\n",
       " ('West', 2021, 'I-PACE', Decimal('6'), Decimal('0')),\n",
       " ('West', 2021, 'E-TRON', Decimal('177'), Decimal('0')),\n",
       " ('South', 2020, 'NIRO', Decimal('1'), Decimal('0')),\n",
       " ('West', 2020, 'MODEL S', Decimal('384'), Decimal('0')),\n",
       " ('West', 2021, 'BOLT EV', Decimal('381'), Decimal('0')),\n",
       " ('West', 2022, 'LUCID AIR', Decimal('95'), Decimal('0')),\n",
       " ('West', 2022, 'Q4', Decimal('78'), Decimal('0')),\n",
       " ('West', 2021, 'COUNTRYMAN', Decimal('12'), Decimal('0')),\n",
       " ('South', 2021, '330E', Decimal('1'), Decimal('0')),\n",
       " ('Northeast', 2020, None, Decimal('0'), Decimal('1767')),\n",
       " ('South', 2021, 'ID.4', Decimal('2'), Decimal('0')),\n",
       " ('West', 2022, 'NX', Decimal('40'), Decimal('0')),\n",
       " ('South', 2020, 'MODEL 3', Decimal('4'), Decimal('0')),\n",
       " ('West', 2022, 'I-PACE', Decimal('19'), Decimal('0')),\n",
       " ('West', 2021, 'MODEL X', Decimal('164'), Decimal('0')),\n",
       " ('Midwest', 2020, 'MODEL Y', Decimal('2'), Decimal('0')),\n",
       " ('West', 2021, 'XC90', Decimal('150'), Decimal('0')),\n",
       " ('West', 2022, 'LEAF', Decimal('945'), Decimal('0')),\n",
       " ('West', 2020, 'LEAF', Decimal('589'), Decimal('0')),\n",
       " ('West', 2021, 'E-TRON SPORTBACK', Decimal('76'), Decimal('0')),\n",
       " ('West', 2020, 'RANGE ROVER', Decimal('10'), Decimal('0')),\n",
       " ('West', 2021, 'IONIQ', Decimal('26'), Decimal('0')),\n",
       " ('West', 2021, 'S60', Decimal('27'), Decimal('0')),\n",
       " ('West', 2020, 'CLARITY', Decimal('79'), Decimal('0')),\n",
       " ('West', 2020, 'COUNTRYMAN', Decimal('3'), Decimal('0')),\n",
       " ('Midwest', 2020, 'MODEL 3', Decimal('3'), Decimal('0')),\n",
       " ('West', 2022, 'MODEL Y', Decimal('7645'), Decimal('0')),\n",
       " ('West', 2021, 'XC60', Decimal('145'), Decimal('0')),\n",
       " ('West', 2022, 'TAYCAN', Decimal('177'), Decimal('0')),\n",
       " ('West', 2020, 'Q5 E', Decimal('196'), Decimal('0')),\n",
       " ('West', 2021, 'PS2', Decimal('98'), Decimal('0')),\n",
       " ('West', 2022, 'NIRO', Decimal('732'), Decimal('0')),\n",
       " ('West', 2020, 'V60', Decimal('3'), Decimal('0')),\n",
       " ('West', 2022, 'KONA ELECTRIC', Decimal('119'), Decimal('0')),\n",
       " ('Northeast', 2021, None, Decimal('5'), Decimal('2484')),\n",
       " (None, 2022, None, Decimal('1'), Decimal('0')),\n",
       " ('West', 2022, None, Decimal('27701'), Decimal('0')),\n",
       " ('Midwest', 2022, None, Decimal('2'), Decimal('0')),\n",
       " ('West', 2020, None, Decimal('10922'), Decimal('5067')),\n",
       " (None, 2020, None, Decimal('2'), Decimal('41')),\n",
       " ('Midwest', 2021, None, Decimal('6'), Decimal('1829')),\n",
       " ('South', 2021, None, Decimal('25'), Decimal('3237')),\n",
       " ('Midwest', 2020, None, Decimal('5'), Decimal('811')),\n",
       " ('West', 2021, None, Decimal('18455'), Decimal('8862')),\n",
       " ('Northeast', 2022, None, Decimal('4'), Decimal('0')),\n",
       " ('South', 2022, None, Decimal('26'), Decimal('0')),\n",
       " ('South', 2020, None, Decimal('11'), Decimal('2199')),\n",
       " ('Northeast', 2020, None, Decimal('2'), Decimal('1767')),\n",
       " (None, 2021, None, Decimal('0'), Decimal('81')),\n",
       " (None, None, None, Decimal('3'), Decimal('122')),\n",
       " ('South', None, None, Decimal('62'), Decimal('5436')),\n",
       " ('West', None, None, Decimal('57078'), Decimal('13929')),\n",
       " ('Midwest', None, None, Decimal('13'), Decimal('2640')),\n",
       " ('Northeast', None, None, Decimal('11'), Decimal('4251'))]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT l.region, d.year, ep.vehicle_model,\n",
    "SUM(ef.vehicle_pop_quantity) AS vehicle_pop_sum, \n",
    "SUM(ef.ev_cs_quantity) AS ev_cs_sum\n",
    "FROM ev_charginging_facts ef\n",
    "JOIN ev_car_populaion ep ON ef.ev_car_pop_id = ep.ev_car_pop_id\n",
    "JOIN ev_charging_stations ecs ON ef.ev_cs_dim_id = ecs.ev_cs_dim_id\n",
    "JOIN location_dim l ON ef.location_id = l.location_id\n",
    "JOIN date_dim d ON ef.date_id = d.date_id\n",
    "WHERE d.year BETWEEN 2020 AND 2022\n",
    "GROUP BY ROLLUP(l.region, d.year,ep.vehicle_model);"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Q3: How does the weight of EV parts relate to their price, and how does this relationship differ across different manufacturers ?"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* The GROUP BY clause ensures that the calculations are done for each combination, \n",
    "and the ORDER BY clause sorts the results by manufacturer name and product category for easy viewing.\n",
    "* The STDDEV_POP function calculates the population standard deviation, and dividing it by the average price gives us the coefficient of variation. The GROUP BY clause groups the results by manufacturer and product category, so we get the averages and coefficient of variation for each combination.\n",
    "* The ::numeric casts the average values to the numeric data type before rounding. \n",
    "* The second argument of the ROUND() function specifies the number of decimal places to round to."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/partsunlimited\n",
      "52 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>manufacturer_name</th>\n",
       "            <th>product_category</th>\n",
       "            <th>average_weight</th>\n",
       "            <th>average_price</th>\n",
       "            <th>correlation_coef</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>AEM</td>\n",
       "            <td>Instrumentation</td>\n",
       "            <td>3.00</td>\n",
       "            <td>1194.00</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>AEM</td>\n",
       "            <td>Miscellaneous</td>\n",
       "            <td>4.00</td>\n",
       "            <td>1598.00</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>AM Racing</td>\n",
       "            <td>Motors</td>\n",
       "            <td>165.00</td>\n",
       "            <td>14694.00</td>\n",
       "            <td>1.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Behr</td>\n",
       "            <td>Used_Components</td>\n",
       "            <td>3.00</td>\n",
       "            <td>99.00</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Canadian EV</td>\n",
       "            <td>Miscellaneous</td>\n",
       "            <td>4.00</td>\n",
       "            <td>549.00</td>\n",
       "            <td>1.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Canadian EV</td>\n",
       "            <td>Motor_Adapters</td>\n",
       "            <td>9.00</td>\n",
       "            <td>1300.00</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Chennic</td>\n",
       "            <td>Charging_Accessories</td>\n",
       "            <td>1.00</td>\n",
       "            <td>89.00</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Curtis</td>\n",
       "            <td>Controller_Accessories</td>\n",
       "            <td>2.50</td>\n",
       "            <td>649.00</td>\n",
       "            <td>1.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Curtis</td>\n",
       "            <td>Controllers</td>\n",
       "            <td>16.33</td>\n",
       "            <td>1787.67</td>\n",
       "            <td>0.71</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Deltec</td>\n",
       "            <td>Instrumentation</td>\n",
       "            <td>2.33</td>\n",
       "            <td>50.67</td>\n",
       "            <td>0.95</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>DilithiumBMS</td>\n",
       "            <td>Charging_Accessories</td>\n",
       "            <td>1.00</td>\n",
       "            <td>370.00</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Elcon</td>\n",
       "            <td>Chargers</td>\n",
       "            <td>17.00</td>\n",
       "            <td>884.00</td>\n",
       "            <td>1.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Elcon</td>\n",
       "            <td>DC_Converters</td>\n",
       "            <td>6.00</td>\n",
       "            <td>549.00</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>EmproShunts</td>\n",
       "            <td>Used_Components</td>\n",
       "            <td>0.00</td>\n",
       "            <td>20.00</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>EV West</td>\n",
       "            <td>Batteries</td>\n",
       "            <td>10.00</td>\n",
       "            <td>648.50</td>\n",
       "            <td>-1.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>EV West</td>\n",
       "            <td>BatteryEnclosuresAcc</td>\n",
       "            <td>13.67</td>\n",
       "            <td>672.33</td>\n",
       "            <td>1.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>EV West</td>\n",
       "            <td>Charging_Accessories</td>\n",
       "            <td>2.62</td>\n",
       "            <td>68.81</td>\n",
       "            <td>0.40</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>EV West</td>\n",
       "            <td>Controller_Accessories</td>\n",
       "            <td>3.75</td>\n",
       "            <td>98.44</td>\n",
       "            <td>0.73</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>EV West</td>\n",
       "            <td>DrivetrainComponents</td>\n",
       "            <td>29.60</td>\n",
       "            <td>766.20</td>\n",
       "            <td>0.83</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>EV West</td>\n",
       "            <td>EV_Conversion_Kits</td>\n",
       "            <td>376.67</td>\n",
       "            <td>16426.65</td>\n",
       "            <td>-0.70</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>EV West</td>\n",
       "            <td>Hose_Fittings</td>\n",
       "            <td>0.10</td>\n",
       "            <td>4.85</td>\n",
       "            <td>-0.02</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>EV West</td>\n",
       "            <td>Instrumentation</td>\n",
       "            <td>1.00</td>\n",
       "            <td>149.00</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>EV West</td>\n",
       "            <td>Miscellaneous</td>\n",
       "            <td>1.43</td>\n",
       "            <td>34.73</td>\n",
       "            <td>0.63</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>EV West</td>\n",
       "            <td>Motor_Accessories</td>\n",
       "            <td>13.71</td>\n",
       "            <td>684.43</td>\n",
       "            <td>0.95</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>EV West</td>\n",
       "            <td>Motor_Adapters</td>\n",
       "            <td>125.00</td>\n",
       "            <td>2197.00</td>\n",
       "            <td>1.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>EV West</td>\n",
       "            <td>Services</td>\n",
       "            <td>0.00</td>\n",
       "            <td>393.75</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>EV West</td>\n",
       "            <td>Used_Components</td>\n",
       "            <td>2.00</td>\n",
       "            <td>139.00</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>HPEVS</td>\n",
       "            <td>Controller_Accessories</td>\n",
       "            <td>3.00</td>\n",
       "            <td>528.50</td>\n",
       "            <td>1.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>HPEVS</td>\n",
       "            <td>Motors</td>\n",
       "            <td>101.54</td>\n",
       "            <td>4940.23</td>\n",
       "            <td>0.82</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Manzanita</td>\n",
       "            <td>Chargers</td>\n",
       "            <td>17.73</td>\n",
       "            <td>3120.00</td>\n",
       "            <td>0.88</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Manzanita</td>\n",
       "            <td>Controllers</td>\n",
       "            <td>27.50</td>\n",
       "            <td>3600.00</td>\n",
       "            <td>1.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Modular EV</td>\n",
       "            <td>Charging_Accessories</td>\n",
       "            <td>1.00</td>\n",
       "            <td>45.00</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>MSD Ignition</td>\n",
       "            <td>Miscellaneous</td>\n",
       "            <td>1.00</td>\n",
       "            <td>19.99</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>NetGainMotors</td>\n",
       "            <td>Controllers</td>\n",
       "            <td>11.00</td>\n",
       "            <td>2050.00</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>NetGainMotors</td>\n",
       "            <td>Motors</td>\n",
       "            <td>120.00</td>\n",
       "            <td>5275.00</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>QuickChargePower</td>\n",
       "            <td>Charging_Accessories</td>\n",
       "            <td>2.00</td>\n",
       "            <td>389.00</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>RinconPower</td>\n",
       "            <td>BatteryEnclosuresAcc</td>\n",
       "            <td>3.00</td>\n",
       "            <td>159.00</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>RinehartMotionSystems</td>\n",
       "            <td>Controllers</td>\n",
       "            <td>22.50</td>\n",
       "            <td>7800.00</td>\n",
       "            <td>1.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Samsung</td>\n",
       "            <td>Batteries</td>\n",
       "            <td>63.75</td>\n",
       "            <td>521.75</td>\n",
       "            <td>0.95</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Smart</td>\n",
       "            <td>Used_Components</td>\n",
       "            <td>2.40</td>\n",
       "            <td>68.00</td>\n",
       "            <td>0.04</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>SSBC</td>\n",
       "            <td>Miscellaneous</td>\n",
       "            <td>9.00</td>\n",
       "            <td>288.00</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>TBSElectronics</td>\n",
       "            <td>Instrumentation</td>\n",
       "            <td>1.00</td>\n",
       "            <td>182.00</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Tesla</td>\n",
       "            <td>Batteries</td>\n",
       "            <td>48.50</td>\n",
       "            <td>1189.50</td>\n",
       "            <td>1.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Tesla</td>\n",
       "            <td>Controllers</td>\n",
       "            <td>3.00</td>\n",
       "            <td>2699.00</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Tesla</td>\n",
       "            <td>DrivetrainComponents</td>\n",
       "            <td>1.00</td>\n",
       "            <td>8.00</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Tesla</td>\n",
       "            <td>Motors</td>\n",
       "            <td>305.00</td>\n",
       "            <td>11900.00</td>\n",
       "            <td>1.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Tesla</td>\n",
       "            <td>Used_Components</td>\n",
       "            <td>27.50</td>\n",
       "            <td>804.50</td>\n",
       "            <td>1.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Tyco Kilovac</td>\n",
       "            <td>Used_Components</td>\n",
       "            <td>5.00</td>\n",
       "            <td>60.00</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>UnknownVendor</td>\n",
       "            <td>Batteries</td>\n",
       "            <td>39.00</td>\n",
       "            <td>839.00</td>\n",
       "            <td>1.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>UnknownVendor</td>\n",
       "            <td>Instrumentation</td>\n",
       "            <td>1.00</td>\n",
       "            <td>179.50</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>UnknownVendor</td>\n",
       "            <td>Miscellaneous</td>\n",
       "            <td>4.43</td>\n",
       "            <td>256.00</td>\n",
       "            <td>0.81</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>UnknownVendor</td>\n",
       "            <td>Used_Components</td>\n",
       "            <td>1.00</td>\n",
       "            <td>25.00</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('AEM', 'Instrumentation', Decimal('3.00'), Decimal('1194.00'), None),\n",
       " ('AEM', 'Miscellaneous', Decimal('4.00'), Decimal('1598.00'), None),\n",
       " ('AM Racing', 'Motors', Decimal('165.00'), Decimal('14694.00'), Decimal('1.00')),\n",
       " ('Behr', 'Used_Components', Decimal('3.00'), Decimal('99.00'), None),\n",
       " ('Canadian EV', 'Miscellaneous', Decimal('4.00'), Decimal('549.00'), Decimal('1.00')),\n",
       " ('Canadian EV', 'Motor_Adapters', Decimal('9.00'), Decimal('1300.00'), None),\n",
       " ('Chennic', 'Charging_Accessories', Decimal('1.00'), Decimal('89.00'), None),\n",
       " ('Curtis', 'Controller_Accessories', Decimal('2.50'), Decimal('649.00'), Decimal('1.00')),\n",
       " ('Curtis', 'Controllers', Decimal('16.33'), Decimal('1787.67'), Decimal('0.71')),\n",
       " ('Deltec', 'Instrumentation', Decimal('2.33'), Decimal('50.67'), Decimal('0.95')),\n",
       " ('DilithiumBMS', 'Charging_Accessories', Decimal('1.00'), Decimal('370.00'), None),\n",
       " ('Elcon', 'Chargers', Decimal('17.00'), Decimal('884.00'), Decimal('1.00')),\n",
       " ('Elcon', 'DC_Converters', Decimal('6.00'), Decimal('549.00'), None),\n",
       " ('EmproShunts', 'Used_Components', Decimal('0.00'), Decimal('20.00'), None),\n",
       " ('EV West', 'Batteries', Decimal('10.00'), Decimal('648.50'), Decimal('-1.00')),\n",
       " ('EV West', 'BatteryEnclosuresAcc', Decimal('13.67'), Decimal('672.33'), Decimal('1.00')),\n",
       " ('EV West', 'Charging_Accessories', Decimal('2.62'), Decimal('68.81'), Decimal('0.40')),\n",
       " ('EV West', 'Controller_Accessories', Decimal('3.75'), Decimal('98.44'), Decimal('0.73')),\n",
       " ('EV West', 'DrivetrainComponents', Decimal('29.60'), Decimal('766.20'), Decimal('0.83')),\n",
       " ('EV West', 'EV_Conversion_Kits', Decimal('376.67'), Decimal('16426.65'), Decimal('-0.70')),\n",
       " ('EV West', 'Hose_Fittings', Decimal('0.10'), Decimal('4.85'), Decimal('-0.02')),\n",
       " ('EV West', 'Instrumentation', Decimal('1.00'), Decimal('149.00'), None),\n",
       " ('EV West', 'Miscellaneous', Decimal('1.43'), Decimal('34.73'), Decimal('0.63')),\n",
       " ('EV West', 'Motor_Accessories', Decimal('13.71'), Decimal('684.43'), Decimal('0.95')),\n",
       " ('EV West', 'Motor_Adapters', Decimal('125.00'), Decimal('2197.00'), Decimal('1.00')),\n",
       " ('EV West', 'Services', Decimal('0.00'), Decimal('393.75'), None),\n",
       " ('EV West', 'Used_Components', Decimal('2.00'), Decimal('139.00'), None),\n",
       " ('HPEVS', 'Controller_Accessories', Decimal('3.00'), Decimal('528.50'), Decimal('1.00')),\n",
       " ('HPEVS', 'Motors', Decimal('101.54'), Decimal('4940.23'), Decimal('0.82')),\n",
       " ('Manzanita', 'Chargers', Decimal('17.73'), Decimal('3120.00'), Decimal('0.88')),\n",
       " ('Manzanita', 'Controllers', Decimal('27.50'), Decimal('3600.00'), Decimal('1.00')),\n",
       " ('Modular EV', 'Charging_Accessories', Decimal('1.00'), Decimal('45.00'), None),\n",
       " ('MSD Ignition', 'Miscellaneous', Decimal('1.00'), Decimal('19.99'), None),\n",
       " ('NetGainMotors', 'Controllers', Decimal('11.00'), Decimal('2050.00'), None),\n",
       " ('NetGainMotors', 'Motors', Decimal('120.00'), Decimal('5275.00'), None),\n",
       " ('QuickChargePower', 'Charging_Accessories', Decimal('2.00'), Decimal('389.00'), None),\n",
       " ('RinconPower', 'BatteryEnclosuresAcc', Decimal('3.00'), Decimal('159.00'), None),\n",
       " ('RinehartMotionSystems', 'Controllers', Decimal('22.50'), Decimal('7800.00'), Decimal('1.00')),\n",
       " ('Samsung', 'Batteries', Decimal('63.75'), Decimal('521.75'), Decimal('0.95')),\n",
       " ('Smart', 'Used_Components', Decimal('2.40'), Decimal('68.00'), Decimal('0.04')),\n",
       " ('SSBC', 'Miscellaneous', Decimal('9.00'), Decimal('288.00'), None),\n",
       " ('TBSElectronics', 'Instrumentation', Decimal('1.00'), Decimal('182.00'), None),\n",
       " ('Tesla', 'Batteries', Decimal('48.50'), Decimal('1189.50'), Decimal('1.00')),\n",
       " ('Tesla', 'Controllers', Decimal('3.00'), Decimal('2699.00'), None),\n",
       " ('Tesla', 'DrivetrainComponents', Decimal('1.00'), Decimal('8.00'), None),\n",
       " ('Tesla', 'Motors', Decimal('305.00'), Decimal('11900.00'), Decimal('1.00')),\n",
       " ('Tesla', 'Used_Components', Decimal('27.50'), Decimal('804.50'), Decimal('1.00')),\n",
       " ('Tyco Kilovac', 'Used_Components', Decimal('5.00'), Decimal('60.00'), None),\n",
       " ('UnknownVendor', 'Batteries', Decimal('39.00'), Decimal('839.00'), Decimal('1.00')),\n",
       " ('UnknownVendor', 'Instrumentation', Decimal('1.00'), Decimal('179.50'), None),\n",
       " ('UnknownVendor', 'Miscellaneous', Decimal('4.43'), Decimal('256.00'), Decimal('0.81')),\n",
       " ('UnknownVendor', 'Used_Components', Decimal('1.00'), Decimal('25.00'), None)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT m.manufacturer_name, p.product_category,\n",
    "       ROUND(AVG(mf.weight)::numeric, 2) AS average_weight,\n",
    "       ROUND(AVG(mf.price)::numeric, 2) AS average_price,\n",
    "       ROUND(CORR(mf.weight, mf.price)::numeric, 2) AS correlation_coef\n",
    "FROM manufacturer_fact mf\n",
    "JOIN manufacturers m ON mf.manufacture_id = m.manufacture_id\n",
    "JOIN products p ON mf.product_id = p.product_id\n",
    "GROUP BY m.manufacturer_name, p.product_category\n",
    "ORDER BY m.manufacturer_name, p.product_category;"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The analysis reveals that the weight of EV parts is positively correlated with their prices for most manufacturers, including Tesla. For instance, batteries have a correlation coefficient of 1, indicating a strong positive correlation between weight and price. However, some manufacturers, such as EV West, exhibit a negative correlation for some products, like EV conversion kits, suggesting that as the weight of these parts increase, their prices decrease.\n",
    "\n",
    "The query provides valuable insights for the supply chain department, helping them understand how the weight of EV parts affects their prices across different manufacturers. By leveraging this information, the department can make informed decisions when negotiating prices with suppliers, identifying cost-saving opportunities, and optimizing their inventory management strategies."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Q4: What is the average price, weight, and quantity of products sold by Parts Unlimited over time, and how does this vary across different geographic regions in which the products have been manufactured?"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This shows how many different types products has been released in 2020"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/partsunlimited\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>sum</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>109</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(Decimal('109'),)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT SUM(mf2.quantity)::numeric\n",
    "              FROM manufacturer_fact mf2\n",
    "              JOIN date_dim d2 ON mf2.date_id = d2.date_id\n",
    "                 WHERE d2.year = 2020\n",
    "             "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In here we can see over all summary also showing the what percentage of the product has been provided by the manufacturer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/partsunlimited\n",
      "26 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>region</th>\n",
       "            <th>city</th>\n",
       "            <th>country</th>\n",
       "            <th>manufacturer_name</th>\n",
       "            <th>year</th>\n",
       "            <th>average_weight</th>\n",
       "            <th>average_price</th>\n",
       "            <th>total_distinct_product</th>\n",
       "            <th>percent_of_products_provided</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Midwest</td>\n",
       "            <td>Batavia</td>\n",
       "            <td>US</td>\n",
       "            <td>Deltec</td>\n",
       "            <td>2022</td>\n",
       "            <td>2.33</td>\n",
       "            <td>50.67</td>\n",
       "            <td>3</td>\n",
       "            <td>10.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Midwest</td>\n",
       "            <td>Georgetown</td>\n",
       "            <td>US</td>\n",
       "            <td>NetGainMotors</td>\n",
       "            <td>2021</td>\n",
       "            <td>83.67</td>\n",
       "            <td>4200.00</td>\n",
       "            <td>3</td>\n",
       "            <td>23.08</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Midwest</td>\n",
       "            <td>Indianapolis</td>\n",
       "            <td>US</td>\n",
       "            <td>EmproShunts</td>\n",
       "            <td>2019</td>\n",
       "            <td>0.00</td>\n",
       "            <td>20.00</td>\n",
       "            <td>1</td>\n",
       "            <td>4.17</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Northeast</td>\n",
       "            <td>Clarence</td>\n",
       "            <td>US</td>\n",
       "            <td>SSBC</td>\n",
       "            <td>2019</td>\n",
       "            <td>9.00</td>\n",
       "            <td>288.00</td>\n",
       "            <td>1</td>\n",
       "            <td>4.17</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Northeast</td>\n",
       "            <td>Mount Kisco</td>\n",
       "            <td>US</td>\n",
       "            <td>Curtis</td>\n",
       "            <td>2020</td>\n",
       "            <td>10.80</td>\n",
       "            <td>1332.20</td>\n",
       "            <td>5</td>\n",
       "            <td>4.59</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>Cedar Park</td>\n",
       "            <td>US</td>\n",
       "            <td>AM Racing</td>\n",
       "            <td>2019</td>\n",
       "            <td>165.00</td>\n",
       "            <td>14694.00</td>\n",
       "            <td>2</td>\n",
       "            <td>8.33</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>El Paso</td>\n",
       "            <td>US</td>\n",
       "            <td>MSD Ignition</td>\n",
       "            <td>2021</td>\n",
       "            <td>1.00</td>\n",
       "            <td>19.99</td>\n",
       "            <td>1</td>\n",
       "            <td>7.69</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>Pryor</td>\n",
       "            <td>US</td>\n",
       "            <td>Modular EV</td>\n",
       "            <td>2019</td>\n",
       "            <td>1.00</td>\n",
       "            <td>45.00</td>\n",
       "            <td>1</td>\n",
       "            <td>4.17</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>Round Rock</td>\n",
       "            <td>US</td>\n",
       "            <td>HPEVS</td>\n",
       "            <td>2020</td>\n",
       "            <td>88.40</td>\n",
       "            <td>4352.00</td>\n",
       "            <td>15</td>\n",
       "            <td>13.76</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Unknown</td>\n",
       "            <td>Delta</td>\n",
       "            <td>Canada</td>\n",
       "            <td>Canadian EV</td>\n",
       "            <td>2022</td>\n",
       "            <td>8.58</td>\n",
       "            <td>1237.42</td>\n",
       "            <td>24</td>\n",
       "            <td>80.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Unknown</td>\n",
       "            <td>Lod</td>\n",
       "            <td>Israel</td>\n",
       "            <td>Elcon</td>\n",
       "            <td>2020</td>\n",
       "            <td>10.40</td>\n",
       "            <td>683.00</td>\n",
       "            <td>5</td>\n",
       "            <td>4.59</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Unknown</td>\n",
       "            <td>Mississauga</td>\n",
       "            <td>Canada</td>\n",
       "            <td>TBSElectronics</td>\n",
       "            <td>2022</td>\n",
       "            <td>1.00</td>\n",
       "            <td>182.00</td>\n",
       "            <td>2</td>\n",
       "            <td>6.67</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Unknown</td>\n",
       "            <td>Richmond Hill</td>\n",
       "            <td>Canada</td>\n",
       "            <td>Manzanita</td>\n",
       "            <td>2020</td>\n",
       "            <td>21.64</td>\n",
       "            <td>3312.00</td>\n",
       "            <td>5</td>\n",
       "            <td>4.59</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Unknown</td>\n",
       "            <td>Seoul</td>\n",
       "            <td>South Korea</td>\n",
       "            <td>Samsung</td>\n",
       "            <td>2019</td>\n",
       "            <td>63.75</td>\n",
       "            <td>521.75</td>\n",
       "            <td>4</td>\n",
       "            <td>16.67</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Unknown</td>\n",
       "            <td>Shenzhen</td>\n",
       "            <td>China</td>\n",
       "            <td>Chennic</td>\n",
       "            <td>2020</td>\n",
       "            <td>1.00</td>\n",
       "            <td>89.00</td>\n",
       "            <td>1</td>\n",
       "            <td>0.92</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Unknown</td>\n",
       "            <td>Stuttgart</td>\n",
       "            <td>Germany</td>\n",
       "            <td>Behr</td>\n",
       "            <td>2019</td>\n",
       "            <td>3.00</td>\n",
       "            <td>99.00</td>\n",
       "            <td>1</td>\n",
       "            <td>4.17</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Unknown</td>\n",
       "            <td>Stuttgart</td>\n",
       "            <td>Germany</td>\n",
       "            <td>Smart</td>\n",
       "            <td>2021</td>\n",
       "            <td>2.40</td>\n",
       "            <td>68.00</td>\n",
       "            <td>5</td>\n",
       "            <td>38.46</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Unknown</td>\n",
       "            <td>Unknown</td>\n",
       "            <td>Unknown</td>\n",
       "            <td>UnknownVendor</td>\n",
       "            <td>2019</td>\n",
       "            <td>9.33</td>\n",
       "            <td>321.17</td>\n",
       "            <td>12</td>\n",
       "            <td>50.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Unknown</td>\n",
       "            <td>Victoria</td>\n",
       "            <td>Canada</td>\n",
       "            <td>QuickChargePower</td>\n",
       "            <td>2021</td>\n",
       "            <td>2.00</td>\n",
       "            <td>389.00</td>\n",
       "            <td>1</td>\n",
       "            <td>7.69</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>Hawthorne</td>\n",
       "            <td>US</td>\n",
       "            <td>AEM</td>\n",
       "            <td>2020</td>\n",
       "            <td>3.33</td>\n",
       "            <td>1328.67</td>\n",
       "            <td>3</td>\n",
       "            <td>2.75</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>Menlo Park</td>\n",
       "            <td>US</td>\n",
       "            <td>Tyco Kilovac</td>\n",
       "            <td>2022</td>\n",
       "            <td>5.00</td>\n",
       "            <td>60.00</td>\n",
       "            <td>1</td>\n",
       "            <td>3.33</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>Oceanside</td>\n",
       "            <td>US</td>\n",
       "            <td>DilithiumBMS</td>\n",
       "            <td>2021</td>\n",
       "            <td>1.00</td>\n",
       "            <td>370.00</td>\n",
       "            <td>2</td>\n",
       "            <td>15.38</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>Oceanside</td>\n",
       "            <td>US</td>\n",
       "            <td>RinconPower</td>\n",
       "            <td>2021</td>\n",
       "            <td>3.00</td>\n",
       "            <td>159.00</td>\n",
       "            <td>1</td>\n",
       "            <td>7.69</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>Palo Alto</td>\n",
       "            <td>US</td>\n",
       "            <td>Tesla</td>\n",
       "            <td>2018</td>\n",
       "            <td>152.82</td>\n",
       "            <td>6017.73</td>\n",
       "            <td>11</td>\n",
       "            <td>100.00</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>San Marcos</td>\n",
       "            <td>US</td>\n",
       "            <td>EV West</td>\n",
       "            <td>2020</td>\n",
       "            <td>68.69</td>\n",
       "            <td>2896.52</td>\n",
       "            <td>75</td>\n",
       "            <td>68.81</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>Wilsonville</td>\n",
       "            <td>US</td>\n",
       "            <td>RinehartMotionSystems</td>\n",
       "            <td>2019</td>\n",
       "            <td>22.50</td>\n",
       "            <td>7800.00</td>\n",
       "            <td>2</td>\n",
       "            <td>8.33</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Midwest', 'Batavia', 'US', 'Deltec', 2022, Decimal('2.33'), Decimal('50.67'), Decimal('3'), Decimal('10.00')),\n",
       " ('Midwest', 'Georgetown', 'US', 'NetGainMotors', 2021, Decimal('83.67'), Decimal('4200.00'), Decimal('3'), Decimal('23.08')),\n",
       " ('Midwest', 'Indianapolis', 'US', 'EmproShunts', 2019, Decimal('0.00'), Decimal('20.00'), Decimal('1'), Decimal('4.17')),\n",
       " ('Northeast', 'Clarence', 'US', 'SSBC', 2019, Decimal('9.00'), Decimal('288.00'), Decimal('1'), Decimal('4.17')),\n",
       " ('Northeast', 'Mount Kisco', 'US', 'Curtis', 2020, Decimal('10.80'), Decimal('1332.20'), Decimal('5'), Decimal('4.59')),\n",
       " ('South', 'Cedar Park', 'US', 'AM Racing', 2019, Decimal('165.00'), Decimal('14694.00'), Decimal('2'), Decimal('8.33')),\n",
       " ('South', 'El Paso', 'US', 'MSD Ignition', 2021, Decimal('1.00'), Decimal('19.99'), Decimal('1'), Decimal('7.69')),\n",
       " ('South', 'Pryor', 'US', 'Modular EV', 2019, Decimal('1.00'), Decimal('45.00'), Decimal('1'), Decimal('4.17')),\n",
       " ('South', 'Round Rock', 'US', 'HPEVS', 2020, Decimal('88.40'), Decimal('4352.00'), Decimal('15'), Decimal('13.76')),\n",
       " ('Unknown', 'Delta', 'Canada', 'Canadian EV', 2022, Decimal('8.58'), Decimal('1237.42'), Decimal('24'), Decimal('80.00')),\n",
       " ('Unknown', 'Lod', 'Israel', 'Elcon', 2020, Decimal('10.40'), Decimal('683.00'), Decimal('5'), Decimal('4.59')),\n",
       " ('Unknown', 'Mississauga', 'Canada', 'TBSElectronics', 2022, Decimal('1.00'), Decimal('182.00'), Decimal('2'), Decimal('6.67')),\n",
       " ('Unknown', 'Richmond Hill', 'Canada', 'Manzanita', 2020, Decimal('21.64'), Decimal('3312.00'), Decimal('5'), Decimal('4.59')),\n",
       " ('Unknown', 'Seoul', 'South Korea', 'Samsung', 2019, Decimal('63.75'), Decimal('521.75'), Decimal('4'), Decimal('16.67')),\n",
       " ('Unknown', 'Shenzhen', 'China', 'Chennic', 2020, Decimal('1.00'), Decimal('89.00'), Decimal('1'), Decimal('0.92')),\n",
       " ('Unknown', 'Stuttgart', 'Germany', 'Behr', 2019, Decimal('3.00'), Decimal('99.00'), Decimal('1'), Decimal('4.17')),\n",
       " ('Unknown', 'Stuttgart', 'Germany', 'Smart', 2021, Decimal('2.40'), Decimal('68.00'), Decimal('5'), Decimal('38.46')),\n",
       " ('Unknown', 'Unknown', 'Unknown', 'UnknownVendor', 2019, Decimal('9.33'), Decimal('321.17'), Decimal('12'), Decimal('50.00')),\n",
       " ('Unknown', 'Victoria', 'Canada', 'QuickChargePower', 2021, Decimal('2.00'), Decimal('389.00'), Decimal('1'), Decimal('7.69')),\n",
       " ('West', 'Hawthorne', 'US', 'AEM', 2020, Decimal('3.33'), Decimal('1328.67'), Decimal('3'), Decimal('2.75')),\n",
       " ('West', 'Menlo Park', 'US', 'Tyco Kilovac', 2022, Decimal('5.00'), Decimal('60.00'), Decimal('1'), Decimal('3.33')),\n",
       " ('West', 'Oceanside', 'US', 'DilithiumBMS', 2021, Decimal('1.00'), Decimal('370.00'), Decimal('2'), Decimal('15.38')),\n",
       " ('West', 'Oceanside', 'US', 'RinconPower', 2021, Decimal('3.00'), Decimal('159.00'), Decimal('1'), Decimal('7.69')),\n",
       " ('West', 'Palo Alto', 'US', 'Tesla', 2018, Decimal('152.82'), Decimal('6017.73'), Decimal('11'), Decimal('100.00')),\n",
       " ('West', 'San Marcos', 'US', 'EV West', 2020, Decimal('68.69'), Decimal('2896.52'), Decimal('75'), Decimal('68.81')),\n",
       " ('West', 'Wilsonville', 'US', 'RinehartMotionSystems', 2019, Decimal('22.50'), Decimal('7800.00'), Decimal('2'), Decimal('8.33'))]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT l.region, l.city, l.country, m.manufacturer_name, d.year,\n",
    "       ROUND(AVG(mf.weight)::numeric, 2) AS average_weight,\n",
    "       ROUND(AVG(mf.price)::numeric, 2) AS average_price,\n",
    "       SUM(mf.quantity)::numeric AS total_distinct_product,\n",
    "       ROUND(SUM(mf.quantity)::numeric / \n",
    "             (SELECT SUM(mf2.quantity)::numeric\n",
    "              FROM manufacturer_fact mf2\n",
    "              JOIN date_dim d2 ON mf2.date_id = d2.date_id\n",
    "              WHERE d2.year = d.year)::numeric * 100, 2) AS percent_of_products_provided\n",
    "FROM manufacturer_fact mf\n",
    "JOIN manufacturers m ON mf.manufacture_id = m.manufacture_id\n",
    "JOIN products p ON mf.product_id = p.product_id\n",
    "JOIN location_dim l ON mf.location_id = l.location_id\n",
    "JOIN date_dim d ON mf.date_id = d.date_id\n",
    "GROUP BY l.region, l.city, l.country, m.manufacturer_name, d.year\n",
    "ORDER BY l.region, l.city, l.country, m.manufacturer_name, d.year;\n",
    "\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "we can breakdown a year to see a better picture, For 2020 the biggiest wendor is EV West and follows by HPEVS\t"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/partsunlimited\n",
      "7 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>region</th>\n",
       "            <th>city</th>\n",
       "            <th>country</th>\n",
       "            <th>manufacturer_name</th>\n",
       "            <th>year</th>\n",
       "            <th>average_weight</th>\n",
       "            <th>average_price</th>\n",
       "            <th>total_distinct_product</th>\n",
       "            <th>percent_of_products_provided</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Northeast</td>\n",
       "            <td>Mount Kisco</td>\n",
       "            <td>US</td>\n",
       "            <td>Curtis</td>\n",
       "            <td>2020</td>\n",
       "            <td>10.80</td>\n",
       "            <td>1332.20</td>\n",
       "            <td>5</td>\n",
       "            <td>4.59</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>Round Rock</td>\n",
       "            <td>US</td>\n",
       "            <td>HPEVS</td>\n",
       "            <td>2020</td>\n",
       "            <td>88.40</td>\n",
       "            <td>4352.00</td>\n",
       "            <td>15</td>\n",
       "            <td>13.76</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Unknown</td>\n",
       "            <td>Lod</td>\n",
       "            <td>Israel</td>\n",
       "            <td>Elcon</td>\n",
       "            <td>2020</td>\n",
       "            <td>10.40</td>\n",
       "            <td>683.00</td>\n",
       "            <td>5</td>\n",
       "            <td>4.59</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Unknown</td>\n",
       "            <td>Richmond Hill</td>\n",
       "            <td>Canada</td>\n",
       "            <td>Manzanita</td>\n",
       "            <td>2020</td>\n",
       "            <td>21.64</td>\n",
       "            <td>3312.00</td>\n",
       "            <td>5</td>\n",
       "            <td>4.59</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Unknown</td>\n",
       "            <td>Shenzhen</td>\n",
       "            <td>China</td>\n",
       "            <td>Chennic</td>\n",
       "            <td>2020</td>\n",
       "            <td>1.00</td>\n",
       "            <td>89.00</td>\n",
       "            <td>1</td>\n",
       "            <td>0.92</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>Hawthorne</td>\n",
       "            <td>US</td>\n",
       "            <td>AEM</td>\n",
       "            <td>2020</td>\n",
       "            <td>3.33</td>\n",
       "            <td>1328.67</td>\n",
       "            <td>3</td>\n",
       "            <td>2.75</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>San Marcos</td>\n",
       "            <td>US</td>\n",
       "            <td>EV West</td>\n",
       "            <td>2020</td>\n",
       "            <td>68.69</td>\n",
       "            <td>2896.52</td>\n",
       "            <td>75</td>\n",
       "            <td>68.81</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Northeast', 'Mount Kisco', 'US', 'Curtis', 2020, Decimal('10.80'), Decimal('1332.20'), Decimal('5'), Decimal('4.59')),\n",
       " ('South', 'Round Rock', 'US', 'HPEVS', 2020, Decimal('88.40'), Decimal('4352.00'), Decimal('15'), Decimal('13.76')),\n",
       " ('Unknown', 'Lod', 'Israel', 'Elcon', 2020, Decimal('10.40'), Decimal('683.00'), Decimal('5'), Decimal('4.59')),\n",
       " ('Unknown', 'Richmond Hill', 'Canada', 'Manzanita', 2020, Decimal('21.64'), Decimal('3312.00'), Decimal('5'), Decimal('4.59')),\n",
       " ('Unknown', 'Shenzhen', 'China', 'Chennic', 2020, Decimal('1.00'), Decimal('89.00'), Decimal('1'), Decimal('0.92')),\n",
       " ('West', 'Hawthorne', 'US', 'AEM', 2020, Decimal('3.33'), Decimal('1328.67'), Decimal('3'), Decimal('2.75')),\n",
       " ('West', 'San Marcos', 'US', 'EV West', 2020, Decimal('68.69'), Decimal('2896.52'), Decimal('75'), Decimal('68.81'))]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT l.region, l.city, l.country, m.manufacturer_name, d.year,\n",
    "       ROUND(AVG(mf.weight)::numeric, 2) AS average_weight,\n",
    "       ROUND(AVG(mf.price)::numeric, 2) AS average_price,\n",
    "       SUM(mf.quantity)::numeric AS total_distinct_product,\n",
    "       ROUND(SUM(mf.quantity)::numeric / \n",
    "             (SELECT SUM(mf2.quantity)::numeric\n",
    "              FROM manufacturer_fact mf2\n",
    "              JOIN date_dim d2 ON mf2.date_id = d2.date_id\n",
    "              WHERE d2.year = d.year)::numeric * 100, 2) AS percent_of_products_provided\n",
    "FROM manufacturer_fact mf\n",
    "JOIN manufacturers m ON mf.manufacture_id = m.manufacture_id\n",
    "JOIN products p ON mf.product_id = p.product_id\n",
    "JOIN location_dim l ON mf.location_id = l.location_id\n",
    "JOIN date_dim d ON mf.date_id = d.date_id\n",
    "WHERE d.year = 2020 \n",
    "GROUP BY l.region, l.city, l.country, m.manufacturer_name, d.year\n",
    "ORDER BY l.region, l.city, l.country, m.manufacturer_name, d.year;\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Q5 : How does the demand for charging stations and the number of vendors operating in different regions relate to the size of the EV market, and what trends can be identified over time?"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In here we can look at the sum of the population and charging stations. One thing that we notice that in west we do not see new charging stations for the last two years but for those years I see increase in the other regions. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/partsunlimited\n",
      "18 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>region</th>\n",
       "            <th>year</th>\n",
       "            <th>number_of_cars</th>\n",
       "            <th>number_of_chargingsta</th>\n",
       "            <th>number_of_manufacturers</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Midwest</td>\n",
       "            <td>2020</td>\n",
       "            <td>3</td>\n",
       "            <td>624</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Midwest</td>\n",
       "            <td>2021</td>\n",
       "            <td>4</td>\n",
       "            <td>1474</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Midwest</td>\n",
       "            <td>2022</td>\n",
       "            <td>2</td>\n",
       "            <td>0</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Northeast</td>\n",
       "            <td>2020</td>\n",
       "            <td>2</td>\n",
       "            <td>1219</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Northeast</td>\n",
       "            <td>2021</td>\n",
       "            <td>2</td>\n",
       "            <td>1685</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Northeast</td>\n",
       "            <td>2022</td>\n",
       "            <td>4</td>\n",
       "            <td>0</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Northeast</td>\n",
       "            <td>2023</td>\n",
       "            <td>0</td>\n",
       "            <td>0</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>2020</td>\n",
       "            <td>10</td>\n",
       "            <td>1697</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>2021</td>\n",
       "            <td>20</td>\n",
       "            <td>2595</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>2022</td>\n",
       "            <td>17</td>\n",
       "            <td>0</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>South</td>\n",
       "            <td>2023</td>\n",
       "            <td>3</td>\n",
       "            <td>0</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Unknown</td>\n",
       "            <td>2020</td>\n",
       "            <td>0</td>\n",
       "            <td>0</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Unknown</td>\n",
       "            <td>2021</td>\n",
       "            <td>0</td>\n",
       "            <td>0</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Unknown</td>\n",
       "            <td>2023</td>\n",
       "            <td>0</td>\n",
       "            <td>0</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2020</td>\n",
       "            <td>5490</td>\n",
       "            <td>3140</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2021</td>\n",
       "            <td>9175</td>\n",
       "            <td>5312</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2022</td>\n",
       "            <td>13817</td>\n",
       "            <td>0</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>West</td>\n",
       "            <td>2023</td>\n",
       "            <td>5053</td>\n",
       "            <td>0</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Midwest', 2020, 3, 624, 2),\n",
       " ('Midwest', 2021, 4, 1474, 2),\n",
       " ('Midwest', 2022, 2, 0, 2),\n",
       " ('Northeast', 2020, 2, 1219, 1),\n",
       " ('Northeast', 2021, 2, 1685, 1),\n",
       " ('Northeast', 2022, 4, 0, 1),\n",
       " ('Northeast', 2023, 0, 0, 1),\n",
       " ('South', 2020, 10, 1697, 1),\n",
       " ('South', 2021, 20, 2595, 1),\n",
       " ('South', 2022, 17, 0, 2),\n",
       " ('South', 2023, 3, 0, 1),\n",
       " ('Unknown', 2020, 0, 0, 3),\n",
       " ('Unknown', 2021, 0, 0, 1),\n",
       " ('Unknown', 2023, 0, 0, 2),\n",
       " ('West', 2020, 5490, 3140, 1),\n",
       " ('West', 2021, 9175, 5312, 2),\n",
       " ('West', 2022, 13817, 0, 2),\n",
       " ('West', 2023, 5053, 0, 2)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select l.region, m.year,\n",
    "sum(mfc.vehicle_pop_quantity) as number_of_cars,\n",
    "sum(mfc.ev_cs_quantity) as number_of_chargingSta,\n",
    "COUNT(DISTINCT ma.manufacture_id) AS number_of_manufacturers\n",
    "from manufacturer_fact_cumulative mfc\n",
    "JOIN manufacturers ma ON mfc.manufacture_id = ma.manufacture_id\n",
    "JOIN location_dim l ON mfc.location_id = l.location_id\n",
    "JOIN month_dim m ON mfc.month_id = m.month_id\n",
    "WHERE m.year BETWEEN 2020 AND 2023\n",
    "GROUP BY l.region, m.year;"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.6"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}