SQL for Data Analytics - Practice Problems 1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<font size=\"3\" color=\"brown\">Author : UTSAV AWASTHI</font><br>\n",
    "<font size=\"1\" color=\"brown\">About Me: http://iamawesome.com/ </font> <br>\n",
    "Subscribe to my YouTube channel <a href=\"https://www.youtube.com/channel/UCJpp55YcP5swWj8TmIOd0OA/\">here</a><br> \n",
    "For serious conversations related to career and jobs, follow me on <a href=\"https://www.linkedin.com/in/utsavawasthi/\">LinkedIn</a><br>\n",
    "For low-effort dank memes and one-liners, follow me on <a href=\"https://twitter.com/utsavsaidwhat\">Twitter</a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div align=\"center\">\n",
    "    <font size = 3 color=\"orange\" style=\"font-family: 'Comic Sans MS'\">// ज्ञानं परमं बलम् //</font><br>\n",
    "</div>\n",
    "<img src = \"https://cloudblogs.microsoft.com/uploads/prod/sites/32/2020/05/SQL.png\" width=\"30%\" height=\"30%\">\n",
    "<br>\n",
    "\n",
    "<div align=\"center\" id=\"contents\"> <font size=\"5\" color=\"orange\"><b>STRUCTURE QUERY LANGUAGE</b></font> </div>\n",
    "<br>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h2> Importing Datasets </h2>\n",
    "Let's import the relevant datasets on which we will run SQL queries. Press Ctrl+Enter to run the codes in each of the boxes below.<br><br>\n",
    "I highly advice you to download the excel file (available <a href=\"https://github.com/ItsAwSM/SQL-for-Data-Analytics/blob/main/sales_database.xlsx?raw=true\">here</a>) which has the schema and the data which we will query in this assignment. Spend some time to go through the schema, understand the relationships between different datasets and then analyze all the datasets in excel once. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pip install pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pip install pandasql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pip install xlrd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import pandasql as ps"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Importing customer dataset\n",
    "customer_dim = pd.read_excel(\"https://github.com/ItsAwSM/SQL-for-Data-Analytics/blob/main/sales_database.xlsx?raw=true\", \n",
    "                             sheet_name=\"customer_dim\")\n",
    "customer_dim.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Importing product dataset\n",
    "product_dim = pd.read_excel(\"https://github.com/ItsAwSM/SQL-for-Data-Analytics/blob/main/sales_database.xlsx?raw=true\", \n",
    "                            sheet_name=\"product_dim\")\n",
    "product_dim.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Importing delivery person dataset\n",
    "delivery_person_dim = pd.read_excel(\"https://github.com/ItsAwSM/SQL-for-Data-Analytics/blob/main/sales_database.xlsx?raw=true\",\n",
    "                                    sheet_name=\"delivery_person_dim\")\n",
    "delivery_person_dim.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#Importing pincode dataset\n",
    "pincode_dim = pd.read_excel(\"https://github.com/ItsAwSM/SQL-for-Data-Analytics/blob/main/sales_database.xlsx?raw=true\", \n",
    "                            sheet_name=\"pincode_dim\")\n",
    "pincode_dim.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "#Importing order dataset\n",
    "order_dim = pd.read_excel(\"https://github.com/ItsAwSM/SQL-for-Data-Analytics/blob/main/sales_database.xlsx?raw=true\", \n",
    "                          sheet_name=\"order_dim\")\n",
    "order_dim.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Sample question</b><br>\n",
    "You need to write the SQL query between the space mentioned in the box below and then press Ctrl+Enter to run the query. Here is an example - "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Q0.</b> How many customers are male and how many are female?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ans = ps.sqldf(\"\"\"\n",
    "---------------------WRITE QUERY BELOW---------------------\n",
    "\n",
    "\n",
    "\n",
    "---------------------WRITE QUERY ABOVE---------------------\n",
    "\"\"\")\n",
    "\n",
    "ans"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h2> Questions on Basic SQL</h2>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Q1.</b> How many customers do not have DOB information available?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ans = ps.sqldf(\"\"\"\n",
    "---------------------WRITE QUERY BELOW---------------------\n",
    "\n",
    "\n",
    "\n",
    "---------------------WRITE QUERY ABOVE---------------------\n",
    "\"\"\")\n",
    "\n",
    "ans"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Q2.</b> How many customers are there in each pincode and gender combination?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ans = ps.sqldf(\"\"\"\n",
    "---------------------WRITE QUERY BELOW---------------------\n",
    "\n",
    "\n",
    "\n",
    "---------------------WRITE QUERY ABOVE---------------------\n",
    "\"\"\")\n",
    "\n",
    "ans"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Q3.</b> Print product name and mrp for products which have more than 50000 MRP"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ans = ps.sqldf(\"\"\"\n",
    "---------------------WRITE QUERY BELOW---------------------\n",
    "\n",
    "\n",
    "\n",
    "---------------------WRITE QUERY ABOVE---------------------\n",
    "\"\"\")\n",
    "\n",
    "ans"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Q4.</b> How many delivery personal are there in each pincode?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ans = ps.sqldf(\"\"\"\n",
    "---------------------WRITE QUERY BELOW---------------------\n",
    "\n",
    "\n",
    "\n",
    "---------------------WRITE QUERY ABOVE---------------------\n",
    "\"\"\")\n",
    "\n",
    "ans"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Q5.</b> For each Pin code, print the count of orders, sum of total amount paid, average amount paid, maximum amount paid, minimum amount paid for the transactions which were paid by 'cash'. Take only 'buy' order types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ans = ps.sqldf(\"\"\"\n",
    "---------------------WRITE QUERY BELOW---------------------\n",
    "\n",
    "\n",
    "\n",
    "---------------------WRITE QUERY ABOVE---------------------\n",
    "\"\"\")\n",
    "\n",
    "ans"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Q6.</b> For each delivery_person_id, print the count of orders and total amount paid for product_id = 12350 or 12348 and total units > 8. Sort the output by total amount paid in descending order. Take only 'buy' order types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ans = ps.sqldf(\"\"\"\n",
    "---------------------WRITE QUERY BELOW---------------------\n",
    "\n",
    "\n",
    "\n",
    "---------------------WRITE QUERY ABOVE---------------------\n",
    "\"\"\")\n",
    "\n",
    "ans"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Q7.</b> Print the Full names (first name plus last name) for customers that have email on \"gmail.com\"?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ans = ps.sqldf(\"\"\"\n",
    "---------------------WRITE QUERY BELOW---------------------\n",
    "\n",
    "\n",
    "\n",
    "---------------------WRITE QUERY ABOVE---------------------\n",
    "\"\"\")\n",
    "\n",
    "ans"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Q8.</b> How many orders had #units between 1-3, 4-6 and 7+? Take only 'buy' order types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ans = ps.sqldf(\"\"\"\n",
    "---------------------WRITE QUERY BELOW---------------------\n",
    "\n",
    "\n",
    "\n",
    "---------------------WRITE QUERY ABOVE---------------------\n",
    "\"\"\")\n",
    "\n",
    "ans"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Q9.</b> Which pincode has average amount paid more than 150,000? Take only 'buy' order types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ans = ps.sqldf(\"\"\"\n",
    "---------------------WRITE QUERY BELOW---------------------\n",
    "\n",
    "\n",
    "\n",
    "---------------------WRITE QUERY ABOVE---------------------\n",
    "\"\"\")\n",
    "\n",
    "ans"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Q10.</b> Create following columns from order_dim data - \n",
    "<ol>\n",
    "    <li>order_date</li>\n",
    "    <li>Order day</li>\n",
    "    <li>Order month</li>\n",
    "    <li>Order year</li> \n",
    "</ol>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ans = ps.sqldf(\"\"\"\n",
    "---------------------WRITE QUERY BELOW---------------------\n",
    "\n",
    "\n",
    "\n",
    "---------------------WRITE QUERY ABOVE---------------------\n",
    "\"\"\")\n",
    "\n",
    "ans.sample(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Q11.</b> How many total orders were there in each month and how many of them were returned? Add a column for return rate too.<br>\n",
    "return rate = (100.0 * total return orders) / total buy orders<br>\n",
    "Hint: You will need to combine SUM() with CASE WHEN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "ans = ps.sqldf(\"\"\"\n",
    "---------------------WRITE QUERY BELOW---------------------\n",
    "\n",
    "\n",
    "\n",
    "---------------------WRITE QUERY ABOVE---------------------\n",
    "\"\"\")\n",
    "\n",
    "ans"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h2> Questions on SQL Joins</h2>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Q12.</b> How many units have been sold by each brand? Also get total returned units for each brand."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ans = ps.sqldf(\"\"\"\n",
    "---------------------WRITE QUERY BELOW---------------------\n",
    "\n",
    "\n",
    "\n",
    "---------------------WRITE QUERY ABOVE---------------------\n",
    "\"\"\")\n",
    "\n",
    "ans"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Q13.</b> How many distinct customers and delivery boys are there in each state?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ans = ps.sqldf(\"\"\"\n",
    "---------------------WRITE QUERY BELOW---------------------\n",
    "\n",
    "\n",
    "\n",
    "---------------------WRITE QUERY ABOVE---------------------\n",
    "\"\"\")\n",
    "\n",
    "ans"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Q14.</b> For every customer, print how many total units were ordered, how many units were ordered from their primary_pincode and how many were ordered not from the primary_pincode. Also calulate the percentage of total units which were ordered from primary_pincode(remember to multiply the numerator by 100.0). Sort by the percentage column in descending order."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ans = ps.sqldf(\"\"\"\n",
    "---------------------WRITE QUERY BELOW---------------------\n",
    "\n",
    "\n",
    "\n",
    "---------------------WRITE QUERY ABOVE---------------------\n",
    "\"\"\")\n",
    "\n",
    "ans"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Q15.</b> For each product name, print the sum of number of units, total amount paid, total displayed selling price, total mrp of these units, and finally the net discount from selling price (i.e. 100.0 - 100.0 * total amount paid / total displayed selling price) AND the net discount from mrp (i.e. 100.0 - 100.0 * total amount paid / total mrp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ans = ps.sqldf(\"\"\"\n",
    "---------------------WRITE QUERY BELOW---------------------\n",
    "\n",
    "\n",
    "\n",
    "---------------------WRITE QUERY ABOVE---------------------\n",
    "\"\"\")\n",
    "\n",
    "ans"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Q16.</b> For every order_id (exclude returns), get the product name and calculate the discount percentage from selling price. Sort by highest discount and print only those rows where discount percentage was above 10.10%."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "ans = ps.sqldf(\"\"\"\n",
    "---------------------WRITE QUERY BELOW---------------------\n",
    "\n",
    "\n",
    "\n",
    "---------------------WRITE QUERY ABOVE---------------------\n",
    "\"\"\")\n",
    "\n",
    "ans"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Q17.</b> Using the per unit procurement cost in product_dim, find which product category has made the most profit in both absolute amount and percentage<br>\n",
    "Absolute Profit = Total Amt Sold - Total Procurement Cost<br>\n",
    "Percentage Profit = 100.0 * Total Amt Sold / Total Procurement Cost - 100.0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "ans = ps.sqldf(\"\"\"\n",
    "---------------------WRITE QUERY BELOW---------------------\n",
    "\n",
    "\n",
    "\n",
    "---------------------WRITE QUERY ABOVE---------------------\n",
    "\"\"\")\n",
    "\n",
    "ans"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Q18.</b> For every delivery person(use their name), print the total number of order ids (exclude returns) by month in seperate columns i.e. there should be one row for each delivery_person_id and 12 columns for every month in the year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "ans = ps.sqldf(\"\"\"\n",
    "---------------------WRITE QUERY BELOW---------------------\n",
    "\n",
    "\n",
    "\n",
    "---------------------WRITE QUERY ABOVE---------------------\n",
    "\"\"\")\n",
    "\n",
    "ans"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Q19.</b> For each gender - male and female - find the absolute and percentage profit (like in Q16) by product name. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "ans = ps.sqldf(\"\"\"\n",
    "---------------------WRITE QUERY BELOW---------------------\n",
    "\n",
    "\n",
    "\n",
    "---------------------WRITE QUERY ABOVE---------------------\n",
    "\"\"\")\n",
    "\n",
    "ans"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Q20.</b> Generally the more numbers of units you buy, the more discount seller will give you. For 'Dell AX420' is there a relationship between number of units ordered and average discount from selling price? Take only 'buy' order types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "ans = ps.sqldf(\"\"\"\n",
    "---------------------WRITE QUERY BELOW---------------------\n",
    "\n",
    "\n",
    "\n",
    "---------------------WRITE QUERY ABOVE---------------------\n",
    "\"\"\")\n",
    "\n",
    "ans"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And lastly, remember this :) -"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<font size = 10 color=\"green\">\"</font><br>\n",
    "<div align = center> \n",
    "    <font size = 5 color=\"green\" style=\"font-family: 'Comic Sans MS'\">Pass on what you have learned</font><br>\n",
    "</div>\n",
    "<div align = right> \n",
    "    <font size = 10 color=\"green\"t>\"</font><br>\n",
    "</div>\n",
    "<img align = \"right\" src = \"https://orig00.deviantart.net/ff5e/f/2013/102/4/4/yoda_by_valval-d61g9rh.png\" width=\"20%\" height=\"20%\">"
   ]
  }
 ],
 "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}