Report.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "219904c6",
   "metadata": {},
   "source": [
    "# UDEMY COURSES DATA ANALYSIS REPORT\n",
    "\n",
    "In this notebook, we will analyze Udemy course data. The project relies on the Udemy Courses Data 2023 provided by Kaggle. It answers five simple questions to show my proficiency in SQL for data analysis.\n",
    "\n",
    "We will find answers to questions such as:\n",
    "\n",
    "* How many instructors and courses are there in total?\n",
    "\n",
    "* What are the 10 most popular courses? \n",
    "\n",
    "* What are the 10 most popular trainers? \n",
    "\n",
    "* What are the most viewed instructor's courses? \n",
    "\n",
    "* What are the 5 most popular data analysis courses?"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "453135d9",
   "metadata": {},
   "source": [
    "Jupyter notebooks can be powerful tools to connect to your remote database. They allow you to streamline, replicate, and document your data. Python Database (DB) APIs are compatible with various databases, and in particular, Python supports relational database systems. In this stage, using a Jupyter notebook, we will briefly see how to connect to a PostgreSQL database, which is a popular open-source relational database, and how to make queries in a Jupyter Notebook using Python language."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "583a3338",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: sqlalchemy in c:\\users\\gokcen\\anaconda3\\lib\\site-packages (2.0.5.post1)\n",
      "Requirement already satisfied: greenlet!=0.4.17 in c:\\users\\gokcen\\anaconda3\\lib\\site-packages (from sqlalchemy) (1.1.1)\n",
      "Requirement already satisfied: typing-extensions>=4.2.0 in c:\\users\\gokcen\\anaconda3\\lib\\site-packages (from sqlalchemy) (4.3.0)\n",
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    }
   ],
   "source": [
    "pip install sqlalchemy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "357e5435",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlalchemy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "ba4f1d30",
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = sqlalchemy.create_engine('postgresql://postgres:postgres@localhost/archive')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "0f9c2cbc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: ipython-sql in c:\\users\\gokcen\\anaconda3\\lib\\site-packages (0.5.0)\n",
      "Requirement already satisfied: ipython-genutils in c:\\users\\gokcen\\anaconda3\\lib\\site-packages (from ipython-sql) (0.2.0)\n",
      "Requirement already satisfied: sqlparse in c:\\users\\gokcen\\anaconda3\\lib\\site-packages (from ipython-sql) (0.4.3)\n",
      "Requirement already satisfied: ipython in c:\\users\\gokcen\\anaconda3\\lib\\site-packages (from ipython-sql) (7.31.1)\n",
      "Requirement already satisfied: prettytable in c:\\users\\gokcen\\anaconda3\\lib\\site-packages (from ipython-sql) (3.6.0)\n",
      "Requirement already satisfied: sqlalchemy>=2.0 in c:\\users\\gokcen\\anaconda3\\lib\\site-packages (from ipython-sql) (2.0.5.post1)\n",
      "Requirement already satisfied: six in c:\\users\\gokcen\\anaconda3\\lib\\site-packages (from ipython-sql) (1.16.0)\n",
      "Requirement already satisfied: greenlet!=0.4.17 in c:\\users\\gokcen\\anaconda3\\lib\\site-packages (from sqlalchemy>=2.0->ipython-sql) (1.1.1)\n",
      "Requirement already satisfied: typing-extensions>=4.2.0 in c:\\users\\gokcen\\anaconda3\\lib\\site-packages (from sqlalchemy>=2.0->ipython-sql) (4.3.0)\n",
      "Requirement already satisfied: matplotlib-inline in c:\\users\\gokcen\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (0.1.6)\n",
      "Requirement already satisfied: backcall in c:\\users\\gokcen\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (0.2.0)\n",
      "Requirement already satisfied: colorama in c:\\users\\gokcen\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (0.4.5)\n",
      "Requirement already satisfied: jedi>=0.16 in c:\\users\\gokcen\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (0.18.1)\n",
      "Requirement already satisfied: pygments in c:\\users\\gokcen\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (2.11.2)\n",
      "Requirement already satisfied: traitlets>=4.2 in c:\\users\\gokcen\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (5.1.1)\n",
      "Requirement already satisfied: pickleshare in c:\\users\\gokcen\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (0.7.5)\n",
      "Requirement already satisfied: setuptools>=18.5 in c:\\users\\gokcen\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (63.4.1)\n",
      "Requirement already satisfied: decorator in c:\\users\\gokcen\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (5.1.1)\n",
      "Requirement already satisfied: prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0 in c:\\users\\gokcen\\anaconda3\\lib\\site-packages (from ipython->ipython-sql) (3.0.20)\n",
      "Requirement already satisfied: wcwidth in c:\\users\\gokcen\\anaconda3\\lib\\site-packages (from prettytable->ipython-sql) (0.2.5)\n",
      "Requirement already satisfied: parso<0.9.0,>=0.8.0 in c:\\users\\gokcen\\anaconda3\\lib\\site-packages (from jedi>=0.16->ipython->ipython-sql) (0.8.3)\n",
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    }
   ],
   "source": [
    "pip install ipython-sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "6663e5ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "407cbbdc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "env: database_url=postgresql://postgres:postgres@localhost/archive\n"
     ]
    }
   ],
   "source": [
    "%env database_url=postgresql://postgres:postgres@localhost/archive"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "18fbdfbd",
   "metadata": {},
   "source": [
    "## How many instructors and courses are there in total?\n",
    "Let's start looking for answers to our questions.\n",
    "The first line of code will uniquely tell us how many instructors and courses there are in total."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "71401893",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>total_instructors</th>\n",
       "            <th>total_courses</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>32233</td>\n",
       "            <td>83104</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(32233, 83104)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT COUNT(DISTINCT instructors_id) AS total_instructors, \n",
    "COUNT(id) AS total_courses \n",
    "FROM courses;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4474ac25",
   "metadata": {},
   "source": [
    "## What are the 10 most popular courses?\n",
    "When looking for the most popular courses, we'll consider those with statistically high reviews and above average ratings."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "817bf5f7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/archive\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>title</th>\n",
       "            <th>rating</th>\n",
       "            <th>num_reviews</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>The Complete Python Bootcamp From Zero to Hero in Python</td>\n",
       "            <td>4.5927815</td>\n",
       "            <td>452973</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Microsoft Excel - Excel from Beginner to Advanced</td>\n",
       "            <td>4.659531</td>\n",
       "            <td>357442</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>The Complete 2023 Web Development Bootcamp</td>\n",
       "            <td>4.667258</td>\n",
       "            <td>263152</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>The Web Developer Bootcamp 2023</td>\n",
       "            <td>4.6961474</td>\n",
       "            <td>254711</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Angular - The Complete Guide (2023 Edition)</td>\n",
       "            <td>4.5926924</td>\n",
       "            <td>180257</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>100 Days of Code: The Complete Python Pro Bootcamp for 2023</td>\n",
       "            <td>4.6952515</td>\n",
       "            <td>177568</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Java Programming Masterclass updated to Java 17</td>\n",
       "            <td>4.5502763</td>\n",
       "            <td>177184</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>React - The Complete Guide (incl Hooks, React Router, Redux)</td>\n",
       "            <td>4.609824</td>\n",
       "            <td>176452</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ultimate AWS Certified Solutions Architect Associate SAA-C03</td>\n",
       "            <td>4.70473</td>\n",
       "            <td>168032</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>The Complete JavaScript Course 2023: From Zero to Expert!</td>\n",
       "            <td>4.728533</td>\n",
       "            <td>167670</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('The Complete Python Bootcamp From Zero to Hero in Python', Decimal('4.5927815'), Decimal('452973')),\n",
       " ('Microsoft Excel - Excel from Beginner to Advanced', Decimal('4.659531'), Decimal('357442')),\n",
       " ('The Complete 2023 Web Development Bootcamp', Decimal('4.667258'), Decimal('263152')),\n",
       " ('The Web Developer Bootcamp 2023', Decimal('4.6961474'), Decimal('254711')),\n",
       " ('Angular - The Complete Guide (2023 Edition)', Decimal('4.5926924'), Decimal('180257')),\n",
       " ('100 Days of Code: The Complete Python Pro Bootcamp for 2023', Decimal('4.6952515'), Decimal('177568')),\n",
       " ('Java Programming Masterclass updated to Java 17', Decimal('4.5502763'), Decimal('177184')),\n",
       " ('React - The Complete Guide (incl Hooks, React Router, Redux)', Decimal('4.609824'), Decimal('176452')),\n",
       " ('Ultimate AWS Certified Solutions Architect Associate SAA-C03', Decimal('4.70473'), Decimal('168032')),\n",
       " ('The Complete JavaScript Course 2023: From Zero to Expert!', Decimal('4.728533'), Decimal('167670'))]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT title, rating, num_reviews \n",
    "FROM courses\n",
    "WHERE rating >= (SELECT avg(rating) FROM courses) \n",
    "ORDER BY num_reviews DESC\n",
    "LIMIT 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8fda0db9",
   "metadata": {},
   "source": [
    "## What are the 10 most popular instructors?\n",
    "We'll consider the number of reviews when querying the most popular instructors. Looking by the results, Jose Portilla makes a good difference in this regard."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "2f0b7f50",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/archive\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>title</th>\n",
       "            <th>num_reviews</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Jose Portilla</td>\n",
       "            <td>452973</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Kyle Pew</td>\n",
       "            <td>357442</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Dr. Angela Yu</td>\n",
       "            <td>263152</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Colt Steele</td>\n",
       "            <td>254711</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Maximilian Schwarzmüller</td>\n",
       "            <td>180257</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Dr. Angela Yu</td>\n",
       "            <td>177568</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Tim Buchalka</td>\n",
       "            <td>177184</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Academind by Maximilian Schwarzmüller</td>\n",
       "            <td>176452</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Stephane Maarek | AWS Certified Cloud Practitioner,Solutions Architect,Developer</td>\n",
       "            <td>168032</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Jonas Schmedtmann</td>\n",
       "            <td>167670</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Jose Portilla', Decimal('452973')),\n",
       " ('Kyle Pew', Decimal('357442')),\n",
       " ('Dr. Angela Yu', Decimal('263152')),\n",
       " ('Colt Steele', Decimal('254711')),\n",
       " ('Maximilian Schwarzmüller', Decimal('180257')),\n",
       " ('Dr. Angela Yu', Decimal('177568')),\n",
       " ('Tim Buchalka', Decimal('177184')),\n",
       " ('Academind by Maximilian Schwarzmüller', Decimal('176452')),\n",
       " ('Stephane Maarek | AWS Certified Cloud Practitioner,Solutions Architect,Developer', Decimal('168032')),\n",
       " ('Jonas Schmedtmann', Decimal('167670'))]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT i.title, c.num_reviews \n",
    "FROM courses c INNER JOIN instructors i ON i.id = c.instructors_id \n",
    "ORDER BY num_reviews DESC LIMIT 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2616db26",
   "metadata": {},
   "source": [
    "## What are the most reviewed instructor's courses?\n",
    "In this line we see all the courses of the most popular instructor, sorted by their rating. The ratings are pretty good too."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "ba4da2fa",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/archive\n",
      "38 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>title</th>\n",
       "            <th>rating</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Python for Finance and Algorithmic Trading with QuantConnect</td>\n",
       "            <td>4.707246</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Complete Tensorflow 2 and Keras Deep Learning Bootcamp</td>\n",
       "            <td>4.68416</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Go Bootcamp: Master Golang with 1000+ Exercises and Projects</td>\n",
       "            <td>4.6828847</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>The Complete SQL Bootcamp: Go from Zero to Hero</td>\n",
       "            <td>4.6764865</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>PyTorch for Deep Learning with Python Bootcamp</td>\n",
       "            <td>4.67154</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Master Git and GitHub in 5 Days: Go from Zero to Hero</td>\n",
       "            <td>4.667742</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Python and Flask Bootcamp: Create Websites using Flask!</td>\n",
       "            <td>4.6664762</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Python for Machine Learning &amp; Data Science Masterclass</td>\n",
       "            <td>4.6310763</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Probability and Statistics for Business and Data Science</td>\n",
       "            <td>4.630266</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Math for Data Science Masterclass</td>\n",
       "            <td>4.629254</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>The Complete Tableau Bootcamp for Data Visualization</td>\n",
       "            <td>4.6259975</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Python for Data Science and Machine Learning Bootcamp</td>\n",
       "            <td>4.622137</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Data Science and Machine Learning Bootcamp with R</td>\n",
       "            <td>4.621786</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Interactive Python Dashboards with Plotly and Dash</td>\n",
       "            <td>4.6212773</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Python for Time Series Data Analysis</td>\n",
       "            <td>4.617966</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Python pour l&#x27;Analyse Financière &amp; Algorithmes de Trading</td>\n",
       "            <td>4.605028</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>The Complete Python Bootcamp From Zero to Hero in Python</td>\n",
       "            <td>4.5927815</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Google Cloud - Associate Cloud Engineer Certification Course</td>\n",
       "            <td>4.572871</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Django 4 and Python Full-Stack Developer Masterclass</td>\n",
       "            <td>4.569152</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Data Science Career Guide - Interview Preparation</td>\n",
       "            <td>4.5680137</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>NLP - Natural Language Processing with Python</td>\n",
       "            <td>4.565402</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Python for Computer Vision with OpenCV and Deep Learning</td>\n",
       "            <td>4.561217</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Spark and Python for Big Data with PySpark</td>\n",
       "            <td>4.5412364</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Web Automation with Selenium WebDriver and Python</td>\n",
       "            <td>4.5069556</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Leading Agile Teams with Jira Software Cloud</td>\n",
       "            <td>4.4850693</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Complete Excel Bootcamp: Zero to Hero in Excel</td>\n",
       "            <td>4.4801717</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Practical AI with Python and Reinforcement Learning</td>\n",
       "            <td>4.4764137</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Python for Data Structures, Algorithms, and Interviews!</td>\n",
       "            <td>4.4599075</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Complete Python 3 Masterclass Journey</td>\n",
       "            <td>4.4498806</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Python and Django Full Stack Web Developer Bootcamp</td>\n",
       "            <td>4.440449</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Deep Learning with PyTorch for Medical Image Analysis</td>\n",
       "            <td>4.426891</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Scala and Spark for Big Data and Machine Learning</td>\n",
       "            <td>4.393946</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Terraform for Google Cloud: Learn Infrastructure as Code</td>\n",
       "            <td>4.390502</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Python for Financial Analysis and Algorithmic Trading</td>\n",
       "            <td>4.3687706</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Learning Python for Data Analysis and Visualization Ver 1</td>\n",
       "            <td>4.323516</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Complete Guide to TensorFlow for Deep Learning with Python</td>\n",
       "            <td>4.209859</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Advanced SQL Bootcamp</td>\n",
       "            <td>4.0625734</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Complete Power BI Bootcamp: Go from Zero to Hero</td>\n",
       "            <td>3.3959134</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Python for Finance and Algorithmic Trading with QuantConnect', Decimal('4.707246')),\n",
       " ('Complete Tensorflow 2 and Keras Deep Learning Bootcamp', Decimal('4.68416')),\n",
       " ('Go Bootcamp: Master Golang with 1000+ Exercises and Projects', Decimal('4.6828847')),\n",
       " ('The Complete SQL Bootcamp: Go from Zero to Hero', Decimal('4.6764865')),\n",
       " ('PyTorch for Deep Learning with Python Bootcamp', Decimal('4.67154')),\n",
       " ('Master Git and GitHub in 5 Days: Go from Zero to Hero', Decimal('4.667742')),\n",
       " ('Python and Flask Bootcamp: Create Websites using Flask!', Decimal('4.6664762')),\n",
       " ('Python for Machine Learning & Data Science Masterclass', Decimal('4.6310763')),\n",
       " ('Probability and Statistics for Business and Data Science', Decimal('4.630266')),\n",
       " ('Math for Data Science Masterclass', Decimal('4.629254')),\n",
       " ('The Complete Tableau Bootcamp for Data Visualization', Decimal('4.6259975')),\n",
       " ('Python for Data Science and Machine Learning Bootcamp', Decimal('4.622137')),\n",
       " ('Data Science and Machine Learning Bootcamp with R', Decimal('4.621786')),\n",
       " ('Interactive Python Dashboards with Plotly and Dash', Decimal('4.6212773')),\n",
       " ('Python for Time Series Data Analysis', Decimal('4.617966')),\n",
       " (\"Python pour l'Analyse Financière & Algorithmes de Trading\", Decimal('4.605028')),\n",
       " ('The Complete Python Bootcamp From Zero to Hero in Python', Decimal('4.5927815')),\n",
       " ('Google Cloud - Associate Cloud Engineer Certification Course', Decimal('4.572871')),\n",
       " ('Django 4 and Python Full-Stack Developer Masterclass', Decimal('4.569152')),\n",
       " ('Data Science Career Guide - Interview Preparation', Decimal('4.5680137')),\n",
       " ('NLP - Natural Language Processing with Python', Decimal('4.565402')),\n",
       " ('Python for Computer Vision with OpenCV and Deep Learning', Decimal('4.561217')),\n",
       " ('Spark and Python for Big Data with PySpark', Decimal('4.5412364')),\n",
       " ('Web Automation with Selenium WebDriver and Python', Decimal('4.5069556')),\n",
       " ('Leading Agile Teams with Jira Software Cloud', Decimal('4.4850693')),\n",
       " ('Complete Excel Bootcamp: Zero to Hero in Excel', Decimal('4.4801717')),\n",
       " ('Practical AI with Python and Reinforcement Learning', Decimal('4.4764137')),\n",
       " ('Python for Data Structures, Algorithms, and Interviews!', Decimal('4.4599075')),\n",
       " ('Complete Python 3 Masterclass Journey', Decimal('4.4498806')),\n",
       " ('Python and Django Full Stack Web Developer Bootcamp', Decimal('4.440449')),\n",
       " ('Deep Learning with PyTorch for Medical Image Analysis', Decimal('4.426891')),\n",
       " ('Scala and Spark for Big Data and Machine Learning', Decimal('4.393946')),\n",
       " ('Terraform for Google Cloud: Learn Infrastructure as Code', Decimal('4.390502')),\n",
       " ('Python for Financial Analysis and Algorithmic Trading', Decimal('4.3687706')),\n",
       " ('Learning Python for Data Analysis and Visualization Ver 1', Decimal('4.323516')),\n",
       " ('Complete Guide to TensorFlow for Deep Learning with Python', Decimal('4.209859')),\n",
       " ('Advanced SQL Bootcamp', Decimal('4.0625734')),\n",
       " ('Complete Power BI Bootcamp: Go from Zero to Hero', Decimal('3.3959134'))]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT title,rating FROM courses\n",
    "WHERE instructors_id = (SELECT i.id FROM courses c INNER JOIN instructors i ON i.id = c.instructors_id \n",
    "                        ORDER BY num_reviews DESC\n",
    "                        LIMIT 1)\n",
    "ORDER BY rating DESC;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1767f4d3",
   "metadata": {},
   "source": [
    "## What are the 5 most popular data analysis courses?\n",
    "Here, when we query the most popular data analysis courses, we display the highest reviewed courses with a rating greater than 4,50."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "56012c42",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/archive\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>title</th>\n",
       "            <th>num_reviews</th>\n",
       "            <th>rating</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Microsoft Excel - Data Analysis with Excel Pivot Tables</td>\n",
       "            <td>46711</td>\n",
       "            <td>4.6264486</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Data Analysis with Pandas and Python</td>\n",
       "            <td>18831</td>\n",
       "            <td>4.668358</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Complete Introduction to Business Data Analysis</td>\n",
       "            <td>12161</td>\n",
       "            <td>4.602791</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Data Analysis Essentials Using Excel</td>\n",
       "            <td>9904</td>\n",
       "            <td>4.5659075</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>SQL for Data Analysis: Beginner MySQL Business Intelligence</td>\n",
       "            <td>7412</td>\n",
       "            <td>4.651596</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Microsoft Excel - Data Analysis with Excel Pivot Tables', Decimal('46711'), Decimal('4.6264486')),\n",
       " ('Data Analysis with Pandas and Python', Decimal('18831'), Decimal('4.668358')),\n",
       " ('Complete Introduction to Business Data Analysis', Decimal('12161'), Decimal('4.602791')),\n",
       " ('Data Analysis Essentials Using Excel', Decimal('9904'), Decimal('4.5659075')),\n",
       " ('SQL for Data Analysis: Beginner MySQL Business Intelligence', Decimal('7412'), Decimal('4.651596'))]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT title,num_reviews,rating \n",
    "FROM courses\n",
    "WHERE title ILIKE '%Data Analysis%' AND rating > 4.5\n",
    "ORDER BY num_reviews DESC\n",
    "LIMIT 5;"
   ]
  }
 ],
 "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}