ex09-Advanced Query Techniques of CASE and Subquery.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## ex09-Advanced Query Techniques of CASE and Subquery\n",
    "\n",
    "The SQLite CASE expression evaluates a list of conditions and returns an expression based on the result of the evaluation. The CASE expression is similar to the IF-THEN-ELSE statement in other programming languages. You can use the CASE statement in any clause or statement that accepts a valid expression. For example, you can use the CASE statement in clauses such as WHERE, ORDER BY, HAVING, IN, SELECT and statements such as SELECT, UPDATE, and DELETE. See more at http://www.sqlitetutorial.net/sqlite-case/. \n",
    "\n",
    "A subquery, simply put, is a query written as a part of a bigger statement. Think of it as a SELECT statement inside another one. The result of the inner SELECT can then be used in the outer query.\n",
    "\n",
    "In this notebook, we put these two query techniques together to calculate seasonal runoff from year-month data in the table of rch."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. Connet to the given database of demo.db3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "u'Connected: @data/demo.db3'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql sqlite:///data/demo.db3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you do not remember the tables in the demo data, you can always use the following command to query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///data/demo.db3\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>rch</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>hru</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>sub</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>sed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>watershed_daily</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>watershed_monthly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>watershed_yearly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>channel_dimension</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>hru_info</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>sub_info</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>rch_info</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ave_plant</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ave_annual_hru</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ave_monthly_basin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ave_annual_basin</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(u'rch',),\n",
       " (u'hru',),\n",
       " (u'sub',),\n",
       " (u'sed',),\n",
       " (u'watershed_daily',),\n",
       " (u'watershed_monthly',),\n",
       " (u'watershed_yearly',),\n",
       " (u'channel_dimension',),\n",
       " (u'hru_info',),\n",
       " (u'sub_info',),\n",
       " (u'rch_info',),\n",
       " (u'ave_plant',),\n",
       " (u'ave_annual_hru',),\n",
       " (u'ave_monthly_basin',),\n",
       " (u'ave_annual_basin',)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT name FROM sqlite_master WHERE type='table'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Chek the rch table\n",
    "\n",
    "We can find that the rch table contains time series data with year and month for each river reach. Therefore, it is natural to calculate some seasonal statistics. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///data/demo.db3\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>RCH</th>\n",
       "        <th>YR</th>\n",
       "        <th>MO</th>\n",
       "        <th>FLOW_INcms</th>\n",
       "        <th>FLOW_OUTcms</th>\n",
       "        <th>EVAPcms</th>\n",
       "        <th>TLOSScms</th>\n",
       "        <th>SED_INtons</th>\n",
       "        <th>SED_OUTtons</th>\n",
       "        <th>SEDCONCmg_kg</th>\n",
       "        <th>ORGN_INkg</th>\n",
       "        <th>ORGN_OUTkg</th>\n",
       "        <th>ORGP_INkg</th>\n",
       "        <th>ORGP_OUTkg</th>\n",
       "        <th>NO3_INkg</th>\n",
       "        <th>NO3_OUTkg</th>\n",
       "        <th>NH4_INkg</th>\n",
       "        <th>NH4_OUTkg</th>\n",
       "        <th>NO2_INkg</th>\n",
       "        <th>NO2_OUTkg</th>\n",
       "        <th>MINP_INkg</th>\n",
       "        <th>MINP_OUTkg</th>\n",
       "        <th>CHLA_INkg</th>\n",
       "        <th>CHLA_OUTkg</th>\n",
       "        <th>CBOD_INkg</th>\n",
       "        <th>CBOD_OUTkg</th>\n",
       "        <th>DISOX_INkg</th>\n",
       "        <th>DISOX_OUTkg</th>\n",
       "        <th>SOLPST_INmg</th>\n",
       "        <th>SOLPST_OUTmg</th>\n",
       "        <th>SORPST_INmg</th>\n",
       "        <th>SORPST_OUTmg</th>\n",
       "        <th>REACTPSTmg</th>\n",
       "        <th>VOLPSTmg</th>\n",
       "        <th>SETTLPSTmg</th>\n",
       "        <th>RESUSP_PSTmg</th>\n",
       "        <th>DIFFUSEPSTmg</th>\n",
       "        <th>REACBEDPSTmg</th>\n",
       "        <th>BURYPSTmg</th>\n",
       "        <th>BED_PSTmg</th>\n",
       "        <th>BACTP_OUTct</th>\n",
       "        <th>BACTLP_OUTct</th>\n",
       "        <th>CMETAL_1kg</th>\n",
       "        <th>CMETAL_2kg</th>\n",
       "        <th>CMETAL_3kg</th>\n",
       "        <th>TOT_Nkg</th>\n",
       "        <th>TOT_Pkg</th>\n",
       "        <th>NO3ConcMg_l</th>\n",
       "        <th>WTMPdegc</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>1981</td>\n",
       "        <td>1</td>\n",
       "        <td>146.343765259</td>\n",
       "        <td>146.252487183</td>\n",
       "        <td>0.0912808850408</td>\n",
       "        <td>0.0</td>\n",
       "        <td>2.33204616507e-07</td>\n",
       "        <td>61619.4648438</td>\n",
       "        <td>155.371902466</td>\n",
       "        <td>0.0160862877965</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0482588782907</td>\n",
       "        <td>0.0</td>\n",
       "        <td>362.048675537</td>\n",
       "        <td>361.81350708</td>\n",
       "        <td>203.620849609</td>\n",
       "        <td>421.183776855</td>\n",
       "        <td>0.0</td>\n",
       "        <td>23.0184326172</td>\n",
       "        <td>0.0161072444171</td>\n",
       "        <td>0.0</td>\n",
       "        <td>1.18390523079e-11</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>5627225.0</td>\n",
       "        <td>5623486.5</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>806.01574707</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>1981</td>\n",
       "        <td>1</td>\n",
       "        <td>96.225692749</td>\n",
       "        <td>96.1828536987</td>\n",
       "        <td>0.0428212843835</td>\n",
       "        <td>0.0</td>\n",
       "        <td>1.64267646596e-07</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0136315366253</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0408946201205</td>\n",
       "        <td>0.0</td>\n",
       "        <td>315.600524902</td>\n",
       "        <td>315.457977295</td>\n",
       "        <td>0.0</td>\n",
       "        <td>127.005020142</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0136560499668</td>\n",
       "        <td>0.0</td>\n",
       "        <td>4.13697217716e-16</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>3757606.5</td>\n",
       "        <td>3698301.5</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>442.463012695</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>1981</td>\n",
       "        <td>1</td>\n",
       "        <td>11.9527187347</td>\n",
       "        <td>11.8613681793</td>\n",
       "        <td>0.0913518294692</td>\n",
       "        <td>0.0</td>\n",
       "        <td>2.03258238685e-07</td>\n",
       "        <td>2.03258238685e-07</td>\n",
       "        <td>6.59506094181e-09</td>\n",
       "        <td>0.0114662880078</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0343988612294</td>\n",
       "        <td>0.00911803822964</td>\n",
       "        <td>48.2963752747</td>\n",
       "        <td>47.9315032959</td>\n",
       "        <td>0.0</td>\n",
       "        <td>62.4676208496</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0114851053804</td>\n",
       "        <td>0.0</td>\n",
       "        <td>5.9410287833e-14</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>360979.90625</td>\n",
       "        <td>456115.90625</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>110.399124146</td>\n",
       "        <td>0.00911803822964</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 1981, 1, 146.34376525878906, 146.2524871826172, 0.09128088504076004, 0.0, 2.3320461650655488e-07, 61619.46484375, 155.3719024658203, 0.016086287796497345, 0.0, 0.04825887829065323, 0.0, 362.0486755371094, 361.8135070800781, 203.620849609375, 421.18377685546875, 0.0, 23.0184326171875, 0.016107244417071342, 0.0, 1.1839052307949238e-11, 0.0, 0.0, 0.0, 5627225.0, 5623486.5, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 806.0157470703125, 0.0, 0.0, 0.0),\n",
       " (2, 1981, 1, 96.22569274902344, 96.18285369873047, 0.042821284383535385, 0.0, 1.6426764659627224e-07, 0.0, 0.0, 0.01363153662532568, 0.0, 0.04089462012052536, 0.0, 315.60052490234375, 315.4579772949219, 0.0, 127.00502014160156, 0.0, 0.0, 0.013656049966812134, 0.0, 4.136972177163251e-16, 0.0, 0.0, 0.0, 3757606.5, 3698301.5, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 442.4630126953125, 0.0, 0.0, 0.0),\n",
       " (3, 1981, 1, 11.952718734741211, 11.861368179321289, 0.09135182946920395, 0.0, 2.0325823868461157e-07, 2.0325823868461157e-07, 6.595060941805286e-09, 0.01146628800779581, 0.0, 0.03439886122941971, 0.009118038229644299, 48.2963752746582, 47.93150329589844, 0.0, 62.467620849609375, 0.0, 0.0, 0.011485105380415916, 0.0, 5.941028783295818e-14, 0.0, 0.0, 0.0, 360979.90625, 456115.90625, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 110.39912414550781, 0.009118038229644299, 0.0, 0.0)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * From rch LIMIT 3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. Calculate Seasonal Runoff\n",
    "\n",
    "There are two key steps: \n",
    ">(1) use the CASE and Subquery to convert months to named seasons;<br>\n",
    ">(2) calculate seasonal mean with aggregate functions on groups.\n",
    "\n",
    "In addition, we also use another filter keyword of ***BETWEEN*** to span months into seasons."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>RCH</th>\n",
       "        <th>Quarter</th>\n",
       "        <th>Runoff</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>DJF</td>\n",
       "        <td>99.2049905353</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>JJA</td>\n",
       "        <td>1405.26229799</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>MAM</td>\n",
       "        <td>559.746932019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>SON</td>\n",
       "        <td>454.737985314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>DJF</td>\n",
       "        <td>56.3285390854</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>JJA</td>\n",
       "        <td>773.664279811</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>MAM</td>\n",
       "        <td>203.242827762</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>SON</td>\n",
       "        <td>297.934326341</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>DJF</td>\n",
       "        <td>32.8383281098</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>JJA</td>\n",
       "        <td>166.310382387</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>MAM</td>\n",
       "        <td>379.771358498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>SON</td>\n",
       "        <td>27.4254875527</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>DJF</td>\n",
       "        <td>28.3845193916</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>JJA</td>\n",
       "        <td>619.18451697</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>MAM</td>\n",
       "        <td>278.99680089</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>SON</td>\n",
       "        <td>155.766144731</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>DJF</td>\n",
       "        <td>336.824441274</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>JJA</td>\n",
       "        <td>1635.51886359</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>MAM</td>\n",
       "        <td>1215.4239624</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>SON</td>\n",
       "        <td>499.265578885</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>DJF</td>\n",
       "        <td>779.822326914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>JJA</td>\n",
       "        <td>4358.75700616</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>MAM</td>\n",
       "        <td>4503.39223294</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>SON</td>\n",
       "        <td>1149.24015113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7</td>\n",
       "        <td>DJF</td>\n",
       "        <td>97.2866216262</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7</td>\n",
       "        <td>JJA</td>\n",
       "        <td>25.7325216908</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7</td>\n",
       "        <td>MAM</td>\n",
       "        <td>143.932030305</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7</td>\n",
       "        <td>SON</td>\n",
       "        <td>7.1780088827</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>DJF</td>\n",
       "        <td>441.329559199</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>JJA</td>\n",
       "        <td>2725.98232964</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>MAM</td>\n",
       "        <td>3286.82109918</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>SON</td>\n",
       "        <td>650.94858</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>DJF</td>\n",
       "        <td>297.14823051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>JJA</td>\n",
       "        <td>2707.41317681</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>MAM</td>\n",
       "        <td>3106.77992113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>SON</td>\n",
       "        <td>643.755872218</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>DJF</td>\n",
       "        <td>25.9806366854</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>JJA</td>\n",
       "        <td>505.326437802</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>MAM</td>\n",
       "        <td>250.112638274</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>SON</td>\n",
       "        <td>152.611278979</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11</td>\n",
       "        <td>DJF</td>\n",
       "        <td>189.01615821</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11</td>\n",
       "        <td>JJA</td>\n",
       "        <td>2681.09412028</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11</td>\n",
       "        <td>MAM</td>\n",
       "        <td>2951.61630111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11</td>\n",
       "        <td>SON</td>\n",
       "        <td>636.42009201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12</td>\n",
       "        <td>DJF</td>\n",
       "        <td>48.5311904748</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12</td>\n",
       "        <td>JJA</td>\n",
       "        <td>1197.76135356</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12</td>\n",
       "        <td>MAM</td>\n",
       "        <td>806.151377228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12</td>\n",
       "        <td>SON</td>\n",
       "        <td>325.091775428</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>13</td>\n",
       "        <td>DJF</td>\n",
       "        <td>31.1290657202</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>13</td>\n",
       "        <td>JJA</td>\n",
       "        <td>918.847972276</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>13</td>\n",
       "        <td>MAM</td>\n",
       "        <td>769.916959339</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>13</td>\n",
       "        <td>SON</td>\n",
       "        <td>218.182685365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14</td>\n",
       "        <td>DJF</td>\n",
       "        <td>15.1418755611</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14</td>\n",
       "        <td>JJA</td>\n",
       "        <td>454.803625573</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14</td>\n",
       "        <td>MAM</td>\n",
       "        <td>288.028043466</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14</td>\n",
       "        <td>SON</td>\n",
       "        <td>115.841468938</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>15</td>\n",
       "        <td>DJF</td>\n",
       "        <td>122.269135498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>15</td>\n",
       "        <td>JJA</td>\n",
       "        <td>22.7604106626</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>15</td>\n",
       "        <td>MAM</td>\n",
       "        <td>149.863775469</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>15</td>\n",
       "        <td>SON</td>\n",
       "        <td>9.69810930346</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16</td>\n",
       "        <td>DJF</td>\n",
       "        <td>17.394960822</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16</td>\n",
       "        <td>JJA</td>\n",
       "        <td>534.455105167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16</td>\n",
       "        <td>MAM</td>\n",
       "        <td>437.891748575</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16</td>\n",
       "        <td>SON</td>\n",
       "        <td>133.019053608</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>17</td>\n",
       "        <td>DJF</td>\n",
       "        <td>1.99504058129</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>17</td>\n",
       "        <td>JJA</td>\n",
       "        <td>70.9285306931</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>17</td>\n",
       "        <td>MAM</td>\n",
       "        <td>62.8675777341</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>17</td>\n",
       "        <td>SON</td>\n",
       "        <td>15.0284008218</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>18</td>\n",
       "        <td>DJF</td>\n",
       "        <td>9.23698493507</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>18</td>\n",
       "        <td>JJA</td>\n",
       "        <td>285.821954939</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>18</td>\n",
       "        <td>MAM</td>\n",
       "        <td>167.676846024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>18</td>\n",
       "        <td>SON</td>\n",
       "        <td>72.3385225773</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>19</td>\n",
       "        <td>DJF</td>\n",
       "        <td>5.88178349468</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>19</td>\n",
       "        <td>JJA</td>\n",
       "        <td>168.238578457</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>19</td>\n",
       "        <td>MAM</td>\n",
       "        <td>119.147855094</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>19</td>\n",
       "        <td>SON</td>\n",
       "        <td>43.3626210054</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>20</td>\n",
       "        <td>DJF</td>\n",
       "        <td>9.98033892049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>20</td>\n",
       "        <td>JJA</td>\n",
       "        <td>287.668195173</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>20</td>\n",
       "        <td>MAM</td>\n",
       "        <td>229.235642958</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>20</td>\n",
       "        <td>SON</td>\n",
       "        <td>74.7938857661</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>21</td>\n",
       "        <td>DJF</td>\n",
       "        <td>4.3587709003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>21</td>\n",
       "        <td>JJA</td>\n",
       "        <td>136.128692203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>21</td>\n",
       "        <td>MAM</td>\n",
       "        <td>85.7665674183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>21</td>\n",
       "        <td>SON</td>\n",
       "        <td>34.1510832098</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>22</td>\n",
       "        <td>DJF</td>\n",
       "        <td>14.5497625139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>22</td>\n",
       "        <td>JJA</td>\n",
       "        <td>420.857440355</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>22</td>\n",
       "        <td>MAM</td>\n",
       "        <td>423.459259864</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>22</td>\n",
       "        <td>SON</td>\n",
       "        <td>94.0899063004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>23</td>\n",
       "        <td>DJF</td>\n",
       "        <td>4.32677901851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>23</td>\n",
       "        <td>JJA</td>\n",
       "        <td>141.995886527</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>23</td>\n",
       "        <td>MAM</td>\n",
       "        <td>90.8375598037</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>23</td>\n",
       "        <td>SON</td>\n",
       "        <td>34.6547450622</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, u'DJF', 99.20499053531223),\n",
       " (1, u'JJA', 1405.262297990587),\n",
       " (1, u'MAM', 559.7469320191277),\n",
       " (1, u'SON', 454.73798531426326),\n",
       " (2, u'DJF', 56.328539085388186),\n",
       " (2, u'JJA', 773.6642798105876),\n",
       " (2, u'MAM', 203.2428277624978),\n",
       " (2, u'SON', 297.9343263414171),\n",
       " (3, u'DJF', 32.838328109847176),\n",
       " (3, u'JJA', 166.31038238737318),\n",
       " (3, u'MAM', 379.7713584979375),\n",
       " (3, u'SON', 27.425487552748788),\n",
       " (4, u'DJF', 28.384519391589695),\n",
       " (4, u'JJA', 619.1845169703166),\n",
       " (4, u'MAM', 278.99680089023377),\n",
       " (4, u'SON', 155.76614473130968),\n",
       " (5, u'DJF', 336.82444127400714),\n",
       " (5, u'JJA', 1635.5188635932075),\n",
       " (5, u'MAM', 1215.4239624023437),\n",
       " (5, u'SON', 499.26557888454863),\n",
       " (6, u'DJF', 779.8223269144694),\n",
       " (6, u'JJA', 4358.757006157769),\n",
       " (6, u'MAM', 4503.392232937283),\n",
       " (6, u'SON', 1149.2401511298285),\n",
       " (7, u'DJF', 97.28662162621816),\n",
       " (7, u'JJA', 25.732521690828435),\n",
       " (7, u'MAM', 143.93203030533567),\n",
       " (7, u'SON', 7.178008882701397),\n",
       " (8, u'DJF', 441.3295591990153),\n",
       " (8, u'JJA', 2725.9823296440973),\n",
       " (8, u'MAM', 3286.821099175347),\n",
       " (8, u'SON', 650.9485800001356),\n",
       " (9, u'DJF', 297.1482305102878),\n",
       " (9, u'JJA', 2707.413176812066),\n",
       " (9, u'MAM', 3106.779921129015),\n",
       " (9, u'SON', 643.7558722178142),\n",
       " (10, u'DJF', 25.98063668542438),\n",
       " (10, u'JJA', 505.32643780178495),\n",
       " (10, u'MAM', 250.11263827350405),\n",
       " (10, u'SON', 152.61127897898356),\n",
       " (11, u'DJF', 189.01615820990668),\n",
       " (11, u'JJA', 2681.094120279948),\n",
       " (11, u'MAM', 2951.6163011127046),\n",
       " (11, u'SON', 636.420092010498),\n",
       " (12, u'DJF', 48.53119047482809),\n",
       " (12, u'JJA', 1197.761353556315),\n",
       " (12, u'MAM', 806.1513772275713),\n",
       " (12, u'SON', 325.0917754279243),\n",
       " (13, u'DJF', 31.129065720240273),\n",
       " (13, u'JJA', 918.8479722764757),\n",
       " (13, u'MAM', 769.916959338718),\n",
       " (13, u'SON', 218.18268536461724),\n",
       " (14, u'DJF', 15.14187556107839),\n",
       " (14, u'JJA', 454.8036255730523),\n",
       " (14, u'MAM', 288.02804346614414),\n",
       " (14, u'SON', 115.84146893819174),\n",
       " (15, u'DJF', 122.269135497676),\n",
       " (15, u'JJA', 22.760410662551617),\n",
       " (15, u'MAM', 149.8637754691144),\n",
       " (15, u'SON', 9.698109303456214),\n",
       " (16, u'DJF', 17.394960821999444),\n",
       " (16, u'JJA', 534.455105166965),\n",
       " (16, u'MAM', 437.89174857470726),\n",
       " (16, u'SON', 133.0190536075168),\n",
       " (17, u'DJF', 1.9950405812925762),\n",
       " (17, u'JJA', 70.9285306930542),\n",
       " (17, u'MAM', 62.86757773409287),\n",
       " (17, u'SON', 15.028400821818245),\n",
       " (18, u'DJF', 9.236984935071733),\n",
       " (18, u'JJA', 285.82195493910046),\n",
       " (18, u'MAM', 167.6768460240629),\n",
       " (18, u'SON', 72.33852257728577),\n",
       " (19, u'DJF', 5.881783494684432),\n",
       " (19, u'JJA', 168.2385784573025),\n",
       " (19, u'MAM', 119.14785509374407),\n",
       " (19, u'SON', 43.36262100537618),\n",
       " (20, u'DJF', 9.980338920487299),\n",
       " (20, u'JJA', 287.66819517347545),\n",
       " (20, u'MAM', 229.23564295834964),\n",
       " (20, u'SON', 74.79388576613532),\n",
       " (21, u'DJF', 4.3587709003024635),\n",
       " (21, u'JJA', 136.12869220309787),\n",
       " (21, u'MAM', 85.76656741831037),\n",
       " (21, u'SON', 34.15108320977953),\n",
       " (22, u'DJF', 14.549762513902452),\n",
       " (22, u'JJA', 420.85744035508895),\n",
       " (22, u'MAM', 423.4592598636945),\n",
       " (22, u'SON', 94.08990630043877),\n",
       " (23, u'DJF', 4.326779018508064),\n",
       " (23, u'JJA', 141.99588652716741),\n",
       " (23, u'MAM', 90.83755980365807),\n",
       " (23, u'SON', 34.65474506219228)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql sqlite://\n",
    "SELECT RCH, Quarter, AVG(FLOW_OUTcms) as Runoff\n",
    "FROM(\n",
    "SELECT RCH, YR, \n",
    "CASE \n",
    "    WHEN (MO) BETWEEN 3 AND 5 THEN 'MAM'   \n",
    "    WHEN (MO) BETWEEN 6 and 8 THEN 'JJA'\n",
    "    WHEN (MO) BETWEEN 9 and 11 THEN 'SON'\n",
    "    ELSE 'DJF' \n",
    "END Quarter,\n",
    "FLOW_OUTcms\n",
    "from rch)\n",
    "GROUP BY RCH, Quarter"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Summary\n",
    "\n",
    "Sometimes, we may need construct complicated requires that go beyond a table join or basic SELECT query. For example, we might need to write a query that uses the results of other queries as inputs (i.e., SUBQUERY). Or we might need to reclassify numerical values into categories before counting them (i.e., CASE). \n",
    "\n",
    "In this notebook, we explored a collection of SQL functions and options essential for solving more complex problems. Now we can add subqueries in multiple locations to provide finer control over filtering or preprocessing data before analyzing it in a main query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [conda root]",
   "language": "python",
   "name": "conda-root-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}