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": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The sql extension is already loaded. To reload it, use:\n",
      "  %reload_ext sql\n"
     ]
    }
   ],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: student@sparkifydb'"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql postgresql://student:student@127.0.0.1/sparkifydb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>1543449657796</td>\n",
       "        <td>73</td>\n",
       "        <td>paid</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>954</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>2</td>\n",
       "        <td>1543449690796</td>\n",
       "        <td>24</td>\n",
       "        <td>paid</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>984</td>\n",
       "        <td>Lake Havasu City-Kingman, AZ</td>\n",
       "        <td>&quot;Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.125 Safari/537.36&quot;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>1543449841796</td>\n",
       "        <td>24</td>\n",
       "        <td>paid</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>984</td>\n",
       "        <td>Lake Havasu City-Kingman, AZ</td>\n",
       "        <td>&quot;Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.125 Safari/537.36&quot;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>1543449895796</td>\n",
       "        <td>73</td>\n",
       "        <td>paid</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>954</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>1543450033796</td>\n",
       "        <td>24</td>\n",
       "        <td>paid</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>984</td>\n",
       "        <td>Lake Havasu City-Kingman, AZ</td>\n",
       "        <td>&quot;Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.125 Safari/537.36&quot;</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 1543449657796, 73, 'paid', None, None, 954, '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",
       " (2, 1543449690796, 24, 'paid', None, None, 984, 'Lake Havasu City-Kingman, AZ', '\"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.125 Safari/537.36\"'),\n",
       " (3, 1543449841796, 24, 'paid', None, None, 984, 'Lake Havasu City-Kingman, AZ', '\"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.125 Safari/537.36\"'),\n",
       " (4, 1543449895796, 73, 'paid', None, None, 954, '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, 1543450033796, 24, 'paid', None, None, 984, 'Lake Havasu City-Kingman, AZ', '\"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.125 Safari/537.36\"')]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM songplays LIMIT 5;"
   ]
  },
  {
   "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>50</td>\n",
       "        <td>Ava</td>\n",
       "        <td>Robinson</td>\n",
       "        <td>F</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14</td>\n",
       "        <td>Theodore</td>\n",
       "        <td>Harris</td>\n",
       "        <td>M</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>Sylvie</td>\n",
       "        <td>Cruz</td>\n",
       "        <td>F</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>98</td>\n",
       "        <td>Jordyn</td>\n",
       "        <td>Powell</td>\n",
       "        <td>F</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>32</td>\n",
       "        <td>Lily</td>\n",
       "        <td>Burns</td>\n",
       "        <td>F</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(50, 'Ava', 'Robinson', 'F', 'free'),\n",
       " (14, 'Theodore', 'Harris', 'M', 'free'),\n",
       " (10, 'Sylvie', 'Cruz', 'F', 'free'),\n",
       " (98, 'Jordyn', 'Powell', 'F', 'free'),\n",
       " (32, 'Lily', 'Burns', 'F', 'free')]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM users LIMIT 5;"
   ]
  },
  {
   "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>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>SOINLJW12A8C13314C</td>\n",
       "        <td>City Slickers</td>\n",
       "        <td>AR8IEZO1187B99055E</td>\n",
       "        <td>2008</td>\n",
       "        <td>149.86404</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SOGDBUF12A8C140FAA</td>\n",
       "        <td>Intro</td>\n",
       "        <td>AR558FS1187FB45658</td>\n",
       "        <td>2003</td>\n",
       "        <td>75.67628</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SORRZGD12A6310DBC3</td>\n",
       "        <td>Harajuku Girls</td>\n",
       "        <td>ARVBRGZ1187FB4675A</td>\n",
       "        <td>2004</td>\n",
       "        <td>290.55955</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SONWXQJ12A8C134D94</td>\n",
       "        <td>The Ballad Of Sleeping Beauty</td>\n",
       "        <td>ARNF6401187FB57032</td>\n",
       "        <td>1994</td>\n",
       "        <td>305.162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SOFCHDR12AB01866EF</td>\n",
       "        <td>Living Hell</td>\n",
       "        <td>AREVWGE1187B9B890A</td>\n",
       "        <td>0</td>\n",
       "        <td>282.43546</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('SOINLJW12A8C13314C', 'City Slickers', 'AR8IEZO1187B99055E', 2008, Decimal('149.86404')),\n",
       " ('SOGDBUF12A8C140FAA', 'Intro', 'AR558FS1187FB45658', 2003, Decimal('75.67628')),\n",
       " ('SORRZGD12A6310DBC3', 'Harajuku Girls', 'ARVBRGZ1187FB4675A', 2004, Decimal('290.55955')),\n",
       " ('SONWXQJ12A8C134D94', 'The Ballad Of Sleeping Beauty', 'ARNF6401187FB57032', 1994, Decimal('305.162')),\n",
       " ('SOFCHDR12AB01866EF', 'Living Hell', 'AREVWGE1187B9B890A', 0, Decimal('282.43546'))]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM songs 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",
      "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>latitude</th>\n",
       "        <th>longitude</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AR8IEZO1187B99055E</td>\n",
       "        <td>Marc Shaiman</td>\n",
       "        <td></td>\n",
       "        <td>NaN</td>\n",
       "        <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AR558FS1187FB45658</td>\n",
       "        <td>40 Grit</td>\n",
       "        <td></td>\n",
       "        <td>NaN</td>\n",
       "        <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ARVBRGZ1187FB4675A</td>\n",
       "        <td>Gwen Stefani</td>\n",
       "        <td></td>\n",
       "        <td>NaN</td>\n",
       "        <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ARNF6401187FB57032</td>\n",
       "        <td>Sophie B. Hawkins</td>\n",
       "        <td>New York, NY [Manhattan]</td>\n",
       "        <td>40.79086</td>\n",
       "        <td>-73.96644</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AREVWGE1187B9B890A</td>\n",
       "        <td>Bitter End</td>\n",
       "        <td>Noci (BA)</td>\n",
       "        <td>-13.442</td>\n",
       "        <td>-41.9952</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('AR8IEZO1187B99055E', 'Marc Shaiman', '', Decimal('NaN'), Decimal('NaN')),\n",
       " ('AR558FS1187FB45658', '40 Grit', '', Decimal('NaN'), Decimal('NaN')),\n",
       " ('ARVBRGZ1187FB4675A', 'Gwen Stefani', '', Decimal('NaN'), Decimal('NaN')),\n",
       " ('ARNF6401187FB57032', 'Sophie B. Hawkins', 'New York, NY [Manhattan]', Decimal('40.79086'), Decimal('-73.96644')),\n",
       " ('AREVWGE1187B9B890A', 'Bitter End', 'Noci (BA)', Decimal('-13.442'), Decimal('-41.9952'))]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM artists LIMIT 5;"
   ]
  },
  {
   "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>1543449657796</td>\n",
       "        <td>0</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>1970</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1543449690796</td>\n",
       "        <td>0</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>1970</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1543449841796</td>\n",
       "        <td>0</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>1970</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1543449895796</td>\n",
       "        <td>0</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>1970</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1543450033796</td>\n",
       "        <td>0</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>1970</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1543449657796, 0, 1, 1, 1, 1970, 3),\n",
       " (1543449690796, 0, 1, 1, 1, 1970, 3),\n",
       " (1543449841796, 0, 1, 1, 1, 1970, 3),\n",
       " (1543449895796, 0, 1, 1, 1, 1970, 3),\n",
       " (1543450033796, 0, 1, 1, 1, 1970, 3)]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM time LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "# Exploratory Data Analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "## Top 10 locations with highest users "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1/sparkifydb\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>location</th>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>San Francisco-Oakland-Hayward, CA</td>\n",
       "        <td>691</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Portland-South Portland, ME</td>\n",
       "        <td>665</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Lansing-East Lansing, MI</td>\n",
       "        <td>557</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Chicago-Naperville-Elgin, IL-IN-WI</td>\n",
       "        <td>475</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Atlanta-Sandy Springs-Roswell, GA</td>\n",
       "        <td>456</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Waterloo-Cedar Falls, IA</td>\n",
       "        <td>397</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Lake Havasu City-Kingman, AZ</td>\n",
       "        <td>321</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Tampa-St. Petersburg-Clearwater, FL</td>\n",
       "        <td>307</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>San Jose-Sunnyvale-Santa Clara, CA</td>\n",
       "        <td>292</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sacramento--Roseville--Arden-Arcade, CA</td>\n",
       "        <td>270</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('San Francisco-Oakland-Hayward, CA', 691),\n",
       " ('Portland-South Portland, ME', 665),\n",
       " ('Lansing-East Lansing, MI', 557),\n",
       " ('Chicago-Naperville-Elgin, IL-IN-WI', 475),\n",
       " ('Atlanta-Sandy Springs-Roswell, GA', 456),\n",
       " ('Waterloo-Cedar Falls, IA', 397),\n",
       " ('Lake Havasu City-Kingman, AZ', 321),\n",
       " ('Tampa-St. Petersburg-Clearwater, FL', 307),\n",
       " ('San Jose-Sunnyvale-Santa Clara, CA', 292),\n",
       " ('Sacramento--Roseville--Arden-Arcade, CA', 270)]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT location, count(*) FROM songplays GROUP BY location ORDER BY count(*) DESC LIMIT 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "## Access by platform"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1/sparkifydb\n",
      "8 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>platforms</th>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Macintosh</td>\n",
       "        <td>3000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>X11</td>\n",
       "        <td>1153</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Windows NT 5.1</td>\n",
       "        <td>955</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Windows NT 6.1</td>\n",
       "        <td>884</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Windows NT 6.3</td>\n",
       "        <td>576</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>iPhone</td>\n",
       "        <td>239</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>compatible</td>\n",
       "        <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Windows NT 6.2</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Macintosh', 3000),\n",
       " ('X11', 1153),\n",
       " ('Windows NT 5.1', 955),\n",
       " ('Windows NT 6.1', 884),\n",
       " ('Windows NT 6.3', 576),\n",
       " ('iPhone', 239),\n",
       " ('compatible', 11),\n",
       " ('Windows NT 6.2', 2)]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT SPLIT_PART(SPLIT_PART(SPLIT_PART(user_agent,'(', 2),';',1),')',1) AS platforms, count(*) FROM songplays GROUP BY platforms ORDER BY 2 DESC;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "## Total minutes for all albums by each artist (Top 10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1/sparkifydb\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>artist</th>\n",
       "        <th>total_album_minutes</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Clp</td>\n",
       "        <td>605</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Faiz Ali Faiz</td>\n",
       "        <td>599</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Montserrat Caballé</td>\n",
       "        <td>511</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Blue Rodeo</td>\n",
       "        <td>491</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>John Wesley</td>\n",
       "        <td>485</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Casual</td>\n",
       "        <td>478</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Trafik</td>\n",
       "        <td>424</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jinx</td>\n",
       "        <td>407</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Steve Morse</td>\n",
       "        <td>364</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Terry Callier</td>\n",
       "        <td>343</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Clp', Decimal('605')),\n",
       " ('Faiz Ali Faiz', Decimal('599')),\n",
       " ('Montserrat Caballé', Decimal('511')),\n",
       " ('Blue Rodeo', Decimal('491')),\n",
       " ('John Wesley', Decimal('485')),\n",
       " ('Casual', Decimal('478')),\n",
       " ('Trafik', Decimal('424')),\n",
       " ('Jinx', Decimal('407')),\n",
       " ('Steve Morse', Decimal('364')),\n",
       " ('Terry Callier', Decimal('343'))]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT SPLIT_PART(name,';',1) AS artist, ROUND(SUM(duration),0) AS total_album_minutes FROM songs INNER JOIN artists USING (artist_id) GROUP BY name ORDER BY total_album_minutes DESC LIMIT 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "## Access by Week"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>week</th>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>6813</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 6813)]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT week, count(*) FROM time GROUP BY week LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "## Type(Level) of user"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1/sparkifydb\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>level</th>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>free</td>\n",
       "        <td>1229</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>paid</td>\n",
       "        <td>5591</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('free', 1229), ('paid', 5591)]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT level, count(level) FROM songplays GROUP BY level LIMIT 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "## Type(Level) of user by Gender"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1/sparkifydb\n",
      "4 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>gender</th>\n",
       "        <th>level</th>\n",
       "        <th>sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>F</td>\n",
       "        <td>free</td>\n",
       "        <td>820</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>F</td>\n",
       "        <td>paid</td>\n",
       "        <td>120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>M</td>\n",
       "        <td>free</td>\n",
       "        <td>595</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>M</td>\n",
       "        <td>paid</td>\n",
       "        <td>28</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('F', 'free', Decimal('820')),\n",
       " ('F', 'paid', Decimal('120')),\n",
       " ('M', 'free', Decimal('595')),\n",
       " ('M', 'paid', Decimal('28'))]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT gender, level, sum(rows) FROM (SELECT gender, level, ROW_NUMBER(*) over(partition by gender, level) AS rows FROM users) AS stats GROUP BY gender, level;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "## Highest listened user by location"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>location</th>\n",
       "        <th>name</th>\n",
       "        <th>counts</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Chicago-Naperville-Elgin, IL-IN-WI</td>\n",
       "        <td>Elena</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Chicago-Naperville-Elgin, IL-IN-WI', 'Elena', 1)]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT songplays.location, name, count(*) AS counts\\\n",
    "     FROM songplays INNER JOIN artists USING (artist_id)\\\n",
    "     GROUP BY songplays.location, name\\\n",
    "     ORDER BY counts DESC\\\n",
    "     LIMIT 10;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>location</th>\n",
       "        <th>artist_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Chicago-Naperville-Elgin, IL-IN-WI</td>\n",
       "        <td>Elena</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Chicago-Naperville-Elgin, IL-IN-WI', 'Elena')]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT location, artist_name FROM (SELECT songplays.location AS location, name AS artist_name, count(*) over(PARTITION BY songplays.location) AS counts, dense_rank() over(PARTITION BY songplays.location) AS rank FROM songplays INNER JOIN artists USING(artist_id) GROUP BY 1, 2) AS stats WHERE stats.rank =1 LIMIT 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "## Average album length over years"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1/sparkifydb\n",
      "21 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>year</th>\n",
       "        <th>average_album_length</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>0</td>\n",
       "        <td>245.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1961</td>\n",
       "        <td>156.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1964</td>\n",
       "        <td>164.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1969</td>\n",
       "        <td>148.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1972</td>\n",
       "        <td>342.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1982</td>\n",
       "        <td>233.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1984</td>\n",
       "        <td>269.82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1985</td>\n",
       "        <td>124.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1986</td>\n",
       "        <td>307.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1987</td>\n",
       "        <td>491.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1992</td>\n",
       "        <td>133.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1993</td>\n",
       "        <td>270.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1994</td>\n",
       "        <td>270.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1997</td>\n",
       "        <td>240.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1999</td>\n",
       "        <td>236.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2000</td>\n",
       "        <td>203.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2003</td>\n",
       "        <td>136.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2004</td>\n",
       "        <td>249.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2005</td>\n",
       "        <td>262.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2007</td>\n",
       "        <td>209.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2008</td>\n",
       "        <td>149.86</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(0, Decimal('245.57')),\n",
       " (1961, Decimal('156.39')),\n",
       " (1964, Decimal('164.81')),\n",
       " (1969, Decimal('148.04')),\n",
       " (1972, Decimal('342.57')),\n",
       " (1982, Decimal('233.40')),\n",
       " (1984, Decimal('269.82')),\n",
       " (1985, Decimal('124.86')),\n",
       " (1986, Decimal('307.38')),\n",
       " (1987, Decimal('491.13')),\n",
       " (1992, Decimal('133.33')),\n",
       " (1993, Decimal('270.60')),\n",
       " (1994, Decimal('270.67')),\n",
       " (1997, Decimal('240.69')),\n",
       " (1999, Decimal('236.25')),\n",
       " (2000, Decimal('203.61')),\n",
       " (2003, Decimal('136.44')),\n",
       " (2004, Decimal('249.02')),\n",
       " (2005, Decimal('262.09')),\n",
       " (2007, Decimal('209.61')),\n",
       " (2008, Decimal('149.86'))]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT year, round(sum(duration)/count(year),2) AS average_album_length FROM songs GROUP BY year ORDER BY year;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": []
  },
  {
   "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
}