exercise-writing-efficient-queries.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "d4505503",
   "metadata": {
    "papermill": {
     "duration": 0.003435,
     "end_time": "2023-05-22T15:27:20.199941",
     "exception": false,
     "start_time": "2023-05-22T15:27:20.196506",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "**This notebook is an exercise in the [Advanced SQL](https://www.kaggle.com/learn/advanced-sql) course.  You can reference the tutorial at [this link](https://www.kaggle.com/alexisbcook/writing-efficient-queries).**\n",
    "\n",
    "---\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "39353188",
   "metadata": {
    "papermill": {
     "duration": 0.002487,
     "end_time": "2023-05-22T15:27:20.205326",
     "exception": false,
     "start_time": "2023-05-22T15:27:20.202839",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "# Introduction\n",
    "\n",
    "Now, you'll use what you learned in the previous tutorial to improve the efficiency of several queries.\n",
    "\n",
    "Before you get started, run the following cell to set everything up."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "dad63e71",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-05-22T15:27:20.213310Z",
     "iopub.status.busy": "2023-05-22T15:27:20.212558Z",
     "iopub.status.idle": "2023-05-22T15:27:20.291471Z",
     "shell.execute_reply": "2023-05-22T15:27:20.289572Z"
    },
    "papermill": {
     "duration": 0.086334,
     "end_time": "2023-05-22T15:27:20.294356",
     "exception": false,
     "start_time": "2023-05-22T15:27:20.208022",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Setup Complete\n"
     ]
    }
   ],
   "source": [
    "# Set up feedback system\n",
    "from learntools.core import binder\n",
    "binder.bind(globals())\n",
    "from learntools.sql_advanced.ex4 import *\n",
    "print(\"Setup Complete\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a29a81b4",
   "metadata": {
    "papermill": {
     "duration": 0.004177,
     "end_time": "2023-05-22T15:27:20.302728",
     "exception": false,
     "start_time": "2023-05-22T15:27:20.298551",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "# Exercises\n",
    "\n",
    "### 1) You work for Pet Costumes International.\n",
    "\n",
    "You need to write three queries this afternoon. You have enough time to write working versions of all three, but only enough time to think about optimizing one of them.  Which of these queries is most worth optimizing?\n",
    "\n",
    "1. A software engineer wrote an app for the shipping department, to see what items need to be shipped and which aisle of the warehouse to go to for those items. She wants you to write the query. It will involve data that is stored in an `orders` table, a `shipments` table and a `warehouseLocation` table. The employees in the shipping department will pull up this app on a tablet, hit refresh, and your query results will be shown in a nice interface so they can see what costumes to send where.\n",
    "\n",
    "\n",
    "2. The CEO wants a list of all customer reviews and complaints… which are conveniently stored in a single `reviews` table. Some of the reviews are really long… because people love your pirate costumes for parrots, and they can’t stop writing about how cute they are.\n",
    "\n",
    "\n",
    "3. Dog owners are getting more protective than ever. So your engineering department has made costumes with embedded GPS trackers and wireless communication devices. They send the costumes’ coordinates to your database once a second. You then have a website where owners can find the location of their dogs (or at least the costumes they have for those dogs). For this service to work, you need a query that shows the most recent location for all costumes owned by a given human. This will involve data in a `CostumeLocations` table as well as a `CostumeOwners` table.\n",
    "\n",
    "So, which of these could benefit most from being written efficiently?  Set the value of the `query_to_optimize` variable below to one of `1`, `2`, or `3`.  (Your answer should have type **integer**.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "f9e49f57",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-05-22T15:27:20.312600Z",
     "iopub.status.busy": "2023-05-22T15:27:20.312242Z",
     "iopub.status.idle": "2023-05-22T15:27:20.323573Z",
     "shell.execute_reply": "2023-05-22T15:27:20.320847Z"
    },
    "papermill": {
     "duration": 0.018982,
     "end_time": "2023-05-22T15:27:20.326453",
     "exception": false,
     "start_time": "2023-05-22T15:27:20.307471",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.5, \"interactionType\": 1, \"questionType\": 1, \"questionId\": \"1_PetCostumes\", \"learnToolsVersion\": \"0.3.4\", \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\"}}, \"*\")"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<span style=\"color:#33cc33\">Correct:</span> \n",
       "\n",
       "\n",
       "`query_to_optimize = 3`\n",
       "\n",
       "Why **3**: Because data is sent for each costume at each second, this is the query that is likely to involve the most data (by far). And it will be run on a recurring basis. So writing this well could pay off on a recurring basis.\n",
       "\n",
       "Why not **1**: This is the second most valuable query to optimize. It will be run on a recurring basis, and it involves merges, which is commonly a place where you can make your queries more efficient\n",
       "\n",
       "Why not **2**: This sounds like it will be run only one time. So, it probably doesn’t matter if it takes a few seconds extra or costs a few cents more to run that one time. Also, it doesn’t involve JOINs. While the data has text fields (the reviews), that is the data you need. So, you can’t leave these out of your select query to save computation.\n"
      ],
      "text/plain": [
       "Correct: \n",
       "\n",
       "\n",
       "`query_to_optimize = 3`\n",
       "\n",
       "Why **3**: Because data is sent for each costume at each second, this is the query that is likely to involve the most data (by far). And it will be run on a recurring basis. So writing this well could pay off on a recurring basis.\n",
       "\n",
       "Why not **1**: This is the second most valuable query to optimize. It will be run on a recurring basis, and it involves merges, which is commonly a place where you can make your queries more efficient\n",
       "\n",
       "Why not **2**: This sounds like it will be run only one time. So, it probably doesn’t matter if it takes a few seconds extra or costs a few cents more to run that one time. Also, it doesn’t involve JOINs. While the data has text fields (the reviews), that is the data you need. So, you can’t leave these out of your select query to save computation."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Fill in your answer\n",
    "query_to_optimize = 3\n",
    "\n",
    "# Check your answer\n",
    "q_1.check()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "db610f53",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-05-22T15:27:20.338606Z",
     "iopub.status.busy": "2023-05-22T15:27:20.337884Z",
     "iopub.status.idle": "2023-05-22T15:27:20.350594Z",
     "shell.execute_reply": "2023-05-22T15:27:20.349858Z"
    },
    "papermill": {
     "duration": 0.021749,
     "end_time": "2023-05-22T15:27:20.353082",
     "exception": false,
     "start_time": "2023-05-22T15:27:20.331333",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"interactionType\": 2, \"questionType\": 1, \"questionId\": \"1_PetCostumes\", \"learnToolsVersion\": \"0.3.4\", \"valueTowardsCompletion\": 0.0, \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\", \"outcomeType\": 4}}, \"*\")"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<span style=\"color:#3366cc\">Hint:</span> Do the queries run on big or small datasets?  Do they need to be run many times, or just once?  Is the data spread over multiple tables, or contained in just one?"
      ],
      "text/plain": [
       "Hint: Do the queries run on big or small datasets?  Do they need to be run many times, or just once?  Is the data spread over multiple tables, or contained in just one?"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"interactionType\": 3, \"questionType\": 1, \"questionId\": \"1_PetCostumes\", \"learnToolsVersion\": \"0.3.4\", \"valueTowardsCompletion\": 0.0, \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\", \"outcomeType\": 4}}, \"*\")"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<span style=\"color:#33cc99\">Solution:</span> \n",
       "`query_to_optimize = 3`\n",
       "\n",
       "Why **3**: Because data is sent for each costume at each second, this is the query that is likely to involve the most data (by far). And it will be run on a recurring basis. So writing this well could pay off on a recurring basis.\n",
       "\n",
       "Why not **1**: This is the second most valuable query to optimize. It will be run on a recurring basis, and it involves merges, which is commonly a place where you can make your queries more efficient\n",
       "\n",
       "Why not **2**: This sounds like it will be run only one time. So, it probably doesn’t matter if it takes a few seconds extra or costs a few cents more to run that one time. Also, it doesn’t involve JOINs. While the data has text fields (the reviews), that is the data you need. So, you can’t leave these out of your select query to save computation.\n"
      ],
      "text/plain": [
       "Solution: \n",
       "`query_to_optimize = 3`\n",
       "\n",
       "Why **3**: Because data is sent for each costume at each second, this is the query that is likely to involve the most data (by far). And it will be run on a recurring basis. So writing this well could pay off on a recurring basis.\n",
       "\n",
       "Why not **1**: This is the second most valuable query to optimize. It will be run on a recurring basis, and it involves merges, which is commonly a place where you can make your queries more efficient\n",
       "\n",
       "Why not **2**: This sounds like it will be run only one time. So, it probably doesn’t matter if it takes a few seconds extra or costs a few cents more to run that one time. Also, it doesn’t involve JOINs. While the data has text fields (the reviews), that is the data you need. So, you can’t leave these out of your select query to save computation."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Lines below will give you a hint or solution code\n",
    "q_1.hint()\n",
    "q_1.solution()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "430f90b4",
   "metadata": {
    "papermill": {
     "duration": 0.003489,
     "end_time": "2023-05-22T15:27:20.360759",
     "exception": false,
     "start_time": "2023-05-22T15:27:20.357270",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "### 2) Make it easier to find Mitzie! \n",
    "\n",
    "You have the following two tables:\n",
    "\n",
    "![](https://storage.googleapis.com/kaggle-media/learn/images/E9jikOQ.png)\n",
    "\n",
    "The `CostumeLocations` table shows timestamped GPS data for all of the pet costumes in the database, where `CostumeID` is a unique identifier for each costume.  \n",
    "\n",
    "The `CostumeOwners` table shows who owns each costume, where the `OwnerID` column contains unique identifiers for each (human) owner.  Note that each owner can have more than one costume!  And, each costume can have more than one owner: this allows multiple individuals from the same household (all with their own, unique `OwnerID`) to access the locations of their pets' costumes.\n",
    "\n",
    "Say you need to use these tables to get the current location of one pet in particular: Mitzie the Dog recently ran off chasing a squirrel, but thankfully she was last seen in her hot dog costume!\n",
    "\n",
    "One of Mitzie's owners (with owner ID `MitzieOwnerID`) logs into your website to pull the last locations of every costume in his possession.  Currently, you get this information by running the following query:\n",
    "\n",
    "```sql\n",
    "WITH LocationsAndOwners AS \n",
    "(\n",
    "SELECT * \n",
    "FROM CostumeOwners co INNER JOIN CostumeLocations cl\n",
    "   ON co.CostumeID = cl.CostumeID\n",
    "),\n",
    "LastSeen AS\n",
    "(\n",
    "SELECT CostumeID, MAX(Timestamp)\n",
    "FROM LocationsAndOwners\n",
    "GROUP BY CostumeID\n",
    ")\n",
    "SELECT lo.CostumeID, Location \n",
    "FROM LocationsAndOwners lo INNER JOIN LastSeen ls \n",
    "\tON lo.Timestamp = ls.Timestamp AND lo.CostumeID = ls.CostumeID\n",
    "WHERE OwnerID = MitzieOwnerID\n",
    "```\n",
    "\n",
    "Is there a way to make this faster or cheaper?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "1af425d4",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-05-22T15:27:20.369982Z",
     "iopub.status.busy": "2023-05-22T15:27:20.369478Z",
     "iopub.status.idle": "2023-05-22T15:27:20.378344Z",
     "shell.execute_reply": "2023-05-22T15:27:20.376641Z"
    },
    "papermill": {
     "duration": 0.01682,
     "end_time": "2023-05-22T15:27:20.381178",
     "exception": false,
     "start_time": "2023-05-22T15:27:20.364358",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"interactionType\": 2, \"questionType\": 4, \"questionId\": \"2_Mitzie\", \"learnToolsVersion\": \"0.3.4\", \"valueTowardsCompletion\": 0.0, \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\", \"outcomeType\": 4}}, \"*\")"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<span style=\"color:#3366cc\">Hint:</span> Do you see any large merges in the query?"
      ],
      "text/plain": [
       "Hint: Do you see any large merges in the query?"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Line below will give you a hint\n",
    "q_2.hint()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "883750b9",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-05-22T15:27:20.392063Z",
     "iopub.status.busy": "2023-05-22T15:27:20.390942Z",
     "iopub.status.idle": "2023-05-22T15:27:20.398492Z",
     "shell.execute_reply": "2023-05-22T15:27:20.397332Z"
    },
    "papermill": {
     "duration": 0.015635,
     "end_time": "2023-05-22T15:27:20.401197",
     "exception": false,
     "start_time": "2023-05-22T15:27:20.385562",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"interactionType\": 3, \"questionType\": 4, \"questionId\": \"2_Mitzie\", \"learnToolsVersion\": \"0.3.4\", \"valueTowardsCompletion\": 0.0, \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\", \"outcomeType\": 4}}, \"*\")"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<span style=\"color:#33cc99\">Solution:</span> \n",
       "Yes. Working with the LocationsAndOwners table is very inefficient, because it’s a big table. There are a few options here, and which works best depends on database specifics. One likely improvement is\n",
       "\n",
       "```\n",
       "WITH CurrentOwnersCostumes AS\n",
       "(\n",
       "SELECT CostumeID \n",
       "FROM CostumeOwners \n",
       "WHERE OwnerID = MitzieOwnerID\n",
       "),\n",
       "OwnersCostumesLocations AS\n",
       "(\n",
       "SELECT cc.CostumeID, Timestamp, Location \n",
       "FROM CurrentOwnersCostumes cc INNER JOIN CostumeLocations cl\n",
       "    ON cc.CostumeID = cl.CostumeID\n",
       "),\n",
       "LastSeen AS\n",
       "(\n",
       "SELECT CostumeID, MAX(Timestamp)\n",
       "FROM OwnersCostumesLocations\n",
       "GROUP BY CostumeID\n",
       ")\n",
       "SELECT ocl.CostumeID, Location \n",
       "FROM OwnersCostumesLocations ocl INNER JOIN LastSeen ls \n",
       "    ON ocl.timestamp = ls.timestamp AND ocl.CostumeID = ls.costumeID\n",
       "```\n",
       "\n",
       "**Why is this better?**\n",
       "\n",
       "Instead of doing large merges and running calculations (like finding the last timestamp) for every costume, we discard the rows for other owners as the first step. So each subsequent step (like calculating the last timestamp) is working with something like 99.999% fewer rows than what was needed in the original query.\n",
       "\n",
       "Databases have something called “Query Planners” to optimize details of how a query executes even after you write it. Perhaps some query planner would figure out the ability to do this. But the original query as written would be very inefficient on large datasets.\n"
      ],
      "text/plain": [
       "Solution: \n",
       "Yes. Working with the LocationsAndOwners table is very inefficient, because it’s a big table. There are a few options here, and which works best depends on database specifics. One likely improvement is\n",
       "\n",
       "```\n",
       "WITH CurrentOwnersCostumes AS\n",
       "(\n",
       "SELECT CostumeID \n",
       "FROM CostumeOwners \n",
       "WHERE OwnerID = MitzieOwnerID\n",
       "),\n",
       "OwnersCostumesLocations AS\n",
       "(\n",
       "SELECT cc.CostumeID, Timestamp, Location \n",
       "FROM CurrentOwnersCostumes cc INNER JOIN CostumeLocations cl\n",
       "    ON cc.CostumeID = cl.CostumeID\n",
       "),\n",
       "LastSeen AS\n",
       "(\n",
       "SELECT CostumeID, MAX(Timestamp)\n",
       "FROM OwnersCostumesLocations\n",
       "GROUP BY CostumeID\n",
       ")\n",
       "SELECT ocl.CostumeID, Location \n",
       "FROM OwnersCostumesLocations ocl INNER JOIN LastSeen ls \n",
       "    ON ocl.timestamp = ls.timestamp AND ocl.CostumeID = ls.costumeID\n",
       "```\n",
       "\n",
       "**Why is this better?**\n",
       "\n",
       "Instead of doing large merges and running calculations (like finding the last timestamp) for every costume, we discard the rows for other owners as the first step. So each subsequent step (like calculating the last timestamp) is working with something like 99.999% fewer rows than what was needed in the original query.\n",
       "\n",
       "Databases have something called “Query Planners” to optimize details of how a query executes even after you write it. Perhaps some query planner would figure out the ability to do this. But the original query as written would be very inefficient on large datasets."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# View the solution (Run this code cell to receive credit!)\n",
    "q_2.solution()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a38b8cdf",
   "metadata": {
    "papermill": {
     "duration": 0.004086,
     "end_time": "2023-05-22T15:27:20.409713",
     "exception": false,
     "start_time": "2023-05-22T15:27:20.405627",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "# Conclusion\n",
    "\n",
    "Congratulations!  You have made it to the end of the micro-course.  You should be proud of all of your hard work!\n",
    "\n",
    "There is still a lot of room to build knowledge and experience.  In particular, you're encouraged to look through our BigQuery **[datasets](https://kaggle.com/datasets)** to practice your new skills."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5d6a89c2",
   "metadata": {
    "papermill": {
     "duration": 0.003975,
     "end_time": "2023-05-22T15:27:20.418269",
     "exception": false,
     "start_time": "2023-05-22T15:27:20.414294",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "---\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "*Have questions or comments? Visit the [course discussion forum](https://www.kaggle.com/learn/advanced-sql/discussion) to chat with other learners.*"
   ]
  }
 ],
 "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.10.10"
  },
  "papermill": {
   "default_parameters": {},
   "duration": 12.981598,
   "end_time": "2023-05-22T15:27:21.447639",
   "environment_variables": {},
   "exception": null,
   "input_path": "__notebook__.ipynb",
   "output_path": "__notebook__.ipynb",
   "parameters": {},
   "start_time": "2023-05-22T15:27:08.466041",
   "version": "2.4.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}