techhelp/python_sql_tech_session.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Python + SQL Tech Session\n",
    "\n",
    "Today we'll be covering:\n",
    "1. Connecting to the database from python\n",
    "1. Using templated SQL in python\n",
    "1. getting data into and out of postgres efficiently\n",
    "1. Advanced SQL\n",
    "    - CTEs (WITH clauses)\n",
    "    - window functions\n",
    "    - indices / check plan\n",
    "    - temp tables\n",
    "\n",
    "### Some initial setup\n",
    "Requirements:\n",
    "- You should have a database.yaml file in your home directory with your credentials - please check if it's there.\n",
    "\n",
    "Downloading the materials we'll need:\n",
    "1. SSH to the class server\n",
    "2. Go to your working directory in your project folder using `cd your_working_directory` (/mnt/data/......)\n",
    "3. Download the notebook: `wget https://raw.githubusercontent.com/dssg/mlforpublicpolicylab/master/techhelp/python_sql_tech_session.ipynb`\n",
    "4. Download the sql template example: `wget https://raw.githubusercontent.com/dssg/mlforpublicpolicylab/master/techhelp/tech_session_template.sql`\n",
    "5. Take a look at the sql template: `less tech_session_template.sql` (Type `q` to exit)\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import packages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "import pandas as pd\n",
    "from sqlalchemy import create_engine\n",
    "import yaml\n",
    "\n",
    "import ohio.ext.pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## TOPIC 1: Connect to the database from python"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('database.yaml', 'r') as f:\n",
    "    db_params = yaml.safe_load(f)\n",
    "\n",
    "db_params['db'] = 'group_students_database'\n",
    "engine = create_engine('postgresql://{user}:{password}@{host}:{port}/{dbname}'.format(\n",
    "  host=db_params['host'],\n",
    "  port=db_params['port'],\n",
    "  dbname=db_params['db'],\n",
    "  user=db_params['user'],\n",
    "  password=db_params['pass']    \n",
    "))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We're connected to a database with data from the DonorsChoose organization. It has a few useful tables:\n",
    "- `donorschoose.projects` -- general information about projects\n",
    "- `donorschoose.resources` -- detailed information about requested resources\n",
    "- `donorschoose.essays` -- project titles and descriptions\n",
    "- `donorschoose.donations` -- separate record for each donation to a project\n",
    "\n",
    "There's also a `sketch` schema you can use to create tables in"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Simple select statement with sqlalchemy engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"SELECT projectid, schoolid, resource_type FROM donorschoose.projects LIMIT 3\"\n",
    "\n",
    "result_set = engine.execute(sql)\n",
    "for rec in result_set:\n",
    "    print(rec)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pandas will give a little cleaner output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"SELECT projectid, schoolid, resource_type FROM donorschoose.projects LIMIT 3\"\n",
    "\n",
    "pd.read_sql(sql, engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Simple Table Manipulation with sqlalchemy (we'll do something more efficient below)\n",
    "\n",
    "Let's create a little table to track your stocks of halloween candy (fill in your andrew id below)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "andrew_id =  # FILL IN YOUR andrew_id HERE!\n",
    "candy_table = '{}_candy'.format(andrew_id)\n",
    "table_schema = 'sketch'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Execute an appropriate CREATE statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "create_sql = '''CREATE TABLE IF NOT EXISTS {}.{} (\n",
    "    candy_type varchar NULL,\n",
    "    amount int,\n",
    "    units varchar\n",
    ");'''.format(table_schema, candy_table)\n",
    "\n",
    "engine.execute(create_sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**IMPORTANT NOTE**: Statements that modify the state of the database will not be physically reflected until we tell the connection to commit these changes. If you went into DBeaver now, you still wouldn't see this new table!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine.execute(\"COMMIT\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's insert a few records (again note that we have to **commit** for the records to show up):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "insert_sql = '''INSERT INTO {}.{}\n",
    "    (candy_type, amount, units)\n",
    "    VALUES(%s, %s, %s);\n",
    "'''.format(table_schema, candy_table)\n",
    "\n",
    "records_to_insert = [('snickers', 10, 'bars'), ('candy corn', 5, 'bags'), ('peanut butter cups', 15, 'cups')]\n",
    "\n",
    "for record in records_to_insert:\n",
    "    engine.execute(insert_sql, record)\n",
    "\n",
    "engine.execute(\"COMMIT\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's look at the results:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"SELECT * FROM {}.{}\".format(table_schema, candy_table)\n",
    "\n",
    "pd.read_sql(sql, engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Clean up: drop the table and commit:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "drop_sql = \"DROP TABLE {}.{}\".format(table_schema, candy_table)\n",
    "\n",
    "engine.execute(drop_sql)\n",
    "engine.execute(\"COMMIT\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## TOPIC 2: Using Templated SQL\n",
    "\n",
    "Templating SQL statements and filling them in dynamically with python can be very helpful as you're transforming data for your projects, for instance, creating features, labels, and matrices for different temporal validation splits in your data.\n",
    "\n",
    "We've actually been doing a little bit of this already (e.g., filling in table names and insert values above), but let's look at a couple of examples in more detail with the donors choose data. Suppose we wanted to look at the sets of projects posted on a few given days:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql_template = \"\"\"\n",
    "SELECT projectid, resource_type, poverty_level, date_posted\n",
    "FROM donorschoose.projects\n",
    "WHERE date_posted::DATE = '{}'::DATE\n",
    "\"\"\"\n",
    "\n",
    "results = []\n",
    "for dt in ['2014-05-01', '2014-04-15', '2014-04-01']:\n",
    "    sql = sql_template.format(dt)\n",
    "    results.append(pd.read_sql(sql, engine))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Do some quick checks:\n",
    "1. How many result sets did we get back?\n",
    "1. Look at the first few results of one of the sets, are they all on the right date?\n",
    "1. How many projects were posted on each of these days?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Number of result sets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# First few records of one set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Number of projects on each date"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Some simple data visualization\n",
    "\n",
    "We won't go into detail here, but just to provide a quick example. See the matplot (or seaborn) documentation for more plot types and examples."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ix = 0\n",
    "df = results[ix].groupby('resource_type')['projectid'].count().reset_index()\n",
    "dt = results[ix]['date_posted'].max()\n",
    "\n",
    "fig, ax = plt.subplots()\n",
    "ax.bar('resource_type', 'projectid', data=df)\n",
    "ax.set_title('Counts by resource type for %s' % dt)\n",
    "ax.set_ylabel('Number of Projects')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Templated SQL stored in a file\n",
    "\n",
    "If your queries get long or complex, you might want to move them out to separate files to keep your code a bit cleaner. We've provided an example to work with in `tech_session_template.sql` -- let's read that in here.\n",
    "\n",
    "Note that here we're just making use of basic python templating here, but if you want to use more complex logic in your templates, check out packages like [Jinja2](https://jinja.palletsprojects.com/en/2.11.x/)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read the template file\n",
    "with open('tech_session_template.sql', 'r') as f:\n",
    "    sql_template = f.read()\n",
    "\n",
    "# Look at the contents:\n",
    "print(sql_template)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Looks like we'll need a few parameters:**\n",
    "- table_schema\n",
    "- table_name\n",
    "- state_list\n",
    "- start_dt\n",
    "- end_dt\n",
    "\n",
    "Notice as well that we've explicitly encoded all of these columns by hand, but you might want to think about how you might construct the sets of columns for one-hot encoded categoricals programmatically from the data, as well as the other types of features we've discussed (like aggregations in different time windows)..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "table_schema = 'donorschoose'\n",
    "table_name = 'projects'\n",
    "state_list = ['CA', 'NY', 'PA']\n",
    "start_dt = '2014-03-14'\n",
    "end_dt = '2014-04-30'\n",
    "\n",
    "sql = sql_template.format(\n",
    "    table_schema=table_schema,\n",
    "    table_name=table_name,\n",
    "    state_list=state_list,\n",
    "    start_dt=start_dt,\n",
    "    end_dt=end_dt\n",
    ")\n",
    "\n",
    "# Let's take a look...\n",
    "print(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Looks like the square brackets in that state list will generate an error!**\n",
    "\n",
    "Let's try formatting it before doing the templating:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def list_to_string(l, dtype='string'):\n",
    "    if dtype=='string':\n",
    "        return ','.join([\"'%s'\" % elm for elm in l])\n",
    "    else:\n",
    "        return ','.join([\"%s\" % elm for elm in l])\n",
    "\n",
    "\n",
    "state_list = list_to_string(['CA', 'NY', 'PA'])\n",
    "\n",
    "print(state_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = sql_template.format(\n",
    "    table_schema=table_schema,\n",
    "    table_name=table_name,\n",
    "    state_list=state_list,\n",
    "    start_dt=start_dt,\n",
    "    end_dt=end_dt\n",
    ")\n",
    "\n",
    "# Let's take a look...\n",
    "print(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Looks better!** Let's try running it now..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_sql(sql, engine)\n",
    "\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## TOPIC 3: Getting data into and out of postgres efficiently\n",
    "\n",
    "At the command line, one very efficient way of getting data into postgres is to stream it to a `COPY` statement on `STDIN`, this might look something like:\n",
    "```\n",
    "cat my_file.csv | psql -h database.mlpolicylab.dssg.io {group_database} -c \"COPY {schema}.{table} FROM STDIN CSV HEADER\"\n",
    "```\n",
    "(more details in the [postgres documentation](https://www.postgresql.org/docs/11/sql-copy.html))\n",
    "\n",
    "Similarly, you can use the `\\copy` command from within `psql` itself -- you can find [documentation here](https://www.postgresql.org/docs/11/app-psql.html) (seach for \"\\copy\").\n",
    "\n",
    "For today, we'll focus on a package called `ohio` that provides efficient tools for moving data between postgres and python. `ohio` provides interfaces for both `pandas` dataframes and `numpy` arrays, but we'll focus on the `pandas` tools here, which are provided via `import ohio.ext.pandas` (see the [docs for the numpy examples](https://github.com/dssg/ohio#extensions-for-numpy))\n",
    "\n",
    "Note that `ohio` is dramatically more efficient than the built-in `df.to_sql()` (see the benchmarking graph below). The pandas function tries to be agnostic about SQL flavor by inserting data row-by-row, while `ohio` uses postgres-specific copy functionality to move the data much more quickly (and with lower memory overhead as well):\n",
    "\n",
    "![ohio benchmarking](https://raw.githubusercontent.com/dssg/ohio/0.5.0/doc/img/profile-copy-from-dataframe-to-databas-1555458507.svg?sanitize=true)\n",
    "\n",
    "Let's try it out by re-creating our halloween candy table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "andrew_id =  # FILL IN YOUR andrew_id HERE!\n",
    "candy_table = '{}_candy'.format(andrew_id)\n",
    "table_schema = 'sketch'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "create_sql = '''CREATE TABLE IF NOT EXISTS {}.{} (\n",
    "    candy_type varchar NULL,\n",
    "    amount int,\n",
    "    units varchar\n",
    ");'''.format(table_schema, candy_table)\n",
    "\n",
    "engine.execute(create_sql)\n",
    "engine.execute(\"COMMIT\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Inserting data with df.pg_copy_to()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame({\n",
    "    'candy_type': ['snickers', 'cookies', 'candy apples', 'peanut butter cups', 'candy corn'],\n",
    "    'amount': [1,1,2,3,5],\n",
    "    'units': ['bars', 'cookies', 'apples', 'cups', 'bags']\n",
    "})\n",
    "\n",
    "# The ohio package adds a `pg_copy_to` method to your dataframes...\n",
    "df.pg_copy_to(candy_table, engine, schema=table_schema, index=False, if_exists='append')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reading data with pd.DataFrame.pg_copy_from()\n",
    "\n",
    "We can read the data from the table we just created using `pg_copy_from`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "result_df = pd.DataFrame.pg_copy_from(candy_table, engine, schema=table_schema)\n",
    "\n",
    "result_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that `pg_copy_from` can accept a query as well:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"\"\"\n",
    "SELECT\n",
    "    CASE WHEN candy_type IN ('snickers', 'cookies', 'peanut butter cups') THEN 'has chocolate' ELSE 'non-chocolate' END AS chocolate_flag,\n",
    "    SUM(amount) AS total_number\n",
    "FROM {}.{}\n",
    "GROUP BY 1\n",
    "\"\"\".format(table_schema, candy_table)\n",
    "\n",
    "result_df = pd.DataFrame.pg_copy_from(sql, engine)\n",
    "\n",
    "result_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## TOPIC 4: Advanced SQL\n",
    "\n",
    "Finally for today, we want to talk about a few more advanced SQL functions that will likely be helpful as you're starting to prepare your features and training/test matrices. We **strongly encourage** you to do as much of that data manipulation as you can in the database, as postgres is well-optimized for this sort of work. The functions here should help make that work a bit easier as well.\n",
    "\n",
    "The idea here is to give you an overview of some of the things that are possible that you might want to explore further. You can find a more in-depth [tutorial here](https://dssg.github.io/hitchhikers-guide/curriculum/2_data_exploration_and_analysis/advanced_sql/), with links out to additional documentation as well."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### CTEs (WITH clauses)\n",
    "\n",
    "Common table expressions (CTEs), also known as WITH clauses, are a better alternative to subqueries both in terms of code readability as well as (in some cases) performance improvements. They can allow you to break up a complex query into consituent parts, making the logic of your code a little easier to follow.\n",
    "\n",
    "By way of example, suppose we wanted to calculate the fraction of different types of projects (based on their requested type of resource) that were fully funded in MD in January 2013. Here's how we might do that with CTEs:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"\"\"\n",
    "WITH md_projects AS (\n",
    "    SELECT *\n",
    "    FROM donorschoose.projects\n",
    "    WHERE school_state='MD'\n",
    "        AND date_posted::DATE BETWEEN '2013-01-01'::DATE AND '2013-01-31'::DATE\n",
    ")\n",
    ", total_donations AS (\n",
    "    SELECT p.projectid, COALESCE(SUM(d.donation_total), 0) AS total_amount\n",
    "    FROM md_projects p\n",
    "    LEFT JOIN donorschoose.donations d USING(projectid)\n",
    "    GROUP BY 1\n",
    ")\n",
    ", fully_funded AS (\n",
    "    SELECT p.*, td.total_amount,\n",
    "        CASE WHEN td.total_amount > p.total_price_excluding_optional_support THEN 1 ELSE 0 END AS funded_flag\n",
    "    FROM md_projects p\n",
    "    LEFT JOIN total_donations td USING(projectid)\n",
    ")\n",
    "SELECT resource_type, COUNT(*) AS num_projects, AVG(funded_flag) AS frac_funded\n",
    "FROM fully_funded\n",
    "GROUP BY 1\n",
    "ORDER BY 3 DESC\n",
    "\"\"\"\n",
    "\n",
    "pd.read_sql(sql, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "### HANDS-ON: For all the MD projects posted in January 2013 that received any donations\n",
    "###           what is the average fraction of donations coming from teachers by resource type?\n",
    "###           (note: the donations table has a boolean `is_teacher_acct` column that will be useful)\n",
    "\n",
    "sql = \"\"\"\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "pd.read_sql(sql, engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Analytic (Window) Functions\n",
    "\n",
    "Postgres provides powerful functionality for calculating complex metrics such as within-group aggregates, running averages, etc., called \"window functions\" (because they operate over a defined window of the data relative to a given row):\n",
    "- They are similar to aggregate functions, but instead of operating on groups of rows to produce a single row, they act on rows related to the current row to produce the same amount of rows.\n",
    "- There are several window functions like `row_number`, `rank`, `ntile`, `lag`, `lead`, `first_value`, `last_value`, `nth_value`.\n",
    "- And you can use any aggregation functions: `sum`, `count`, `avg`, `json_agg`, `array_agg`, etc\n",
    "\n",
    "Supposed we want to answer a couple questions:\n",
    "- What fraction of all projects in MD are posted by each schoolid?\n",
    "- What is the most recently posted project for each school in MD?\n",
    "- Calculate a running average of the total ask amount of the 4 most recent projects at a given school (say, `schoolid='ff2695b8b7f3ade678358f6e5c621c1e'`)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## HANDS-ON: Try answering those questions with SELECT, GROUP BY, HAVING, AND WHERE alone"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's look at how we'd answer these questions with window functions...\n",
    "\n",
    "**Fraction of projects by school**\n",
    "\n",
    "Here, we'll group by schools but calculate the number of projects across all schools in MD using:\n",
    "\n",
    "`SUM(COUNT(*)) OVER ()`\n",
    "\n",
    "In that statement, `COUNT(*)` is the number of projects at the given school, then we're summing that count across all the aggregated rows with `SUM(.) OVER ()`. There, the `OVER ()` indicates the window across which to take the sum -- in this case, an empty window (that is, `()`) indicates using all records in the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "result_df = pd.read_sql(\"\"\"\n",
    "SELECT schoolid, \n",
    "       COUNT(*) AS num_projects, \n",
    "       1.000*COUNT(*)/SUM(COUNT(*)) OVER () AS frac_at_school\n",
    "FROM donorschoose.projects\n",
    "WHERE school_state = 'MD'\n",
    "GROUP BY 1\n",
    "ORDER BY 3 DESC\n",
    "\"\"\", engine)\n",
    "\n",
    "result_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Most recent project by school**\n",
    "\n",
    "Here, we'll use `row_number` to rank the projects (without ties) within school and by posting date. Note that the window here, `(PARTITION BY schoolid ORDER BY date_posted DESC)` means: within each school id, calculate a row number ordered by the posting date in descending order (so the most recent project by a given school will have `rn=1`, the second most recent will have `rn=2`, and so on).\n",
    "\n",
    "We do this row number calculation in a CTE, allowing us to pick out the most recent project for each school simply by looking for those with `rn=1` in a subsequent step:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "result_df = pd.read_sql(\"\"\"\n",
    "WITH school_rns AS (\n",
    "    SELECT *, row_number() OVER (PARTITION BY schoolid ORDER BY date_posted::DATE DESC) AS rn\n",
    "    FROM donorschoose.projects\n",
    "    WHERE school_state = 'MD'\n",
    ")\n",
    "SELECT *\n",
    "FROM school_rns\n",
    "WHERE rn=1\n",
    ";\n",
    "\"\"\", engine)\n",
    "\n",
    "result_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Running average of ask from last four projects**\n",
    "\n",
    "Here, we use postgres's functionality to restrict a window to certain rows relative to the given row. Our window is:\n",
    "```\n",
    "(PARTITION BY schoolid ORDER BY date_posted ASC ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)\n",
    "```\n",
    "That is,\n",
    "- `PARTITION BY schoolid`: Do the calculation among records at the same school\n",
    "- `ORDER BY date_posted ASC`: Order the records by posting date (earliest first)\n",
    "- `ROWS BETWEEN 3 PRECEDING AND CURRENT ROW`: Given this ordering, calculate the average across the four most recent rows (including the current row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "result_df = pd.read_sql(\"\"\"\n",
    "SELECT date_posted, projectid, schoolid, total_price_excluding_optional_support AS current_ask,\n",
    "      AVG(total_price_excluding_optional_support) OVER (\n",
    "          PARTITION BY schoolid ORDER BY date_posted::DATE ASC\n",
    "          ROWS BETWEEN 3 PRECEDING AND CURRENT ROW\n",
    "      ) AS running_avg_ask\n",
    "FROM donorschoose.projects\n",
    "WHERE schoolid = 'ff2695b8b7f3ade678358f6e5c621c1e'\n",
    "ORDER BY date_posted::DATE DESC\n",
    ";\n",
    "\"\"\", engine)\n",
    "\n",
    "result_df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Days since last project was posted**\n",
    "\n",
    "We can use the `lag()` window function to get the date of the most recent previously-posted project (see also `last_value` for more flexibility):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "result_df = pd.read_sql(\"\"\"\n",
    "SELECT date_posted, projectid, schoolid, total_price_excluding_optional_support AS current_ask,\n",
    "      date_posted::DATE - (lag(date_posted) OVER (PARTITION BY schoolid ORDER BY date_posted::DATE ASC))::DATE AS days_since_last_proj\n",
    "FROM donorschoose.projects\n",
    "WHERE schoolid = 'ff2695b8b7f3ade678358f6e5c621c1e'\n",
    "ORDER BY date_posted::DATE DESC\n",
    ";\n",
    "\"\"\", engine)\n",
    "\n",
    "result_df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# What happens when we hit the end of the series?\n",
    "result_df.tail(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice the `NaN` (will be `NULL` in postgres) for the first record that doesn't have any previously-posted project, so you'd have to think about how you wanted to handle these edge cases in your feature development."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Indices / Checking the Query Plan\n",
    "\n",
    "Indices are particularly critical to the performance of postgres queries, especially as the data gets larger. You should think about adding indices to tables based on columns that will frequently be used for joins or filtering rows with `WHERE` clauses.\n",
    "\n",
    "A useful tool for understanding how the database will treat a given query is checking the query plan by using the `EXPLAIN` keyword before a `SELECT` statement:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Eliminate column width truncating\n",
    "pd.set_option('display.max_colwidth', None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.read_sql(\"\"\"\n",
    "EXPLAIN SELECT * FROM donorschoose.projects WHERE projectid = '32943bb1063267de6ed19fc0ceb4b9a7'\n",
    "\"\"\", engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that picking out a specific project is making use of the index via `Index Scan`.\n",
    "\n",
    "By contrast, if we select projects for a given school:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.read_sql(\"\"\"\n",
    "EXPLAIN SELECT * FROM donorschoose.projects WHERE schoolid = 'ff2695b8b7f3ade678358f6e5c621c1e'\n",
    "\"\"\", engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here, `Seq Scan` tells us that postgres has to scan the entire table to find the right projects, which can be very expensive (especially with joins!). Also note how much higher the overall estimated cost is for this query in the first row here than for the query above.\n",
    "\n",
    "Likewise for joins, compare the two query plans below:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.read_sql(\"\"\"\n",
    "EXPLAIN SELECT * FROM donorschoose.projects JOIN donorschoose.donations USING(projectid)\n",
    "\"\"\", engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## NOTE: Please don't actually run this query without the select!!!\n",
    "\n",
    "pd.read_sql(\"\"\"\n",
    "EXPLAIN SELECT * FROM donorschoose.projects p JOIN donorschoose.donations d ON d.donation_timestamp::DATE > p.date_posted::DATE\n",
    "\"\"\", engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**CREATING INDICES**\n",
    "\n",
    "When you need to create indices as you build tables for your project, you can use this syntax:\n",
    "\n",
    "```\n",
    "CREATE INDEX ON {schema}.{table}({column});\n",
    "```\n",
    "\n",
    "Note that you can also specify a list of columns. If the given column (or set of columns) is a unique key for the table, you can get additional gains by declaring it as a primary key instead of simply creating an index:\n",
    "\n",
    "```\n",
    "ALTER TABLE {schema}.{table} ADD PRIMARY KEY ({column});\n",
    "```\n",
    "\n",
    "You can also find a little more documentation of postgres indices [here](https://www.postgresqltutorial.com/postgresql-indexes/postgresql-create-index/)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Temporary Tables\n",
    "\n",
    "Breaking up complex queries with CTEs can make your code much more readable and may provide some performance gains, but further gains can often be realized by creating and indexing temporary tables. \n",
    "\n",
    "Let's rework one of the CTE examples from above using temporary tables: For all the MD projects posted in January 2013 that received any donations what is the average fraction of donations coming from teachers by resource type?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "andrew_id =  # FILL IN YOUR andrew_id HERE!\n",
    "\n",
    "# Temporary table and index for projects posted by MD schools in Jan 2013\n",
    "engine.execute(\"\"\"\n",
    "CREATE LOCAL TEMPORARY TABLE tmp_{}_md_projects\n",
    "    ON COMMIT PRESERVE ROWS\n",
    "    AS\n",
    "    SELECT *\n",
    "    FROM donorschoose.projects\n",
    "    WHERE school_state='MD'\n",
    "        AND date_posted::DATE BETWEEN '2013-01-01'::DATE AND '2013-01-31'::DATE\n",
    ";\n",
    "\"\"\".format(andrew_id))\n",
    "engine.execute(\"\"\"CREATE INDEX ON tmp_{}_md_projects(projectid);\"\"\".format(andrew_id))\n",
    "engine.execute(\"COMMIT;\")\n",
    "\n",
    "# Temporary table and index for donations by teachers\n",
    "engine.execute(\"\"\"\n",
    "CREATE LOCAL TEMPORARY TABLE tmp_{}_teacher_donations\n",
    "    ON COMMIT PRESERVE ROWS\n",
    "    AS\n",
    "    SELECT d.projectid, SUM(CASE WHEN is_teacher_acct::boolean THEN d.donation_total ELSE 0 END)/SUM(d.donation_total) AS teacher_frac\n",
    "    FROM tmp_{}_md_projects p\n",
    "    JOIN donorschoose.donations d USING(projectid)\n",
    "    GROUP BY 1\n",
    ";\n",
    "\"\"\".format(andrew_id, andrew_id))\n",
    "engine.execute(\"\"\"CREATE INDEX ON tmp_{}_teacher_donations(projectid);\"\"\".format(andrew_id))\n",
    "engine.execute(\"COMMIT;\")\n",
    "\n",
    "# Join these two temporary tables to get our result\n",
    "pd.read_sql(\"\"\"\n",
    "SELECT p.resource_type, AVG(td.teacher_frac) AS avg_teacher_frac\n",
    "FROM tmp_{}_md_projects p\n",
    "JOIN tmp_{}_teacher_donations td USING(projectid)\n",
    "GROUP BY 1\n",
    "ORDER BY 2 DESC\n",
    "\"\"\".format(andrew_id, andrew_id), engine)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Clean Up\n",
    "\n",
    "drop the candy table and commit; dispose of the sqlalchemy engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "drop_sql = \"DROP TABLE {}.{}\".format(table_schema, candy_table)\n",
    "\n",
    "engine.execute(drop_sql)\n",
    "engine.execute(\"COMMIT\")\n",
    "\n",
    "engine.execute(\"DROP TABLE IF EXISTS tmp_{}_md_projects\".format(andrew_id))\n",
    "engine.execute(\"COMMIT\")\n",
    "\n",
    "engine.execute(\"DROP TABLE IF EXISTS tmp_{}_teacher_donations\".format(andrew_id))\n",
    "engine.execute(\"COMMIT\")\n",
    "\n",
    "engine.dispose()"
   ]
  }
 ],
 "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.16"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}