SQLSummativeLab.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction\n",
    "\n",
    "In part 1 of this assessment, you will complete several requested SQL queries in order to extract data, analyze, and provide insights from a single provided SQL database. You will also visualize the key results of 3 of these queries. There are also several 'Reflection' questions that ask you to write out a text based answer in the provided markdown cell. Following the guided question and answer section, in part 2 you will explore a second dataset on your own using SQL in order to conduct a preliminary analysis. You will be asked to produce a very short slide presentation highlighting the work you did for this second section.\n",
    "\n",
    "## Objectives\n",
    "You will be able to:\n",
    "- Interpret \"word problems\" and translate them into SQL queries\n",
    "- Decide and perform whichever type of JOIN is best for retrieving desired data\n",
    "- Use GROUP BY statements to apply aggregate functions like COUNT, MAX, MIN, and SUM\n",
    "- Use the HAVING clause to compare different aggregates\n",
    "- Write subqueries to decompose complex queries\n",
    "- Visualize data using matplotlib, seaborn, or pandas\n",
    "- Choose the correct chart type based on the given data\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Part 1: Guided SQL Queries\n",
    "\n",
    "### Your Task: Querying a Customer Database\n",
    "\n",
    "![toy car picture](images/toycars.jpg)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Business Understanding\n",
    "Your employer sells wholesale miniature models of products such as classic cars, motorcycles, and planes. They want you to pull several reports on different segments of their past customers, in order to better understand past sales as well as determine which customers will receive promotional material. They are also interested in investigating which products have performed the best, as well as having several smaller asks.\n",
    "\n",
    "In addition to providing the requested data from the SQL database you have also been asked to create some basic visuals to display some of the more insightful information. It is up to your discretion to choose the correct plot/chart type for the data in question. **Questions that want you to visualize the results will be explicitly marked**.\n",
    "\n",
    "### Data Understanding\n",
    "You may remember this database from a previous lab. As a refresher, here's the ERD diagram for this database:\n",
    "\n",
    "![ERD picture](images/ERD.png)\n",
    "\n",
    "The queries you are asked to write will become more complex over the course of the lab.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Getting Started\n",
    "For this assessment you are expected to make use of both sqlite3 and the Pandas libraries in order to write, execute, and return SQL queries as a Pandas DataFrame. Assign each returned answer as its own explicit variable.\n",
    "\n",
    "For the visualization piece you are expected to utilize either Pandas, Seaborn, or Matplotlib to create your visuals. Make sure you are providing verbose labels and titles according to the data you are being asked to visualize. Do not worry too much about choosing a 'style' or 'context' instead focus on conveying the requested information correctly.\n",
    "\n",
    "### Step 1: Connect to Data\n",
    "\n",
    "In the cell below\n",
    "- Import the necessary libraries\n",
    "- Establish a connection to the database data.sqlite"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace None with your code\n",
    "# Imports\n",
    "None\n",
    "\n",
    "# Create connection to database\n",
    "None"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 2: Limited Edition California Product\n",
    "The California sales rep team is interested in running promotional material for a new limited edition model they are releasing based on the famous San Francisco Cable Cars. This product will only be available to customer stores based in California and given its high price value they want to first target promotional material to existing California customers with a high credit limit. Upon communicating with the accounting department, a credit limit of over 25,000 is considered to be high. \n",
    "\n",
    "Execute a SQl query that returns which customers the sales rep team wants to market to first."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace None with your code\n",
    "None"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 3: International Collectable Campaign\n",
    "\n",
    "The international sales rep team has reached out to you to help them identify partners for a 'Collectable' marketing campaign that highlights the potential collectors value in purchasing these model kits. They want to try and promote a 'collect them all' mentality. The team had a great idea to partner with any of their international customers (non-US) who have \"Collect\" in their name as a tie in to the larger theme.\n",
    "\n",
    "Execute a SQL that returns the customers in question."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace None with your code\n",
    "None"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reflection Question:\n",
    "\n",
    "Describe the WHERE clause you used in the above query to a non-technical manager who wants to be ensured that you are properly filtering and only selecting the requested data. How is the operator and conditional expression you are using acting to accomplish this?\n",
    "\n",
    "## Type your answer in markdown here!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 4: USA Credit and Inventory Policy - Visual Required\n",
    "The USA based product team is planning to adjust its credit policies and inventory allocation strategy based on the average credit limit of its customers. They would like to target this strategy at a state level with several goals in mind. \n",
    "1. Optimize inventory distribution:\n",
    "    - States with higher average credit limits might be able to place larger orders, justifying priority in inventory allocation.\n",
    "    - This could help ensure that states with more purchasing power always have products in stock.\n",
    "2. Tailor credit policies:\n",
    "    - Adjust credit limits for new customers based on the state average.\n",
    "    - Identify states where they might be too conservative or too liberal with credit limits.\n",
    "3. Target marketing and sales efforts:\n",
    "    - Focus promotional campaigns on states with higher credit limits, potentially leading to larger orders.\n",
    "    - Develop strategies to increase sales in states with lower average credit limits.\n",
    "\n",
    "Execute a SQl query that returns the information required to address this ask."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace None with your code\n",
    "None"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once you have the information returned in a dataframe, select an appropriate visualization to represent this data. You are welcome to utilize matplotlib, seaborn, or pandas plotting to produce your visual. Ensure that it has a verbose title and axis labels!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace None with your visual code\n",
    "# Produce a visual to represent the average credit limit by state\n",
    "None "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 5: Top Customers - Visual Required\n",
    "The company is approaching its 10 year anniversary and wants to acknowledge and thank its top customers with personalized communication. They have asked you to determine the top 10 customers based on the total amount of payments made, making sure to return the customer name for clarity. \n",
    "\n",
    "Execute a SQl query that returns the information required to address this ask.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace None with your code\n",
    "None"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once you have the information returned in a dataframe, select an appropriate visualization to represent this data. You are welcome to utilize matplotlib, seaborn, or pandas plotting to produce your visual. Ensure that it has a verbose title and axis labels!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace None with your visual code\n",
    "# Produce a visual to represent the top ten customers in terms of total payments\n",
    "None "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 6: Top Customer + Product Quantities\n",
    "The product team is running an analysis on popular and common products sold to each customer in order to try and determine what new products they should be looking at to include in their catalog. This data will also be used by individual sales reps to recommend similar products to each customer next time they place an order. \n",
    "\n",
    "They have asked you to query information, for each customer, about any product they have purchased 10 or more units of. In addition they would like the full set of data to be sorted in ascending order by the total amount purchased.\n",
    "\n",
    "Execute a SQl query that returns the information required to address this ask.\n",
    "\n",
    "Hint: For this one, you'll need to make use of HAVING, GROUP BY, and ORDER BY — make sure you get the order of them correct!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace None with you code\n",
    "None"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 7: Product Analysis - Visual Required\n",
    "\n",
    "The product team is looking into the demand across its different product lines. They are conducting a comprehensive review of its product portfolio and inventory management strategies. You have been asked to query data pertaining to each different product line, that contains the total quantity ordered and the total number of products for each respective product line. By examining the number of products and total quantity ordered for each product line, the company aims to:\n",
    "1. Optimize product mix:\n",
    "    - Identify which product lines have the most diverse offerings (high number of products)\n",
    "    - Determine which lines are most popular (high total quantity ordered)\n",
    "    - Compare if lines with more products necessarily lead to more orders\n",
    "2. Improve inventory management:\n",
    "    - Adjust stock levels based on the popularity of each product line\n",
    "    - Identify potential overstocking in lines with low order quantities\n",
    "    - Ensure adequate variety in high-performing product lines\n",
    "3. Adjust marketing strategy:\n",
    "    - Focus promotional efforts on product lines with high potential (many products but lower order quantities)\n",
    "    - Capitalize on the popularity of high-performing lines in marketing campaigns\n",
    "4. Advise Product development:\n",
    "    - Invest in expanding product ranges for lines with high order quantities\n",
    "    - Consider phasing out or revamping product lines with low numbers of products and low order quantities\n",
    "\n",
    "Hint: Think about how you can and might have to utilize SQL DISTINCT statement\n",
    "\n",
    "Execute a SQl query that returns the information required to address this ask."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace None with your code\n",
    "None"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once you have the information returned in a dataframe, select an appropriate visualization to represent the relationship between total quantity ordered and the number of products in order to perform a preliminary investigation into the question of if more products lead to more orders. You are welcome to utilize matplotlib, seaborn, or pandas plotting to produce your visual. Ensure that it has a verbose title and axis labels!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace None with your visual code\n",
    "# Produce a visual to represent the the relation between number of products and the total amount ordered\n",
    "None "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reflection Question:\n",
    "\n",
    "Please explain your choice in the type of visual you used in order to highlight and represent the data from the above query. In a non-technical manner explain why that chart type makes sense for the information being conveyed. What does this visual convey in the context of the question it was asked for?\n",
    "\n",
    "## Type your answer in markdown here!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 8: Remote Offices\n",
    "Upper management is considering a shift to hybrid and remote work for certain locations and roles. They have tasked you with providing them data about employees who work in any office that has fewer than 5 total employees so they can better understand how to support those employees remotely when offices are shut down. \n",
    "\n",
    "Be sure to include information about the employees job and supervisor so management can adjust everyone to remote work properly.\n",
    "\n",
    "Hint: Utilize a subquery to find the relevant offices\n",
    "\n",
    "Execute a SQl query that returns the information required to address this ask."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace None with your code\n",
    "None"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reflection Question:\n",
    "\n",
    "Describe how you decided on the subquery that you used in the query above? This answer can be technically in nature, describing your thought process in how the main query is utilizing the subquery to return the correct data.\n",
    "\n",
    "## Type your answer in markdown here!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 9: Close the Connection\n",
    "\n",
    "Now that you are finished executing your queries and retrieving the required information you always want to make sure to close the connection to your database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace None with your code\n",
    "None"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### End of Guided Section\n",
    "In this initial portion of the assessment, you produced several data queries and visualizations for a model company, mainly focused around its customer and product data. You wrote and engineered specific SQL queries to address pertinent questions and asks from the company. Along the way, you utilized many of the major concepts and keywords associated with SQL SELECT queries: FROM, WHERE, GROUP BY, HAVING, ORDER BY, JOIN, SUM, COUNT, and AVG.\n",
    "\n",
    "## Part 2: Exploratory Analysis with SQL\n",
    "In this open-ended exploratory section, you will analyze real-world data from the movie industry. As a data analyst, you have the freedom to investigate questions and topics that intrigue you within this dataset. The database schema and Entity-Relationship Diagram (ERD) are provided below for your reference. A general overview and instructions are also provided below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "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>type</th>\n",
       "      <th>name</th>\n",
       "      <th>tbl_name</th>\n",
       "      <th>rootpage</th>\n",
       "      <th>sql</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>table</td>\n",
       "      <td>movie_basics</td>\n",
       "      <td>movie_basics</td>\n",
       "      <td>2</td>\n",
       "      <td>CREATE TABLE \"movie_basics\" (\\n\"movie_id\" TEXT...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>table</td>\n",
       "      <td>directors</td>\n",
       "      <td>directors</td>\n",
       "      <td>3</td>\n",
       "      <td>CREATE TABLE \"directors\" (\\n\"movie_id\" TEXT,\\n...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>table</td>\n",
       "      <td>known_for</td>\n",
       "      <td>known_for</td>\n",
       "      <td>4</td>\n",
       "      <td>CREATE TABLE \"known_for\" (\\n\"person_id\" TEXT,\\...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>table</td>\n",
       "      <td>movie_akas</td>\n",
       "      <td>movie_akas</td>\n",
       "      <td>5</td>\n",
       "      <td>CREATE TABLE \"movie_akas\" (\\n\"movie_id\" TEXT,\\...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>table</td>\n",
       "      <td>movie_ratings</td>\n",
       "      <td>movie_ratings</td>\n",
       "      <td>6</td>\n",
       "      <td>CREATE TABLE \"movie_ratings\" (\\n\"movie_id\" TEX...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>table</td>\n",
       "      <td>persons</td>\n",
       "      <td>persons</td>\n",
       "      <td>7</td>\n",
       "      <td>CREATE TABLE \"persons\" (\\n\"person_id\" TEXT,\\n ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>table</td>\n",
       "      <td>principals</td>\n",
       "      <td>principals</td>\n",
       "      <td>8</td>\n",
       "      <td>CREATE TABLE \"principals\" (\\n\"movie_id\" TEXT,\\...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>table</td>\n",
       "      <td>writers</td>\n",
       "      <td>writers</td>\n",
       "      <td>9</td>\n",
       "      <td>CREATE TABLE \"writers\" (\\n\"movie_id\" TEXT,\\n  ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    type           name       tbl_name  rootpage  \\\n",
       "0  table   movie_basics   movie_basics         2   \n",
       "1  table      directors      directors         3   \n",
       "2  table      known_for      known_for         4   \n",
       "3  table     movie_akas     movie_akas         5   \n",
       "4  table  movie_ratings  movie_ratings         6   \n",
       "5  table        persons        persons         7   \n",
       "6  table     principals     principals         8   \n",
       "7  table        writers        writers         9   \n",
       "\n",
       "                                                 sql  \n",
       "0  CREATE TABLE \"movie_basics\" (\\n\"movie_id\" TEXT...  \n",
       "1  CREATE TABLE \"directors\" (\\n\"movie_id\" TEXT,\\n...  \n",
       "2  CREATE TABLE \"known_for\" (\\n\"person_id\" TEXT,\\...  \n",
       "3  CREATE TABLE \"movie_akas\" (\\n\"movie_id\" TEXT,\\...  \n",
       "4  CREATE TABLE \"movie_ratings\" (\\n\"movie_id\" TEX...  \n",
       "5  CREATE TABLE \"persons\" (\\n\"person_id\" TEXT,\\n ...  \n",
       "6  CREATE TABLE \"principals\" (\\n\"movie_id\" TEXT,\\...  \n",
       "7  CREATE TABLE \"writers\" (\\n\"movie_id\" TEXT,\\n  ...  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Run this cell without changes\n",
    "import zipfile\n",
    "\n",
    "zip_file_path = 'im.db.zip'\n",
    "extract_to_path = './'\n",
    "\n",
    "with zipfile.ZipFile(zip_file_path, 'r') as zip_ref:\n",
    "    zip_ref.extractall(extract_to_path)\n",
    "\n",
    "# Connection\n",
    "conn4 = sqlite3.connect('im.db')\n",
    "\n",
    "# Schema\n",
    "schema_df = pd.read_sql(\"\"\"\n",
    "SElECT * FROM sqlite_master                        \n",
    "\"\"\", conn4)\n",
    "schema_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The Data\n",
    "\n",
    "![movie ERD](images/movie_data_erd.jpeg)\n",
    "### Database Content:\n",
    "\n",
    "- Source: IMDB\n",
    "- Time Range: Movies released between 2010 and 2019\n",
    "- Note: Exclude any movies with a start_year after 2019 as this data is not current or accurate\n",
    "\n",
    "Available Data Categories:\n",
    "- Genre\n",
    "- Runtime\n",
    "- Personnel (writers, directors, actors)\n",
    "- Movie ratings\n",
    "\n",
    "### Objectives:\n",
    "\n",
    "Initial Exploration:\n",
    "- Use SQL in combination with Pandas to explore the database\n",
    "- Identify interesting trends, patterns, or relationships in the data\n",
    "\n",
    "Business Question Formulation:\n",
    "- Develop at least one substantial business question for deeper analysis\n",
    "- Ensure the question is relevant, specific, and can be addressed with the available data\n",
    "\n",
    "Data Cleaning Assessment:\n",
    "- Identify potential data cleaning tasks necessary for your deeper analysis\n",
    "- Note: You are not required to perform the cleaning, only to recognize and list the necessary tasks\n",
    "\n",
    "Null Value Handling:\n",
    "- Be aware that the dataset contains null values in certain fields\n",
    "- Exclude these null values from your exploration\n",
    "- Do not attempt to input or fill in missing information\n",
    "\n",
    "### Deliverables:\n",
    "\n",
    "You need to produce a short slide presentation (3-5 slides) that highlights the three key deliverables below. Utilize a data visualization to support the second deliverable.\n",
    "\n",
    "1. A summary of your initial data exploration findings\n",
    "    - Can be bulleted or sentence form\n",
    "2. At least one well-formulated business question for further analysis\n",
    "    - Should stem from a relevant trend or pattern your initial exploration identified\n",
    "3. A list of potential data cleaning tasks identified during your exploration\n",
    "    - This can and should include things like data normalization/standardization and null handling\n",
    "\n",
    "Tips for Success:\n",
    "\n",
    "Begin with broad exploratory queries to understand the data's scope and content. Then focus on honing in on interesting relationships between different data categories. Consider industry trends, audience preferences, or financial aspects when formulating your business question. Pay attention to data quality issues, inconsistencies, or limitations that might affect your analysis. Remember, the goal is to demonstrate your analytical thinking and ability to derive meaningful insights from complex datasets. Good luck with your exploration!\n",
    "\n",
    "NOTE: You do not need to explore every aspect of this database. Find something that you think is interesting or relevant about the data and focus your exploration there."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Begin your code here\n",
    "None"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "test-env01",
   "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.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}