example_queries.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "## Example queries to test the `sparkifydb` database\n",
    "\n",
    "Remember that, in order to run the cells below, the `sparkifydb` database must be in place. Otherwise, run the scripts `./create_tables.py` and `./etl.py` to set it up."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'Connected: student@sparkifydb'"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# This cell sets connection to the sparkifydb database\n",
    "%load_ext sql\n",
    "%sql postgresql://student:student@127.0.0.1/sparkifydb"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### Example query 1: How many users purchased the paid subscription? In comparison, are there more free tier users?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>level_count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>free</td>\n",
       "        <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>paid</td>\n",
       "        <td>20</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('free', 76), ('paid', 20)]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT level, COUNT(level) as level_count FROM users GROUP BY level ORDER BY level_count DESC"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### Example query 2: What are the longest songs in the database?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>title</th>\n",
       "        <th>duration</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sohna Nee Sohna Data</td>\n",
       "        <td>599.24853</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sono andati? Fingevo di dormire</td>\n",
       "        <td>511.16363</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Floating</td>\n",
       "        <td>491.12771</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>The Emperor Falls</td>\n",
       "        <td>484.62322</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Salt In NYC</td>\n",
       "        <td>424.12363</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Sohna Nee Sohna Data', 599.24853),\n",
       " ('Sono andati? Fingevo di dormire', 511.16363),\n",
       " ('Floating', 491.12771),\n",
       " ('The Emperor Falls', 484.62322),\n",
       " ('Salt In NYC', 424.12363)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT title, duration FROM songs ORDER BY duration DESC 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>artist_name</th>\n",
       "        <th>title</th>\n",
       "        <th>duration</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Faiz Ali Faiz</td>\n",
       "        <td>Sohna Nee Sohna Data</td>\n",
       "        <td>599.24853</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Montserrat Caballé;Placido Domingo;Vicente Sardinero;Judith Blegen;Sherrill Milnes;Georg Solti</td>\n",
       "        <td>Sono andati? Fingevo di dormire</td>\n",
       "        <td>511.16363</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Blue Rodeo</td>\n",
       "        <td>Floating</td>\n",
       "        <td>491.12771</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>John Wesley</td>\n",
       "        <td>The Emperor Falls</td>\n",
       "        <td>484.62322</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Trafik</td>\n",
       "        <td>Salt In NYC</td>\n",
       "        <td>424.12363</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Faiz Ali Faiz', 'Sohna Nee Sohna Data', 599.24853),\n",
       " ('Montserrat Caballé;Placido Domingo;Vicente Sardinero;Judith Blegen;Sherrill Milnes;Georg Solti', 'Sono andati? Fingevo di dormire', 511.16363),\n",
       " ('Blue Rodeo', 'Floating', 491.12771),\n",
       " ('John Wesley', 'The Emperor Falls', 484.62322),\n",
       " ('Trafik', 'Salt In NYC', 424.12363)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Extension of the above query by adding the artist name\n",
    "%sql SELECT a.artist_name, s.title, s.duration FROM songs AS s JOIN artists AS a ON s.artist_id = a.artist_id ORDER BY duration DESC LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### Example query 3: How many songs and artists are there in the database?"
   ]
  },
  {
   "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>number_songs</th>\n",
       "        <th>number_artists</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71</td>\n",
       "        <td>69</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(71, 69)]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT COUNT(DISTINCT(song_id)) AS number_songs, COUNT(DISTINCT(artist_id)) AS number_artists FROM songs LIMIT 5;"
   ]
  },
  {
   "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
}