advanced-JOINS.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load SQL Magics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load sqlalchemy to create a local environment of PostgreSQL server"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: jovyan@rsm-docker'"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# %sql dialect+driver://username:password@host:port/database\n",
    "%sql postgresql://jovyan:postgres@localhost:8765/rsm-docker"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = create_engine(\"postgresql://jovyan:postgres@localhost:8765/rsm-docker\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Engine(postgresql://jovyan:***@localhost:8765/rsm-docker)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get files as data frames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "accounts = pd.read_excel(\"data/accounts.xlsx\")\n",
    "orders = pd.read_excel(\"data/orders.xlsx\")\n",
    "region = pd.read_excel(\"data/region.xlsx\")\n",
    "sales_reps = pd.read_excel(\"data/sales_reps.xlsx\")\n",
    "web_events = pd.read_excel(\"data/web_events.xlsx\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data ingestion to tables in a database (here - rsm-docker)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "accounts.to_sql(\"accounts\", engine, if_exists=\"replace\")\n",
    "orders.to_sql(\"orders\", engine, if_exists=\"replace\")\n",
    "region.to_sql(\"region\", engine, if_exists=\"replace\")\n",
    "sales_reps.to_sql(\"sales_reps\", engine, if_exists=\"replace\")\n",
    "web_events.to_sql(\"web_events\", engine, if_exists=\"replace\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['accounts', 'orders', 'region', 'sales_reps', 'web_events']"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine.table_names()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Queries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. Write a query that uses UNION ALL on two instances (and selecting all columns) of the accounts table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# UNION aLL appends all data rows from the two tables inspite of repeated entries */"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jovyan:***@localhost:8765/rsm-docker\n",
      "100 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>index</th>\n",
       "        <th>id</th>\n",
       "        <th>name</th>\n",
       "        <th>website</th>\n",
       "        <th>lat</th>\n",
       "        <th>long</th>\n",
       "        <th>primary_poc</th>\n",
       "        <th>sales_rep_id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>0</td>\n",
       "        <td>1001</td>\n",
       "        <td>Walmart</td>\n",
       "        <td>www.walmart.com</td>\n",
       "        <td>40.23849561</td>\n",
       "        <td>-75.10329704</td>\n",
       "        <td>Tamara Tuma</td>\n",
       "        <td>321500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>1011</td>\n",
       "        <td>Exxon Mobil</td>\n",
       "        <td>www.exxonmobil.com</td>\n",
       "        <td>41.1691563</td>\n",
       "        <td>-73.84937379</td>\n",
       "        <td>Sung Shields</td>\n",
       "        <td>321510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>1021</td>\n",
       "        <td>Apple</td>\n",
       "        <td>www.apple.com</td>\n",
       "        <td>42.29049481</td>\n",
       "        <td>-76.08400942</td>\n",
       "        <td>Jodee Lupo</td>\n",
       "        <td>321520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>1031</td>\n",
       "        <td>Berkshire Hathaway</td>\n",
       "        <td>www.berkshirehathaway.com</td>\n",
       "        <td>40.94902131</td>\n",
       "        <td>-75.76389759</td>\n",
       "        <td>Serafina Banda</td>\n",
       "        <td>321530</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>1041</td>\n",
       "        <td>McKesson</td>\n",
       "        <td>www.mckesson.com</td>\n",
       "        <td>42.21709326</td>\n",
       "        <td>-75.28499823</td>\n",
       "        <td>Angeles Crusoe</td>\n",
       "        <td>321540</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>1051</td>\n",
       "        <td>UnitedHealth Group</td>\n",
       "        <td>www.unitedhealthgroup.com</td>\n",
       "        <td>40.08792542</td>\n",
       "        <td>-75.57569396</td>\n",
       "        <td>Savanna Gayman</td>\n",
       "        <td>321550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>1061</td>\n",
       "        <td>CVS Health</td>\n",
       "        <td>www.cvshealth.com</td>\n",
       "        <td>41.46779585</td>\n",
       "        <td>-73.76763638</td>\n",
       "        <td>Anabel Haskell</td>\n",
       "        <td>321560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7</td>\n",
       "        <td>1071</td>\n",
       "        <td>General Motors</td>\n",
       "        <td>www.gm.com</td>\n",
       "        <td>40.80551762</td>\n",
       "        <td>-76.7101814</td>\n",
       "        <td>Barrie Omeara</td>\n",
       "        <td>321570</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>1081</td>\n",
       "        <td>Ford Motor</td>\n",
       "        <td>www.ford.com</td>\n",
       "        <td>41.113942</td>\n",
       "        <td>-75.85422452</td>\n",
       "        <td>Kym Hagerman</td>\n",
       "        <td>321580</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>1091</td>\n",
       "        <td>AT&amp;T</td>\n",
       "        <td>www.att.com</td>\n",
       "        <td>42.4974627</td>\n",
       "        <td>-74.90271225</td>\n",
       "        <td>Jamel Mosqueda</td>\n",
       "        <td>321590</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>1101</td>\n",
       "        <td>General Electric</td>\n",
       "        <td>www.ge.com</td>\n",
       "        <td>41.1697121</td>\n",
       "        <td>-77.29713174</td>\n",
       "        <td>Parker Hoggan</td>\n",
       "        <td>321600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11</td>\n",
       "        <td>1111</td>\n",
       "        <td>AmerisourceBergen</td>\n",
       "        <td>www.amerisourcebergen.com</td>\n",
       "        <td>41.91146908</td>\n",
       "        <td>-74.4762077</td>\n",
       "        <td>Tuan Trainer</td>\n",
       "        <td>321610</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12</td>\n",
       "        <td>1121</td>\n",
       "        <td>Verizon</td>\n",
       "        <td>www.verizon.com</td>\n",
       "        <td>41.3414106</td>\n",
       "        <td>-75.77193559</td>\n",
       "        <td>Chantell Drescher</td>\n",
       "        <td>321620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>13</td>\n",
       "        <td>1131</td>\n",
       "        <td>Chevron</td>\n",
       "        <td>www.chevron.com</td>\n",
       "        <td>42.6119413</td>\n",
       "        <td>-76.36123105</td>\n",
       "        <td>Paige Bartos</td>\n",
       "        <td>321630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14</td>\n",
       "        <td>1141</td>\n",
       "        <td>Costco</td>\n",
       "        <td>www.costco.com</td>\n",
       "        <td>42.26304566</td>\n",
       "        <td>-74.80916921</td>\n",
       "        <td>Dominique Favela</td>\n",
       "        <td>321640</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>15</td>\n",
       "        <td>1151</td>\n",
       "        <td>Fannie Mae</td>\n",
       "        <td>www.fanniemae.com</td>\n",
       "        <td>41.40919044</td>\n",
       "        <td>-73.95770097</td>\n",
       "        <td>Terrilyn Kesler</td>\n",
       "        <td>321650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16</td>\n",
       "        <td>1161</td>\n",
       "        <td>Kroger</td>\n",
       "        <td>www.thekrogerco.com</td>\n",
       "        <td>42.57610194</td>\n",
       "        <td>-76.35945488</td>\n",
       "        <td>Nannie Brinkman</td>\n",
       "        <td>321660</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>17</td>\n",
       "        <td>1171</td>\n",
       "        <td>Amazon.com</td>\n",
       "        <td>www.amazon.com</td>\n",
       "        <td>42.03552936</td>\n",
       "        <td>-77.08453787</td>\n",
       "        <td>Buffy Azure</td>\n",
       "        <td>321670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>18</td>\n",
       "        <td>1181</td>\n",
       "        <td>Walgreens Boots Alliance</td>\n",
       "        <td>www.walgreensbootsalliance.com</td>\n",
       "        <td>41.76033082</td>\n",
       "        <td>-75.76168171</td>\n",
       "        <td>Esta Engelhardt</td>\n",
       "        <td>321680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>19</td>\n",
       "        <td>1191</td>\n",
       "        <td>HP</td>\n",
       "        <td>www.hp.com</td>\n",
       "        <td>41.01091302</td>\n",
       "        <td>-74.38047472</td>\n",
       "        <td>Khadijah Riemann</td>\n",
       "        <td>321690</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>20</td>\n",
       "        <td>1201</td>\n",
       "        <td>Cardinal Health</td>\n",
       "        <td>www.cardinal.com</td>\n",
       "        <td>41.31906537</td>\n",
       "        <td>-77.52512192</td>\n",
       "        <td>Deanne Hertlein</td>\n",
       "        <td>321700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>21</td>\n",
       "        <td>1211</td>\n",
       "        <td>Express Scripts Holding</td>\n",
       "        <td>www.express-scripts.com</td>\n",
       "        <td>41.47633009</td>\n",
       "        <td>-76.07993254</td>\n",
       "        <td>Jewell Likes</td>\n",
       "        <td>321500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>22</td>\n",
       "        <td>1221</td>\n",
       "        <td>J.P. Morgan Chase</td>\n",
       "        <td>www.jpmorganchase.com</td>\n",
       "        <td>40.39050914</td>\n",
       "        <td>-74.35128299</td>\n",
       "        <td>Florentino Madrigal</td>\n",
       "        <td>321510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>23</td>\n",
       "        <td>1231</td>\n",
       "        <td>Boeing</td>\n",
       "        <td>www.boeing.com</td>\n",
       "        <td>40.23107071</td>\n",
       "        <td>-76.51519085</td>\n",
       "        <td>Melva Lyall</td>\n",
       "        <td>321520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>24</td>\n",
       "        <td>1241</td>\n",
       "        <td>Microsoft</td>\n",
       "        <td>www.microsoft.com</td>\n",
       "        <td>41.80083073</td>\n",
       "        <td>-74.01331115</td>\n",
       "        <td>Dennise Hungate</td>\n",
       "        <td>321530</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>25</td>\n",
       "        <td>1251</td>\n",
       "        <td>Bank of America Corp.</td>\n",
       "        <td>www.bankofamerica.com</td>\n",
       "        <td>41.58226327</td>\n",
       "        <td>-74.03976515</td>\n",
       "        <td>Janise Voth</td>\n",
       "        <td>321540</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>26</td>\n",
       "        <td>1261</td>\n",
       "        <td>Wells Fargo</td>\n",
       "        <td>www.wellsfargo.com</td>\n",
       "        <td>42.29559131</td>\n",
       "        <td>-76.35302092</td>\n",
       "        <td>Lakenya Volker</td>\n",
       "        <td>321550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>27</td>\n",
       "        <td>1271</td>\n",
       "        <td>Home Depot</td>\n",
       "        <td>www.homedepot.com</td>\n",
       "        <td>41.38089123</td>\n",
       "        <td>-75.49637851</td>\n",
       "        <td>Alexandria Zielke</td>\n",
       "        <td>321560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>28</td>\n",
       "        <td>1281</td>\n",
       "        <td>Citigroup</td>\n",
       "        <td>www.citigroup.com</td>\n",
       "        <td>40.85305429</td>\n",
       "        <td>-76.49044823</td>\n",
       "        <td>Bea Sera</td>\n",
       "        <td>321570</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>29</td>\n",
       "        <td>1291</td>\n",
       "        <td>Phillips 66</td>\n",
       "        <td>www.phillips66.com</td>\n",
       "        <td>42.63789896</td>\n",
       "        <td>-76.16215145</td>\n",
       "        <td>Maria Garibay</td>\n",
       "        <td>321580</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>30</td>\n",
       "        <td>1301</td>\n",
       "        <td>IBM</td>\n",
       "        <td>www.ibm.com</td>\n",
       "        <td>40.74381454</td>\n",
       "        <td>-76.80764423</td>\n",
       "        <td>Denis Gros</td>\n",
       "        <td>321590</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>31</td>\n",
       "        <td>1311</td>\n",
       "        <td>Valero Energy</td>\n",
       "        <td>www.valero.com</td>\n",
       "        <td>41.73014984</td>\n",
       "        <td>-76.37600422</td>\n",
       "        <td>Taylor Capone</td>\n",
       "        <td>321600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>32</td>\n",
       "        <td>1321</td>\n",
       "        <td>Anthem</td>\n",
       "        <td>www.antheminc.com</td>\n",
       "        <td>41.1538988</td>\n",
       "        <td>-74.98958069</td>\n",
       "        <td>Hanh Oram</td>\n",
       "        <td>321610</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>33</td>\n",
       "        <td>1331</td>\n",
       "        <td>Procter &amp; Gamble</td>\n",
       "        <td>www.pg.com</td>\n",
       "        <td>41.33403574</td>\n",
       "        <td>-74.18997513</td>\n",
       "        <td>Cordia Winters</td>\n",
       "        <td>321620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>34</td>\n",
       "        <td>1341</td>\n",
       "        <td>State Farm Insurance Cos.</td>\n",
       "        <td>www.statefarm.com</td>\n",
       "        <td>42.22108577</td>\n",
       "        <td>-74.70364263</td>\n",
       "        <td>Dionne Hensel</td>\n",
       "        <td>321630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>35</td>\n",
       "        <td>1351</td>\n",
       "        <td>Alphabet</td>\n",
       "        <td>www.google.com</td>\n",
       "        <td>40.20427811</td>\n",
       "        <td>-75.07724145</td>\n",
       "        <td>Pamula Hammel</td>\n",
       "        <td>321640</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>36</td>\n",
       "        <td>1361</td>\n",
       "        <td>Comcast</td>\n",
       "        <td>www.comcastcorporation.com</td>\n",
       "        <td>42.54154764</td>\n",
       "        <td>-76.24992387</td>\n",
       "        <td>Shana Sanborn</td>\n",
       "        <td>321650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>37</td>\n",
       "        <td>1371</td>\n",
       "        <td>Target</td>\n",
       "        <td>www.target.com</td>\n",
       "        <td>40.76381281</td>\n",
       "        <td>-76.13369753</td>\n",
       "        <td>Luba Streett</td>\n",
       "        <td>321660</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>38</td>\n",
       "        <td>1381</td>\n",
       "        <td>Johnson &amp; Johnson</td>\n",
       "        <td>www.jnj.com</td>\n",
       "        <td>40.6266526</td>\n",
       "        <td>-75.1543044</td>\n",
       "        <td>Genevieve Fenton</td>\n",
       "        <td>321670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>39</td>\n",
       "        <td>1391</td>\n",
       "        <td>MetLife</td>\n",
       "        <td>www.metlife.com</td>\n",
       "        <td>41.15717866</td>\n",
       "        <td>-77.29437183</td>\n",
       "        <td>Faustino Henshaw</td>\n",
       "        <td>321680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>40</td>\n",
       "        <td>1401</td>\n",
       "        <td>Archer Daniels Midland</td>\n",
       "        <td>www.adm.com</td>\n",
       "        <td>41.92373676</td>\n",
       "        <td>-75.99411481</td>\n",
       "        <td>Fay Rogowski</td>\n",
       "        <td>321690</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>41</td>\n",
       "        <td>1411</td>\n",
       "        <td>Marathon Petroleum</td>\n",
       "        <td>www.marathonpetroleum.com</td>\n",
       "        <td>41.43222569</td>\n",
       "        <td>-75.07374417</td>\n",
       "        <td>Emmitt Mcnab</td>\n",
       "        <td>321700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>42</td>\n",
       "        <td>1421</td>\n",
       "        <td>Freddie Mac</td>\n",
       "        <td>www.freddiemac.com</td>\n",
       "        <td>42.77051343</td>\n",
       "        <td>-75.18810094</td>\n",
       "        <td>Elayne Grunewald</td>\n",
       "        <td>321500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>43</td>\n",
       "        <td>1431</td>\n",
       "        <td>PepsiCo</td>\n",
       "        <td>www.pepsico.com</td>\n",
       "        <td>40.85630041</td>\n",
       "        <td>-75.72230334</td>\n",
       "        <td>Cathleen Delamater</td>\n",
       "        <td>321510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>44</td>\n",
       "        <td>1441</td>\n",
       "        <td>United Technologies</td>\n",
       "        <td>www.utc.com</td>\n",
       "        <td>42.329397</td>\n",
       "        <td>-75.15785469</td>\n",
       "        <td>Janett Wisecarver</td>\n",
       "        <td>321520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>45</td>\n",
       "        <td>1451</td>\n",
       "        <td>Aetna</td>\n",
       "        <td>www.aetna.com</td>\n",
       "        <td>40.66127584</td>\n",
       "        <td>-76.85110769</td>\n",
       "        <td>Noah Melugin</td>\n",
       "        <td>321530</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>46</td>\n",
       "        <td>1461</td>\n",
       "        <td>Lowe&#x27;s</td>\n",
       "        <td>www.lowes.com</td>\n",
       "        <td>42.02305207</td>\n",
       "        <td>-74.82210597</td>\n",
       "        <td>Roselee Pardini</td>\n",
       "        <td>321540</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>47</td>\n",
       "        <td>1471</td>\n",
       "        <td>UPS</td>\n",
       "        <td>www.ups.com</td>\n",
       "        <td>41.87883813</td>\n",
       "        <td>-73.84930041</td>\n",
       "        <td>Cedrick Weiss</td>\n",
       "        <td>321550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>48</td>\n",
       "        <td>1481</td>\n",
       "        <td>AIG</td>\n",
       "        <td>www.aig.com</td>\n",
       "        <td>42.51281169</td>\n",
       "        <td>-76.84077808</td>\n",
       "        <td>Aja Sandusky</td>\n",
       "        <td>321560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>49</td>\n",
       "        <td>1491</td>\n",
       "        <td>Prudential Financial</td>\n",
       "        <td>www.prudential.com</td>\n",
       "        <td>41.60234569</td>\n",
       "        <td>-75.21696273</td>\n",
       "        <td>Lyda Lamarr</td>\n",
       "        <td>321570</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>50</td>\n",
       "        <td>1501</td>\n",
       "        <td>Intel</td>\n",
       "        <td>www.intel.com</td>\n",
       "        <td>41.03153857</td>\n",
       "        <td>-74.66846407</td>\n",
       "        <td>Elouise Beyers</td>\n",
       "        <td>321580</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>51</td>\n",
       "        <td>1511</td>\n",
       "        <td>Humana</td>\n",
       "        <td>www.humana.com</td>\n",
       "        <td>41.43233665</td>\n",
       "        <td>-77.00496342</td>\n",
       "        <td>Bettye Close</td>\n",
       "        <td>321590</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>52</td>\n",
       "        <td>1521</td>\n",
       "        <td>Disney</td>\n",
       "        <td>www.disney.com</td>\n",
       "        <td>41.87879976</td>\n",
       "        <td>-74.81102607</td>\n",
       "        <td>Timika Mistretta</td>\n",
       "        <td>321600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>53</td>\n",
       "        <td>1531</td>\n",
       "        <td>Cisco Systems</td>\n",
       "        <td>www.cisco.com</td>\n",
       "        <td>41.20101093</td>\n",
       "        <td>-76.53824668</td>\n",
       "        <td>Deadra Waggener</td>\n",
       "        <td>321610</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>54</td>\n",
       "        <td>1541</td>\n",
       "        <td>Pfizer</td>\n",
       "        <td>www.pfizer.com</td>\n",
       "        <td>40.69325986</td>\n",
       "        <td>-75.79453197</td>\n",
       "        <td>Olevia Taubman</td>\n",
       "        <td>321620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>55</td>\n",
       "        <td>1551</td>\n",
       "        <td>Dow Chemical</td>\n",
       "        <td>www.dow.com</td>\n",
       "        <td>40.53050671</td>\n",
       "        <td>-74.66656358</td>\n",
       "        <td>Lillia Ogden</td>\n",
       "        <td>321630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>56</td>\n",
       "        <td>1561</td>\n",
       "        <td>Sysco</td>\n",
       "        <td>www.sysco.com</td>\n",
       "        <td>40.76252413</td>\n",
       "        <td>-73.97753022</td>\n",
       "        <td>Julia Laracuente</td>\n",
       "        <td>321640</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>57</td>\n",
       "        <td>1571</td>\n",
       "        <td>FedEx</td>\n",
       "        <td>www.fedex.com</td>\n",
       "        <td>40.75290524</td>\n",
       "        <td>-73.97519248</td>\n",
       "        <td>Keren Mcconn</td>\n",
       "        <td>321650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>58</td>\n",
       "        <td>1581</td>\n",
       "        <td>Caterpillar</td>\n",
       "        <td>www.caterpillar.com</td>\n",
       "        <td>40.74789383</td>\n",
       "        <td>-73.97738651</td>\n",
       "        <td>Latia Beene</td>\n",
       "        <td>321660</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>59</td>\n",
       "        <td>1591</td>\n",
       "        <td>Lockheed Martin</td>\n",
       "        <td>www.lockheedmartin.com</td>\n",
       "        <td>40.75497089</td>\n",
       "        <td>-73.98233134</td>\n",
       "        <td>Jared Aikins</td>\n",
       "        <td>321670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>60</td>\n",
       "        <td>1601</td>\n",
       "        <td>New York Life Insurance</td>\n",
       "        <td>www.newyorklife.com</td>\n",
       "        <td>40.77072257</td>\n",
       "        <td>-73.9840303</td>\n",
       "        <td>Arlyne Mauzy</td>\n",
       "        <td>321680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>61</td>\n",
       "        <td>1611</td>\n",
       "        <td>Coca-Cola</td>\n",
       "        <td>www.coca-colacompany.com</td>\n",
       "        <td>40.76216116</td>\n",
       "        <td>-73.9802105</td>\n",
       "        <td>Tisha Coletti</td>\n",
       "        <td>321690</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>62</td>\n",
       "        <td>1621</td>\n",
       "        <td>HCA Holdings</td>\n",
       "        <td>www.hcahealthcare.com</td>\n",
       "        <td>40.76981958</td>\n",
       "        <td>-73.98813714</td>\n",
       "        <td>Hubert Blanch</td>\n",
       "        <td>321700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>63</td>\n",
       "        <td>1631</td>\n",
       "        <td>Ingram Micro</td>\n",
       "        <td>www.ingrammicro.com</td>\n",
       "        <td>40.76945841</td>\n",
       "        <td>-73.98081794</td>\n",
       "        <td>Chanelle Keach</td>\n",
       "        <td>321500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>64</td>\n",
       "        <td>1641</td>\n",
       "        <td>Energy Transfer Equity</td>\n",
       "        <td>www.energytransfer.com</td>\n",
       "        <td>40.75633906</td>\n",
       "        <td>-73.99627258</td>\n",
       "        <td>Wynell Lepore</td>\n",
       "        <td>321510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>65</td>\n",
       "        <td>1651</td>\n",
       "        <td>Tyson Foods</td>\n",
       "        <td>www.tysonfoods.com</td>\n",
       "        <td>40.75518241</td>\n",
       "        <td>-73.98323704</td>\n",
       "        <td>Ardelle Khoury</td>\n",
       "        <td>321520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>66</td>\n",
       "        <td>1661</td>\n",
       "        <td>American Airlines Group</td>\n",
       "        <td>www.aa.com</td>\n",
       "        <td>40.771585</td>\n",
       "        <td>-73.9819547</td>\n",
       "        <td>Sasha Haughey</td>\n",
       "        <td>321500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>67</td>\n",
       "        <td>1671</td>\n",
       "        <td>Delta Air Lines</td>\n",
       "        <td>www.delta.com</td>\n",
       "        <td>40.75860903</td>\n",
       "        <td>-73.99067048</td>\n",
       "        <td>Enola Thoms</td>\n",
       "        <td>321510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>68</td>\n",
       "        <td>1681</td>\n",
       "        <td>Nationwide</td>\n",
       "        <td>www.nationwide.com</td>\n",
       "        <td>40.77080408</td>\n",
       "        <td>-73.97765036</td>\n",
       "        <td>Henriette Dawes</td>\n",
       "        <td>321520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>69</td>\n",
       "        <td>1691</td>\n",
       "        <td>Johnson Controls</td>\n",
       "        <td>www.johnsoncontrols.com</td>\n",
       "        <td>40.76346837</td>\n",
       "        <td>-73.97890353</td>\n",
       "        <td>Cammy Sosnowski</td>\n",
       "        <td>321500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>70</td>\n",
       "        <td>1701</td>\n",
       "        <td>Best Buy</td>\n",
       "        <td>www.bestbuy.com</td>\n",
       "        <td>40.75969212</td>\n",
       "        <td>-73.97630101</td>\n",
       "        <td>Laurette Sakai</td>\n",
       "        <td>321510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71</td>\n",
       "        <td>1711</td>\n",
       "        <td>Merck</td>\n",
       "        <td>www.merck.com</td>\n",
       "        <td>40.76536333</td>\n",
       "        <td>-73.97318125</td>\n",
       "        <td>Willodean Steinberger</td>\n",
       "        <td>321520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>72</td>\n",
       "        <td>1721</td>\n",
       "        <td>Liberty Mutual Insurance Group</td>\n",
       "        <td>www.libertymutual.com</td>\n",
       "        <td>40.74823604</td>\n",
       "        <td>-73.97899541</td>\n",
       "        <td>Maranda Papas</td>\n",
       "        <td>321680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>73</td>\n",
       "        <td>1731</td>\n",
       "        <td>Goldman Sachs Group</td>\n",
       "        <td>www.gs.com</td>\n",
       "        <td>40.75744399</td>\n",
       "        <td>-73.96730918</td>\n",
       "        <td>Loris Manfredi</td>\n",
       "        <td>321690</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>74</td>\n",
       "        <td>1741</td>\n",
       "        <td>Honeywell International</td>\n",
       "        <td>www.honeywell.com</td>\n",
       "        <td>40.75117046</td>\n",
       "        <td>-73.98191857</td>\n",
       "        <td>Jeanne Harty</td>\n",
       "        <td>321700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>75</td>\n",
       "        <td>1751</td>\n",
       "        <td>Massachusetts Mutual Life Insurance</td>\n",
       "        <td>www.massmutual.com</td>\n",
       "        <td>40.76359786</td>\n",
       "        <td>-73.98798615</td>\n",
       "        <td>Cassey Hewey</td>\n",
       "        <td>321680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>76</td>\n",
       "        <td>1761</td>\n",
       "        <td>Oracle</td>\n",
       "        <td>www.oracle.com</td>\n",
       "        <td>40.76649223</td>\n",
       "        <td>-73.98860609</td>\n",
       "        <td>Antonia Parkhurst</td>\n",
       "        <td>321690</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>77</td>\n",
       "        <td>1771</td>\n",
       "        <td>Morgan Stanley</td>\n",
       "        <td>www.morganstanley.com</td>\n",
       "        <td>40.76035058</td>\n",
       "        <td>-73.98234372</td>\n",
       "        <td>Elvia Snipes</td>\n",
       "        <td>321700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>78</td>\n",
       "        <td>1781</td>\n",
       "        <td>Cigna</td>\n",
       "        <td>www.cigna.com</td>\n",
       "        <td>40.75566271</td>\n",
       "        <td>-73.97723308</td>\n",
       "        <td>Kandi Mailloux</td>\n",
       "        <td>321630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>79</td>\n",
       "        <td>3361</td>\n",
       "        <td>Ross Stores</td>\n",
       "        <td>www.rossstores.com</td>\n",
       "        <td>34.82021219</td>\n",
       "        <td>-87.8701462</td>\n",
       "        <td>Latosha Hollar</td>\n",
       "        <td>321840</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>80</td>\n",
       "        <td>1791</td>\n",
       "        <td>United Continental Holdings</td>\n",
       "        <td>www.unitedcontinentalholdings.com</td>\n",
       "        <td>40.75034782</td>\n",
       "        <td>-73.9777814</td>\n",
       "        <td>Diedra Crossman</td>\n",
       "        <td>321640</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>81</td>\n",
       "        <td>1801</td>\n",
       "        <td>Allstate</td>\n",
       "        <td>www.allstate.com</td>\n",
       "        <td>40.75827945</td>\n",
       "        <td>-73.98136016</td>\n",
       "        <td>Latesha Garcia</td>\n",
       "        <td>321630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>82</td>\n",
       "        <td>1811</td>\n",
       "        <td>TIAA</td>\n",
       "        <td>www.tiaa.org</td>\n",
       "        <td>42.3595683</td>\n",
       "        <td>-71.05133234</td>\n",
       "        <td>Deeanna Burpo</td>\n",
       "        <td>321640</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>83</td>\n",
       "        <td>1821</td>\n",
       "        <td>INTL FCStone</td>\n",
       "        <td>www.intlfcstone.com</td>\n",
       "        <td>42.35998456</td>\n",
       "        <td>-71.06380494</td>\n",
       "        <td>Ericka Hamid</td>\n",
       "        <td>321630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>84</td>\n",
       "        <td>1831</td>\n",
       "        <td>CHS</td>\n",
       "        <td>www.chsinc.com</td>\n",
       "        <td>42.36335095</td>\n",
       "        <td>-71.06592737</td>\n",
       "        <td>Sheilah Truss</td>\n",
       "        <td>321640</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>85</td>\n",
       "        <td>1841</td>\n",
       "        <td>American Express</td>\n",
       "        <td>www.americanexpress.com</td>\n",
       "        <td>42.35739849</td>\n",
       "        <td>-71.06018574</td>\n",
       "        <td>Horacio Weidman</td>\n",
       "        <td>321630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>86</td>\n",
       "        <td>1851</td>\n",
       "        <td>Gilead Sciences</td>\n",
       "        <td>www.gilead.com</td>\n",
       "        <td>42.35445028</td>\n",
       "        <td>-71.05788145</td>\n",
       "        <td>Nickie Moulden</td>\n",
       "        <td>321640</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>87</td>\n",
       "        <td>1861</td>\n",
       "        <td>Publix Super Markets</td>\n",
       "        <td>www.publix.com</td>\n",
       "        <td>42.35850315</td>\n",
       "        <td>-71.05281548</td>\n",
       "        <td>Cherri Sienkiewicz</td>\n",
       "        <td>321630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>88</td>\n",
       "        <td>1871</td>\n",
       "        <td>General Dynamics</td>\n",
       "        <td>www.generaldynamics.com</td>\n",
       "        <td>42.35930205</td>\n",
       "        <td>-71.05137356</td>\n",
       "        <td>Erin Viverette</td>\n",
       "        <td>321640</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>89</td>\n",
       "        <td>1881</td>\n",
       "        <td>TJX</td>\n",
       "        <td>www.tjx.com</td>\n",
       "        <td>42.36202717</td>\n",
       "        <td>-71.06394369</td>\n",
       "        <td>Sheryl Diamond</td>\n",
       "        <td>321580</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>90</td>\n",
       "        <td>1891</td>\n",
       "        <td>ConocoPhillips</td>\n",
       "        <td>www.conocophillips.com</td>\n",
       "        <td>42.35647074</td>\n",
       "        <td>-71.05656643</td>\n",
       "        <td>Lisa Rayborn</td>\n",
       "        <td>321590</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>91</td>\n",
       "        <td>1901</td>\n",
       "        <td>Nike</td>\n",
       "        <td>www.nike.com</td>\n",
       "        <td>42.35672264</td>\n",
       "        <td>-71.05401481</td>\n",
       "        <td>Adam Ptacek</td>\n",
       "        <td>321580</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>92</td>\n",
       "        <td>1911</td>\n",
       "        <td>World Fuel Services</td>\n",
       "        <td>www.wfscorp.com</td>\n",
       "        <td>42.3646094</td>\n",
       "        <td>-71.06350665</td>\n",
       "        <td>Kasandra Correa</td>\n",
       "        <td>321590</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>93</td>\n",
       "        <td>1921</td>\n",
       "        <td>3M</td>\n",
       "        <td>www.3m.com</td>\n",
       "        <td>42.35869695</td>\n",
       "        <td>-71.05271055</td>\n",
       "        <td>Orville Leavell</td>\n",
       "        <td>321580</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>94</td>\n",
       "        <td>1931</td>\n",
       "        <td>Mondelez International</td>\n",
       "        <td>www.mondelezinternational.com</td>\n",
       "        <td>42.35493079</td>\n",
       "        <td>-71.05615465</td>\n",
       "        <td>Vanetta Lerch</td>\n",
       "        <td>321590</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>95</td>\n",
       "        <td>1941</td>\n",
       "        <td>Exelon</td>\n",
       "        <td>www.exeloncorp.com</td>\n",
       "        <td>42.35515794</td>\n",
       "        <td>-71.05362272</td>\n",
       "        <td>Leana Godines</td>\n",
       "        <td>321560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>96</td>\n",
       "        <td>1951</td>\n",
       "        <td>Twenty-First Century Fox</td>\n",
       "        <td>www.21cf.com</td>\n",
       "        <td>42.35467661</td>\n",
       "        <td>-71.05476697</td>\n",
       "        <td>Nichelle Singley</td>\n",
       "        <td>321560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>97</td>\n",
       "        <td>1961</td>\n",
       "        <td>Deere</td>\n",
       "        <td>www.johndeere.com</td>\n",
       "        <td>42.36409081</td>\n",
       "        <td>-71.05689009</td>\n",
       "        <td>Aura Walz</td>\n",
       "        <td>321560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>98</td>\n",
       "        <td>1971</td>\n",
       "        <td>Tesoro</td>\n",
       "        <td>www.tsocorp.com</td>\n",
       "        <td>42.36061671</td>\n",
       "        <td>-71.05311005</td>\n",
       "        <td>Mammie Koff</td>\n",
       "        <td>321520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>99</td>\n",
       "        <td>1981</td>\n",
       "        <td>Time Warner</td>\n",
       "        <td>www.timewarner.com</td>\n",
       "        <td>42.35976799</td>\n",
       "        <td>-71.05667622</td>\n",
       "        <td>Lakeisha Kridler</td>\n",
       "        <td>321530</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(0, 1001, 'Walmart', 'www.walmart.com', 40.23849561, -75.10329704, 'Tamara Tuma', 321500),\n",
       " (1, 1011, 'Exxon Mobil', 'www.exxonmobil.com', 41.1691563, -73.84937379, 'Sung Shields', 321510),\n",
       " (2, 1021, 'Apple', 'www.apple.com', 42.29049481, -76.08400942, 'Jodee Lupo', 321520),\n",
       " (3, 1031, 'Berkshire Hathaway', 'www.berkshirehathaway.com', 40.94902131, -75.76389759, 'Serafina Banda', 321530),\n",
       " (4, 1041, 'McKesson', 'www.mckesson.com', 42.21709326, -75.28499823, 'Angeles Crusoe', 321540),\n",
       " (5, 1051, 'UnitedHealth Group', 'www.unitedhealthgroup.com', 40.08792542, -75.57569396, 'Savanna Gayman', 321550),\n",
       " (6, 1061, 'CVS Health', 'www.cvshealth.com', 41.46779585, -73.76763638, 'Anabel Haskell', 321560),\n",
       " (7, 1071, 'General Motors', 'www.gm.com', 40.80551762, -76.7101814, 'Barrie Omeara', 321570),\n",
       " (8, 1081, 'Ford Motor', 'www.ford.com', 41.113942, -75.85422452, 'Kym Hagerman', 321580),\n",
       " (9, 1091, 'AT&T', 'www.att.com', 42.4974627, -74.90271225, 'Jamel Mosqueda', 321590),\n",
       " (10, 1101, 'General Electric', 'www.ge.com', 41.1697121, -77.29713174, 'Parker Hoggan', 321600),\n",
       " (11, 1111, 'AmerisourceBergen', 'www.amerisourcebergen.com', 41.91146908, -74.4762077, 'Tuan Trainer', 321610),\n",
       " (12, 1121, 'Verizon', 'www.verizon.com', 41.3414106, -75.77193559, 'Chantell Drescher', 321620),\n",
       " (13, 1131, 'Chevron', 'www.chevron.com', 42.6119413, -76.36123105, 'Paige Bartos', 321630),\n",
       " (14, 1141, 'Costco', 'www.costco.com', 42.26304566, -74.80916921, 'Dominique Favela', 321640),\n",
       " (15, 1151, 'Fannie Mae', 'www.fanniemae.com', 41.40919044, -73.95770097, 'Terrilyn Kesler', 321650),\n",
       " (16, 1161, 'Kroger', 'www.thekrogerco.com', 42.57610194, -76.35945488, 'Nannie Brinkman', 321660),\n",
       " (17, 1171, 'Amazon.com', 'www.amazon.com', 42.03552936, -77.08453787, 'Buffy Azure', 321670),\n",
       " (18, 1181, 'Walgreens Boots Alliance', 'www.walgreensbootsalliance.com', 41.76033082, -75.76168171, 'Esta Engelhardt', 321680),\n",
       " (19, 1191, 'HP', 'www.hp.com', 41.01091302, -74.38047472, 'Khadijah Riemann', 321690),\n",
       " (20, 1201, 'Cardinal Health', 'www.cardinal.com', 41.31906537, -77.52512192, 'Deanne Hertlein', 321700),\n",
       " (21, 1211, 'Express Scripts Holding', 'www.express-scripts.com', 41.47633009, -76.07993254, 'Jewell Likes', 321500),\n",
       " (22, 1221, 'J.P. Morgan Chase', 'www.jpmorganchase.com', 40.39050914, -74.35128299, 'Florentino Madrigal', 321510),\n",
       " (23, 1231, 'Boeing', 'www.boeing.com', 40.23107071, -76.51519085, 'Melva Lyall', 321520),\n",
       " (24, 1241, 'Microsoft', 'www.microsoft.com', 41.80083073, -74.01331115, 'Dennise Hungate', 321530),\n",
       " (25, 1251, 'Bank of America Corp.', 'www.bankofamerica.com', 41.58226327, -74.03976515, 'Janise Voth', 321540),\n",
       " (26, 1261, 'Wells Fargo', 'www.wellsfargo.com', 42.29559131, -76.35302092, 'Lakenya Volker', 321550),\n",
       " (27, 1271, 'Home Depot', 'www.homedepot.com', 41.38089123, -75.49637851, 'Alexandria Zielke', 321560),\n",
       " (28, 1281, 'Citigroup', 'www.citigroup.com', 40.85305429, -76.49044823, 'Bea Sera', 321570),\n",
       " (29, 1291, 'Phillips 66', 'www.phillips66.com', 42.63789896, -76.16215145, 'Maria Garibay', 321580),\n",
       " (30, 1301, 'IBM', 'www.ibm.com', 40.74381454, -76.80764423, 'Denis Gros', 321590),\n",
       " (31, 1311, 'Valero Energy', 'www.valero.com', 41.73014984, -76.37600422, 'Taylor Capone', 321600),\n",
       " (32, 1321, 'Anthem', 'www.antheminc.com', 41.1538988, -74.98958069, 'Hanh Oram', 321610),\n",
       " (33, 1331, 'Procter & Gamble', 'www.pg.com', 41.33403574, -74.18997513, 'Cordia Winters', 321620),\n",
       " (34, 1341, 'State Farm Insurance Cos.', 'www.statefarm.com', 42.22108577, -74.70364263, 'Dionne Hensel', 321630),\n",
       " (35, 1351, 'Alphabet', 'www.google.com', 40.20427811, -75.07724145, 'Pamula Hammel', 321640),\n",
       " (36, 1361, 'Comcast', 'www.comcastcorporation.com', 42.54154764, -76.24992387, 'Shana Sanborn', 321650),\n",
       " (37, 1371, 'Target', 'www.target.com', 40.76381281, -76.13369753, 'Luba Streett', 321660),\n",
       " (38, 1381, 'Johnson & Johnson', 'www.jnj.com', 40.6266526, -75.1543044, 'Genevieve Fenton', 321670),\n",
       " (39, 1391, 'MetLife', 'www.metlife.com', 41.15717866, -77.29437183, 'Faustino Henshaw', 321680),\n",
       " (40, 1401, 'Archer Daniels Midland', 'www.adm.com', 41.92373676, -75.99411481, 'Fay Rogowski', 321690),\n",
       " (41, 1411, 'Marathon Petroleum', 'www.marathonpetroleum.com', 41.43222569, -75.07374417, 'Emmitt Mcnab', 321700),\n",
       " (42, 1421, 'Freddie Mac', 'www.freddiemac.com', 42.77051343, -75.18810094, 'Elayne Grunewald', 321500),\n",
       " (43, 1431, 'PepsiCo', 'www.pepsico.com', 40.85630041, -75.72230334, 'Cathleen Delamater', 321510),\n",
       " (44, 1441, 'United Technologies', 'www.utc.com', 42.329397, -75.15785469, 'Janett Wisecarver', 321520),\n",
       " (45, 1451, 'Aetna', 'www.aetna.com', 40.66127584, -76.85110769, 'Noah Melugin', 321530),\n",
       " (46, 1461, \"Lowe's\", 'www.lowes.com', 42.02305207, -74.82210597, 'Roselee Pardini', 321540),\n",
       " (47, 1471, 'UPS', 'www.ups.com', 41.87883813, -73.84930041, 'Cedrick Weiss', 321550),\n",
       " (48, 1481, 'AIG', 'www.aig.com', 42.51281169, -76.84077808, 'Aja Sandusky', 321560),\n",
       " (49, 1491, 'Prudential Financial', 'www.prudential.com', 41.60234569, -75.21696273, 'Lyda Lamarr', 321570),\n",
       " (50, 1501, 'Intel', 'www.intel.com', 41.03153857, -74.66846407, 'Elouise Beyers', 321580),\n",
       " (51, 1511, 'Humana', 'www.humana.com', 41.43233665, -77.00496342, 'Bettye Close', 321590),\n",
       " (52, 1521, 'Disney', 'www.disney.com', 41.87879976, -74.81102607, 'Timika Mistretta', 321600),\n",
       " (53, 1531, 'Cisco Systems', 'www.cisco.com', 41.20101093, -76.53824668, 'Deadra Waggener', 321610),\n",
       " (54, 1541, 'Pfizer', 'www.pfizer.com', 40.69325986, -75.79453197, 'Olevia Taubman', 321620),\n",
       " (55, 1551, 'Dow Chemical', 'www.dow.com', 40.53050671, -74.66656358, 'Lillia Ogden', 321630),\n",
       " (56, 1561, 'Sysco', 'www.sysco.com', 40.76252413, -73.97753022, 'Julia Laracuente', 321640),\n",
       " (57, 1571, 'FedEx', 'www.fedex.com', 40.75290524, -73.97519248, 'Keren Mcconn', 321650),\n",
       " (58, 1581, 'Caterpillar', 'www.caterpillar.com', 40.74789383, -73.97738651, 'Latia Beene', 321660),\n",
       " (59, 1591, 'Lockheed Martin', 'www.lockheedmartin.com', 40.75497089, -73.98233134, 'Jared Aikins', 321670),\n",
       " (60, 1601, 'New York Life Insurance', 'www.newyorklife.com', 40.77072257, -73.9840303, 'Arlyne Mauzy', 321680),\n",
       " (61, 1611, 'Coca-Cola', 'www.coca-colacompany.com', 40.76216116, -73.9802105, 'Tisha Coletti', 321690),\n",
       " (62, 1621, 'HCA Holdings', 'www.hcahealthcare.com', 40.76981958, -73.98813714, 'Hubert Blanch', 321700),\n",
       " (63, 1631, 'Ingram Micro', 'www.ingrammicro.com', 40.76945841, -73.98081794, 'Chanelle Keach', 321500),\n",
       " (64, 1641, 'Energy Transfer Equity', 'www.energytransfer.com', 40.75633906, -73.99627258, 'Wynell Lepore', 321510),\n",
       " (65, 1651, 'Tyson Foods', 'www.tysonfoods.com', 40.75518241, -73.98323704, 'Ardelle Khoury', 321520),\n",
       " (66, 1661, 'American Airlines Group', 'www.aa.com', 40.771585, -73.9819547, 'Sasha Haughey', 321500),\n",
       " (67, 1671, 'Delta Air Lines', 'www.delta.com', 40.75860903, -73.99067048, 'Enola Thoms', 321510),\n",
       " (68, 1681, 'Nationwide', 'www.nationwide.com', 40.77080408, -73.97765036, 'Henriette Dawes', 321520),\n",
       " (69, 1691, 'Johnson Controls', 'www.johnsoncontrols.com', 40.76346837, -73.97890353, 'Cammy Sosnowski', 321500),\n",
       " (70, 1701, 'Best Buy', 'www.bestbuy.com', 40.75969212, -73.97630101, 'Laurette Sakai', 321510),\n",
       " (71, 1711, 'Merck', 'www.merck.com', 40.76536333, -73.97318125, 'Willodean Steinberger', 321520),\n",
       " (72, 1721, 'Liberty Mutual Insurance Group', 'www.libertymutual.com', 40.74823604, -73.97899541, 'Maranda Papas', 321680),\n",
       " (73, 1731, 'Goldman Sachs Group', 'www.gs.com', 40.75744399, -73.96730918, 'Loris Manfredi', 321690),\n",
       " (74, 1741, 'Honeywell International', 'www.honeywell.com', 40.75117046, -73.98191857, 'Jeanne Harty', 321700),\n",
       " (75, 1751, 'Massachusetts Mutual Life Insurance', 'www.massmutual.com', 40.76359786, -73.98798615, 'Cassey Hewey', 321680),\n",
       " (76, 1761, 'Oracle', 'www.oracle.com', 40.76649223, -73.98860609, 'Antonia Parkhurst', 321690),\n",
       " (77, 1771, 'Morgan Stanley', 'www.morganstanley.com', 40.76035058, -73.98234372, 'Elvia Snipes', 321700),\n",
       " (78, 1781, 'Cigna', 'www.cigna.com', 40.75566271, -73.97723308, 'Kandi Mailloux', 321630),\n",
       " (79, 3361, 'Ross Stores', 'www.rossstores.com', 34.82021219, -87.8701462, 'Latosha Hollar', 321840),\n",
       " (80, 1791, 'United Continental Holdings', 'www.unitedcontinentalholdings.com', 40.75034782, -73.9777814, 'Diedra Crossman', 321640),\n",
       " (81, 1801, 'Allstate', 'www.allstate.com', 40.75827945, -73.98136016, 'Latesha Garcia', 321630),\n",
       " (82, 1811, 'TIAA', 'www.tiaa.org', 42.3595683, -71.05133234, 'Deeanna Burpo', 321640),\n",
       " (83, 1821, 'INTL FCStone', 'www.intlfcstone.com', 42.35998456, -71.06380494, 'Ericka Hamid', 321630),\n",
       " (84, 1831, 'CHS', 'www.chsinc.com', 42.36335095, -71.06592737, 'Sheilah Truss', 321640),\n",
       " (85, 1841, 'American Express', 'www.americanexpress.com', 42.35739849, -71.06018574, 'Horacio Weidman', 321630),\n",
       " (86, 1851, 'Gilead Sciences', 'www.gilead.com', 42.35445028, -71.05788145, 'Nickie Moulden', 321640),\n",
       " (87, 1861, 'Publix Super Markets', 'www.publix.com', 42.35850315, -71.05281548, 'Cherri Sienkiewicz', 321630),\n",
       " (88, 1871, 'General Dynamics', 'www.generaldynamics.com', 42.35930205, -71.05137356, 'Erin Viverette', 321640),\n",
       " (89, 1881, 'TJX', 'www.tjx.com', 42.36202717, -71.06394369, 'Sheryl Diamond', 321580),\n",
       " (90, 1891, 'ConocoPhillips', 'www.conocophillips.com', 42.35647074, -71.05656643, 'Lisa Rayborn', 321590),\n",
       " (91, 1901, 'Nike', 'www.nike.com', 42.35672264, -71.05401481, 'Adam Ptacek', 321580),\n",
       " (92, 1911, 'World Fuel Services', 'www.wfscorp.com', 42.3646094, -71.06350665, 'Kasandra Correa', 321590),\n",
       " (93, 1921, '3M', 'www.3m.com', 42.35869695, -71.05271055, 'Orville Leavell', 321580),\n",
       " (94, 1931, 'Mondelez International', 'www.mondelezinternational.com', 42.35493079, -71.05615465, 'Vanetta Lerch', 321590),\n",
       " (95, 1941, 'Exelon', 'www.exeloncorp.com', 42.35515794, -71.05362272, 'Leana Godines', 321560),\n",
       " (96, 1951, 'Twenty-First Century Fox', 'www.21cf.com', 42.35467661, -71.05476697, 'Nichelle Singley', 321560),\n",
       " (97, 1961, 'Deere', 'www.johndeere.com', 42.36409081, -71.05689009, 'Aura Walz', 321560),\n",
       " (98, 1971, 'Tesoro', 'www.tsocorp.com', 42.36061671, -71.05311005, 'Mammie Koff', 321520),\n",
       " (99, 1981, 'Time Warner', 'www.timewarner.com', 42.35976799, -71.05667622, 'Lakeisha Kridler', 321530)]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT *\n",
    "FROM accounts a1\n",
    "\n",
    "UNION ALL\n",
    "\n",
    "SELECT *\n",
    "FROM accounts a2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Add a WHERE clause to each of the tables that you unioned in the query above, filtering the first table where name equals Walmart and filtering the second table where name equals Disney."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jovyan:***@localhost:8765/rsm-docker\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>index</th>\n",
       "        <th>id</th>\n",
       "        <th>name</th>\n",
       "        <th>website</th>\n",
       "        <th>lat</th>\n",
       "        <th>long</th>\n",
       "        <th>primary_poc</th>\n",
       "        <th>sales_rep_id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>52</td>\n",
       "        <td>1521</td>\n",
       "        <td>Disney</td>\n",
       "        <td>www.disney.com</td>\n",
       "        <td>41.87879976</td>\n",
       "        <td>-74.81102607</td>\n",
       "        <td>Timika Mistretta</td>\n",
       "        <td>321600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>0</td>\n",
       "        <td>1001</td>\n",
       "        <td>Walmart</td>\n",
       "        <td>www.walmart.com</td>\n",
       "        <td>40.23849561</td>\n",
       "        <td>-75.10329704</td>\n",
       "        <td>Tamara Tuma</td>\n",
       "        <td>321500</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(52, 1521, 'Disney', 'www.disney.com', 41.87879976, -74.81102607, 'Timika Mistretta', 321600),\n",
       " (0, 1001, 'Walmart', 'www.walmart.com', 40.23849561, -75.10329704, 'Tamara Tuma', 321500)]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT *\n",
    "FROM accounts a1\n",
    "WHERE name = 'Walmart'\n",
    "\n",
    "UNION \n",
    "\n",
    "SELECT *\n",
    "FROM accounts a2\n",
    "WHERE name = 'Disney';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. Perform the union in your first query (under the Appending Data via UNION header) in a common table expression and name it double_accounts. Then do a COUNT the number of times a name appears in the double_accounts table. If you do this correctly, your query results should have a count of 2 for each name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Performing operations on the combined dataset, limiting rows to 100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jovyan:***@localhost:8765/rsm-docker\n",
      "100 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Microsoft</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Monsanto</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Dean Foods</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>KKR</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Performance Food Group</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Paccar</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>USAA</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CST Brands</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ally Financial</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Amgen</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Broadcom</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AIG</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>W.W. Grainger</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Reynolds American</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sears Holdings</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HCA Holdings</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Aetna</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Deere</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Procter &amp; Gamble</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Gilead Sciences</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Dominion Resources</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Viacom</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Altria Group</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HP</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>General Motors</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>American Family Insurance Group</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Kinder Morgan</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PBF Energy</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Baker Hughes</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bank of America Corp.</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CenturyLink</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Universal Health Services</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Marriott International</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CBRE Group</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AutoNation</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>FedEx</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Best Buy</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Home Depot</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Leucadia National</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>TIAA</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Tenneco</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Avis Budget Group</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>MasterCard</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Western Refining</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>American Airlines Group</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Plains GP Holdings</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Celgene</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ross Stores</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Anthem</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Applied Materials</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Stanley Black &amp; Decker</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ameriprise Financial</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Northrop Grumman</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Progressive</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Allstate</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Freeport-McMoRan</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Valero Energy</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>EOG Resources</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Unum Group</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Fannie Mae</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>MGM Resorts International</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Freddie Mac</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Halliburton</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Costco</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>DISH Network</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Icahn Enterprises</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Priceline Group</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Office Depot</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PPG Industries</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Chevron</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Aramark</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Republic Services</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>BorgWarner</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>D.R. Horton</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>EMC</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Tyson Foods</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Marathon Petroleum</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Dollar General</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Oracle</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Baxter International</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Southern</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Autoliv</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Devon Energy</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Corning</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Xcel Energy</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Fidelity National Financial</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Anadarko Petroleum</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Global Partners</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Enterprise Products Partners</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mohawk Industries</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Navistar International</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>R.R. Donnelley &amp; Sons</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CVS Health</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Kimberly-Clark</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Fluor</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>MetLife</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Time Warner</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Campbell Soup</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mosaic</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Comcast</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Microsoft', 2),\n",
       " ('Monsanto', 2),\n",
       " ('Dean Foods', 2),\n",
       " ('KKR', 2),\n",
       " ('Performance Food Group', 2),\n",
       " ('Paccar', 2),\n",
       " ('USAA', 2),\n",
       " ('CST Brands', 2),\n",
       " ('Ally Financial', 2),\n",
       " ('Amgen', 2),\n",
       " ('Broadcom', 2),\n",
       " ('AIG', 2),\n",
       " ('W.W. Grainger', 2),\n",
       " ('Reynolds American', 2),\n",
       " ('Sears Holdings', 2),\n",
       " ('HCA Holdings', 2),\n",
       " ('Aetna', 2),\n",
       " ('Deere', 2),\n",
       " ('Procter & Gamble', 2),\n",
       " ('Gilead Sciences', 2),\n",
       " ('Dominion Resources', 2),\n",
       " ('Viacom', 2),\n",
       " ('Altria Group', 2),\n",
       " ('HP', 2),\n",
       " ('General Motors', 2),\n",
       " ('American Family Insurance Group', 2),\n",
       " ('Kinder Morgan', 2),\n",
       " ('PBF Energy', 2),\n",
       " ('Baker Hughes', 2),\n",
       " ('Bank of America Corp.', 2),\n",
       " ('CenturyLink', 2),\n",
       " ('Universal Health Services', 2),\n",
       " ('Marriott International', 2),\n",
       " ('CBRE Group', 2),\n",
       " ('AutoNation', 2),\n",
       " ('FedEx', 2),\n",
       " ('Best Buy', 2),\n",
       " ('Home Depot', 2),\n",
       " ('Leucadia National', 2),\n",
       " ('TIAA', 2),\n",
       " ('Tenneco', 2),\n",
       " ('Avis Budget Group', 2),\n",
       " ('MasterCard', 2),\n",
       " ('Western Refining', 2),\n",
       " ('American Airlines Group', 2),\n",
       " ('Plains GP Holdings', 2),\n",
       " ('Celgene', 2),\n",
       " ('Ross Stores', 2),\n",
       " ('Anthem', 2),\n",
       " ('Applied Materials', 2),\n",
       " ('Stanley Black & Decker', 2),\n",
       " ('Ameriprise Financial', 2),\n",
       " ('Northrop Grumman', 2),\n",
       " ('Progressive', 2),\n",
       " ('Allstate', 2),\n",
       " ('Freeport-McMoRan', 2),\n",
       " ('Valero Energy', 2),\n",
       " ('EOG Resources', 2),\n",
       " ('Unum Group', 2),\n",
       " ('Fannie Mae', 2),\n",
       " ('MGM Resorts International', 2),\n",
       " ('Freddie Mac', 2),\n",
       " ('Halliburton', 2),\n",
       " ('Costco', 2),\n",
       " ('DISH Network', 2),\n",
       " ('Icahn Enterprises', 2),\n",
       " ('Priceline Group', 2),\n",
       " ('Office Depot', 2),\n",
       " ('PPG Industries', 2),\n",
       " ('Chevron', 2),\n",
       " ('Aramark', 2),\n",
       " ('Republic Services', 2),\n",
       " ('BorgWarner', 2),\n",
       " ('D.R. Horton', 2),\n",
       " ('EMC', 2),\n",
       " ('Tyson Foods', 2),\n",
       " ('Marathon Petroleum', 2),\n",
       " ('Dollar General', 2),\n",
       " ('Oracle', 2),\n",
       " ('Baxter International', 2),\n",
       " ('Southern', 2),\n",
       " ('Autoliv', 2),\n",
       " ('Devon Energy', 2),\n",
       " ('Corning', 2),\n",
       " ('Xcel Energy', 2),\n",
       " ('Fidelity National Financial', 2),\n",
       " ('Anadarko Petroleum', 2),\n",
       " ('Global Partners', 2),\n",
       " ('Enterprise Products Partners', 2),\n",
       " ('Mohawk Industries', 2),\n",
       " ('Navistar International', 2),\n",
       " ('R.R. Donnelley & Sons', 2),\n",
       " ('CVS Health', 2),\n",
       " ('Kimberly-Clark', 2),\n",
       " ('Fluor', 2),\n",
       " ('MetLife', 2),\n",
       " ('Time Warner', 2),\n",
       " ('Campbell Soup', 2),\n",
       " ('Mosaic', 2),\n",
       " ('Comcast', 2)]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "WITH double_accounts AS \n",
    "(SELECT *\n",
    "FROM accounts a1\n",
    "\n",
    "UNION ALL\n",
    "\n",
    "SELECT *\n",
    "FROM accounts a2)\n",
    "\n",
    "SELECT name, COUNT(*)\n",
    "FROM double_accounts\n",
    "GROUP BY 1\n",
    "ORDER BY 2 DESC\n",
    "LIMIT 100;"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}