test.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: student@sparkifydb'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql postgresql://student:student@127.0.0.1/sparkifydb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1/sparkifydb\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>songplay_id</th>\n",
       "        <th>start_time</th>\n",
       "        <th>user_id</th>\n",
       "        <th>level</th>\n",
       "        <th>song_id</th>\n",
       "        <th>artist_id</th>\n",
       "        <th>session_id</th>\n",
       "        <th>location</th>\n",
       "        <th>user_agent</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>2018-11-30 00:22:07.796000</td>\n",
       "        <td>91</td>\n",
       "        <td>free</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>829</td>\n",
       "        <td>Dallas-Fort Worth-Arlington, TX</td>\n",
       "        <td>Mozilla/5.0 (compatible; MSIE 10.0; Windows NT 6.2; WOW64; Trident/6.0)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>2018-11-30 01:08:41.796000</td>\n",
       "        <td>73</td>\n",
       "        <td>paid</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>1049</td>\n",
       "        <td>Tampa-St. Petersburg-Clearwater, FL</td>\n",
       "        <td>&quot;Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4) AppleWebKit/537.78.2 (KHTML, like Gecko) Version/7.0.6 Safari/537.78.2&quot;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>2018-11-30 01:12:48.796000</td>\n",
       "        <td>73</td>\n",
       "        <td>paid</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>1049</td>\n",
       "        <td>Tampa-St. Petersburg-Clearwater, FL</td>\n",
       "        <td>&quot;Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4) AppleWebKit/537.78.2 (KHTML, like Gecko) Version/7.0.6 Safari/537.78.2&quot;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>2018-11-30 01:17:05.796000</td>\n",
       "        <td>73</td>\n",
       "        <td>paid</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>1049</td>\n",
       "        <td>Tampa-St. Petersburg-Clearwater, FL</td>\n",
       "        <td>&quot;Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4) AppleWebKit/537.78.2 (KHTML, like Gecko) Version/7.0.6 Safari/537.78.2&quot;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>2018-11-30 01:20:56.796000</td>\n",
       "        <td>73</td>\n",
       "        <td>paid</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>1049</td>\n",
       "        <td>Tampa-St. Petersburg-Clearwater, FL</td>\n",
       "        <td>&quot;Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4) AppleWebKit/537.78.2 (KHTML, like Gecko) Version/7.0.6 Safari/537.78.2&quot;</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, datetime.datetime(2018, 11, 30, 0, 22, 7, 796000), 91, 'free', None, None, 829, 'Dallas-Fort Worth-Arlington, TX', 'Mozilla/5.0 (compatible; MSIE 10.0; Windows NT 6.2; WOW64; Trident/6.0)'),\n",
       " (2, datetime.datetime(2018, 11, 30, 1, 8, 41, 796000), 73, 'paid', None, None, 1049, 'Tampa-St. Petersburg-Clearwater, FL', '\"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4) AppleWebKit/537.78.2 (KHTML, like Gecko) Version/7.0.6 Safari/537.78.2\"'),\n",
       " (3, datetime.datetime(2018, 11, 30, 1, 12, 48, 796000), 73, 'paid', None, None, 1049, 'Tampa-St. Petersburg-Clearwater, FL', '\"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4) AppleWebKit/537.78.2 (KHTML, like Gecko) Version/7.0.6 Safari/537.78.2\"'),\n",
       " (4, datetime.datetime(2018, 11, 30, 1, 17, 5, 796000), 73, 'paid', None, None, 1049, 'Tampa-St. Petersburg-Clearwater, FL', '\"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4) AppleWebKit/537.78.2 (KHTML, like Gecko) Version/7.0.6 Safari/537.78.2\"'),\n",
       " (5, datetime.datetime(2018, 11, 30, 1, 20, 56, 796000), 73, 'paid', None, None, 1049, 'Tampa-St. Petersburg-Clearwater, FL', '\"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4) AppleWebKit/537.78.2 (KHTML, like Gecko) Version/7.0.6 Safari/537.78.2\"')]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM song_plays LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1/sparkifydb\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6820</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(6820,)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT count(*) FROM song_plays"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1/sparkifydb\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>user_id</th>\n",
       "        <th>first_name</th>\n",
       "        <th>last_name</th>\n",
       "        <th>gender</th>\n",
       "        <th>level</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>45</td>\n",
       "        <td>Dominick</td>\n",
       "        <td>Norris</td>\n",
       "        <td>M</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>24</td>\n",
       "        <td>Layla</td>\n",
       "        <td>Griffin</td>\n",
       "        <td>F</td>\n",
       "        <td>paid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>74</td>\n",
       "        <td>Braden</td>\n",
       "        <td>Parker</td>\n",
       "        <td>M</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>30</td>\n",
       "        <td>Avery</td>\n",
       "        <td>Watkins</td>\n",
       "        <td>F</td>\n",
       "        <td>paid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>92</td>\n",
       "        <td>Ryann</td>\n",
       "        <td>Smith</td>\n",
       "        <td>F</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(45, 'Dominick', 'Norris', 'M', 'free'),\n",
       " (24, 'Layla', 'Griffin', 'F', 'paid'),\n",
       " (74, 'Braden', 'Parker', 'M', 'free'),\n",
       " (30, 'Avery', 'Watkins', 'F', 'paid'),\n",
       " (92, 'Ryann', 'Smith', 'F', 'free')]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM users LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1/sparkifydb\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>96</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(96,)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT count(*) FROM users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1/sparkifydb\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>song_id</th>\n",
       "        <th>title</th>\n",
       "        <th>artist_id</th>\n",
       "        <th>year</th>\n",
       "        <th>duration</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SOMZWCG12A8C13C480</td>\n",
       "        <td>I Didn&#x27;t Mean To</td>\n",
       "        <td>ARD7TVE1187B99BFB1</td>\n",
       "        <td>0</td>\n",
       "        <td>218.93179</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SOUDSGM12AC9618304</td>\n",
       "        <td>Insatiable (Instrumental Version)</td>\n",
       "        <td>ARNTLGG11E2835DDB9</td>\n",
       "        <td>0</td>\n",
       "        <td>266.39628</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SOIAZJW12AB01853F1</td>\n",
       "        <td>Pink World</td>\n",
       "        <td>AR8ZCNI1187B9A069B</td>\n",
       "        <td>1984</td>\n",
       "        <td>269.81832</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SOHKNRJ12A6701D1F8</td>\n",
       "        <td>Drop of Rain</td>\n",
       "        <td>AR10USD1187B99F3F1</td>\n",
       "        <td>0</td>\n",
       "        <td>189.57016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SOCIWDW12A8C13D406</td>\n",
       "        <td>Soul Deep</td>\n",
       "        <td>ARMJAGH1187FB546F3</td>\n",
       "        <td>1969</td>\n",
       "        <td>148.03546</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('SOMZWCG12A8C13C480', \"I Didn't Mean To\", 'ARD7TVE1187B99BFB1', 0, Decimal('218.93179')),\n",
       " ('SOUDSGM12AC9618304', 'Insatiable (Instrumental Version)', 'ARNTLGG11E2835DDB9', 0, Decimal('266.39628')),\n",
       " ('SOIAZJW12AB01853F1', 'Pink World', 'AR8ZCNI1187B9A069B', 1984, Decimal('269.81832')),\n",
       " ('SOHKNRJ12A6701D1F8', 'Drop of Rain', 'AR10USD1187B99F3F1', 0, Decimal('189.57016')),\n",
       " ('SOCIWDW12A8C13D406', 'Soul Deep', 'ARMJAGH1187FB546F3', 1969, Decimal('148.03546'))]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM songs LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1/sparkifydb\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(71,)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT count(*) FROM songs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1/sparkifydb\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>artist_id</th>\n",
       "        <th>name</th>\n",
       "        <th>location</th>\n",
       "        <th>longitude</th>\n",
       "        <th>latitude</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ARD7TVE1187B99BFB1</td>\n",
       "        <td>Casual</td>\n",
       "        <td>California - LA</td>\n",
       "        <td>nan</td>\n",
       "        <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ARNTLGG11E2835DDB9</td>\n",
       "        <td>Clp</td>\n",
       "        <td></td>\n",
       "        <td>nan</td>\n",
       "        <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AR8ZCNI1187B9A069B</td>\n",
       "        <td>Planet P Project</td>\n",
       "        <td></td>\n",
       "        <td>nan</td>\n",
       "        <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AR10USD1187B99F3F1</td>\n",
       "        <td>Tweeterfriendly Music</td>\n",
       "        <td>Burlington, Ontario, Canada</td>\n",
       "        <td>nan</td>\n",
       "        <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ARMJAGH1187FB546F3</td>\n",
       "        <td>The Box Tops</td>\n",
       "        <td>Memphis, TN</td>\n",
       "        <td>-90.04892</td>\n",
       "        <td>35.14968</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('ARD7TVE1187B99BFB1', 'Casual', 'California - LA', nan, nan),\n",
       " ('ARNTLGG11E2835DDB9', 'Clp', '', nan, nan),\n",
       " ('AR8ZCNI1187B9A069B', 'Planet P Project', '', nan, nan),\n",
       " ('AR10USD1187B99F3F1', 'Tweeterfriendly Music', 'Burlington, Ontario, Canada', nan, nan),\n",
       " ('ARMJAGH1187FB546F3', 'The Box Tops', 'Memphis, TN', -90.04892, 35.14968)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM artists LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1/sparkifydb\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>69</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(69,)]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT count(*) FROM artists"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1/sparkifydb\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>start_time</th>\n",
       "        <th>hour</th>\n",
       "        <th>day</th>\n",
       "        <th>week</th>\n",
       "        <th>month</th>\n",
       "        <th>year</th>\n",
       "        <th>weekday</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2018-11-30 00:22:07.796000</td>\n",
       "        <td>0</td>\n",
       "        <td>30</td>\n",
       "        <td>48</td>\n",
       "        <td>11</td>\n",
       "        <td>2018</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2018-11-30 01:08:41.796000</td>\n",
       "        <td>1</td>\n",
       "        <td>30</td>\n",
       "        <td>48</td>\n",
       "        <td>11</td>\n",
       "        <td>2018</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2018-11-30 01:12:48.796000</td>\n",
       "        <td>1</td>\n",
       "        <td>30</td>\n",
       "        <td>48</td>\n",
       "        <td>11</td>\n",
       "        <td>2018</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2018-11-30 01:17:05.796000</td>\n",
       "        <td>1</td>\n",
       "        <td>30</td>\n",
       "        <td>48</td>\n",
       "        <td>11</td>\n",
       "        <td>2018</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2018-11-30 01:20:56.796000</td>\n",
       "        <td>1</td>\n",
       "        <td>30</td>\n",
       "        <td>48</td>\n",
       "        <td>11</td>\n",
       "        <td>2018</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2018, 11, 30, 0, 22, 7, 796000), 0, 30, 48, 11, 2018, 4),\n",
       " (datetime.datetime(2018, 11, 30, 1, 8, 41, 796000), 1, 30, 48, 11, 2018, 4),\n",
       " (datetime.datetime(2018, 11, 30, 1, 12, 48, 796000), 1, 30, 48, 11, 2018, 4),\n",
       " (datetime.datetime(2018, 11, 30, 1, 17, 5, 796000), 1, 30, 48, 11, 2018, 4),\n",
       " (datetime.datetime(2018, 11, 30, 1, 20, 56, 796000), 1, 30, 48, 11, 2018, 4)]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM time LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1/sparkifydb\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6813</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(6813,)]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT count(*) FROM time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1/sparkifydb\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>songplay_id</th>\n",
       "        <th>start_time</th>\n",
       "        <th>user_id</th>\n",
       "        <th>level</th>\n",
       "        <th>song_id</th>\n",
       "        <th>artist_id</th>\n",
       "        <th>session_id</th>\n",
       "        <th>location</th>\n",
       "        <th>user_agent</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4108</td>\n",
       "        <td>2018-11-21 21:56:47.796000</td>\n",
       "        <td>15</td>\n",
       "        <td>paid</td>\n",
       "        <td>SOZCTXZ12AB0182364</td>\n",
       "        <td>AR5KOSW1187FB35FF4</td>\n",
       "        <td>818</td>\n",
       "        <td>Chicago-Naperville-Elgin, IL-IN-WI</td>\n",
       "        <td>&quot;Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Ubuntu Chromium/36.0.1985.125 Chrome/36.0.1985.125 Safari/537.36&quot;</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(4108, datetime.datetime(2018, 11, 21, 21, 56, 47, 796000), 15, 'paid', 'SOZCTXZ12AB0182364', 'AR5KOSW1187FB35FF4', 818, 'Chicago-Naperville-Elgin, IL-IN-WI', '\"Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Ubuntu Chromium/36.0.1985.125 Chrome/36.0.1985.125 Safari/537.36\"')]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM song_plays WHERE song_id is not null;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "## REMEMBER: Restart this notebook to close connection to `sparkifydb`\n",
    "Each time you run the cells above, remember to restart this notebook to close the connection to your database. Otherwise, you won't be able to run your code in `create_tables.py`, `etl.py`, or `etl.ipynb` files since you can't make multiple connections to the same database (in this case, sparkifydb)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "# Sanity Tests \n",
    "\n",
    "Execute the cells below once you are ready to submit the project. Some basic sanity testing will be performed to esnure that your work does NOT contain any commonly found issues. \n",
    "\n",
    "Run each cell and if a cell produces an warning message is orange, you should make appropriate changes to your code before submitting. If all test in a cell pass,no warnings will be printed. \n",
    "\n",
    "The test cases assume that you are using certain column names in your tables. If you get a `IndexError: single positional indexer is out-of-bounds` you may need to change the column names being used by the test cases. Instructions for doing this appear right boefore cell that may require these changes.\n",
    "\n",
    "The tests below are only meant to help you make your work foolproof. The submission will still be graded by a human grader against the project rubric.\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "## Grab Table Names for Testing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "import sql_queries as sqlq"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1/sparkifydb\n",
      "5 rows affected.\n",
      "Returning data to local variable _tablenames\n"
     ]
    }
   ],
   "source": [
    "%%sql _tablenames <<\n",
    "SELECT tablename\n",
    "FROM pg_catalog.pg_tables\n",
    "WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema' AND tableowner = 'student';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "tablenames = _tablenames.DataFrame()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "user_table = [name for name in list(tablenames.tablename) if name in sqlq.user_table_create][0]\n",
    "song_table = [name for name in list(tablenames.tablename) if name in sqlq.song_table_create][0]\n",
    "artists_table = [name for name in list(tablenames.tablename) if name in sqlq.artist_table_create][0]\n",
    "songplay_table = [name for name in list(tablenames.tablename) if name in sqlq.songplay_table_create][0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "## Run Primary Key Tests"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1/sparkifydb\n",
      "1 rows affected.\n",
      "Returning data to local variable _output\n"
     ]
    }
   ],
   "source": [
    "%sql _output << SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type, a.attnotnull, i.indisprimary \\\n",
    "FROM   pg_index i \\\n",
    "JOIN   pg_attribute a ON a.attrelid = i.indrelid \\\n",
    "                     AND a.attnum = ANY(i.indkey) \\\n",
    "WHERE  i.indrelid = '{user_table}'::regclass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "if not _output:\n",
    "    print('\\033[93m'+'[WARNING] '+ f\"The {user_table} table does not have a primary key!\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1/sparkifydb\n",
      "1 rows affected.\n",
      "Returning data to local variable _output\n"
     ]
    }
   ],
   "source": [
    "%sql _output << SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type, a.attnotnull, i.indisprimary \\\n",
    "FROM   pg_index i \\\n",
    "JOIN   pg_attribute a ON a.attrelid = i.indrelid \\\n",
    "                     AND a.attnum = ANY(i.indkey) \\\n",
    "WHERE  i.indrelid = '{artists_table}'::regclass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "if not _output: \n",
    "    print('\\033[93m'+'[WARNING] '+ f\"The {artists_table} table does not have a primary key!\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1/sparkifydb\n",
      "1 rows affected.\n",
      "Returning data to local variable _output\n"
     ]
    }
   ],
   "source": [
    "%sql _output << SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type, a.attnotnull, i.indisprimary \\\n",
    "FROM   pg_index i \\\n",
    "JOIN   pg_attribute a ON a.attrelid = i.indrelid \\\n",
    "                     AND a.attnum = ANY(i.indkey) \\\n",
    "WHERE  i.indrelid = '{songplay_table}'::regclass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "if not _output:\n",
    "    print('\\033[93m'+'[WARNING] '+ f\"The {songplay_table} table does not have a primary key!\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1/sparkifydb\n",
      "1 rows affected.\n",
      "Returning data to local variable _output\n"
     ]
    }
   ],
   "source": [
    "%sql _output << SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type, a.attnotnull, i.indisprimary \\\n",
    "FROM   pg_index i \\\n",
    "JOIN   pg_attribute a ON a.attrelid = i.indrelid \\\n",
    "                     AND a.attnum = ANY(i.indkey) \\\n",
    "WHERE  i.indrelid = '{song_table}'::regclass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "if not _output:\n",
    "    print('\\033[93m'+'[WARNING] '+ f\"The {song_table} table does not have a primary key!\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "## Run Data Type and Constraints Check"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1/sparkifydb\n",
      "5 rows affected.\n",
      "Returning data to local variable _output\n"
     ]
    }
   ],
   "source": [
    "%sql _output << SELECT * FROM information_schema.columns where table_name='{user_table}'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "**Check the column `user_id` for correct data type.**\n",
    "If you get a `IndexError: single positional indexer is out-of-bounds` error, you may be using a different column name. Change the column name below and run the cell again. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "output = _output.DataFrame()\n",
    "_dtype = output[output.column_name == 'user_id'].data_type.iloc[0]\n",
    "if _dtype not in ['integer', 'bigint']:\n",
    "    print('\\033[93m'+'[WARNING] '+ f\"Type {_dtype} may not be an appropriate data type for column 'user_id' in the '{user_table}' table.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1/sparkifydb\n",
      "5 rows affected.\n",
      "Returning data to local variable _output\n"
     ]
    }
   ],
   "source": [
    "%sql _output << SELECT * FROM information_schema.columns where table_name='{song_table}'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "**Check the column `year` for correct data type.\n",
    "Check columns `title` and `duration` for not-NULL constraints.**\n",
    "\n",
    "If you get a `IndexError: single positional indexer is out-of-bounds` error, you may be using different column names. Change the column name(s) below and run the cell again. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "output = _output.DataFrame()\n",
    "\n",
    "_dtype = output[output.column_name == 'year'].data_type.iloc[0]\n",
    "if _dtype not in ['integer']:\n",
    "    print('\\033[93m'+'[WARNING] '+ f\"Type '{_dtype}' may not be an appropriate data type for column 'year' in the '{song_table}' table.\")\n",
    "\n",
    "_nullable_title = output[output.column_name == 'title'].is_nullable.iloc[0]\n",
    "_nullable_duration = output[output.column_name == 'duration'].is_nullable.iloc[0]\n",
    "if (_nullable_duration != 'NO') or (_nullable_title != 'NO'): \n",
    "    print('\\033[93m'+'[WARNING] '+ f\"You may want to add appropriate NOT NULL constraints to the '{song_table}' table.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1/sparkifydb\n",
      "5 rows affected.\n",
      "Returning data to local variable _output\n"
     ]
    }
   ],
   "source": [
    "%sql _output << SELECT * FROM information_schema.columns where table_name='{artists_table}'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "**Check the columns `latitude` and `longitude` for correct data type.\n",
    "Check column `name` for not-NULL constraint.**\n",
    "\n",
    "If you get a `IndexError: single positional indexer is out-of-bounds` error, you may be using different column names. Change the column name(s) below and run the cell again. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "output = _output.DataFrame()\n",
    "\n",
    "_dtype_latitude = output[output.column_name == 'latitude'].data_type.iloc[0]\n",
    "if _dtype_latitude not in ['double precision']:\n",
    "    print('\\033[93m'+'[WARNING] '+ f\"Type '{_dtype_latitude}' may not be an appropriate data type for column 'latitude' in the '{artists_table}' table\")\n",
    "\n",
    "_dtype_longitude = output[output.column_name == 'longitude'].data_type.iloc[0]\n",
    "if _dtype_longitude not in ['double precision']:\n",
    "    print('\\033[93m'+'[WARNING] '+ f\"Type '{_dtype_longitude}' may not be an appropriate data type for column 'longitude' in the '{artists_table}' table\")\n",
    "\n",
    "\n",
    "_nullable_name = output[output.column_name == 'name'].is_nullable.iloc[0]\n",
    "if _nullable_name != 'NO':\n",
    "    print('\\033[93m'+'[WARNING] '+ f\"You may want to add appropriate NOT NULL constraints to the '{artists_table}' table.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1/sparkifydb\n",
      "9 rows affected.\n",
      "Returning data to local variable _output\n"
     ]
    }
   ],
   "source": [
    "%sql _output << SELECT * FROM information_schema.columns where table_name='{songplay_table}'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "**Check the columns `start_time` and `user_id` for correct data type.\n",
    "Check columns `start_time` and `user_id` for not-NULL constraint.**\n",
    "\n",
    "If you get a `IndexError: single positional indexer is out-of-bounds` error, you may be using different column names. Change the column name(s) below and run the cell again. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "output = _output.DataFrame()\n",
    "\n",
    "_dtype_start_time = output[output.column_name == 'start_time'].data_type.iloc[0]\n",
    "if 'timestamp' not in _dtype_start_time: \n",
    "    print('\\033[93m'+'[WARNING] '+ f\"Type '{_dtype_start_time}' may not be an appropriate data type for column 'start_time' in the '{songplay_table}' table.\")\n",
    "\n",
    "_dtype_user_id = output[output.column_name == 'user_id'].data_type.iloc[0]\n",
    "if _dtype_user_id not in ['integer', 'bigint']:\n",
    "    print('\\033[93m'+'[WARNING] '+ f\"Type '{_dtype_user_id}' may not be an appropriate data type for column 'user_id' in the '{songplay_table}' table.\")\n",
    "\n",
    "\n",
    "_nullable_time = output[output.column_name == 'start_time'].is_nullable.iloc[0]\n",
    "_nullable_uid = output[output.column_name == 'user_id'].is_nullable.iloc[0]\n",
    "\n",
    "if (_nullable_time != 'NO') or (_nullable_uid != 'NO'):\n",
    "    print('\\033[93m'+'[WARNING] '+ f\"You may want to add appropriate NOT NULL constraints to the '{songplay_table}' table.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "## Run Tests for Upsertion Check"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "if not re.search('ON\\s+CONFLICT',sqlq.songplay_table_insert,re.IGNORECASE) or \\\n",
    "   not re.search('ON\\s+CONFLICT',sqlq.user_table_insert,re.IGNORECASE) or \\\n",
    "   not re.search('ON\\s+CONFLICT',sqlq.song_table_insert,re.IGNORECASE) or \\\n",
    "   not re.search('ON\\s+CONFLICT',sqlq.artist_table_insert,re.IGNORECASE):\n",
    "    print('\\033[93m'+'[WARNING]Some of your insert queries may need an \"ON CONFLICT\" clause.')\n",
    "    print('\\033[93m'+'         You can either skip conflicting insertions with with \"ON CONFLICT DO NOTHING\"')\n",
    "    print('\\033[93m'+'         OR use \"ON CONFLICT DO UPDATE SET\"')\n",
    "    print('\\033[93m'+'         Check this link for more details: https://www.postgresqltutorial.com/postgresql-upsert/')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}