3_test_dimensional_model.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Testing the Loaded Dimensional Model in the Redshift Data Warehouse"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import relevant modules\n",
    "import sql_queries\n",
    "import psycopg2\n",
    "import configparser\n",
    "import pandas as pd\n",
    "from time import time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import the configparser module to read configuration files\n",
    "config = configparser.ConfigParser()\n",
    "# Read the configuration file containing AWS and Redshift settings\n",
    "config.read_file(open('dwh.cfg'))\n",
    "\n",
    "# Retrieve the parameters of the Redshift cluster from the config file\n",
    "DWH_DB                 = config.get(\"DWH\", \"DWH_DB\")\n",
    "DWH_DB_USER            = config.get(\"DWH\", \"DWH_DB_USER\")\n",
    "DWH_DB_PASSWORD        = config.get(\"DWH\", \"DWH_DB_PASSWORD\")\n",
    "DWH_PORT               = config.get(\"DWH\", \"DWH_PORT\")\n",
    "DWH_ENDPOINT           = config.get('CLUSTER', 'HOST')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "postgresql://dwhuser:Passw0rd@dwhcluster.cejcerbeak3k.us-west-2.redshift.amazonaws.com:5439/dwh\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'Connected: dwhuser@dwh'"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Connect to the Redshift cluster\n",
    "conn_string = 'postgresql://{}:{}@{}:{}/{}'.format(\n",
    "    DWH_DB_USER, DWH_DB_PASSWORD, DWH_ENDPOINT, DWH_PORT, DWH_DB\n",
    ")\n",
    "print(conn_string)\n",
    "\n",
    "%sql $conn_string"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After running **1_create_tables.py** and **2_etl.py**, the data appears to have been loaded into the Redshift data warehouse and transformed into a dimensional model with fact and dimension tables in a star schema.\n",
    "\n",
    "Let's now **verify that the tables are in the correct schema** and **check the number of rows inserted into each table**."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Check the Schema of Each Table in the Data Warehouse"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@dwhcluster.cejcerbeak3k.us-west-2.redshift.amazonaws.com:5439/dwh\n",
      "59 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>table_name</th>\n",
       "        <th>column_name</th>\n",
       "        <th>data_type</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>dim_artists</td>\n",
       "        <td>longitude</td>\n",
       "        <td>double precision</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>dim_artists</td>\n",
       "        <td>latitude</td>\n",
       "        <td>double precision</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>dim_artists</td>\n",
       "        <td>artist_id</td>\n",
       "        <td>character varying</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>dim_artists</td>\n",
       "        <td>location</td>\n",
       "        <td>character varying</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>dim_artists</td>\n",
       "        <td>name</td>\n",
       "        <td>character varying</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>dim_songs</td>\n",
       "        <td>year</td>\n",
       "        <td>integer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>dim_songs</td>\n",
       "        <td>duration</td>\n",
       "        <td>double precision</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>dim_songs</td>\n",
       "        <td>song_id</td>\n",
       "        <td>character varying</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>dim_songs</td>\n",
       "        <td>artist_id</td>\n",
       "        <td>character varying</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>dim_songs</td>\n",
       "        <td>title</td>\n",
       "        <td>character varying</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>dim_time</td>\n",
       "        <td>weekday</td>\n",
       "        <td>integer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>dim_time</td>\n",
       "        <td>year</td>\n",
       "        <td>integer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>dim_time</td>\n",
       "        <td>month</td>\n",
       "        <td>integer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>dim_time</td>\n",
       "        <td>week</td>\n",
       "        <td>integer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>dim_time</td>\n",
       "        <td>day</td>\n",
       "        <td>integer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>dim_time</td>\n",
       "        <td>hour</td>\n",
       "        <td>integer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>dim_time</td>\n",
       "        <td>start_time</td>\n",
       "        <td>timestamp without time zone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>dim_users</td>\n",
       "        <td>user_id</td>\n",
       "        <td>integer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>dim_users</td>\n",
       "        <td>level</td>\n",
       "        <td>character varying</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>dim_users</td>\n",
       "        <td>gender</td>\n",
       "        <td>character varying</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>dim_users</td>\n",
       "        <td>last_name</td>\n",
       "        <td>character varying</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>dim_users</td>\n",
       "        <td>first_name</td>\n",
       "        <td>character varying</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>fact_songplays</td>\n",
       "        <td>songplay_id</td>\n",
       "        <td>integer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>fact_songplays</td>\n",
       "        <td>session_id</td>\n",
       "        <td>integer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>fact_songplays</td>\n",
       "        <td>user_id</td>\n",
       "        <td>integer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>fact_songplays</td>\n",
       "        <td>user_agent</td>\n",
       "        <td>character varying</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>fact_songplays</td>\n",
       "        <td>location</td>\n",
       "        <td>character varying</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>fact_songplays</td>\n",
       "        <td>artist_id</td>\n",
       "        <td>character varying</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>fact_songplays</td>\n",
       "        <td>song_id</td>\n",
       "        <td>character varying</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>fact_songplays</td>\n",
       "        <td>level</td>\n",
       "        <td>character varying</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>fact_songplays</td>\n",
       "        <td>start_time</td>\n",
       "        <td>timestamp without time zone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>staging_events</td>\n",
       "        <td>registration</td>\n",
       "        <td>bigint</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>staging_events</td>\n",
       "        <td>userid</td>\n",
       "        <td>integer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>staging_events</td>\n",
       "        <td>status</td>\n",
       "        <td>integer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>staging_events</td>\n",
       "        <td>sessionid</td>\n",
       "        <td>integer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>staging_events</td>\n",
       "        <td>iteminsession</td>\n",
       "        <td>integer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>staging_events</td>\n",
       "        <td>length</td>\n",
       "        <td>double precision</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>staging_events</td>\n",
       "        <td>useragent</td>\n",
       "        <td>character varying</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>staging_events</td>\n",
       "        <td>song</td>\n",
       "        <td>character varying</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>staging_events</td>\n",
       "        <td>page</td>\n",
       "        <td>character varying</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>staging_events</td>\n",
       "        <td>method</td>\n",
       "        <td>character varying</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>staging_events</td>\n",
       "        <td>location</td>\n",
       "        <td>character varying</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>staging_events</td>\n",
       "        <td>level</td>\n",
       "        <td>character varying</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>staging_events</td>\n",
       "        <td>lastname</td>\n",
       "        <td>character varying</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>staging_events</td>\n",
       "        <td>gender</td>\n",
       "        <td>character varying</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>staging_events</td>\n",
       "        <td>firstname</td>\n",
       "        <td>character varying</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>staging_events</td>\n",
       "        <td>auth</td>\n",
       "        <td>character varying</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>staging_events</td>\n",
       "        <td>artist</td>\n",
       "        <td>character varying</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>staging_events</td>\n",
       "        <td>ts</td>\n",
       "        <td>timestamp without time zone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>staging_songs</td>\n",
       "        <td>num_songs</td>\n",
       "        <td>integer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>staging_songs</td>\n",
       "        <td>year</td>\n",
       "        <td>integer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>staging_songs</td>\n",
       "        <td>duration</td>\n",
       "        <td>double precision</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>staging_songs</td>\n",
       "        <td>artist_longitude</td>\n",
       "        <td>double precision</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>staging_songs</td>\n",
       "        <td>artist_latitude</td>\n",
       "        <td>double precision</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>staging_songs</td>\n",
       "        <td>title</td>\n",
       "        <td>character varying</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>staging_songs</td>\n",
       "        <td>song_id</td>\n",
       "        <td>character varying</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>staging_songs</td>\n",
       "        <td>artist_name</td>\n",
       "        <td>character varying</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>staging_songs</td>\n",
       "        <td>artist_location</td>\n",
       "        <td>character varying</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>staging_songs</td>\n",
       "        <td>artist_id</td>\n",
       "        <td>character varying</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('dim_artists', 'longitude', 'double precision'),\n",
       " ('dim_artists', 'latitude', 'double precision'),\n",
       " ('dim_artists', 'artist_id', 'character varying'),\n",
       " ('dim_artists', 'location', 'character varying'),\n",
       " ('dim_artists', 'name', 'character varying'),\n",
       " ('dim_songs', 'year', 'integer'),\n",
       " ('dim_songs', 'duration', 'double precision'),\n",
       " ('dim_songs', 'song_id', 'character varying'),\n",
       " ('dim_songs', 'artist_id', 'character varying'),\n",
       " ('dim_songs', 'title', 'character varying'),\n",
       " ('dim_time', 'weekday', 'integer'),\n",
       " ('dim_time', 'year', 'integer'),\n",
       " ('dim_time', 'month', 'integer'),\n",
       " ('dim_time', 'week', 'integer'),\n",
       " ('dim_time', 'day', 'integer'),\n",
       " ('dim_time', 'hour', 'integer'),\n",
       " ('dim_time', 'start_time', 'timestamp without time zone'),\n",
       " ('dim_users', 'user_id', 'integer'),\n",
       " ('dim_users', 'level', 'character varying'),\n",
       " ('dim_users', 'gender', 'character varying'),\n",
       " ('dim_users', 'last_name', 'character varying'),\n",
       " ('dim_users', 'first_name', 'character varying'),\n",
       " ('fact_songplays', 'songplay_id', 'integer'),\n",
       " ('fact_songplays', 'session_id', 'integer'),\n",
       " ('fact_songplays', 'user_id', 'integer'),\n",
       " ('fact_songplays', 'user_agent', 'character varying'),\n",
       " ('fact_songplays', 'location', 'character varying'),\n",
       " ('fact_songplays', 'artist_id', 'character varying'),\n",
       " ('fact_songplays', 'song_id', 'character varying'),\n",
       " ('fact_songplays', 'level', 'character varying'),\n",
       " ('fact_songplays', 'start_time', 'timestamp without time zone'),\n",
       " ('staging_events', 'registration', 'bigint'),\n",
       " ('staging_events', 'userid', 'integer'),\n",
       " ('staging_events', 'status', 'integer'),\n",
       " ('staging_events', 'sessionid', 'integer'),\n",
       " ('staging_events', 'iteminsession', 'integer'),\n",
       " ('staging_events', 'length', 'double precision'),\n",
       " ('staging_events', 'useragent', 'character varying'),\n",
       " ('staging_events', 'song', 'character varying'),\n",
       " ('staging_events', 'page', 'character varying'),\n",
       " ('staging_events', 'method', 'character varying'),\n",
       " ('staging_events', 'location', 'character varying'),\n",
       " ('staging_events', 'level', 'character varying'),\n",
       " ('staging_events', 'lastname', 'character varying'),\n",
       " ('staging_events', 'gender', 'character varying'),\n",
       " ('staging_events', 'firstname', 'character varying'),\n",
       " ('staging_events', 'auth', 'character varying'),\n",
       " ('staging_events', 'artist', 'character varying'),\n",
       " ('staging_events', 'ts', 'timestamp without time zone'),\n",
       " ('staging_songs', 'num_songs', 'integer'),\n",
       " ('staging_songs', 'year', 'integer'),\n",
       " ('staging_songs', 'duration', 'double precision'),\n",
       " ('staging_songs', 'artist_longitude', 'double precision'),\n",
       " ('staging_songs', 'artist_latitude', 'double precision'),\n",
       " ('staging_songs', 'title', 'character varying'),\n",
       " ('staging_songs', 'song_id', 'character varying'),\n",
       " ('staging_songs', 'artist_name', 'character varying'),\n",
       " ('staging_songs', 'artist_location', 'character varying'),\n",
       " ('staging_songs', 'artist_id', 'character varying')]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Execute the SQL command from sql_queries.py to check \n",
    "# the schema of the staging, fact and dim tables\n",
    "%sql $sql_queries.CHECK_SCHEMA_QUERY"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Check the Number of Rows Loaded into Each Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://dwhuser:***@dwhcluster.cejcerbeak3k.us-west-2.redshift.amazonaws.com:5439/dwh\n",
      "1 rows affected.\n",
      " * postgresql://dwhuser:***@dwhcluster.cejcerbeak3k.us-west-2.redshift.amazonaws.com:5439/dwh\n",
      "1 rows affected.\n",
      " * postgresql://dwhuser:***@dwhcluster.cejcerbeak3k.us-west-2.redshift.amazonaws.com:5439/dwh\n",
      "1 rows affected.\n",
      " * postgresql://dwhuser:***@dwhcluster.cejcerbeak3k.us-west-2.redshift.amazonaws.com:5439/dwh\n",
      "1 rows affected.\n",
      " * postgresql://dwhuser:***@dwhcluster.cejcerbeak3k.us-west-2.redshift.amazonaws.com:5439/dwh\n",
      "1 rows affected.\n",
      " * postgresql://dwhuser:***@dwhcluster.cejcerbeak3k.us-west-2.redshift.amazonaws.com:5439/dwh\n",
      "1 rows affected.\n",
      " * postgresql://dwhuser:***@dwhcluster.cejcerbeak3k.us-west-2.redshift.amazonaws.com:5439/dwh\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Table</th>\n",
       "      <th>Row Count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>staging_events</td>\n",
       "      <td>8056</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>staging_songs</td>\n",
       "      <td>14896</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>fact_songplays</td>\n",
       "      <td>6820</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>dim_users</td>\n",
       "      <td>104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>dim_songs</td>\n",
       "      <td>14896</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>dim_artists</td>\n",
       "      <td>10025</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>dim_time</td>\n",
       "      <td>6813</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Table  Row Count\n",
       "0  staging_events       8056\n",
       "1   staging_songs      14896\n",
       "2  fact_songplays       6820\n",
       "3       dim_users        104\n",
       "4       dim_songs      14896\n",
       "5     dim_artists      10025\n",
       "6        dim_time       6813"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Initialize an empty list to store the row counts for each table\n",
    "table_rowcounts = []\n",
    "\n",
    "# Iterate over pairs of table names and corresponding row count queries\n",
    "for table, query in zip(\n",
    "    sql_queries.TABLES, sql_queries.COUNT_ROWS_QUERIES\n",
    "):\n",
    "    # Execute each row count query using the SQL magic command \n",
    "    # and retrieve the result\n",
    "    row_count = %sql $query\n",
    "    # Append the table name and row count to the list\n",
    "    table_rowcounts.append([table, row_count[0][0]])\n",
    "    \n",
    "# Display the number of rows in each table in a pandas Dataframe\n",
    "# for better readability\n",
    "pd.DataFrame(table_rowcounts, columns=['Table', 'Row Count'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Examine the Query Performance of the Dimensional Model"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Below are **example analytic queries** that users can run for analysis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Question: What is the most played song of all time?\n",
      "\n",
      "    SELECT dS.title\n",
      "    FROM fact_songplays AS fS\n",
      "    JOIN dim_songs AS dS\n",
      "    ON fS.song_id = dS.song_id\n",
      "    GROUP BY dS.title\n",
      "    ORDER BY COUNT(fS.songplay_id) DESC\n",
      "    LIMIT 1\n",
      "\n",
      " * postgresql://dwhuser:***@dwhcluster.cejcerbeak3k.us-west-2.redshift.amazonaws.com:5439/dwh\n",
      "1 rows affected.\n",
      "+----------------+\n",
      "|     title      |\n",
      "+----------------+\n",
      "| You're The One |\n",
      "+----------------+\n",
      "\n",
      "Execution Time: 0.08168387413024902 seconds\n",
      "\n",
      "================================================================================\n",
      "\n",
      "Question: When is the highest usage time of day by hour for songs?\n",
      "\n",
      "    SELECT dT.hour\n",
      "    FROM fact_songplays AS fS\n",
      "    JOIN dim_time AS dT\n",
      "    ON fS.start_time = dT.start_time\n",
      "    GROUP BY dT.hour\n",
      "    ORDER BY COUNT(fS.songplay_id) DESC\n",
      "    LIMIT 1\n",
      "\n",
      " * postgresql://dwhuser:***@dwhcluster.cejcerbeak3k.us-west-2.redshift.amazonaws.com:5439/dwh\n",
      "1 rows affected.\n",
      "+------+\n",
      "| hour |\n",
      "+------+\n",
      "|  16  |\n",
      "+------+\n",
      "\n",
      "Execution Time: 0.08136773109436035 seconds\n",
      "\n",
      "================================================================================\n",
      "\n",
      "Question: Who are the top 3 most popular artists?\n",
      "\n",
      "    SELECT dA.name AS artist\n",
      "    FROM fact_songplays AS fS\n",
      "    JOIN dim_artists AS dA\n",
      "    ON fS.artist_id = dA.artist_id\n",
      "    GROUP BY artist\n",
      "    ORDER BY COUNT(fs.songplay_id) DESC\n",
      "    LIMIT 3\n",
      "\n",
      " * postgresql://dwhuser:***@dwhcluster.cejcerbeak3k.us-west-2.redshift.amazonaws.com:5439/dwh\n",
      "3 rows affected.\n",
      "+--------------------------------+\n",
      "|             artist             |\n",
      "+--------------------------------+\n",
      "|         Dwight Yoakam          |\n",
      "| Kid Cudi / Kanye West / Common |\n",
      "|            Kid Cudi            |\n",
      "+--------------------------------+\n",
      "\n",
      "Execution Time: 0.08150935173034668 seconds\n",
      "\n",
      "================================================================================\n",
      "\n",
      "Question: Which five users listen to songs the most between midnight and 1 AM?\n",
      "\n",
      "    SELECT dU.first_name || ' ' || dU.last_name AS user\n",
      "    FROM fact_songplays AS fS\n",
      "    JOIN dim_time AS dT\n",
      "    ON fS.start_time = dT.start_time\n",
      "    JOIN dim_users AS dU\n",
      "    ON fS.user_id = dU.user_id\n",
      "    WHERE dT.hour = 0\n",
      "    GROUP BY dU.first_name, dU.last_name\n",
      "    ORDER BY COUNT(fS.songplay_id) DESC\n",
      "    LIMIT 5\n",
      "\n",
      " * postgresql://dwhuser:***@dwhcluster.cejcerbeak3k.us-west-2.redshift.amazonaws.com:5439/dwh\n",
      "5 rows affected.\n",
      "+----------------+\n",
      "|      user      |\n",
      "+----------------+\n",
      "| Jayden Graves  |\n",
      "| Harper Barrett |\n",
      "|  Kate Harrell  |\n",
      "| Layla Griffin  |\n",
      "|  Sara Johnson  |\n",
      "+----------------+\n",
      "\n",
      "Execution Time: 0.0830831527709961 seconds\n",
      "\n",
      "================================================================================\n"
     ]
    }
   ],
   "source": [
    "# Iterate over each analytic question and corresponding SQL query\n",
    "for question, query in sql_queries.ANALYTIC_QUERIES.items():\n",
    "    # Print the question and SQL query for reference\n",
    "    print('\\nQuestion:', question)\n",
    "    print(query)\n",
    "    \n",
    "     # Measure the time taken to execute the SQL query\n",
    "    t0 = time()\n",
    "    answer = %sql $query\n",
    "    execution_time = time() - t0\n",
    "    \n",
    "    # Print the query result\n",
    "    print(answer)\n",
    "    # Print the execution time\n",
    "    print(\n",
    "        '\\nExecution Time: {} seconds\\n'.format(execution_time)\n",
    "    )\n",
    "    # Print a separator line for better readability\n",
    "    print('=' * 80)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The data model in Redshift is now ready for analytical workloads!"
   ]
  }
 ],
 "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.11.3"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": false,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}