jl/demo/tpch-queries.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# TPC-H Queries\n",
    "\n",
    "In this notebook, we explore how to reimplement SQL queries from the well-known TPC-H benchmark in Rabbit.\n",
    "\n",
    "We start with loading the dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Region:\n",
       "  name :: ASCIIString\n",
       "  comment :: ASCIIString\n",
       "  nation (inverse of Nation.region) :: Array{Nation,1} # unique, covering\n",
       "Nation:\n",
       "  name :: ASCIIString\n",
       "  region :: Region\n",
       "  comment :: ASCIIString\n",
       "  customer (inverse of Customer.nation) :: Array{Customer,1} # unique, covering\n",
       "  supplier (inverse of Supplier.nation) :: Array{Supplier,1} # unique, covering\n",
       "Customer:\n",
       "  name :: ASCIIString\n",
       "  address :: ASCIIString\n",
       "  nation :: Nation\n",
       "  phone :: ASCIIString\n",
       "  acctbal :: RBT.Monetary{:USD}\n",
       "  mktsegment :: ASCIIString\n",
       "  comment :: ASCIIString\n",
       "  order (inverse of Order.customer) :: Array{Order,1} # unique, covering\n",
       "Supplier:\n",
       "  name :: ASCIIString\n",
       "  address :: ASCIIString\n",
       "  nation :: Nation\n",
       "  phone :: ASCIIString\n",
       "  acctbal :: RBT.Monetary{:USD}\n",
       "  comment :: ASCIIString\n",
       "  partsupp (inverse of Partsupp.supplier) :: Array{Partsupp,1} # unique, covering\n",
       "  lineitem (inverse of Lineitem.supplier) :: Array{Lineitem,1} # unique, covering\n",
       "Part:\n",
       "  name :: ASCIIString\n",
       "  mfgr :: ASCIIString\n",
       "  brand :: ASCIIString\n",
       "  type_ :: ASCIIString\n",
       "  size :: Int64\n",
       "  container :: ASCIIString\n",
       "  retailprice :: RBT.Monetary{:USD}\n",
       "  comment :: ASCIIString\n",
       "  partsupp (inverse of Partsupp.part) :: Array{Partsupp,1} # unique, covering\n",
       "  lineitem (inverse of Lineitem.part) :: Array{Lineitem,1} # unique, covering\n",
       "Partsupp:\n",
       "  part :: Part\n",
       "  supplier :: Supplier\n",
       "  availqty :: Int64\n",
       "  supplycost :: RBT.Monetary{:USD}\n",
       "  comment :: ASCIIString\n",
       "  lineitem (inverse of Lineitem.partsupp) :: Array{Lineitem,1} # unique, covering\n",
       "Order:\n",
       "  customer :: Customer\n",
       "  orderstatus :: ASCIIString\n",
       "  totalprice :: RBT.Monetary{:USD}\n",
       "  orderdate :: Date\n",
       "  orderpriority :: ASCIIString\n",
       "  clerk :: ASCIIString\n",
       "  shippriority :: Int64\n",
       "  comment :: ASCIIString\n",
       "  lineitem (inverse of Lineitem.order) :: Array{Lineitem,1} # unique, covering\n",
       "Lineitem:\n",
       "  order :: Order\n",
       "  part :: Part\n",
       "  supplier :: Supplier\n",
       "  partsupp :: Partsupp\n",
       "  linenumber :: Int64\n",
       "  quantity :: Int64\n",
       "  extendedprice :: RBT.Monetary{:USD}\n",
       "  discount :: Float64\n",
       "  tax :: Float64\n",
       "  returnflag :: ASCIIString\n",
       "  linestatus :: ASCIIString\n",
       "  shipdate :: Date\n",
       "  commitdate :: Date\n",
       "  receiptdate :: Date\n",
       "  shipinstruct :: ASCIIString\n",
       "  shipmode :: ASCIIString\n",
       "  comment :: ASCIIString"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "include(\"../tpch.jl\")\n",
    "\n",
    "using RBT\n",
    "setdb(tpch)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The TPC-H benchmark consists of a generated dataset of customers, suppliers, products and orders as well as a collection of queries that perform various types of business analysis.\n",
    "\n",
    "Table `order` lists all orders made by customers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>name</th><th>totalprice</th><th>orderdate</th></tr><tr><th>1</th><td>Customer#000000296</td><td>159877.96</td><td>1996-01-02</td></tr><tr><th>2</th><td>Customer#000000625</td><td>69858.91</td><td>1996-12-01</td></tr><tr><th>3</th><td>Customer#000000988</td><td>183718.98</td><td>1993-10-14</td></tr><tr><th>4</th><td>Customer#000001096</td><td>50463.93</td><td>1995-10-11</td></tr><tr><th>5</th><td>Customer#000000356</td><td>133409.48</td><td>1994-07-30</td></tr><tr><th>6</th><td>Customer#000000445</td><td>35731.21</td><td>1992-02-21</td></tr><tr><th>7</th><td>Customer#000000314</td><td>229881.30</td><td>1996-01-10</td></tr><tr><th>8</th><td>Customer#000001042</td><td>173503.97</td><td>1995-07-16</td></tr><tr><th>9</th><td>Customer#000000536</td><td>135080.42</td><td>1993-10-27</td></tr><tr><th>10</th><td>Customer#000000490</td><td>64891.67</td><td>1998-07-21</td></tr><tr><th>&vellip;</th><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td></tr></table>"
      ],
      "text/plain": [
       "12000x3 DataFrames.DataFrame\n",
       "| Row   | name                 | totalprice | orderdate  |\n",
       "|-------|----------------------|------------|------------|\n",
       "| 1     | \"Customer#000000296\" | 159877.96  | 1996-01-02 |\n",
       "⋮\n",
       "| 11999 | \"Customer#000001036\" | 96525.52   | 1993-08-15 |\n",
       "| 12000 | \"Customer#000000871\" | 43237.08   | 1992-08-14 |"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    order\n",
    "    :select(\n",
    "        customer.name, totalprice, orderdate))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Individual items in each order are stored in the `lineitem` table.  Each item describes the ordered part, the quantity, the supplier and the pricing information."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>customer</th><th>linenumber</th><th>part</th><th>supplier</th><th>quantity</th><th>extendedprice</th><th>discount</th><th>tax</th><th>shipdate</th></tr><tr><th>1</th><td>Customer#000000296</td><td>1</td><td>dodger brown ivory blue pink</td><td>Supplier#000000078</td><td>17</td><td>19435.08</td><td>0.04</td><td>0.02</td><td>1996-03-13</td></tr><tr><th>2</th><td>Customer#000000296</td><td>2</td><td>dodger midnight salmon drab saddle</td><td>Supplier#000000060</td><td>36</td><td>51823.08</td><td>0.09</td><td>0.06</td><td>1996-04-12</td></tr><tr><th>3</th><td>Customer#000000296</td><td>3</td><td>blush thistle orchid red lace</td><td>Supplier#000000031</td><td>8</td><td>11284.08</td><td>0.1</td><td>0.02</td><td>1996-01-29</td></tr><tr><th>4</th><td>Customer#000000296</td><td>4</td><td>turquoise indian lemon lavender misty</td><td>Supplier#000000039</td><td>28</td><td>25704.28</td><td>0.09</td><td>0.06</td><td>1996-04-21</td></tr><tr><th>5</th><td>Customer#000000296</td><td>5</td><td>turquoise lime royal metallic azure</td><td>Supplier#000000020</td><td>24</td><td>26236.56</td><td>0.1</td><td>0.04</td><td>1996-03-30</td></tr><tr><th>6</th><td>Customer#000000296</td><td>6</td><td>burnished black blue metallic orchid</td><td>Supplier#000000009</td><td>32</td><td>32835.84</td><td>0.07</td><td>0.02</td><td>1996-01-30</td></tr><tr><th>7</th><td>Customer#000000625</td><td>1</td><td>peach goldenrod honeydew moccasin sienna</td><td>Supplier#000000031</td><td>38</td><td>66532.30</td><td>0.0</td><td>0.05</td><td>1997-01-28</td></tr><tr><th>8</th><td>Customer#000000988</td><td>1</td><td>green blush tomato burlywood seashell</td><td>Supplier#000000016</td><td>45</td><td>42076.35</td><td>0.06</td><td>0.0</td><td>1994-02-02</td></tr><tr><th>9</th><td>Customer#000000988</td><td>2</td><td>linen frosted slate coral peru</td><td>Supplier#000000057</td><td>49</td><td>51604.35</td><td>0.1</td><td>0.0</td><td>1993-11-09</td></tr><tr><th>10</th><td>Customer#000000988</td><td>3</td><td>blue drab lime khaki sienna</td><td>Supplier#000000053</td><td>27</td><td>25083.54</td><td>0.06</td><td>0.07</td><td>1994-01-16</td></tr><tr><th>&vellip;</th><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td></tr></table>"
      ],
      "text/plain": [
       "48214x9 DataFrames.DataFrame\n",
       "| Row   | customer             | linenumber |\n",
       "|-------|----------------------|------------|\n",
       "| 1     | \"Customer#000000296\" | 1          |\n",
       "⋮\n",
       "| 48213 | \"Customer#000001036\" | 3          |\n",
       "| 48214 | \"Customer#000000871\" | 1          |\n",
       "\n",
       "| Row   | part                                | supplier             |\n",
       "|-------|-------------------------------------|----------------------|\n",
       "| 1     | \"dodger brown ivory blue pink\"      | \"Supplier#000000078\" |\n",
       "⋮\n",
       "| 48213 | \"peach antique deep peru saddle\"    | \"Supplier#000000016\" |\n",
       "| 48214 | \"burlywood orchid dark drab dodger\" | \"Supplier#000000007\" |\n",
       "\n",
       "| Row   | quantity | extendedprice | discount | tax  | shipdate   |\n",
       "|-------|----------|---------------|----------|------|------------|\n",
       "| 1     | 17       | 19435.08      | 0.04     | 0.02 | 1996-03-13 |\n",
       "⋮\n",
       "| 48213 | 29       | 34489.12      | 0.06     | 0.0  | 1993-11-08 |\n",
       "| 48214 | 39       | 44703.36      | 0.07     | 0.04 | 1992-11-24 |"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    lineitem\n",
    "    :select(\n",
    "        order.customer, linenumber, part, supplier, quantity, extendedprice, discount, tax, shipdate))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For each customer and supplier, we track their country of origin.  Table `nation` contains a list of all countries partitioned by region."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>name</th><th>region</th><th>cust_count</th><th>supp_count</th></tr><tr><th>1</th><td>ALGERIA</td><td>AFRICA</td><td>51</td><td>3</td></tr><tr><th>2</th><td>ARGENTINA</td><td>AMERICA</td><td>50</td><td>3</td></tr><tr><th>3</th><td>BRAZIL</td><td>AMERICA</td><td>63</td><td>1</td></tr><tr><th>4</th><td>CANADA</td><td>AMERICA</td><td>48</td><td>2</td></tr><tr><th>5</th><td>EGYPT</td><td>MIDDLE EAST</td><td>56</td><td>4</td></tr><tr><th>6</th><td>ETHIOPIA</td><td>AFRICA</td><td>44</td><td>3</td></tr><tr><th>7</th><td>FRANCE</td><td>EUROPE</td><td>32</td><td>1</td></tr><tr><th>8</th><td>GERMANY</td><td>EUROPE</td><td>41</td><td>4</td></tr><tr><th>9</th><td>INDIA</td><td>ASIA</td><td>52</td><td>5</td></tr><tr><th>10</th><td>INDONESIA</td><td>ASIA</td><td>56</td><td>4</td></tr><tr><th>&vellip;</th><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td></tr></table>"
      ],
      "text/plain": [
       "25x4 DataFrames.DataFrame\n",
       "| Row | name             | region    | cust_count | supp_count |\n",
       "|-----|------------------|-----------|------------|------------|\n",
       "| 1   | \"ALGERIA\"        | \"AFRICA\"  | 51         | 3          |\n",
       "⋮\n",
       "| 24  | \"UNITED KINGDOM\" | \"EUROPE\"  | 42         | 3          |\n",
       "| 25  | \"UNITED STATES\"  | \"AMERICA\" | 31         | 6          |"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    nation\n",
    "    :select(\n",
    "        name,\n",
    "        region,\n",
    "        cust_count => count(customer),\n",
    "        supp_count => count(supplier)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following query definitions are taken directly from TPC-H documentation.  We will show how to incrementally construct these queries in Rabbit."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pricing Summary Report Query (Q1)\n",
    "*[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 29]*\n",
    "\n",
    "This query reports the amount of business that was billed, shipped, and returned.\n",
    "\n",
    "### Business Question\n",
    "The Pricing Summary Report Query provides a summary pricing report for all lineitems shipped as of a given date.\n",
    "The date is within 60 - 120 days of the greatest ship date contained in the database. The query lists totals for\n",
    "extended price, discounted extended price, discounted extended price plus tax, average quantity, average extended\n",
    "price, and average discount. These aggregates are grouped by RETURNFLAG and LINESTATUS, and listed in\n",
    "ascending order of RETURNFLAG and LINESTATUS. A count of the number of lineitems in each group is\n",
    "included.\n",
    "        \n",
    "### Query Definition\n",
    "```\n",
    "select\n",
    "    l_returnflag,\n",
    "    l_linestatus,\n",
    "    sum(l_quantity) as sum_qty,\n",
    "    sum(l_extendedprice) as sum_base_price,\n",
    "    sum(l_extendedprice*(1-l_discount)) as sum_disc_price,\n",
    "    sum(l_extendedprice*(1-l_discount)*(1+l_tax)) as sum_charge,\n",
    "    avg(l_quantity) as avg_qty,\n",
    "    avg(l_extendedprice) as avg_price,\n",
    "    avg(l_discount) as avg_disc,\n",
    "    count(*) as count_order\n",
    "from\n",
    "    lineitem\n",
    "where\n",
    "    l_shipdate <= date '1998-12-01' - interval '[DELTA]' day\n",
    "group by\n",
    "    l_returnflag,\n",
    "    l_linestatus\n",
    "order by\n",
    "    l_returnflag,\n",
    "    l_linestatus;\n",
    "```\n",
    "\n",
    "### Substitution Parameters\n",
    "1. DELTA = 90."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To generate this report, we use the data from the `lineitem` table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>order</th><th>linenumber</th><th>shipdate</th><th>quantity</th><th>extendedprice</th><th>discount</th><th>tax</th><th>returnflag</th><th>linestatus</th></tr><tr><th>1</th><td>Customer#000000296</td><td>1</td><td>1996-03-13</td><td>17</td><td>19435.08</td><td>0.04</td><td>0.02</td><td>N</td><td>O</td></tr><tr><th>2</th><td>Customer#000000296</td><td>2</td><td>1996-04-12</td><td>36</td><td>51823.08</td><td>0.09</td><td>0.06</td><td>N</td><td>O</td></tr><tr><th>3</th><td>Customer#000000296</td><td>3</td><td>1996-01-29</td><td>8</td><td>11284.08</td><td>0.1</td><td>0.02</td><td>N</td><td>O</td></tr><tr><th>4</th><td>Customer#000000296</td><td>4</td><td>1996-04-21</td><td>28</td><td>25704.28</td><td>0.09</td><td>0.06</td><td>N</td><td>O</td></tr><tr><th>5</th><td>Customer#000000296</td><td>5</td><td>1996-03-30</td><td>24</td><td>26236.56</td><td>0.1</td><td>0.04</td><td>N</td><td>O</td></tr><tr><th>6</th><td>Customer#000000296</td><td>6</td><td>1996-01-30</td><td>32</td><td>32835.84</td><td>0.07</td><td>0.02</td><td>N</td><td>O</td></tr><tr><th>7</th><td>Customer#000000625</td><td>1</td><td>1997-01-28</td><td>38</td><td>66532.30</td><td>0.0</td><td>0.05</td><td>N</td><td>O</td></tr><tr><th>8</th><td>Customer#000000988</td><td>1</td><td>1994-02-02</td><td>45</td><td>42076.35</td><td>0.06</td><td>0.0</td><td>R</td><td>F</td></tr><tr><th>9</th><td>Customer#000000988</td><td>2</td><td>1993-11-09</td><td>49</td><td>51604.35</td><td>0.1</td><td>0.0</td><td>R</td><td>F</td></tr><tr><th>10</th><td>Customer#000000988</td><td>3</td><td>1994-01-16</td><td>27</td><td>25083.54</td><td>0.06</td><td>0.07</td><td>A</td><td>F</td></tr><tr><th>&vellip;</th><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td></tr></table>"
      ],
      "text/plain": [
       "48214x9 DataFrames.DataFrame\n",
       "| Row   | order                | linenumber | shipdate   | quantity |\n",
       "|-------|----------------------|------------|------------|----------|\n",
       "| 1     | \"Customer#000000296\" | 1          | 1996-03-13 | 17       |\n",
       "⋮\n",
       "| 48213 | \"Customer#000001036\" | 3          | 1993-11-08 | 29       |\n",
       "| 48214 | \"Customer#000000871\" | 1          | 1992-11-24 | 39       |\n",
       "\n",
       "| Row   | extendedprice | discount | tax  | returnflag | linestatus |\n",
       "|-------|---------------|----------|------|------------|------------|\n",
       "| 1     | 19435.08      | 0.04     | 0.02 | \"N\"        | \"O\"        |\n",
       "⋮\n",
       "| 48213 | 34489.12      | 0.06     | 0.0  | \"R\"        | \"F\"        |\n",
       "| 48214 | 44703.36      | 0.07     | 0.04 | \"R\"        | \"F\"        |"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    lineitem\n",
    "    :select(order, linenumber, shipdate, quantity, extendedprice, discount, tax, returnflag, linestatus))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We start with extracting all lineitems in the selected date range."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>order</th><th>part</th><th>supplier</th><th>linenumber</th><th>quantity</th><th>extendedprice</th><th>discount</th><th>tax</th><th>returnflag</th><th>linestatus</th><th>shipdate</th><th>commitdate</th><th>receiptdate</th><th>shipinstruct</th><th>shipmode</th><th>comment</th></tr><tr><th>1</th><td>Customer#000000296</td><td>dodger brown ivory blue pink</td><td>Supplier#000000078</td><td>1</td><td>17</td><td>19435.08</td><td>0.04</td><td>0.02</td><td>N</td><td>O</td><td>1996-03-13</td><td>1996-02-12</td><td>1996-03-22</td><td>DELIVER IN PERSON</td><td>TRUCK</td><td>egular courts above the</td></tr><tr><th>2</th><td>Customer#000000296</td><td>dodger midnight salmon drab saddle</td><td>Supplier#000000060</td><td>2</td><td>36</td><td>51823.08</td><td>0.09</td><td>0.06</td><td>N</td><td>O</td><td>1996-04-12</td><td>1996-02-28</td><td>1996-04-20</td><td>TAKE BACK RETURN</td><td>MAIL</td><td>ly final dependencies: slyly bold </td></tr><tr><th>3</th><td>Customer#000000296</td><td>blush thistle orchid red lace</td><td>Supplier#000000031</td><td>3</td><td>8</td><td>11284.08</td><td>0.1</td><td>0.02</td><td>N</td><td>O</td><td>1996-01-29</td><td>1996-03-05</td><td>1996-01-31</td><td>TAKE BACK RETURN</td><td>REG AIR</td><td>riously. regular, express dep</td></tr><tr><th>4</th><td>Customer#000000296</td><td>turquoise indian lemon lavender misty</td><td>Supplier#000000039</td><td>4</td><td>28</td><td>25704.28</td><td>0.09</td><td>0.06</td><td>N</td><td>O</td><td>1996-04-21</td><td>1996-03-30</td><td>1996-05-16</td><td>NONE</td><td>AIR</td><td>lites. fluffily even de</td></tr><tr><th>5</th><td>Customer#000000296</td><td>turquoise lime royal metallic azure</td><td>Supplier#000000020</td><td>5</td><td>24</td><td>26236.56</td><td>0.1</td><td>0.04</td><td>N</td><td>O</td><td>1996-03-30</td><td>1996-03-14</td><td>1996-04-01</td><td>NONE</td><td>FOB</td><td> pending foxes. slyly re</td></tr><tr><th>6</th><td>Customer#000000296</td><td>burnished black blue metallic orchid</td><td>Supplier#000000009</td><td>6</td><td>32</td><td>32835.84</td><td>0.07</td><td>0.02</td><td>N</td><td>O</td><td>1996-01-30</td><td>1996-02-07</td><td>1996-02-03</td><td>DELIVER IN PERSON</td><td>MAIL</td><td>arefully slyly ex</td></tr><tr><th>7</th><td>Customer#000000625</td><td>peach goldenrod honeydew moccasin sienna</td><td>Supplier#000000031</td><td>1</td><td>38</td><td>66532.30</td><td>0.0</td><td>0.05</td><td>N</td><td>O</td><td>1997-01-28</td><td>1997-01-14</td><td>1997-02-02</td><td>TAKE BACK RETURN</td><td>RAIL</td><td>ven requests. deposits breach a</td></tr><tr><th>8</th><td>Customer#000000988</td><td>green blush tomato burlywood seashell</td><td>Supplier#000000016</td><td>1</td><td>45</td><td>42076.35</td><td>0.06</td><td>0.0</td><td>R</td><td>F</td><td>1994-02-02</td><td>1994-01-04</td><td>1994-02-23</td><td>NONE</td><td>AIR</td><td>ongside of the furiously brave acco</td></tr><tr><th>9</th><td>Customer#000000988</td><td>linen frosted slate coral peru</td><td>Supplier#000000057</td><td>2</td><td>49</td><td>51604.35</td><td>0.1</td><td>0.0</td><td>R</td><td>F</td><td>1993-11-09</td><td>1993-12-20</td><td>1993-11-24</td><td>TAKE BACK RETURN</td><td>RAIL</td><td> unusual accounts. eve</td></tr><tr><th>10</th><td>Customer#000000988</td><td>blue drab lime khaki sienna</td><td>Supplier#000000053</td><td>3</td><td>27</td><td>25083.54</td><td>0.06</td><td>0.07</td><td>A</td><td>F</td><td>1994-01-16</td><td>1993-11-22</td><td>1994-01-23</td><td>DELIVER IN PERSON</td><td>SHIP</td><td>nal foxes wake. </td></tr><tr><th>&vellip;</th><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td></tr></table>"
      ],
      "text/plain": [
       "47535x16 DataFrames.DataFrame\n",
       "| Row   | order                | part                                |\n",
       "|-------|----------------------|-------------------------------------|\n",
       "| 1     | \"Customer#000000296\" | \"dodger brown ivory blue pink\"      |\n",
       "⋮\n",
       "| 47534 | \"Customer#000001036\" | \"peach antique deep peru saddle\"    |\n",
       "| 47535 | \"Customer#000000871\" | \"burlywood orchid dark drab dodger\" |\n",
       "\n",
       "| Row   | supplier             | linenumber | quantity | extendedprice |\n",
       "|-------|----------------------|------------|----------|---------------|\n",
       "| 1     | \"Supplier#000000078\" | 1          | 17       | 19435.08      |\n",
       "⋮\n",
       "| 47534 | \"Supplier#000000016\" | 3          | 29       | 34489.12      |\n",
       "| 47535 | \"Supplier#000000007\" | 1          | 39       | 44703.36      |\n",
       "\n",
       "| Row   | discount | tax  | returnflag | linestatus | shipdate   | commitdate |\n",
       "|-------|----------|------|------------|------------|------------|------------|\n",
       "| 1     | 0.04     | 0.02 | \"N\"        | \"O\"        | 1996-03-13 | 1996-02-12 |\n",
       "⋮\n",
       "| 47534 | 0.06     | 0.0  | \"R\"        | \"F\"        | 1993-11-08 | 1993-09-29 |\n",
       "| 47535 | 0.07     | 0.04 | \"R\"        | \"F\"        | 1992-11-24 | 1992-10-21 |\n",
       "\n",
       "| Row   | receiptdate | shipinstruct        | shipmode |\n",
       "|-------|-------------|---------------------|----------|\n",
       "| 1     | 1996-03-22  | \"DELIVER IN PERSON\" | \"TRUCK\"  |\n",
       "⋮\n",
       "| 47534 | 1993-11-16  | \"TAKE BACK RETURN\"  | \"MAIL\"   |\n",
       "| 47535 | 1992-12-12  | \"TAKE BACK RETURN\"  | \"MAIL\"   |\n",
       "\n",
       "| Row   | comment                                |\n",
       "|-------|----------------------------------------|\n",
       "| 1     | \"egular courts above the\"              |\n",
       "⋮\n",
       "| 47534 | \"ackages may serve above the furiousl\" |\n",
       "| 47535 | \"arefully ironic as\"                   |"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    lineitem\n",
    "    :filter(shipdate <= date(\"1998-12-01\") - DELTA*days),\n",
    "    DELTA=90)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, we group the lineitems by values of `returnflag` and `linestatus` attributes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>returnflag</th><th>linestatus</th><th>lineitem</th></tr><tr><th>1</th><td>A</td><td>F</td><td><table class=\"data-frame\"><tr><th></th><th>order</th><th>part</th><th>supplier</th><th>linenumber</th><th>quantity</th><th>extendedprice</th><th>discount</th><th>tax</th><th>returnflag</th><th>linestatus</th><th>shipdate</th><th>commitdate</th><th>receiptdate</th><th>shipinstruct</th><th>shipmode</th><th>comment</th></tr><tr><th>1</th><td>Customer#000000988</td><td>blue drab lime khaki sienna</td><td>Supplier#000000053</td><td>3</td><td>27</td><td>25083.54</td><td>0.06</td><td>0.07</td><td>A</td><td>F</td><td>1994-01-16</td><td>1993-11-22</td><td>1994-01-23</td><td>DELIVER IN PERSON</td><td>SHIP</td><td>nal foxes wake. </td></tr><tr><th>2</th><td>Customer#000000988</td><td>salmon antique burlywood linen peach</td><td>Supplier#000000019</td><td>4</td><td>2</td><td>2272.46</td><td>0.01</td><td>0.06</td><td>A</td><td>F</td><td>1993-12-04</td><td>1994-01-07</td><td>1994-01-01</td><td>NONE</td><td>TRUCK</td><td>y. fluffily pending d</td></tr><tr><th>3</th><td>Customer#000000988</td><td>sandy sky gainsboro peach cornflower</td><td>Supplier#000000017</td><td>6</td><td>26</td><td>36360.74</td><td>0.1</td><td>0.02</td><td>A</td><td>F</td><td>1993-10-29</td><td>1993-12-18</td><td>1993-11-04</td><td>TAKE BACK RETURN</td><td>RAIL</td><td>ges sleep after the caref</td></tr><tr><th>4</th><td>Customer#000000356</td><td>misty snow lace burnished linen</td><td>Supplier#000000005</td><td>3</td><td>50</td><td>60065.00</td><td>0.08</td><td>0.03</td><td>A</td><td>F</td><td>1994-08-08</td><td>1994-10-13</td><td>1994-08-26</td><td>DELIVER IN PERSON</td><td>AIR</td><td>eodolites. fluffily unusual</td></tr><tr><th>&vellip;</th><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td></tr></table></td></tr><tr><th>2</th><td>N</td><td>F</td><td><table class=\"data-frame\"><tr><th></th><th>order</th><th>part</th><th>supplier</th><th>linenumber</th><th>quantity</th><th>extendedprice</th><th>discount</th><th>tax</th><th>returnflag</th><th>linestatus</th><th>shipdate</th><th>commitdate</th><th>receiptdate</th><th>shipinstruct</th><th>shipmode</th><th>comment</th></tr><tr><th>1</th><td>Customer#000000262</td><td>hot midnight orchid dim steel</td><td>Supplier#000000027</td><td>4</td><td>25</td><td>26103.50</td><td>0.04</td><td>0.01</td><td>N</td><td>F</td><td>1995-06-13</td><td>1995-05-23</td><td>1995-06-24</td><td>TAKE BACK RETURN</td><td>FOB</td><td>s-- quickly final accounts</td></tr><tr><th>2</th><td>Customer#000000760</td><td>tan thistle frosted indian lawn</td><td>Supplier#000000011</td><td>1</td><td>31</td><td>32523.34</td><td>0.0</td><td>0.03</td><td>N</td><td>F</td><td>1995-06-05</td><td>1995-06-18</td><td>1995-06-26</td><td>COLLECT COD</td><td>FOB</td><td>final theodolites. fluffil</td></tr><tr><th>3</th><td>Customer#000000380</td><td>pale navajo royal papaya thistle</td><td>Supplier#000000042</td><td>1</td><td>42</td><td>50160.18</td><td>0.03</td><td>0.0</td><td>N</td><td>F</td><td>1995-06-07</td><td>1995-05-29</td><td>1995-06-23</td><td>TAKE BACK RETURN</td><td>SHIP</td><td>y asymptotes. regular depen</td></tr><tr><th>4</th><td>Customer#000001066</td><td>navy light red royal olive</td><td>Supplier#000000061</td><td>1</td><td>16</td><td>18468.00</td><td>0.1</td><td>0.06</td><td>N</td><td>F</td><td>1995-05-23</td><td>1995-05-07</td><td>1995-06-19</td><td>NONE</td><td>TRUCK</td><td>un quickly slyly</td></tr><tr><th>&vellip;</th><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td></tr></table></td></tr><tr><th>3</th><td>N</td><td>O</td><td><table class=\"data-frame\"><tr><th></th><th>order</th><th>part</th><th>supplier</th><th>linenumber</th><th>quantity</th><th>extendedprice</th><th>discount</th><th>tax</th><th>returnflag</th><th>linestatus</th><th>shipdate</th><th>commitdate</th><th>receiptdate</th><th>shipinstruct</th><th>shipmode</th><th>comment</th></tr><tr><th>1</th><td>Customer#000000296</td><td>dodger brown ivory blue pink</td><td>Supplier#000000078</td><td>1</td><td>17</td><td>19435.08</td><td>0.04</td><td>0.02</td><td>N</td><td>O</td><td>1996-03-13</td><td>1996-02-12</td><td>1996-03-22</td><td>DELIVER IN PERSON</td><td>TRUCK</td><td>egular courts above the</td></tr><tr><th>2</th><td>Customer#000000296</td><td>dodger midnight salmon drab saddle</td><td>Supplier#000000060</td><td>2</td><td>36</td><td>51823.08</td><td>0.09</td><td>0.06</td><td>N</td><td>O</td><td>1996-04-12</td><td>1996-02-28</td><td>1996-04-20</td><td>TAKE BACK RETURN</td><td>MAIL</td><td>ly final dependencies: slyly bold </td></tr><tr><th>3</th><td>Customer#000000296</td><td>blush thistle orchid red lace</td><td>Supplier#000000031</td><td>3</td><td>8</td><td>11284.08</td><td>0.1</td><td>0.02</td><td>N</td><td>O</td><td>1996-01-29</td><td>1996-03-05</td><td>1996-01-31</td><td>TAKE BACK RETURN</td><td>REG AIR</td><td>riously. regular, express dep</td></tr><tr><th>4</th><td>Customer#000000296</td><td>turquoise indian lemon lavender misty</td><td>Supplier#000000039</td><td>4</td><td>28</td><td>25704.28</td><td>0.09</td><td>0.06</td><td>N</td><td>O</td><td>1996-04-21</td><td>1996-03-30</td><td>1996-05-16</td><td>NONE</td><td>AIR</td><td>lites. fluffily even de</td></tr><tr><th>&vellip;</th><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td></tr></table></td></tr><tr><th>4</th><td>R</td><td>F</td><td><table class=\"data-frame\"><tr><th></th><th>order</th><th>part</th><th>supplier</th><th>linenumber</th><th>quantity</th><th>extendedprice</th><th>discount</th><th>tax</th><th>returnflag</th><th>linestatus</th><th>shipdate</th><th>commitdate</th><th>receiptdate</th><th>shipinstruct</th><th>shipmode</th><th>comment</th></tr><tr><th>1</th><td>Customer#000000988</td><td>green blush tomato burlywood seashell</td><td>Supplier#000000016</td><td>1</td><td>45</td><td>42076.35</td><td>0.06</td><td>0.0</td><td>R</td><td>F</td><td>1994-02-02</td><td>1994-01-04</td><td>1994-02-23</td><td>NONE</td><td>AIR</td><td>ongside of the furiously brave acco</td></tr><tr><th>2</th><td>Customer#000000988</td><td>linen frosted slate coral peru</td><td>Supplier#000000057</td><td>2</td><td>49</td><td>51604.35</td><td>0.1</td><td>0.0</td><td>R</td><td>F</td><td>1993-11-09</td><td>1993-12-20</td><td>1993-11-24</td><td>TAKE BACK RETURN</td><td>RAIL</td><td> unusual accounts. eve</td></tr><tr><th>3</th><td>Customer#000000988</td><td>bisque saddle cyan rose wheat</td><td>Supplier#000000060</td><td>5</td><td>28</td><td>38260.88</td><td>0.04</td><td>0.0</td><td>R</td><td>F</td><td>1993-12-14</td><td>1994-01-10</td><td>1994-01-01</td><td>TAKE BACK RETURN</td><td>FOB</td><td>ages nag slyly pending</td></tr><tr><th>4</th><td>Customer#000000356</td><td>snow blush violet lace ghost</td><td>Supplier#000000070</td><td>1</td><td>15</td><td>26547.90</td><td>0.02</td><td>0.04</td><td>R</td><td>F</td><td>1994-10-31</td><td>1994-08-31</td><td>1994-11-20</td><td>NONE</td><td>AIR</td><td>ts wake furiously </td></tr><tr><th>&vellip;</th><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td></tr></table></td></tr></table>"
      ],
      "text/plain": [
       "4x3 DataFrames.DataFrame\n",
       "| Row | returnflag | linestatus |\n",
       "|-----|------------|------------|\n",
       "| 1   | \"A\"        | \"F\"        |\n",
       "| 2   | \"N\"        | \"F\"        |\n",
       "| 3   | \"N\"        | \"O\"        |\n",
       "| 4   | \"R\"        | \"F\"        |\n",
       "\n",
       "| Row | lineitem                                                                                                                                                                                                                                                                                                                                                        |\n",
       "|-----|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|\n",
       "| 1   | 11958x16 DataFrames.DataFrame\n",
       "| Row   | order                | part                                 |\n",
       "|-------|----------------------|--------------------------------------|\n",
       "| 1     | \"Customer#000000988\" | \"blue drab lime khaki sienna\"        |\n",
       "⋮\n",
       "| 11957 | \"Customer#000000529\" | \"chiffon royal lime almond midnight\" |\n",
       "| 11958 | \"Customer#000001036\" | \"medium forest blue ghost black\"     |\n",
       "\n",
       "| Row   | supplier             | linenumber | quantity | extendedprice |\n",
       "|-------|----------------------|------------|----------|---------------|\n",
       "| 1     | \"Supplier#000000053\" | 3          | 27       | 25083.54      |\n",
       "⋮\n",
       "| 11957 | \"Supplier#000000034\" | 4          | 14       | 24960.32      |\n",
       "| 11958 | \"Supplier#000000063\" | 2          | 42       | 38724.84      |\n",
       "\n",
       "| Row   | discount | tax  | returnflag | linestatus | shipdate   | commitdate |\n",
       "|-------|----------|------|------------|------------|------------|------------|\n",
       "| 1     | 0.06     | 0.07 | \"A\"        | \"F\"        | 1994-01-16 | 1993-11-22 |\n",
       "⋮\n",
       "| 11957 | 0.08     | 0.07 | \"A\"        | \"F\"        | 1994-12-10 | 1994-10-07 |\n",
       "| 11958 | 0.05     | 0.02 | \"A\"        | \"F\"        | 1993-12-13 | 1993-09-29 |\n",
       "\n",
       "| Row   | receiptdate | shipinstruct        | shipmode | comment            |\n",
       "|-------|-------------|---------------------|----------|--------------------|\n",
       "| 1     | 1994-01-23  | \"DELIVER IN PERSON\" | \"SHIP\"   | \"nal foxes wake. \" |\n",
       "⋮\n",
       "| 11957 | 1994-12-27  | \"TAKE BACK RETURN\"  | \"TRUCK\"  | \" express reques\"  |\n",
       "| 11958 | 1993-12-26  | \"COLLECT COD\"       | \"SHIP\"   | \"tions. daringl\"   |                                                                                                                                                                                                                                                                                   |\n",
       "| 2   | 288x16 DataFrames.DataFrame\n",
       "| Row | order                | part                                         |\n",
       "|-----|----------------------|----------------------------------------------|\n",
       "| 1   | \"Customer#000000262\" | \"hot midnight orchid dim steel\"              |\n",
       "⋮\n",
       "| 287 | \"Customer#000000397\" | \"wheat blush forest metallic navajo\"         |\n",
       "| 288 | \"Customer#000000397\" | \"burnished seashell floral moccasin antique\" |\n",
       "\n",
       "| Row | supplier             | linenumber | quantity | extendedprice |\n",
       "|-----|----------------------|------------|----------|---------------|\n",
       "| 1   | \"Supplier#000000027\" | 4          | 25       | 26103.50      |\n",
       "⋮\n",
       "| 287 | \"Supplier#000000045\" | 1          | 2        | 2048.24       |\n",
       "| 288 | \"Supplier#000000075\" | 2          | 50       | 85740.50      |\n",
       "\n",
       "| Row | discount | tax  | returnflag | linestatus | shipdate   | commitdate |\n",
       "|-----|----------|------|------------|------------|------------|------------|\n",
       "| 1   | 0.04     | 0.01 | \"N\"        | \"F\"        | 1995-06-13 | 1995-05-23 |\n",
       "⋮\n",
       "| 287 | 0.04     | 0.0  | \"N\"        | \"F\"        | 1995-06-17 | 1995-07-08 |\n",
       "| 288 | 0.02     | 0.01 | \"N\"        | \"F\"        | 1995-05-28 | 1995-08-03 |\n",
       "\n",
       "| Row | receiptdate | shipinstruct       | shipmode |\n",
       "|-----|-------------|--------------------|----------|\n",
       "| 1   | 1995-06-24  | \"TAKE BACK RETURN\" | \"FOB\"    |\n",
       "⋮\n",
       "| 287 | 1995-06-20  | \"NONE\"             | \"FOB\"    |\n",
       "| 288 | 1995-06-25  | \"COLLECT COD\"      | \"FOB\"    |\n",
       "\n",
       "| Row | comment                      |\n",
       "|-----|------------------------------|\n",
       "| 1   | \"s-- quickly final accounts\" |\n",
       "⋮\n",
       "| 287 | \"refully slyly ironic re\"    |\n",
       "| 288 | \"ly enticing ideas\"          |                                                                                                 |\n",
       "| 3   | 23326x16 DataFrames.DataFrame\n",
       "| Row   | order                | part                                  |\n",
       "|-------|----------------------|---------------------------------------|\n",
       "| 1     | \"Customer#000000296\" | \"dodger brown ivory blue pink\"        |\n",
       "⋮\n",
       "| 23325 | \"Customer#000000110\" | \"pale tan lavender powder cornflower\" |\n",
       "| 23326 | \"Customer#000000110\" | \"plum midnight coral snow lemon\"      |\n",
       "\n",
       "| Row   | supplier             | linenumber | quantity | extendedprice |\n",
       "|-------|----------------------|------------|----------|---------------|\n",
       "| 1     | \"Supplier#000000078\" | 1          | 17       | 19435.08      |\n",
       "⋮\n",
       "| 23325 | \"Supplier#000000051\" | 6          | 30       | 36939.90      |\n",
       "| 23326 | \"Supplier#000000044\" | 7          | 35       | 49893.20      |\n",
       "\n",
       "| Row   | discount | tax  | returnflag | linestatus | shipdate   | commitdate |\n",
       "|-------|----------|------|------------|------------|------------|------------|\n",
       "| 1     | 0.04     | 0.02 | \"N\"        | \"O\"        | 1996-03-13 | 1996-02-12 |\n",
       "⋮\n",
       "| 23325 | 0.07     | 0.05 | \"N\"        | \"O\"        | 1997-08-14 | 1997-09-13 |\n",
       "| 23326 | 0.09     | 0.01 | \"N\"        | \"O\"        | 1997-09-08 | 1997-08-27 |\n",
       "\n",
       "| Row   | receiptdate | shipinstruct        | shipmode |\n",
       "|-------|-------------|---------------------|----------|\n",
       "| 1     | 1996-03-22  | \"DELIVER IN PERSON\" | \"TRUCK\"  |\n",
       "⋮\n",
       "| 23325 | 1997-09-08  | \"NONE\"              | \"TRUCK\"  |\n",
       "| 23326 | 1997-09-15  | \"NONE\"              | \"MAIL\"   |\n",
       "\n",
       "| Row   | comment                                       |\n",
       "|-------|-----------------------------------------------|\n",
       "| 1     | \"egular courts above the\"                     |\n",
       "⋮\n",
       "| 23325 | \"ously bold packages. foxes will have to wak\" |\n",
       "| 23326 | \"cuses use finally unusual platel\"            | |\n",
       "| 4   | 11963x16 DataFrames.DataFrame\n",
       "| Row   | order                | part                                    |\n",
       "|-------|----------------------|-----------------------------------------|\n",
       "| 1     | \"Customer#000000988\" | \"green blush tomato burlywood seashell\" |\n",
       "⋮\n",
       "| 11962 | \"Customer#000001036\" | \"peach antique deep peru saddle\"        |\n",
       "| 11963 | \"Customer#000000871\" | \"burlywood orchid dark drab dodger\"     |\n",
       "\n",
       "| Row   | supplier             | linenumber | quantity | extendedprice |\n",
       "|-------|----------------------|------------|----------|---------------|\n",
       "| 1     | \"Supplier#000000016\" | 1          | 45       | 42076.35      |\n",
       "⋮\n",
       "| 11962 | \"Supplier#000000016\" | 3          | 29       | 34489.12      |\n",
       "| 11963 | \"Supplier#000000007\" | 1          | 39       | 44703.36      |\n",
       "\n",
       "| Row   | discount | tax  | returnflag | linestatus | shipdate   | commitdate |\n",
       "|-------|----------|------|------------|------------|------------|------------|\n",
       "| 1     | 0.06     | 0.0  | \"R\"        | \"F\"        | 1994-02-02 | 1994-01-04 |\n",
       "⋮\n",
       "| 11962 | 0.06     | 0.0  | \"R\"        | \"F\"        | 1993-11-08 | 1993-09-29 |\n",
       "| 11963 | 0.07     | 0.04 | \"R\"        | \"F\"        | 1992-11-24 | 1992-10-21 |\n",
       "\n",
       "| Row   | receiptdate | shipinstruct       | shipmode |\n",
       "|-------|-------------|--------------------|----------|\n",
       "| 1     | 1994-02-23  | \"NONE\"             | \"AIR\"    |\n",
       "⋮\n",
       "| 11962 | 1993-11-16  | \"TAKE BACK RETURN\" | \"MAIL\"   |\n",
       "| 11963 | 1992-12-12  | \"TAKE BACK RETURN\" | \"MAIL\"   |\n",
       "\n",
       "| Row   | comment                                |\n",
       "|-------|----------------------------------------|\n",
       "| 1     | \"ongside of the furiously brave acco\"  |\n",
       "⋮\n",
       "| 11962 | \"ackages may serve above the furiousl\" |\n",
       "| 11963 | \"arefully ironic as\"                   |                                     |"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    lineitem\n",
    "    :filter(shipdate <= date(\"1998-12-01\") - DELTA*days)\n",
    "    :group(returnflag, linestatus),\n",
    "    DELTA=90)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For each distinct combination of `returnflag` and `linestatus` values, we see a collection of corresponding lineitem rows.  We can now use aggregate functions to summarize information about matching lineitems.\n",
    "\n",
    "For example, to find the total number of matching lineitems, we can use `count` aggregate."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>returnflag</th><th>linestatus</th><th></th></tr><tr><th>1</th><td>A</td><td>F</td><td>11958</td></tr><tr><th>2</th><td>N</td><td>F</td><td>288</td></tr><tr><th>3</th><td>N</td><td>O</td><td>23326</td></tr><tr><th>4</th><td>R</td><td>F</td><td>11963</td></tr></table>"
      ],
      "text/plain": [
       "4x3 DataFrames.DataFrame\n",
       "| Row | returnflag | linestatus |       |\n",
       "|-----|------------|------------|-------|\n",
       "| 1   | \"A\"        | \"F\"        | 11958 |\n",
       "| 2   | \"N\"        | \"F\"        | 288   |\n",
       "| 3   | \"N\"        | \"O\"        | 23326 |\n",
       "| 4   | \"R\"        | \"F\"        | 11963 |"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    lineitem\n",
    "    :filter(shipdate <= date(\"1998-12-01\") - DELTA*days)\n",
    "    :group(returnflag, linestatus)\n",
    "    :select(returnflag, linestatus, count(lineitem)),\n",
    "    DELTA=90)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The query output now has the shape of the report.  We only need to calculate the respective fields."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>returnflag</th><th>linestatus</th><th>sum_qty</th><th>sum_base_price</th><th>sum_disc_price</th><th>sum_charge</th><th>avg_qty</th><th>avg_price</th><th>avg_disc</th><th>count_order</th></tr><tr><th>1</th><td>A</td><td>F</td><td>303771</td><td>401515429.00</td><td>381465877.43</td><td>396851569.64</td><td>25.40316106372303</td><td>33577.14</td><td>0.05011958521491882</td><td>11958</td></tr><tr><th>2</th><td>N</td><td>F</td><td>7354</td><td>9721375.74</td><td>9247800.68</td><td>9616674.43</td><td>25.53472222222222</td><td>33754.78</td><td>0.04809027777777776</td><td>288</td></tr><tr><th>3</th><td>N</td><td>O</td><td>593340</td><td>787065026.86</td><td>748034019.78</td><td>777970207.05</td><td>25.436851581925747</td><td>33741.96</td><td>0.04988510674783497</td><td>23326</td></tr><tr><th>4</th><td>R</td><td>F</td><td>306623</td><td>406030555.01</td><td>385792652.67</td><td>401431970.22</td><td>25.630945415029675</td><td>33940.53</td><td>0.04973083674663541</td><td>11963</td></tr></table>"
      ],
      "text/plain": [
       "4x10 DataFrames.DataFrame\n",
       "| Row | returnflag | linestatus | sum_qty | sum_base_price | sum_disc_price |\n",
       "|-----|------------|------------|---------|----------------|----------------|\n",
       "| 1   | \"A\"        | \"F\"        | 303771  | 401515429.00   | 381465877.43   |\n",
       "| 2   | \"N\"        | \"F\"        | 7354    | 9721375.74     | 9247800.68     |\n",
       "| 3   | \"N\"        | \"O\"        | 593340  | 787065026.86   | 748034019.78   |\n",
       "| 4   | \"R\"        | \"F\"        | 306623  | 406030555.01   | 385792652.67   |\n",
       "\n",
       "| Row | sum_charge   | avg_qty | avg_price | avg_disc  | count_order |\n",
       "|-----|--------------|---------|-----------|-----------|-------------|\n",
       "| 1   | 396851569.64 | 25.4032 | 33577.14  | 0.0501196 | 11958       |\n",
       "| 2   | 9616674.43   | 25.5347 | 33754.78  | 0.0480903 | 288         |\n",
       "| 3   | 777970207.05 | 25.4369 | 33741.96  | 0.0498851 | 23326       |\n",
       "| 4   | 401431970.22 | 25.6309 | 33940.53  | 0.0497308 | 11963       |"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    lineitem\n",
    "    :filter(shipdate <= date(\"1998-12-01\") - DELTA*days)\n",
    "    :group(returnflag, linestatus)\n",
    "    :select(\n",
    "        returnflag,\n",
    "        linestatus,\n",
    "        sum_qty => sum(lineitem.quantity),\n",
    "        sum_base_price => sum(lineitem.extendedprice),\n",
    "        sum_disc_price => sum(lineitem.(extendedprice*(1-discount))),\n",
    "        sum_charge => sum(lineitem.(extendedprice*(1-discount)*(1+tax))),\n",
    "        avg_qty => mean(lineitem.quantity),\n",
    "        avg_price => mean(lineitem.extendedprice),\n",
    "        avg_disc => mean(lineitem.discount),\n",
    "        count_order => count(lineitem)),\n",
    "    DELTA=90)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Minimum Cost Supplier Query (Q2)\n",
    "*[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 30]*\n",
    "\n",
    "This query finds which supplier should be selected to place an order for a given part in a given region.\n",
    "\n",
    "### Business Question\n",
    "The Minimum Cost Supplier Query finds, in a given region, for each part of a certain type and size, the supplier who\n",
    "can supply it at minimum cost. If several suppliers in that region offer the desired part type and size at the same\n",
    "(minimum) cost, the query lists the parts from suppliers with the 100 highest account balances. For each supplier,\n",
    "the query lists the supplier's account balance, name and nation; the part's number and manufacturer; the supplier's\n",
    "address, phone number and comment information.\n",
    "\n",
    "\n",
    "### Query Definition\n",
    "```\n",
    "select\n",
    "    s_acctbal,\n",
    "    s_name,\n",
    "    n_name,\n",
    "    p_partkey,\n",
    "    p_mfgr,\n",
    "    s_address,\n",
    "    s_phone,\n",
    "    s_comment\n",
    "from\n",
    "    part,\n",
    "    supplier,\n",
    "    partsupp,\n",
    "    nation,\n",
    "    region\n",
    "where\n",
    "    p_partkey = ps_partkey\n",
    "    and s_suppkey = ps_suppkey\n",
    "    and p_size = [SIZE]\n",
    "    and p_type like '%[TYPE]'\n",
    "    and s_nationkey = n_nationkey\n",
    "    and n_regionkey = r_regionkey\n",
    "    and r_name = '[REGION]'\n",
    "    and ps_supplycost = (\n",
    "        select\n",
    "            min(ps_supplycost)\n",
    "        from\n",
    "            partsupp, supplier,\n",
    "            nation, region\n",
    "        where\n",
    "            p_partkey = ps_partkey\n",
    "            and s_suppkey = ps_suppkey\n",
    "            and s_nationkey = n_nationkey\n",
    "            and n_regionkey = r_regionkey\n",
    "            and r_name = '[REGION]')\n",
    "order by\n",
    "    s_acctbal desc,\n",
    "    n_name,\n",
    "    s_name,\n",
    "    p_partkey\n",
    "limit 100;\n",
    "```\n",
    "\n",
    "### Substitution Parameters\n",
    "1. SIZE = 8;\n",
    "2. TYPE = BRASS;\n",
    "3. REGION = EUROPE."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This report extracts data from tables `part`, `supplier` as well as table `partsupp` that relates each supplier to their inventory of parts.  We start with getting a list of parts of the given size and type in the inventories of all suppliers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>part</th><th>supplier</th><th>availqty</th><th>supplycost</th><th>comment</th></tr><tr><th>1</th><td>drab aquamarine red papaya pale</td><td>Supplier#000000009</td><td>7731</td><td>65.54</td><td>ronic accounts haggle blithely across the blithely special platelets. furiously final ideas haggle carefully after the fluffily unusual dep</td></tr><tr><th>2</th><td>drab aquamarine red papaya pale</td><td>Supplier#000000032</td><td>8847</td><td>935.64</td><td>ular, regular requests affix ironically enticing ideas. fur</td></tr><tr><th>3</th><td>drab aquamarine red papaya pale</td><td>Supplier#000000055</td><td>6329</td><td>148.79</td><td>enticingly final ideas sleep fluffily. blithely final ideas boost fluffily ironic asymptote</td></tr><tr><th>4</th><td>drab aquamarine red papaya pale</td><td>Supplier#000000078</td><td>8054</td><td>425.78</td><td>the express accounts. even, ironic pinto beans wake furious</td></tr><tr><th>5</th><td>blue gainsboro sky burnished puff</td><td>Supplier#000000014</td><td>8029</td><td>765.35</td><td>ous packages sleep slyly across the quickly ironic accounts. regular packages thrash above the furiously final theodolites. furi</td></tr><tr><th>6</th><td>blue gainsboro sky burnished puff</td><td>Supplier#000000040</td><td>1481</td><td>319.99</td><td>coys against the even instructions cajole furiously bold, daring courts. slyly ironic deposits ought to wake blithely regular ideas. slyly regular ideas according to the slyly express ac</td></tr><tr><th>7</th><td>blue gainsboro sky burnished puff</td><td>Supplier#000000066</td><td>718</td><td>634.58</td><td>furiously slyly ironic packages. pending requests use ruthlessly across the blithely bold deposits. furiously fin</td></tr><tr><th>8</th><td>blue gainsboro sky burnished puff</td><td>Supplier#000000012</td><td>2818</td><td>237.16</td><td> the carefully special frays. final excuses haggle slyly. furiously regular deposits across t</td></tr><tr><th>9</th><td>plum indian cornflower frosted purple</td><td>Supplier#000000073</td><td>1076</td><td>844.74</td><td>y ironic instructions. even, final requests are. bold instructions use silent, regular accounts-- slyly final excuses doubt fluffily after the quickly p</td></tr><tr><th>10</th><td>plum indian cornflower frosted purple</td><td>Supplier#000000022</td><td>9272</td><td>904.83</td><td>ly regular packages. ruthless instructions haggle carefully final, special courts. silently ruthless courts hinder slyly regular packages; regular, regular pac</td></tr><tr><th>&vellip;</th><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td></tr></table>"
      ],
      "text/plain": [
       "32x5 DataFrames.DataFrame\n",
       "| Row | part                                | supplier             | availqty |\n",
       "|-----|-------------------------------------|----------------------|----------|\n",
       "| 1   | \"drab aquamarine red papaya pale\"   | \"Supplier#000000009\" | 7731     |\n",
       "⋮\n",
       "| 31  | \"plum beige misty cream cornflower\" | \"Supplier#000000013\" | 2973     |\n",
       "| 32  | \"plum beige misty cream cornflower\" | \"Supplier#000000050\" | 5417     |\n",
       "\n",
       "| Row | supplycost |\n",
       "|-----|------------|\n",
       "| 1   | 65.54      |\n",
       "⋮\n",
       "| 31  | 727.39     |\n",
       "| 32  | 620.51     |\n",
       "\n",
       "| Row | comment                                                                                                                                                 |\n",
       "|-----|---------------------------------------------------------------------------------------------------------------------------------------------------------|\n",
       "| 1   | \"ronic accounts haggle blithely across the blithely special platelets. furiously final ideas haggle carefully after the fluffily unusual dep\"           |\n",
       "⋮\n",
       "| 31  | \"at slyly final packages. enticingly pending excuses across the sometimes final dependencies sleep evenly above the express dep\"                        |\n",
       "| 32  | \"requests against the blithely unusual deposits wake furiously furiously special platelets. ironically unusual accounts detect blithely. finally unusu\" |"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    partsupp\n",
    "    :filter(\n",
    "        (part.size == SIZE) &\n",
    "        contains(part.type_, TYPE)),\n",
    "    SIZE=8, TYPE=\"BRASS\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Among all suppliers, we are only interested in those who are situated in the given REGION."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>part</th><th>supplier</th><th>availqty</th><th>supplycost</th><th>comment</th></tr><tr><th>1</th><td>drab aquamarine red papaya pale</td><td>Supplier#000000032</td><td>8847</td><td>935.64</td><td>ular, regular requests affix ironically enticing ideas. fur</td></tr><tr><th>2</th><td>blue gainsboro sky burnished puff</td><td>Supplier#000000040</td><td>1481</td><td>319.99</td><td>coys against the even instructions cajole furiously bold, daring courts. slyly ironic deposits ought to wake blithely regular ideas. slyly regular ideas according to the slyly express ac</td></tr><tr><th>3</th><td>blue gainsboro sky burnished puff</td><td>Supplier#000000066</td><td>718</td><td>634.58</td><td>furiously slyly ironic packages. pending requests use ruthlessly across the blithely bold deposits. furiously fin</td></tr><tr><th>4</th><td>tan ghost cyan salmon goldenrod</td><td>Supplier#000000066</td><td>322</td><td>104.62</td><td>g the quickly express dolphins. slyly even instructions nag furiously. carefully final ideas are idly above the ironic asymptotes. unusual requests haggle about t</td></tr><tr><th>5</th><td>tan ghost cyan salmon goldenrod</td><td>Supplier#000000016</td><td>6830</td><td>298.77</td><td>ss orbits. furiously regular deposits haggle blithely carefully final pinto beans. ironic, silent accounts thrash quickly about the accounts. iron</td></tr><tr><th>6</th><td>frosted blue ghost purple coral</td><td>Supplier#000000025</td><td>290</td><td>774.80</td><td>r pinto beans-- quickly final instructions integrate alongside of the sly, silent th</td></tr><tr><th>7</th><td>burlywood seashell slate white cream</td><td>Supplier#000000040</td><td>9737</td><td>799.94</td><td>ns cajole idly. deposits are quickly blithely even deposits. quickly regular packages boost carefully. blithely ironic asympto</td></tr><tr><th>8</th><td>burlywood seashell slate white cream</td><td>Supplier#000000077</td><td>9194</td><td>240.78</td><td>l requests cajole carefully. blithely express gifts</td></tr></table>"
      ],
      "text/plain": [
       "8x5 DataFrames.DataFrame\n",
       "| Row | part                                   | supplier             |\n",
       "|-----|----------------------------------------|----------------------|\n",
       "| 1   | \"drab aquamarine red papaya pale\"      | \"Supplier#000000032\" |\n",
       "⋮\n",
       "| 7   | \"burlywood seashell slate white cream\" | \"Supplier#000000040\" |\n",
       "| 8   | \"burlywood seashell slate white cream\" | \"Supplier#000000077\" |\n",
       "\n",
       "| Row | availqty | supplycost |\n",
       "|-----|----------|------------|\n",
       "| 1   | 8847     | 935.64     |\n",
       "⋮\n",
       "| 7   | 9737     | 799.94     |\n",
       "| 8   | 9194     | 240.78     |\n",
       "\n",
       "| Row | comment                                                                                                                          |\n",
       "|-----|----------------------------------------------------------------------------------------------------------------------------------|\n",
       "| 1   | \"ular, regular requests affix ironically enticing ideas. fur\"                                                                    |\n",
       "⋮\n",
       "| 7   | \"ns cajole idly. deposits are quickly blithely even deposits. quickly regular packages boost carefully. blithely ironic asympto\" |\n",
       "| 8   | \"l requests cajole carefully. blithely express gifts\"                                                                            |"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    partsupp\n",
    "    :filter(\n",
    "        (part.size == SIZE) &\n",
    "        contains(part.type_, TYPE) &\n",
    "        (supplier.nation.region.name == REGION)),\n",
    "    SIZE=8, TYPE=\"BRASS\", REGION=\"EUROPE\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Some parts are offered by more than one supplier.  We can use combinator `and_around(part)` to relate each row of the output to all the others that provide the same part."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>part</th><th>supplier</th><th>supplycost</th><th>othersupplycost</th></tr><tr><th>1</th><td>drab aquamarine red papaya pale</td><td>Supplier#000000032</td><td>935.64</td><td>[935.64]</td></tr><tr><th>2</th><td>blue gainsboro sky burnished puff</td><td>Supplier#000000040</td><td>319.99</td><td>[319.99,634.58]</td></tr><tr><th>3</th><td>blue gainsboro sky burnished puff</td><td>Supplier#000000066</td><td>634.58</td><td>[319.99,634.58]</td></tr><tr><th>4</th><td>tan ghost cyan salmon goldenrod</td><td>Supplier#000000066</td><td>104.62</td><td>[104.62,298.77]</td></tr><tr><th>5</th><td>tan ghost cyan salmon goldenrod</td><td>Supplier#000000016</td><td>298.77</td><td>[104.62,298.77]</td></tr><tr><th>6</th><td>frosted blue ghost purple coral</td><td>Supplier#000000025</td><td>774.80</td><td>[774.80]</td></tr><tr><th>7</th><td>burlywood seashell slate white cream</td><td>Supplier#000000040</td><td>799.94</td><td>[799.94,240.78]</td></tr><tr><th>8</th><td>burlywood seashell slate white cream</td><td>Supplier#000000077</td><td>240.78</td><td>[799.94,240.78]</td></tr></table>"
      ],
      "text/plain": [
       "8x4 DataFrames.DataFrame\n",
       "| Row | part                                   | supplier             |\n",
       "|-----|----------------------------------------|----------------------|\n",
       "| 1   | \"drab aquamarine red papaya pale\"      | \"Supplier#000000032\" |\n",
       "⋮\n",
       "| 7   | \"burlywood seashell slate white cream\" | \"Supplier#000000040\" |\n",
       "| 8   | \"burlywood seashell slate white cream\" | \"Supplier#000000077\" |\n",
       "\n",
       "| Row | supplycost | othersupplycost |\n",
       "|-----|------------|-----------------|\n",
       "| 1   | 935.64     | [935.64]        |\n",
       "⋮\n",
       "| 7   | 799.94     | [799.94,240.78] |\n",
       "| 8   | 240.78     | [799.94,240.78] |"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    partsupp\n",
    "    :filter(\n",
    "        (part.size == SIZE) &\n",
    "        contains(part.type_, TYPE) &\n",
    "        (supplier.nation.region.name == REGION))\n",
    "    :select(part, supplier, supplycost, othersupplycost => and_around(part).supplycost),\n",
    "    SIZE=8, TYPE=\"BRASS\", REGION=\"EUROPE\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Similarly, we can select rows that provide their part at the minimal cost."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>part</th><th>supplier</th><th>availqty</th><th>supplycost</th><th>comment</th></tr><tr><th>1</th><td>drab aquamarine red papaya pale</td><td>Supplier#000000032</td><td>8847</td><td>935.64</td><td>ular, regular requests affix ironically enticing ideas. fur</td></tr><tr><th>2</th><td>blue gainsboro sky burnished puff</td><td>Supplier#000000040</td><td>1481</td><td>319.99</td><td>coys against the even instructions cajole furiously bold, daring courts. slyly ironic deposits ought to wake blithely regular ideas. slyly regular ideas according to the slyly express ac</td></tr><tr><th>3</th><td>tan ghost cyan salmon goldenrod</td><td>Supplier#000000066</td><td>322</td><td>104.62</td><td>g the quickly express dolphins. slyly even instructions nag furiously. carefully final ideas are idly above the ironic asymptotes. unusual requests haggle about t</td></tr><tr><th>4</th><td>frosted blue ghost purple coral</td><td>Supplier#000000025</td><td>290</td><td>774.80</td><td>r pinto beans-- quickly final instructions integrate alongside of the sly, silent th</td></tr><tr><th>5</th><td>burlywood seashell slate white cream</td><td>Supplier#000000077</td><td>9194</td><td>240.78</td><td>l requests cajole carefully. blithely express gifts</td></tr></table>"
      ],
      "text/plain": [
       "5x5 DataFrames.DataFrame\n",
       "| Row | part                                   | supplier             |\n",
       "|-----|----------------------------------------|----------------------|\n",
       "| 1   | \"drab aquamarine red papaya pale\"      | \"Supplier#000000032\" |\n",
       "| 2   | \"blue gainsboro sky burnished puff\"    | \"Supplier#000000040\" |\n",
       "| 3   | \"tan ghost cyan salmon goldenrod\"      | \"Supplier#000000066\" |\n",
       "| 4   | \"frosted blue ghost purple coral\"      | \"Supplier#000000025\" |\n",
       "| 5   | \"burlywood seashell slate white cream\" | \"Supplier#000000077\" |\n",
       "\n",
       "| Row | availqty | supplycost |\n",
       "|-----|----------|------------|\n",
       "| 1   | 8847     | 935.64     |\n",
       "| 2   | 1481     | 319.99     |\n",
       "| 3   | 322      | 104.62     |\n",
       "| 4   | 290      | 774.80     |\n",
       "| 5   | 9194     | 240.78     |\n",
       "\n",
       "| Row | comment                                                                                                                                                                                      |\n",
       "|-----|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|\n",
       "| 1   | \"ular, regular requests affix ironically enticing ideas. fur\"                                                                                                                                |\n",
       "| 2   | \"coys against the even instructions cajole furiously bold, daring courts. slyly ironic deposits ought to wake blithely regular ideas. slyly regular ideas according to the slyly express ac\" |\n",
       "| 3   | \"g the quickly express dolphins. slyly even instructions nag furiously. carefully final ideas are idly above the ironic asymptotes. unusual requests haggle about t\"                         |\n",
       "| 4   | \"r pinto beans-- quickly final instructions integrate alongside of the sly, silent th\"                                                                                                       |\n",
       "| 5   | \"l requests cajole carefully. blithely express gifts\"                                                                                                                                        |"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    partsupp\n",
    "    :filter(\n",
    "        (part.size == SIZE) &\n",
    "        contains(part.type_, TYPE) &\n",
    "        (supplier.nation.region.name == REGION))\n",
    "    :filter(supplycost == min(and_around(part).supplycost)),\n",
    "    SIZE=8, TYPE=\"BRASS\", REGION=\"EUROPE\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We have the rows that we need in the requested report.  We only need to sort them and show the requested columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>acctbal</th><th>name</th><th>nation</th><th>id</th><th>mfgr</th><th>address</th><th>phone</th><th>comment</th></tr><tr><th>1</th><td>9198.31</td><td>Supplier#000000025</td><td>RUSSIA</td><td>1156</td><td>Manufacturer#5</td><td>RCQKONXMFnrodzz6w7fObFVV6CUm2q</td><td>32-431-945-3541</td><td>ely regular deposits. carefully regular sauternes engage furiously above the regular accounts. idly </td></tr><tr><th>2</th><td>4186.95</td><td>Supplier#000000077</td><td>GERMANY</td><td>1362</td><td>Manufacturer#1</td><td>wVtcr0uH3CyrSiWMLsqnB09Syo,UuZxPMeBghlY</td><td>17-281-345-4863</td><td>the slyly final asymptotes. blithely pending theodoli</td></tr><tr><th>3</th><td>3556.47</td><td>Supplier#000000032</td><td>UNITED KINGDOM</td><td>248</td><td>Manufacturer#1</td><td>yvoD3TtZSx1skQNCK8agk5bZlZLug</td><td>33-484-637-7873</td><td>usly even depths. quickly ironic theodolites s</td></tr><tr><th>4</th><td>2455.98</td><td>Supplier#000000066</td><td>UNITED KINGDOM</td><td>805</td><td>Manufacturer#5</td><td>qYdruFJQJYYiKvnNVmYfCVydVB8bcW,AW,U6SOV3</td><td>33-300-836-9529</td><td>ar requests. express orbits de</td></tr><tr><th>5</th><td>-290.06</td><td>Supplier#000000040</td><td>RUSSIA</td><td>493</td><td>Manufacturer#4</td><td>zyIeWzbbpkTV37vm1nmSGBxSgd2Kp</td><td>32-231-247-6991</td><td> final patterns. accounts haggle idly pas</td></tr></table>"
      ],
      "text/plain": [
       "5x8 DataFrames.DataFrame\n",
       "| Row | acctbal | name                 | nation           | id   |\n",
       "|-----|---------|----------------------|------------------|------|\n",
       "| 1   | 9198.31 | \"Supplier#000000025\" | \"RUSSIA\"         | 1156 |\n",
       "| 2   | 4186.95 | \"Supplier#000000077\" | \"GERMANY\"        | 1362 |\n",
       "| 3   | 3556.47 | \"Supplier#000000032\" | \"UNITED KINGDOM\" | 248  |\n",
       "| 4   | 2455.98 | \"Supplier#000000066\" | \"UNITED KINGDOM\" | 805  |\n",
       "| 5   | -290.06 | \"Supplier#000000040\" | \"RUSSIA\"         | 493  |\n",
       "\n",
       "| Row | mfgr             | address                                    |\n",
       "|-----|------------------|--------------------------------------------|\n",
       "| 1   | \"Manufacturer#5\" | \"RCQKONXMFnrodzz6w7fObFVV6CUm2q\"           |\n",
       "| 2   | \"Manufacturer#1\" | \"wVtcr0uH3CyrSiWMLsqnB09Syo,UuZxPMeBghlY\"  |\n",
       "| 3   | \"Manufacturer#1\" | \"yvoD3TtZSx1skQNCK8agk5bZlZLug\"            |\n",
       "| 4   | \"Manufacturer#5\" | \"qYdruFJQJYYiKvnNVmYfCVydVB8bcW,AW,U6SOV3\" |\n",
       "| 5   | \"Manufacturer#4\" | \"zyIeWzbbpkTV37vm1nmSGBxSgd2Kp\"            |\n",
       "\n",
       "| Row | phone             |\n",
       "|-----|-------------------|\n",
       "| 1   | \"32-431-945-3541\" |\n",
       "| 2   | \"17-281-345-4863\" |\n",
       "| 3   | \"33-484-637-7873\" |\n",
       "| 4   | \"33-300-836-9529\" |\n",
       "| 5   | \"32-231-247-6991\" |\n",
       "\n",
       "| Row | comment                                                                                                |\n",
       "|-----|--------------------------------------------------------------------------------------------------------|\n",
       "| 1   | \"ely regular deposits. carefully regular sauternes engage furiously above the regular accounts. idly \" |\n",
       "| 2   | \"the slyly final asymptotes. blithely pending theodoli\"                                                |\n",
       "| 3   | \"usly even depths. quickly ironic theodolites s\"                                                       |\n",
       "| 4   | \"ar requests. express orbits de\"                                                                       |\n",
       "| 5   | \" final patterns. accounts haggle idly pas\"                                                            |"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    partsupp\n",
    "    :filter(\n",
    "        (part.size == SIZE) &\n",
    "        contains(part.type_, TYPE) &\n",
    "        (supplier.nation.region.name == REGION))\n",
    "    :filter(supplycost == min(and_around(part).supplycost))\n",
    "    :sort(\n",
    "        supplier.acctbal:desc,\n",
    "        supplier.nation.name,\n",
    "        part.name,\n",
    "        part.id)\n",
    "    :select(\n",
    "        supplier.acctbal,\n",
    "        supplier.name,\n",
    "        supplier.nation,\n",
    "        part.id,\n",
    "        part.mfgr,\n",
    "        supplier.address,\n",
    "        supplier.phone,\n",
    "        supplier.comment)\n",
    "    :take(100),\n",
    "    SIZE=8, TYPE=\"BRASS\", REGION=\"EUROPE\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Shipping Priority Query (Q3)\n",
    "*[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 33]*\n",
    "\n",
    "This query retrieves the 10 unshipped orders with the highest value.\n",
    "\n",
    "### Business Question\n",
    "The Shipping Priority Query retrieves the shipping priority and potential revenue, defined as the sum of\n",
    "`l_extendedprice * (1-l_discount)`, of the orders having the largest revenue among those that had not been shipped as of a given date. Orders are listed in decreasing order of revenue. If more than 10 unshipped orders exist, only the 10 orders with the largest revenue are listed.\n",
    "\n",
    "### Query Definition\n",
    "```\n",
    "select\n",
    "    l_orderkey,\n",
    "    sum(l_extendedprice*(1-l_discount)) as revenue,\n",
    "    o_orderdate,\n",
    "    o_shippriority\n",
    "from\n",
    "    customer,\n",
    "    orders,\n",
    "    lineitem\n",
    "where\n",
    "    c_mktsegment = '[SEGMENT]'\n",
    "    and c_custkey = o_custkey\n",
    "    and l_orderkey = o_orderkey\n",
    "    and o_orderdate < date '[DATE]'\n",
    "    and l_shipdate > date '[DATE]'\n",
    "group by\n",
    "    l_orderkey,\n",
    "    o_orderdate,\n",
    "    o_shippriority\n",
    "order by\n",
    "    revenue desc,\n",
    "    o_orderdate\n",
    "limit 10;\n",
    "```\n",
    "\n",
    "### Substitution Parameters\n",
    "1. SEGMENT = BUILDING;\n",
    "2. DATE = 1995-03-15."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>id</th><th>revenue</th><th>orderdate</th><th>shippriority</th></tr><tr><th>1</th><td>6240</td><td>245018.10</td><td>1995-01-28</td><td>0</td></tr><tr><th>2</th><td>5822</td><td>234486.93</td><td>1995-03-14</td><td>0</td></tr><tr><th>3</th><td>9974</td><td>231804.68</td><td>1995-03-06</td><td>0</td></tr><tr><th>4</th><td>4608</td><td>197118.76</td><td>1995-03-13</td><td>0</td></tr><tr><th>5</th><td>4672</td><td>182178.38</td><td>1995-03-06</td><td>0</td></tr><tr><th>6</th><td>7197</td><td>179200.59</td><td>1995-02-11</td><td>0</td></tr><tr><th>7</th><td>114</td><td>173955.47</td><td>1995-03-05</td><td>0</td></tr><tr><th>8</th><td>11089</td><td>172336.48</td><td>1995-02-04</td><td>0</td></tr><tr><th>9</th><td>1870</td><td>170114.81</td><td>1995-02-21</td><td>0</td></tr><tr><th>10</th><td>7189</td><td>165761.65</td><td>1995-03-01</td><td>0</td></tr></table>"
      ],
      "text/plain": [
       "10x4 DataFrames.DataFrame\n",
       "| Row | id   | revenue   | orderdate  | shippriority |\n",
       "|-----|------|-----------|------------|--------------|\n",
       "| 1   | 6240 | 245018.10 | 1995-01-28 | 0            |\n",
       "⋮\n",
       "| 9   | 1870 | 170114.81 | 1995-02-21 | 0            |\n",
       "| 10  | 7189 | 165761.65 | 1995-03-01 | 0            |"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    order\n",
    "    :filter(\n",
    "        (customer.mktsegment == SEGMENT) &\n",
    "        (orderdate < DATE))\n",
    "    :select(\n",
    "        id,\n",
    "        revenue =>\n",
    "            lineitem:filter(shipdate > DATE).(extendedprice*(1-discount)):sum,\n",
    "        orderdate,\n",
    "        shippriority)\n",
    "    :sort(revenue:desc, orderdate)\n",
    "    :take(10),\n",
    "    SEGMENT=\"BUILDING\", DATE=Date(\"1995-03-15\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Order Priority Checking Query (Q4)\n",
    "*[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 35]*\n",
    "\n",
    "This query determines how well the order priority system is working and gives an assessment of customer satisfaction.\n",
    "\n",
    "### Business Question\n",
    "The Order Priority Checking Query counts the number of orders ordered in a given quarter of a given year in which\n",
    "at least one lineitem was received by the customer later than its committed date. The query lists the count of such\n",
    "orders for each order priority sorted in ascending priority order.\n",
    "\n",
    "### Query Definition\n",
    "```\n",
    "select\n",
    "    o_orderpriority,\n",
    "    count(*) as order_count\n",
    "from\n",
    "    orders\n",
    "where\n",
    "    o_orderdate >= date '[DATE]'\n",
    "    and o_orderdate < date '[DATE]' + interval '3' month\n",
    "    and exists (\n",
    "        select\n",
    "            *\n",
    "        from\n",
    "            lineitem\n",
    "        where\n",
    "            l_orderkey = o_orderkey\n",
    "            and l_commitdate < l_receiptdate)\n",
    "group by\n",
    "    o_orderpriority\n",
    "order by\n",
    "    o_orderpriority;\n",
    "```\n",
    "\n",
    "### Substitution Parameters\n",
    "1. DATE = 1993-07-01."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>orderpriority</th><th>order_count</th></tr><tr><th>1</th><td>1-URGENT</td><td>78</td></tr><tr><th>2</th><td>2-HIGH</td><td>80</td></tr><tr><th>3</th><td>3-MEDIUM</td><td>89</td></tr><tr><th>4</th><td>4-NOT SPECIFIED</td><td>85</td></tr><tr><th>5</th><td>5-LOW</td><td>105</td></tr></table>"
      ],
      "text/plain": [
       "5x2 DataFrames.DataFrame\n",
       "| Row | orderpriority     | order_count |\n",
       "|-----|-------------------|-------------|\n",
       "| 1   | \"1-URGENT\"        | 78          |\n",
       "| 2   | \"2-HIGH\"          | 80          |\n",
       "| 3   | \"3-MEDIUM\"        | 89          |\n",
       "| 4   | \"4-NOT SPECIFIED\" | 85          |\n",
       "| 5   | \"5-LOW\"           | 105         |"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    order\n",
    "    :filter(\n",
    "        DATE <= orderdate < DATE + 3_months &&\n",
    "        any(lineitem.(commitdate < receiptdate)))\n",
    "    :group(orderpriority)\n",
    "    :select(\n",
    "        orderpriority,\n",
    "        order_count => count(order)),\n",
    "    DATE=Date(\"1993-07-01\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Local Supplier Volume Query (Q5)\n",
    "*[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 36]*\n",
    "\n",
    "This query lists the revenue volume done through local suppliers.\n",
    "\n",
    "### Business Question\n",
    "The Local Supplier Volume Query lists for each nation in a region the revenue volume that resulted from lineitem\n",
    "transactions in which the customer ordering parts and the supplier filling them were both within that nation. The\n",
    "query is run in order to determine whether to institute local distribution centers in a given region. The query considers only parts ordered in a given year. The query displays the nations and revenue volume in descending order by revenue. Revenue volume for all qualifying lineitems in a particular nation is defined as `sum(l_extendedprice * (1 - l_discount))`.\n",
    "\n",
    "### Query Definition\n",
    "```\n",
    "select\n",
    "    n_name,\n",
    "    sum(l_extendedprice * (1 - l_discount)) as revenue\n",
    "from\n",
    "    customer,\n",
    "    orders,\n",
    "    lineitem,\n",
    "    supplier,\n",
    "    nation,\n",
    "    region\n",
    "where\n",
    "    c_custkey = o_custkey\n",
    "    and l_orderkey = o_orderkey\n",
    "    and l_suppkey = s_suppkey\n",
    "    and c_nationkey = s_nationkey\n",
    "    and s_nationkey = n_nationkey\n",
    "    and n_regionkey = r_regionkey\n",
    "    and r_name = '[REGION]'\n",
    "    and o_orderdate >= date '[DATE]'\n",
    "    and o_orderdate < date '[DATE]' + interval '1' year\n",
    "group by\n",
    "    n_name\n",
    "order by\n",
    "    revenue desc;\n",
    "```\n",
    "\n",
    "### Substitution Parameters\n",
    "1. REGION = ASIA;\n",
    "2. DATE = 1994-01-01."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>nation</th><th>revenue</th></tr><tr><th>1</th><td>VIETNAM</td><td>807082.63</td></tr><tr><th>2</th><td>INDIA</td><td>697063.03</td></tr><tr><th>3</th><td>INDONESIA</td><td>463882.68</td></tr><tr><th>4</th><td>CHINA</td><td>440134.00</td></tr><tr><th>5</th><td>JAPAN</td><td>237479.43</td></tr></table>"
      ],
      "text/plain": [
       "5x2 DataFrames.DataFrame\n",
       "| Row | nation      | revenue   |\n",
       "|-----|-------------|-----------|\n",
       "| 1   | \"VIETNAM\"   | 807082.63 |\n",
       "| 2   | \"INDIA\"     | 697063.03 |\n",
       "| 3   | \"INDONESIA\" | 463882.68 |\n",
       "| 4   | \"CHINA\"     | 440134.00 |\n",
       "| 5   | \"JAPAN\"     | 237479.43 |"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    lineitem\n",
    "    :filter(\n",
    "        DATE <= order.orderdate < DATE + 1_year &&\n",
    "        supplier.nation == order.customer.nation &&\n",
    "        supplier.nation.region.name == REGION)\n",
    "    :group(supplier.nation)\n",
    "    :select(\n",
    "        nation,\n",
    "        revenue => sum(lineitem.(extendedprice*(1-discount))))\n",
    "    :sort(revenue:desc),\n",
    "    REGION=\"ASIA\", DATE=Date(\"1994-01-01\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Forecasting Revenue Change Query (Q6)\n",
    "*[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 38]*\n",
    "\n",
    "This query quantifies the amount of revenue increase that would have resulted from eliminating certain company-wide discounts in a given percentage range in a given year. Asking this type of \"what if\" query can be used to look\n",
    "for ways to increase revenues.\n",
    "\n",
    "### Business Question\n",
    "The Forecasting Revenue Change Query considers all the lineitems shipped in a given year with discounts between\n",
    "DISCOUNT-0.01 and DISCOUNT+0.01. The query lists the amount by which the total revenue would have\n",
    "increased if these discounts had been eliminated for lineitems with `l_quantity` less than `quantity`. Note that the\n",
    "potential revenue increase is equal to the sum of `[l_extendedprice * l_discount]` for all lineitems with discounts and quantities in the qualifying range.\n",
    "\n",
    "### Query Definition\n",
    "```\n",
    "select\n",
    "    sum(l_extendedprice*l_discount) as revenue\n",
    "from\n",
    "    lineitem\n",
    "where\n",
    "    l_shipdate >= date '[DATE]'\n",
    "    and l_shipdate < date '[DATE]' + interval '1' year\n",
    "    and l_discount between [DISCOUNT] - 0.01 and [DISCOUNT] + 0.01\n",
    "    and l_quantity < [QUANTITY];\n",
    "```\n",
    "\n",
    "### Substitution Parameters\n",
    "1. DATE = 1994-01-01;\n",
    "2. DISCOUNT = 0.06;\n",
    "3. QUANTITY = 24."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>revenue</th></tr><tr><th>1</th><td>905922.82</td></tr></table>"
      ],
      "text/plain": [
       "1x1 DataFrames.DataFrame\n",
       "| Row | revenue   |\n",
       "|-----|-----------|\n",
       "| 1   | 905922.82 |"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    lineitem\n",
    "    :filter(\n",
    "        DATE <= shipdate < DATE + 1_year &&\n",
    "        DISCOUNT-0.011 <= discount <= DISCOUNT+0.011 &&\n",
    "        quantity < QUANTITY)\n",
    "    :group\n",
    "    :select(\n",
    "        revenue => sum(lineitem.(extendedprice*discount))),\n",
    "    DATE=Date(\"1994-01-01\"), DISCOUNT=0.06, QUANTITY=24)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Volume Shipping Query (Q7)\n",
    "*[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 39]*\n",
    "\n",
    "This query determines the value of goods shipped between certain nations to help in the re-negotiation of shipping\n",
    "contracts.\n",
    "\n",
    "### Business Question\n",
    "The Volume Shipping Query finds, for two given nations, the gross discounted revenues derived from lineitems in\n",
    "which parts were shipped from a supplier in either nation to a customer in the other nation during 1995 and 1996.\n",
    "The query lists the supplier nation, the customer nation, the year, and the revenue from shipments that took place in that year. The query orders the answer by Supplier nation, Customer nation, and year (all ascending).\n",
    "\n",
    "### Query Definition\n",
    "```\n",
    "select\n",
    "    supp_nation,\n",
    "    cust_nation,\n",
    "    l_year,\n",
    "    sum(volume) as revenue\n",
    "from (\n",
    "    select\n",
    "        n1.n_name as supp_nation,\n",
    "        n2.n_name as cust_nation,\n",
    "        extract(year from l_shipdate) as l_year,\n",
    "        l_extendedprice * (1 - l_discount) as volume\n",
    "    from\n",
    "        supplier,\n",
    "        lineitem,\n",
    "        orders,\n",
    "        customer,\n",
    "        nation n1,\n",
    "        nation n2\n",
    "    where\n",
    "        s_suppkey = l_suppkey\n",
    "        and o_orderkey = l_orderkey\n",
    "        and c_custkey = o_custkey\n",
    "        and s_nationkey = n1.n_nationkey\n",
    "        and c_nationkey = n2.n_nationkey\n",
    "        and (\n",
    "            (n1.n_name = '[NATION1]' and n2.n_name = '[NATION2]')\n",
    "            or (n1.n_name = '[NATION2]' and n2.n_name = '[NATION1]'))\n",
    "        and l_shipdate between date '1995-01-01' and date '1996-12-31') as shipping\n",
    "group by\n",
    "    supp_nation,\n",
    "    cust_nation,\n",
    "    l_year\n",
    "order by\n",
    "    supp_nation,\n",
    "    cust_nation,\n",
    "    l_year;\n",
    "```\n",
    "\n",
    "### Substitution Parameters\n",
    "1. NATION1 = FRANCE;\n",
    "2. NATION2 = GERMANY."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>supp_nation</th><th>cust_nation</th><th>year</th><th>revenue</th></tr><tr><th>1</th><td>FRANCE</td><td>GERMANY</td><td>1995</td><td>263047.87</td></tr><tr><th>2</th><td>FRANCE</td><td>GERMANY</td><td>1996</td><td>154119.14</td></tr><tr><th>3</th><td>GERMANY</td><td>FRANCE</td><td>1995</td><td>205237.67</td></tr><tr><th>4</th><td>GERMANY</td><td>FRANCE</td><td>1996</td><td>407967.21</td></tr></table>"
      ],
      "text/plain": [
       "4x4 DataFrames.DataFrame\n",
       "| Row | supp_nation | cust_nation | year | revenue   |\n",
       "|-----|-------------|-------------|------|-----------|\n",
       "| 1   | \"FRANCE\"    | \"GERMANY\"   | 1995 | 263047.87 |\n",
       "| 2   | \"FRANCE\"    | \"GERMANY\"   | 1996 | 154119.14 |\n",
       "| 3   | \"GERMANY\"   | \"FRANCE\"    | 1995 | 205237.67 |\n",
       "| 4   | \"GERMANY\"   | \"FRANCE\"    | 1996 | 407967.21 |"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    lineitem\n",
    "    :define(\n",
    "        supp_nation => supplier.nation.name,\n",
    "        cust_nation => order.customer.nation.name)\n",
    "    :filter(\n",
    "        date(\"1995-01-01\") <= shipdate <= date(\"1996-12-31\") && (\n",
    "            (supp_nation == NATION1 && cust_nation == NATION2) ||\n",
    "            (supp_nation == NATION2 && cust_nation == NATION1)))\n",
    "    :group(\n",
    "        supp_nation,\n",
    "        cust_nation,\n",
    "        year => year(shipdate))\n",
    "    :select(\n",
    "        supp_nation,\n",
    "        cust_nation,\n",
    "        year,\n",
    "        revenue => sum(lineitem.(extendedprice*(1-discount)))),\n",
    "    NATION1=\"FRANCE\", NATION2=\"GERMANY\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## National Market Share Query (Q8)\n",
    "*[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 41]*\n",
    "\n",
    "This query determines how the market share of a given nation within a given region has changed over two years for\n",
    "a given part type.\n",
    "\n",
    "### Business Question\n",
    "The market share for a given nation within a given region is defined as the fraction of the revenue, the sum of\n",
    "`[l_extendedprice * (1-l_discount)]`, from the products of a specified type in that region that was supplied by suppliers from the given nation. The query determines this for the years 1995 and 1996 presented in this order.\n",
    "\n",
    "### Functional Query Definition\n",
    "```\n",
    "select\n",
    "    o_year,\n",
    "    sum(case\n",
    "            when nation = '[NATION]'\n",
    "            then volume\n",
    "            else 0\n",
    "        end) / sum(volume) as mkt_share\n",
    "from (\n",
    "    select\n",
    "        extract(year from o_orderdate) as o_year,\n",
    "        l_extendedprice * (1-l_discount) as volume,\n",
    "        n2.n_name as nation\n",
    "    from\n",
    "        part,\n",
    "        supplier,\n",
    "        lineitem,\n",
    "        orders,\n",
    "        customer,\n",
    "        nation n1,\n",
    "        nation n2,\n",
    "        region\n",
    "    where\n",
    "        p_partkey = l_partkey\n",
    "        and s_suppkey = l_suppkey\n",
    "        and l_orderkey = o_orderkey\n",
    "        and o_custkey = c_custkey\n",
    "        and c_nationkey = n1.n_nationkey\n",
    "        and n1.n_regionkey = r_regionkey\n",
    "        and r_name = '[REGION]'\n",
    "        and s_nationkey = n2.n_nationkey\n",
    "        and o_orderdate between date '1995-01-01' and date '1996-12-31'\n",
    "        and p_type = '[TYPE]') as all_nations\n",
    "group by\n",
    "    o_year\n",
    "order by\n",
    "    o_year;\n",
    "```\n",
    "\n",
    "### Substitution Parameters\n",
    "1. NATION = CANADA;\n",
    "2. REGION = AMERICA;\n",
    "3. TYPE = ECONOMY ANODIZED STEEL."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>year</th><th>mkt_share</th></tr><tr><th>1</th><td>1995</td><td>0.1379404076847995</td></tr><tr><th>2</th><td>1996</td><td>0.2615672520944461</td></tr></table>"
      ],
      "text/plain": [
       "2x2 DataFrames.DataFrame\n",
       "| Row | year | mkt_share |\n",
       "|-----|------|-----------|\n",
       "| 1   | 1995 | 0.13794   |\n",
       "| 2   | 1996 | 0.261567  |"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    lineitem\n",
    "    :define(\n",
    "        year => year(order.orderdate),\n",
    "        supp_nation => supplier.nation.name,\n",
    "        volume => extendedprice*(1-discount))\n",
    "    :filter(\n",
    "        part.type_ == TYPE &&\n",
    "        order.customer.nation.region.name == REGION &&\n",
    "        1995 <= year <= 1996)\n",
    "    :group(year)\n",
    "    :select(\n",
    "        year,\n",
    "        mkt_share =>\n",
    "            sum(lineitem:filter(supp_nation == NATION).volume) / sum(lineitem.volume)),\n",
    "    NATION=\"CANADA\", REGION=\"AMERICA\", TYPE=\"ECONOMY ANODIZED STEEL\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Product Type Profit Measure Query (Q9)\n",
    "*[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 43]*\n",
    "\n",
    "This query determines how much profit is made on a given line of parts, broken out by supplier nation and year.\n",
    "\n",
    "### Business Question\n",
    "The Product Type Profit Measure Query finds, for each nation and each year, the profit for all parts ordered in that\n",
    "year that contain a specified substring in their names and that were filled by a supplier in that nation. The profit is defined as the sum of `[(l_extendedprice*(1-l_discount)) - (ps_supplycost * l_quantity)]` for all lineitems describing parts in the specified line. The query lists the nations in ascending alphabetical order and, for each nation, the year and profit in descending order by year (most recent first).\n",
    "\n",
    "### Query Definition\n",
    "```\n",
    "select\n",
    "    nation,\n",
    "    o_year,\n",
    "    sum(amount) as sum_profit\n",
    "from (\n",
    "    select\n",
    "        n_name as nation,\n",
    "        extract(year from o_orderdate) as o_year,\n",
    "        l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount\n",
    "    from\n",
    "        part,\n",
    "        supplier,\n",
    "        lineitem,\n",
    "        partsupp,\n",
    "        orders,\n",
    "        nation\n",
    "    where\n",
    "        s_suppkey = l_suppkey\n",
    "        and ps_suppkey = l_suppkey\n",
    "        and ps_partkey = l_partkey\n",
    "        and p_partkey = l_partkey\n",
    "        and o_orderkey = l_orderkey\n",
    "        and s_nationkey = n_nationkey\n",
    "        and p_name like '%[COLOR]%') as profit\n",
    "group by\n",
    "    nation,\n",
    "    o_year\n",
    "order by\n",
    "    nation,\n",
    "    o_year desc;\n",
    "```\n",
    "\n",
    "### Substitution Parameters\n",
    "1. COLOR = green."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>nation</th><th>year</th><th>sum_profit</th></tr><tr><th>1</th><td>ALGERIA</td><td>1998</td><td>197990.07</td></tr><tr><th>2</th><td>ALGERIA</td><td>1997</td><td>209363.95</td></tr><tr><th>3</th><td>ALGERIA</td><td>1996</td><td>508610.09</td></tr><tr><th>4</th><td>ALGERIA</td><td>1995</td><td>321224.39</td></tr><tr><th>5</th><td>ALGERIA</td><td>1994</td><td>323614.10</td></tr><tr><th>6</th><td>ALGERIA</td><td>1993</td><td>429217.36</td></tr><tr><th>7</th><td>ALGERIA</td><td>1992</td><td>313931.42</td></tr><tr><th>8</th><td>ARGENTINA</td><td>1998</td><td>207703.71</td></tr><tr><th>9</th><td>ARGENTINA</td><td>1997</td><td>404879.36</td></tr><tr><th>10</th><td>ARGENTINA</td><td>1996</td><td>277287.30</td></tr><tr><th>&vellip;</th><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td></tr></table>"
      ],
      "text/plain": [
       "173x3 DataFrames.DataFrame\n",
       "| Row | nation    | year | sum_profit |\n",
       "|-----|-----------|------|------------|\n",
       "| 1   | \"ALGERIA\" | 1998 | 197990.07  |\n",
       "⋮\n",
       "| 172 | \"VIETNAM\" | 1993 | 479879.33  |\n",
       "| 173 | \"VIETNAM\" | 1992 | 436117.97  |"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    lineitem\n",
    "    :define(\n",
    "        amount =>\n",
    "            extendedprice*(1-discount) - partsupp.supplycost*quantity)\n",
    "    :filter(contains(part.name, COLOR))\n",
    "    :group(\n",
    "        nation => supplier.nation.name,\n",
    "        year => year(order.orderdate))\n",
    "    :sort(nation, year:desc)\n",
    "    :select(\n",
    "        nation,\n",
    "        year,\n",
    "        sum_profit => sum(lineitem.amount)),\n",
    "    COLOR=\"green\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Returned Item Reporting Query (Q10)\n",
    "*[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 45]*\n",
    "\n",
    "The query identifies customers who might be having problems with the parts that are shipped to them.\n",
    "\n",
    "### Business Question\n",
    "The Returned Item Reporting Query finds the top 20 customers, in terms of their effect on lost revenue for a given\n",
    "quarter, who have returned parts. The query considers only parts that were ordered in the specified quarter. The\n",
    "query lists the customer's name, address, nation, phone number, account balance, comment information and revenue\n",
    "lost. The customers are listed in descending order of lost revenue. Revenue lost is defined as\n",
    "`sum(l_extendedprice*(1-l_discount))` for all qualifying lineitems.\n",
    "\n",
    "### Functional Query Definition\n",
    "```\n",
    "select\n",
    "    c_custkey,\n",
    "    c_name,\n",
    "    sum(l_extendedprice * (1 - l_discount)) as revenue,\n",
    "    c_acctbal,\n",
    "    n_name,\n",
    "    c_address,\n",
    "    c_phone,\n",
    "    c_comment\n",
    "from\n",
    "    customer,\n",
    "    orders,\n",
    "    lineitem,\n",
    "    nation\n",
    "where\n",
    "    c_custkey = o_custkey\n",
    "    and l_orderkey = o_orderkey\n",
    "    and o_orderdate >= date '[DATE]'\n",
    "    and o_orderdate < date '[DATE]' + interval '3' month\n",
    "    and l_returnflag = 'R'\n",
    "    and c_nationkey = n_nationkey\n",
    "group by\n",
    "    c_custkey,\n",
    "    c_name,\n",
    "    c_acctbal,\n",
    "    c_phone,\n",
    "    n_name,\n",
    "    c_address,\n",
    "    c_comment\n",
    "order by\n",
    "    revenue desc\n",
    "limit 20;\n",
    "```\n",
    "\n",
    "### Substitution Parameters\n",
    "1. DATE = 1993-10-01."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>id</th><th>name</th><th>revenue</th><th>acctbal</th><th>nation</th><th>address</th><th>phone</th><th>comment</th></tr><tr><th>1</th><td>544</td><td>Customer#000000544</td><td>391580.05</td><td>4974.68</td><td>ETHIOPIA</td><td>Jv7vcm,oE,HEyxekXKia1V5H1up23</td><td>15-572-651-1323</td><td>bout the packages integrate above the regular instructions. regular ideas hinder s</td></tr><tr><th>2</th><td>1105</td><td>Customer#000001105</td><td>375872.30</td><td>9491.46</td><td>RUSSIA</td><td>cZhhOUzv6,Vbaa2bFT</td><td>32-885-298-6750</td><td>y final packages. furiously ironic packages was. fluffily ironic instructions integrate</td></tr><tr><th>3</th><td>961</td><td>Customer#000000961</td><td>372764.61</td><td>6963.68</td><td>JAPAN</td><td>5,81YDLFuRR47KKzv8GXdmi3zyP37PlPn</td><td>22-989-463-6089</td><td>e final requests: busily final accounts believe a</td></tr><tr><th>4</th><td>266</td><td>Customer#000000266</td><td>347106.76</td><td>5481.00</td><td>ALGERIA</td><td>VSIEruiMdDvjDaTQxkuK60Yw3AGxO</td><td>10-474-243-3974</td><td>ccounts. quickly ironic excuses after the regular foxes wake along the ironic, fina</td></tr><tr><th>5</th><td>683</td><td>Customer#000000683</td><td>328973.73</td><td>9120.93</td><td>FRANCE</td><td>G0, q8c6vBykpiLvcuSJLYvqE</td><td>16-566-251-5446</td><td> somas use-- slyly express foxes wake quickly blithely iro</td></tr><tr><th>6</th><td>415</td><td>Customer#000000415</td><td>327104.67</td><td>2317.93</td><td>UNITED KINGDOM</td><td>334jCRiUb,gx3</td><td>33-346-876-2972</td><td>egular deposits. blithely ironic inst</td></tr><tr><th>7</th><td>746</td><td>Customer#000000746</td><td>311870.96</td><td>1164.46</td><td>SAUDI ARABIA</td><td>JOKj8N2QKUm8Gi,F4qX0fLVy</td><td>30-154-354-9928</td><td> final theodolites. final pinto beans haggle-- furiously </td></tr><tr><th>8</th><td>421</td><td>Customer#000000421</td><td>311164.57</td><td>7073.17</td><td>JORDAN</td><td>it3mUlkZAe9J8gmy</td><td>23-918-228-2560</td><td>lithely final deposits haggle furiously above the</td></tr><tr><th>9</th><td>643</td><td>Customer#000000643</td><td>281356.65</td><td>5184.70</td><td>ALGERIA</td><td>9T 2avhfyF PQ</td><td>10-978-597-2747</td><td>fily along the quickly ironic ideas. final, final</td></tr><tr><th>10</th><td>1051</td><td>Customer#000001051</td><td>275867.46</td><td>9776.39</td><td>BRAZIL</td><td>iHS,UFudVOOe</td><td>12-869-221-1428</td><td>cuses boost furiously silent deposits. quickly silent requests integrate quickly bold asymptotes; slyly regular ide</td></tr><tr><th>&vellip;</th><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td></tr></table>"
      ],
      "text/plain": [
       "20x8 DataFrames.DataFrame\n",
       "| Row | id   | name                 | revenue   | acctbal | nation      |\n",
       "|-----|------|----------------------|-----------|---------|-------------|\n",
       "| 1   | 544  | \"Customer#000000544\" | 391580.05 | 4974.68 | \"ETHIOPIA\"  |\n",
       "⋮\n",
       "| 19  | 875  | \"Customer#000000875\" | 247406.98 | -949.28 | \"CANADA\"    |\n",
       "| 20  | 1183 | \"Customer#000001183\" | 245991.31 | 4455.76 | \"ARGENTINA\" |\n",
       "\n",
       "| Row | address                                | phone             |\n",
       "|-----|----------------------------------------|-------------------|\n",
       "| 1   | \"Jv7vcm,oE,HEyxekXKia1V5H1up23\"        | \"15-572-651-1323\" |\n",
       "⋮\n",
       "| 19  | \"8pQ4YUYox0d\"                          | \"13-146-810-5423\" |\n",
       "| 20  | \"qdIqRUfpmvtWo0NGsyi4qyjkwzlImP9,NrSC\" | \"11-968-244-9275\" |\n",
       "\n",
       "| Row | comment                                                                                          |\n",
       "|-----|--------------------------------------------------------------------------------------------------|\n",
       "| 1   | \"bout the packages integrate above the regular instructions. regular ideas hinder s\"             |\n",
       "⋮\n",
       "| 19  | \"ar theodolites snooze slyly. furiously express packages cajole blithely around the carefully r\" |\n",
       "| 20  | \"arefully regular dependencies. quick\"                                                           |"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    customer\n",
    "    :define(\n",
    "        returned =>\n",
    "            order:filter(DATE <= orderdate < DATE+3_months).lineitem:filter(returnflag == \"R\"))\n",
    "    :select(\n",
    "        id,\n",
    "        name,\n",
    "        revenue => sum(returned.(extendedprice*(1-discount))),\n",
    "        acctbal,\n",
    "        nation,\n",
    "        address,\n",
    "        phone,\n",
    "        comment)\n",
    "    :sort(revenue:desc)\n",
    "    :take(20),\n",
    "    DATE=Date(\"1993-10-01\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Important Stock Identification Query (Q11)\n",
    "*[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 47]*\n",
    "\n",
    "This query finds the most important subset of suppliers' stock in a given nation.\n",
    "\n",
    "### Business Question\n",
    "The Important Stock Identification Query finds, from scanning the available stock of suppliers in a given nation, all the parts that represent a significant percentage of the total value of all available parts. The query displays the part number and the value of those parts in descending order of value.\n",
    "\n",
    "### Query Definition\n",
    "```\n",
    "select\n",
    "    ps_partkey,\n",
    "    sum(ps_supplycost * ps_availqty) as value\n",
    "from\n",
    "    partsupp,\n",
    "    supplier,\n",
    "    nation\n",
    "where\n",
    "    ps_suppkey = s_suppkey\n",
    "    and s_nationkey = n_nationkey\n",
    "    and n_name = '[NATION]'\n",
    "group by\n",
    "    ps_partkey\n",
    "having\n",
    "    sum(ps_supplycost * ps_availqty) > (\n",
    "        select\n",
    "            sum(ps_supplycost * ps_availqty) * [FRACTION]\n",
    "        from\n",
    "            partsupp,\n",
    "            supplier,\n",
    "            nation\n",
    "        where\n",
    "            ps_suppkey = s_suppkey\n",
    "            and s_nationkey = n_nationkey\n",
    "            and n_name = '[NATION]')\n",
    "order by\n",
    "    value desc;\n",
    "```\n",
    "\n",
    "### Substitution Parameters\n",
    "1. NATION = GERMANY;\n",
    "2. FRACTION = 0.0001."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>id</th><th>value</th></tr><tr><th>1</th><td>1083</td><td>13092535.78</td></tr><tr><th>2</th><td>1356</td><td>11542206.53</td></tr><tr><th>3</th><td>716</td><td>9945808.42</td></tr><tr><th>4</th><td>448</td><td>9941036.40</td></tr><tr><th>5</th><td>111</td><td>9382317.55</td></tr><tr><th>6</th><td>1454</td><td>9081368.05</td></tr><tr><th>7</th><td>1218</td><td>8999420.40</td></tr><tr><th>8</th><td>324</td><td>8901344.93</td></tr><tr><th>9</th><td>1312</td><td>8834080.10</td></tr><tr><th>10</th><td>1472</td><td>8609013.30</td></tr><tr><th>&vellip;</th><td>&vellip;</td><td>&vellip;</td></tr></table>"
      ],
      "text/plain": [
       "287x2 DataFrames.DataFrame\n",
       "| Row | id   | value       |\n",
       "|-----|------|-------------|\n",
       "| 1   | 1083 | 13092535.78 |\n",
       "⋮\n",
       "| 286 | 1172 | 88622.73    |\n",
       "| 287 | 462  | 86323.96    |"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    partsupp\n",
    "    :filter(supplier.nation.name == NATION)\n",
    "    :group(part)\n",
    "    :define(value => sum(partsupp.(supplycost * availqty)))\n",
    "    :filter(value > sum(and_around.value)*FRACTION)\n",
    "    :select(part.id, value)\n",
    "    :sort(value:desc),\n",
    "    NATION=\"GERMANY\", FRACTION=0.0001)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Shipping Modes and Order Priority Query (Q12)\n",
    "*[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 49]*\n",
    "\n",
    "This query determines whether selecting less expensive modes of shipping is negatively affecting the critical-priority orders by causing more parts to be received by customers after the committed date.\n",
    "\n",
    "### Business Question\n",
    "The Shipping Modes and Order Priority Query counts, by ship mode, for lineitems actually received by customers in\n",
    "a given year, the number of lineitems belonging to orders for which the `l_receiptdate` exceeds the `l_commitdate` for two different specified ship modes. Only lineitems that were actually shipped before the `l_commitdate` are considered. The late lineitems are partitioned into two groups, those with priority URGENT or HIGH, and those with a\n",
    "priority other than URGENT or HIGH.\n",
    "\n",
    "### Query Definition\n",
    "```\n",
    "select\n",
    "    l_shipmode,\n",
    "    sum(case\n",
    "            when o_orderpriority ='1-URGENT' or o_orderpriority ='2-HIGH'\n",
    "            then 1\n",
    "            else 0\n",
    "        end) as high_line_count,\n",
    "    sum(case\n",
    "            when o_orderpriority <> '1-URGENT' and o_orderpriority <> '2-HIGH'\n",
    "            then 1\n",
    "            else 0\n",
    "        end) as low_line_count\n",
    "from\n",
    "    orders,\n",
    "    lineitem\n",
    "where\n",
    "    o_orderkey = l_orderkey\n",
    "    and l_shipmode in ('[SHIPMODE1]', '[SHIPMODE2]')\n",
    "    and l_commitdate < l_receiptdate\n",
    "    and l_shipdate < l_commitdate\n",
    "    and l_receiptdate >= date '[DATE]'\n",
    "    and l_receiptdate < date '[DATE]' + interval '1' year\n",
    "group by\n",
    "    l_shipmode\n",
    "order by\n",
    "    l_shipmode;\n",
    "```\n",
    "\n",
    "### Substitution Parameters\n",
    "1. SHIPMODE1 = MAIL;\n",
    "2. SHIPMODE2 = SHIP;\n",
    "3. DATE = 1994-01-01."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>shipmode</th><th>high_line_count</th><th>low_line_count</th></tr><tr><th>1</th><td>MAIL</td><td>45</td><td>67</td></tr><tr><th>2</th><td>SHIP</td><td>45</td><td>69</td></tr></table>"
      ],
      "text/plain": [
       "2x3 DataFrames.DataFrame\n",
       "| Row | shipmode | high_line_count | low_line_count |\n",
       "|-----|----------|-----------------|----------------|\n",
       "| 1   | \"MAIL\"   | 45              | 67             |\n",
       "| 2   | \"SHIP\"   | 45              | 69             |"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    lineitem\n",
    "    :define(\n",
    "        high => order.orderpriority in [\"1-URGENT\", \"2-HIGH\"])\n",
    "    :filter(\n",
    "        shipmode in SHIPMODES &&\n",
    "        shipdate < commitdate < receiptdate &&\n",
    "        DATE <= receiptdate < DATE + 1_year)\n",
    "    :group(shipmode)\n",
    "    :select(\n",
    "        shipmode,\n",
    "        high_line_count => count(lineitem:filter(high)),\n",
    "        low_line_count => count(lineitem:filter(!high))),\n",
    "    SHIPMODES=[\"MAIL\", \"SHIP\"], DATE=Date(\"1994-01-01\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Customer Distribution Query (Q13)\n",
    "*[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 51]*\n",
    "\n",
    "This query seeks relationships between customers and the size of their orders.\n",
    "\n",
    "\n",
    "### Business Question\n",
    "This query determines the distribution of customers by the number of orders they have made, including customers\n",
    "who have no record of orders, past or present. It counts and reports how many customers have no orders, how many\n",
    "have 1, 2, 3, etc. A check is made to ensure that the orders counted do not fall into one of several special categories of orders. Special categories are identified in the order comment column by looking for a particular pattern.\n",
    "\n",
    "### Query Definition\n",
    "```\n",
    "select\n",
    "    c_count,\n",
    "    count(*) as custdist\n",
    "from (\n",
    "    select\n",
    "        c_custkey,\n",
    "        count(o_orderkey)\n",
    "    from\n",
    "        customer left outer join orders\n",
    "            on c_custkey = o_custkey and o_comment not like '%[WORD1]%[WORD2]%'\n",
    "    group by\n",
    "        c_custkey) as c_orders (c_custkey, c_count)\n",
    "group by\n",
    "    c_count\n",
    "order by\n",
    "    custdist desc,\n",
    "    c_count desc;\n",
    "```\n",
    "\n",
    "### Substitution Parameters\n",
    "1. WORD1 = special.\n",
    "2. WORD2 = requests."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>c_count</th><th>custdist</th></tr><tr><th>1</th><td>0</td><td>400</td></tr><tr><th>2</th><td>11</td><td>57</td></tr><tr><th>3</th><td>12</td><td>55</td></tr><tr><th>4</th><td>10</td><td>53</td></tr><tr><th>5</th><td>8</td><td>51</td></tr><tr><th>6</th><td>9</td><td>48</td></tr><tr><th>7</th><td>17</td><td>43</td></tr><tr><th>8</th><td>13</td><td>39</td></tr><tr><th>9</th><td>22</td><td>37</td></tr><tr><th>10</th><td>21</td><td>37</td></tr><tr><th>&vellip;</th><td>&vellip;</td><td>&vellip;</td></tr></table>"
      ],
      "text/plain": [
       "33x2 DataFrames.DataFrame\n",
       "| Row | c_count | custdist |\n",
       "|-----|---------|----------|\n",
       "| 1   | 0       | 400      |\n",
       "⋮\n",
       "| 32  | 35      | 1        |\n",
       "| 33  | 33      | 1        |"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    customer\n",
    "    :group(\n",
    "        c_count => count(order:filter(!contains(comment, WORDS))))\n",
    "    :select(\n",
    "        c_count,\n",
    "        custdist => count(customer))\n",
    "    :sort(\n",
    "        custdist:desc,\n",
    "        c_count:desc),\n",
    "    WORDS=r\".*special.*requests.*\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Promotion Effect Query (Q14)\n",
    "*[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 52]*\n",
    "\n",
    "This query monitors the market response to a promotion such as TV advertisements or a special campaign.\n",
    "\n",
    "### Business Question\n",
    "The Promotion Effect Query determines what percentage of the revenue in a given year and month was derived from\n",
    "promotional parts. The query considers only parts actually shipped in that month and gives the percentage. Revenue\n",
    "is defined as `(l_extendedprice * (1-l_discount))`.\n",
    "\n",
    "### Query Definition\n",
    "```\n",
    "select\n",
    "    100.00 * sum(\n",
    "        case\n",
    "            when p_type like 'PROMO%'\n",
    "            then l_extendedprice*(1-l_discount)\n",
    "            else 0\n",
    "        end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue\n",
    "from\n",
    "    lineitem,\n",
    "    part\n",
    "where\n",
    "    l_partkey = p_partkey\n",
    "    and l_shipdate >= date '[DATE]'\n",
    "    and l_shipdate < date '[DATE]' + interval '1' month;\n",
    "```\n",
    "\n",
    "### Substitution Parameters\n",
    "1. DATE = 1995-09-01."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>promo_revenue</th></tr><tr><th>1</th><td>12.409568669150199</td></tr></table>"
      ],
      "text/plain": [
       "1x1 DataFrames.DataFrame\n",
       "| Row | promo_revenue |\n",
       "|-----|---------------|\n",
       "| 1   | 12.4096       |"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    lineitem\n",
    "    :define(\n",
    "        volume => extendedprice*(1-discount),\n",
    "        promo => startswith(part.type_, \"PROMO\"))\n",
    "    :filter(DATE <= shipdate < DATE + 1_month)\n",
    "    :group\n",
    "    :select(\n",
    "        promo_revenue =>\n",
    "            100*sum(lineitem:filter(promo).volume)/sum(lineitem.volume)),\n",
    "    DATE=Date(\"1995-09-01\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Top Supplier Query (Q15)\n",
    "*[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 53]*\n",
    "\n",
    "This query determines the top supplier so it can be rewarded, given more business, or identified for special recognition.\n",
    "\n",
    "### Business Question\n",
    "The Top Supplier Query finds the supplier who contributed the most to the overall revenue for parts shipped during\n",
    "a given quarter of a given year. In case of a tie, the query lists all suppliers whose contribution was equal to the\n",
    "maximum, presented in supplier number order.\n",
    "\n",
    "### Query Definition\n",
    "```\n",
    "create view revenue (supplier_no, total_revenue) as\n",
    "    select\n",
    "        l_suppkey,\n",
    "        sum(l_extendedprice * (1 - l_discount))\n",
    "    from\n",
    "        lineitem\n",
    "    where\n",
    "        l_shipdate >= date '[DATE]'\n",
    "        and l_shipdate < date '[DATE]' + interval '3' month\n",
    "    group by\n",
    "        l_suppkey;\n",
    "        \n",
    "select\n",
    "    s_suppkey,\n",
    "    s_name,\n",
    "    s_address,\n",
    "    s_phone,\n",
    "    total_revenue\n",
    "from\n",
    "    supplier,\n",
    "    revenue\n",
    "where\n",
    "    s_suppkey = supplier_no\n",
    "    and total_revenue = (\n",
    "        select\n",
    "            max(total_revenue)\n",
    "        from\n",
    "            revenue)\n",
    "order by\n",
    "    s_suppkey;\n",
    "    \n",
    "drop view revenue;\n",
    "```\n",
    "\n",
    "### Substitution Parameters\n",
    "1. DATE = 1996-01-01."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>id</th><th>name</th><th>address</th><th>phone</th><th>total_revenue</th></tr><tr><th>1</th><td>17</td><td>Supplier#000000017</td><td>c2d,ESHRSkK3WYnxpgw6aOqN0q</td><td>29-601-884-9219</td><td>1113801.53</td></tr></table>"
      ],
      "text/plain": [
       "1x5 DataFrames.DataFrame\n",
       "| Row | id | name                 | address                      |\n",
       "|-----|----|----------------------|------------------------------|\n",
       "| 1   | 17 | \"Supplier#000000017\" | \"c2d,ESHRSkK3WYnxpgw6aOqN0q\" |\n",
       "\n",
       "| Row | phone             | total_revenue |\n",
       "|-----|-------------------|---------------|\n",
       "| 1   | \"29-601-884-9219\" | 1113801.53    |"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    supplier\n",
    "    :define(\n",
    "        total_revenue =>\n",
    "            sum(lineitem:filter(DATE <= shipdate < DATE+3_months).(extendedprice*(1-discount))))\n",
    "    :first(total_revenue)\n",
    "    :select(\n",
    "        id,\n",
    "        name,\n",
    "        address,\n",
    "        phone,\n",
    "        total_revenue),\n",
    "    DATE=Date(\"1996-01-01\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Parts/Supplier Relationship Query (Q16)\n",
    "*[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 55]*\n",
    "\n",
    "This query finds out how many suppliers can supply parts with given attributes. It might be used, for example, to\n",
    "determine whether there is a sufficient number of suppliers for heavily ordered parts.\n",
    "\n",
    "### Business Question\n",
    "The Parts/Supplier Relationship Query counts the number of suppliers who can supply parts that satisfy a particular\n",
    "customer's requirements. The customer is interested in parts of eight different sizes as long as they are not of a given type, not of a given brand, and not from a supplier who has had complaints registered at the Better Business Bureau. Results must be presented in descending count and ascending brand, type, and size.\n",
    "\n",
    "### Query Definition\n",
    "```\n",
    "select\n",
    "    p_brand,\n",
    "    p_type,\n",
    "    p_size,\n",
    "    count(distinct ps_suppkey) as supplier_cnt\n",
    "from\n",
    "    partsupp,\n",
    "    part\n",
    "where\n",
    "    p_partkey = ps_partkey\n",
    "    and p_brand <> '[BRAND]'\n",
    "    and p_type not like '[TYPE]%'\n",
    "    and p_size in ([SIZE1], [SIZE2], [SIZE3], [SIZE4], [SIZE5], [SIZE6], [SIZE7], [SIZE8])\n",
    "    and ps_suppkey not in (\n",
    "        select\n",
    "            s_suppkey\n",
    "        from\n",
    "            supplier\n",
    "        where\n",
    "            s_comment like '%Customer%Complaints%')\n",
    "group by\n",
    "    p_brand,\n",
    "    p_type,\n",
    "    p_size\n",
    "order by\n",
    "    supplier_cnt desc,\n",
    "    p_brand,\n",
    "    p_type,\n",
    "    p_size;\n",
    "```\n",
    "\n",
    "### Substitution Parameters\n",
    "1. BRAND = Brand#45.\n",
    "2. TYPE = MEDIUM POLISHED.\n",
    "3. SIZE1 = 49\n",
    "4. SIZE2 = 14\n",
    "5. SIZE3 = 23\n",
    "6. SIZE4 = 45\n",
    "7. SIZE5 = 19\n",
    "8. SIZE6 = 3\n",
    "9. SIZE7 = 36\n",
    "10. SIZE8 = 9."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>brand</th><th>type_</th><th>size</th><th>supplier_cnt</th></tr><tr><th>1</th><td>Brand#14</td><td>PROMO BRUSHED STEEL</td><td>9</td><td>8</td></tr><tr><th>2</th><td>Brand#35</td><td>SMALL POLISHED COPPER</td><td>14</td><td>8</td></tr><tr><th>3</th><td>Brand#11</td><td>LARGE PLATED TIN</td><td>23</td><td>4</td></tr><tr><th>4</th><td>Brand#11</td><td>MEDIUM ANODIZED BRASS</td><td>45</td><td>4</td></tr><tr><th>5</th><td>Brand#11</td><td>MEDIUM BRUSHED BRASS</td><td>45</td><td>4</td></tr><tr><th>6</th><td>Brand#11</td><td>PROMO ANODIZED BRASS</td><td>49</td><td>4</td></tr><tr><th>7</th><td>Brand#11</td><td>PROMO ANODIZED TIN</td><td>45</td><td>4</td></tr><tr><th>8</th><td>Brand#11</td><td>PROMO BURNISHED BRASS</td><td>36</td><td>4</td></tr><tr><th>9</th><td>Brand#11</td><td>SMALL ANODIZED TIN</td><td>45</td><td>4</td></tr><tr><th>10</th><td>Brand#11</td><td>SMALL PLATED COPPER</td><td>45</td><td>4</td></tr><tr><th>&vellip;</th><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td></tr></table>"
      ],
      "text/plain": [
       "242x4 DataFrames.DataFrame\n",
       "| Row | brand      | type_                     | size | supplier_cnt |\n",
       "|-----|------------|---------------------------|------|--------------|\n",
       "| 1   | \"Brand#14\" | \"PROMO BRUSHED STEEL\"     | 9    | 8            |\n",
       "⋮\n",
       "| 241 | \"Brand#55\" | \"STANDARD BRUSHED COPPER\" | 3    | 4            |\n",
       "| 242 | \"Brand#55\" | \"STANDARD BRUSHED STEEL\"  | 19   | 4            |"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    partsupp\n",
    "    :filter(\n",
    "        part.brand != BRAND &&\n",
    "        !startswith(part.type_, TYPE) &&\n",
    "        part.size in SIZES &&\n",
    "        !contains(supplier.comment, r\".*Customer.*Complaints.*\"))\n",
    "    :group(\n",
    "        part.brand,\n",
    "        part.type_,\n",
    "        part.size)\n",
    "    :select(\n",
    "        brand,\n",
    "        type_,\n",
    "        size,\n",
    "        supplier_cnt => count(unique(partsupp.supplier)))\n",
    "    :sort(supplier_cnt:desc),\n",
    "    BRAND=\"Brand#45\", TYPE=\"MEDIUM POLISHED\", SIZES=[49, 14, 23, 45, 19, 3, 36, 9])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Small-Quantity-Order Revenue Query (Q17)\n",
    "*[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 57]*\n",
    "\n",
    "This query determines how much average yearly revenue would be lost if orders were no longer filled for small\n",
    "quantities of certain parts. This may reduce overhead expenses by concentrating sales on larger shipments.\n",
    "\n",
    "### Business Question\n",
    "The Small-Quantity-Order Revenue Query considers parts of a given brand and with a given container type and\n",
    "determines the average lineitem quantity of such parts ordered for all orders (past and pending) in the 7-year database. What would be the average yearly gross (undiscounted) loss in revenue if orders for these parts with a quantity of less than 20% of this average were no longer taken?\n",
    "\n",
    "### Functional Query Definition\n",
    "```\n",
    "select\n",
    "    sum(l_extendedprice) / 7.0 as avg_yearly\n",
    "from\n",
    "    lineitem,\n",
    "    part\n",
    "where\n",
    "    p_partkey = l_partkey\n",
    "    and p_brand = '[BRAND]'\n",
    "    and p_container = '[CONTAINER]'\n",
    "    and l_quantity < (\n",
    "        select\n",
    "            0.2 * avg(l_quantity)\n",
    "        from\n",
    "            lineitem\n",
    "        where\n",
    "            l_partkey = p_partkey);\n",
    "```\n",
    "\n",
    "### Substitution Parameters\n",
    "1. BRAND = Brand#23;\n",
    "2. CONTAINER = MED JAR."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>avg_yearly</th></tr><tr><th>1</th><td>1217.62</td></tr></table>"
      ],
      "text/plain": [
       "1x1 DataFrames.DataFrame\n",
       "| Row | avg_yearly |\n",
       "|-----|------------|\n",
       "| 1   | 1217.62    |"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    lineitem\n",
    "    :filter(part.brand == BRAND && part.container == CONTAINER)\n",
    "    :filter(quantity < 0.2 * mean(and_around(part).quantity))\n",
    "    :group\n",
    "    :select(\n",
    "        avg_yearly => sum(lineitem.extendedprice)/7),\n",
    "    BRAND=\"Brand#23\", CONTAINER=\"MED JAR\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Large Volume Customer Query (Q18)\n",
    "*[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 58]*\n",
    "\n",
    "The Large Volume Customer Query ranks customers based on their having placed a large quantity order. Large\n",
    "quantity orders are defined as those orders whose total quantity is above a certain level.\n",
    "\n",
    "\n",
    "### Business Question\n",
    "The Large Volume Customer Query finds a list of the top 100 customers who have ever placed large quantity orders.\n",
    "The query lists the customer name, customer key, the order key, date and total price and the quantity for the order.\n",
    "\n",
    "### Functional Query Definition\n",
    "```\n",
    "select\n",
    "    c_name,\n",
    "    c_custkey,\n",
    "    o_orderkey,\n",
    "    o_orderdate,\n",
    "    o_totalprice,\n",
    "    sum(l_quantity)\n",
    "from\n",
    "    customer,\n",
    "    orders,\n",
    "    lineitem\n",
    "where\n",
    "    o_orderkey in (\n",
    "        select\n",
    "            l_orderkey\n",
    "        from\n",
    "            lineitem\n",
    "        group by\n",
    "            l_orderkey\n",
    "        having\n",
    "            sum(l_quantity) > [QUANTITY])\n",
    "    and c_custkey = o_custkey\n",
    "    and o_orderkey = l_orderkey\n",
    "group by\n",
    "    c_name,\n",
    "    c_custkey,\n",
    "    o_orderkey,\n",
    "    o_orderdate,\n",
    "    o_totalprice\n",
    "order by\n",
    "    o_totalprice desc,\n",
    "    o_orderdate\n",
    "limit 100;\n",
    "```\n",
    "\n",
    "### Substitution Parameters\n",
    "1. QUANTITY = 300"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>name</th><th>custkey</th><th>orderkey</th><th>orderdate</th><th>totalprice</th><th>quantity</th></tr><tr><th>1</th><td>Customer#000000142</td><td>142</td><td>1722</td><td>1997-04-09</td><td>423609.13</td><td>303</td></tr><tr><th>2</th><td>Customer#000000533</td><td>533</td><td>7294</td><td>1995-10-21</td><td>372230.14</td><td>305</td></tr></table>"
      ],
      "text/plain": [
       "2x6 DataFrames.DataFrame\n",
       "| Row | name                 | custkey | orderkey | orderdate  | totalprice |\n",
       "|-----|----------------------|---------|----------|------------|------------|\n",
       "| 1   | \"Customer#000000142\" | 142     | 1722     | 1997-04-09 | 423609.13  |\n",
       "| 2   | \"Customer#000000533\" | 533     | 7294     | 1995-10-21 | 372230.14  |\n",
       "\n",
       "| Row | quantity |\n",
       "|-----|----------|\n",
       "| 1   | 303      |\n",
       "| 2   | 305      |"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    order\n",
    "    :select(\n",
    "        customer.name,\n",
    "        custkey => customer.id,\n",
    "        orderkey => id,\n",
    "        orderdate,\n",
    "        totalprice,\n",
    "        quantity => sum(lineitem.quantity))\n",
    "    :filter(quantity > QUANTITY)\n",
    "    :sort(\n",
    "        totalprice:desc,\n",
    "        orderdate)\n",
    "    :take(100),\n",
    "    QUANTITY=300)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Discounted Revenue Query (Q19)\n",
    "*[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 60]*\n",
    "\n",
    "The Discounted Revenue Query reports the gross discounted revenue attributed to the sale of selected parts handled\n",
    "in a particular manner. This query is an example of code such as might be produced programmatically by a data\n",
    "mining tool.\n",
    "\n",
    "### Business Question\n",
    "The Discounted Revenue query finds the gross discounted revenue for all orders for three different types of parts\n",
    "that were shipped by air and delivered in person. Parts are selected based on the combination of specific brands, a\n",
    "list of containers, and a range of sizes.\n",
    "\n",
    "### Query Definition\n",
    "```\n",
    "select\n",
    "    sum(l_extendedprice * (1 - l_discount) ) as revenue\n",
    "from\n",
    "    lineitem,\n",
    "    part\n",
    "where\n",
    "    (\n",
    "        p_partkey = l_partkey\n",
    "        and p_brand = '[BRAND1]'\n",
    "        and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')\n",
    "        and l_quantity >= [QUANTITY1] and l_quantity <= [QUANTITY1] + 10\n",
    "        and p_size between 1 and 5\n",
    "        and l_shipmode in ('AIR', 'AIR REG')\n",
    "        and l_shipinstruct = 'DELIVER IN PERSON'\n",
    "    )\n",
    "    or\n",
    "    (\n",
    "        p_partkey = l_partkey\n",
    "        and p_brand = '[BRAND2]'\n",
    "        and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')\n",
    "        and l_quantity >= [QUANTITY2] and l_quantity <= [QUANTITY2] + 10\n",
    "        and p_size between 1 and 10\n",
    "        and l_shipmode in ('AIR', 'AIR REG')\n",
    "        and l_shipinstruct = 'DELIVER IN PERSON'\n",
    "    )\n",
    "    or\n",
    "    (\n",
    "        p_partkey = l_partkey\n",
    "        and p_brand = '[BRAND3]'\n",
    "        and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')\n",
    "        and l_quantity >= [QUANTITY3] and l_quantity <= [QUANTITY3] + 10\n",
    "        and p_size between 1 and 15\n",
    "        and l_shipmode in ('AIR', 'AIR REG')\n",
    "        and l_shipinstruct = 'DELIVER IN PERSON'\n",
    "    );\n",
    "```\n",
    "\n",
    "### Substitution Parameters\n",
    "1. QUANTITY1 = 1.\n",
    "2. QUANTITY2 = 10.\n",
    "3. QUANTITY3 = 20.\n",
    "4. BRAND1 = Brand#53.\n",
    "5. BRAND2 = Brand#35.\n",
    "6. BRAND3 = Brand#32."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>revenue</th></tr><tr><th>1</th><td>40908.53</td></tr></table>"
      ],
      "text/plain": [
       "1x1 DataFrames.DataFrame\n",
       "| Row | revenue  |\n",
       "|-----|----------|\n",
       "| 1   | 40908.53 |"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    lineitem\n",
    "    :filter(\n",
    "        (\n",
    "            part.brand == BRAND1 &&\n",
    "            part.container in [\"SM CASE\", \"SM BOX\", \"SM PACK\", \"SM PKG\"] &&\n",
    "            QUANTITY1 <= quantity <= QUANTITY1+10 &&\n",
    "            1 <= part.size <= 5 &&\n",
    "            shipmode in [\"AIR\", \"AIR REG\"] &&\n",
    "            shipinstruct == \"DELIVER IN PERSON\"\n",
    "        )\n",
    "        ||\n",
    "        (\n",
    "            part.brand == BRAND2 && \n",
    "            part.container in [\"MED BAG\", \"MED BOX\", \"MED PKG\", \"MED PACK\"] &&\n",
    "            QUANTITY2 <= quantity <= QUANTITY2+10 &&\n",
    "            1 <= part.size <= 10 &&\n",
    "            shipmode in [\"AIR\", \"AIR REG\"] &&\n",
    "            shipinstruct == \"DELIVER IN PERSON\"\n",
    "        )\n",
    "        ||\n",
    "        (\n",
    "            part.brand == BRAND3 &&\n",
    "            part.container in [\"LG CASE\", \"LG BOX\", \"LG PACK\", \"LG PKG\"] &&\n",
    "            QUANTITY3 <= quantity <= QUANTITY3+10 &&\n",
    "            1 <= part.size <= 15 &&\n",
    "            shipmode in [\"AIR\", \"AIR REG\"] &&\n",
    "            shipinstruct == \"DELIVER IN PERSON\"\n",
    "        ))\n",
    "    :group\n",
    "    :select(\n",
    "        revenue => sum(lineitem.(extendedprice*(1-discount)))),\n",
    "    QUANTITY1=1, QUANTITY2=10, QUANTITY3=20,\n",
    "    BRAND1=\"Brand#53\", BRAND2=\"Brand#35\", BRAND3=\"Brand#32\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Potential Part Promotion Query (Q20)\n",
    "*[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 62]*\n",
    "\n",
    "The Potential Part Promotion Query identifies suppliers in a particular nation having selected parts that may be candidates for a promotional offer.\n",
    "\n",
    "### Business Question\n",
    "The Potential Part Promotion query identifies suppliers who have an excess of a given part available; an excess is\n",
    "defined to be more than 50% of the parts like the given part that the supplier shipped in a given year for a given\n",
    "nation. Only parts whose names share a certain naming convention are considered.\n",
    "\n",
    "### Query Definition\n",
    "```\n",
    "select\n",
    "    s_name,\n",
    "    s_address\n",
    "from\n",
    "    supplier,\n",
    "    nation\n",
    "where\n",
    "    s_suppkey in (\n",
    "        select\n",
    "            ps_suppkey\n",
    "        from\n",
    "            partsupp\n",
    "        where\n",
    "            ps_partkey in (\n",
    "                select\n",
    "                    p_partkey\n",
    "                from\n",
    "                    part\n",
    "                where\n",
    "                    p_name like '[COLOR]%')\n",
    "            and ps_availqty > (\n",
    "                select\n",
    "                    0.5 * sum(l_quantity)\n",
    "                from\n",
    "                    lineitem\n",
    "                where\n",
    "                    l_partkey = ps_partkey\n",
    "                    and l_suppkey = ps_suppkey\n",
    "                    and l_shipdate >= date('[DATE]')\n",
    "                    and l_shipdate < date('[DATE]') + interval '1' year))\n",
    "    and s_nationkey = n_nationkey\n",
    "    and n_name = '[NATION]'\n",
    "order by\n",
    "    s_name;\n",
    "```\n",
    "\n",
    "### Substitution Parameters\n",
    "1. COLOR = forest.\n",
    "2. DATE = 1994-01-01.\n",
    "3. NATION = CANADA."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>name</th><th>address</th></tr><tr><th>1</th><td>Supplier#000000020</td><td>iybAE,RmTymrZVYaFZva2SH,j</td></tr></table>"
      ],
      "text/plain": [
       "1x2 DataFrames.DataFrame\n",
       "| Row | name                 | address                     |\n",
       "|-----|----------------------|-----------------------------|\n",
       "| 1   | \"Supplier#000000020\" | \"iybAE,RmTymrZVYaFZva2SH,j\" |"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    partsupp\n",
    "    :define(\n",
    "        expectedqty =>\n",
    "            sum(lineitem:filter(DATE <= shipdate < DATE + 1_year).quantity))\n",
    "    :filter(\n",
    "        supplier.nation.name == NATION &&\n",
    "        startswith(part.name, COLOR) &&\n",
    "        availqty > 0.5*expectedqty)\n",
    "    :group(supplier)\n",
    "    :select(\n",
    "        supplier.name,\n",
    "        supplier.address)\n",
    "    :sort(name),\n",
    "    COLOR=\"forest\", DATE=Date(\"1994-01-01\"), NATION=\"CANADA\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Suppliers Who Kept Orders Waiting Query (Q21)\n",
    "*[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 64]*\n",
    "\n",
    "This query identifies certain suppliers who were not able to ship required parts in a timely manner.\n",
    "\n",
    "### Business Question\n",
    "The Suppliers Who Kept Orders Waiting query identifies suppliers, for a given nation, whose product was part of a\n",
    "multi-supplier order (with current status of 'F') where they were the only supplier who failed to meet the committed\n",
    "delivery date.\n",
    "\n",
    "### Query Definition\n",
    "```\n",
    "select\n",
    "    s_name,\n",
    "    count(*) as numwait\n",
    "from\n",
    "    supplier,\n",
    "    lineitem l1,\n",
    "    orders,\n",
    "    nation\n",
    "where\n",
    "    s_suppkey = l1.l_suppkey\n",
    "    and o_orderkey = l1.l_orderkey\n",
    "    and o_orderstatus = 'F'\n",
    "    and l1.l_receiptdate > l1.l_commitdate\n",
    "    and exists (\n",
    "        select\n",
    "            *\n",
    "        from\n",
    "            lineitem l2\n",
    "        where\n",
    "            l2.l_orderkey = l1.l_orderkey\n",
    "            and l2.l_suppkey <> l1.l_suppkey)\n",
    "    and not exists (\n",
    "        select\n",
    "            *\n",
    "        from\n",
    "            lineitem l3\n",
    "        where\n",
    "            l3.l_orderkey = l1.l_orderkey\n",
    "            and l3.l_suppkey <> l1.l_suppkey\n",
    "            and l3.l_receiptdate > l3.l_commitdate)\n",
    "    and s_nationkey = n_nationkey\n",
    "    and n_name = '[NATION]'\n",
    "group by\n",
    "    s_name\n",
    "order by\n",
    "    numwait desc,\n",
    "    s_name\n",
    "limit 100;\n",
    "```\n",
    "\n",
    "### Substitution Parameters\n",
    "1. NATION = SAUDI ARABIA."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>name</th><th>numwait</th></tr><tr><th>1</th><td>Supplier#000000074</td><td>10</td></tr></table>"
      ],
      "text/plain": [
       "1x2 DataFrames.DataFrame\n",
       "| Row | name                 | numwait |\n",
       "|-----|----------------------|---------|\n",
       "| 1   | \"Supplier#000000074\" | 10      |"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    lineitem\n",
    "    :filter((\n",
    "        order.orderstatus == \"F\" &&\n",
    "        supplier.nation.name == NATION &&\n",
    "        receiptdate > commitdate &&\n",
    "        any(order.lineitem.supplier != SUPP) &&\n",
    "        !any(order.lineitem.(supplier != SUPP && receiptdate > commitdate)))\n",
    "        :given(SUPP => supplier))\n",
    "    :group(supplier)\n",
    "    :select(\n",
    "        supplier.name,\n",
    "        numwait => count(lineitem))\n",
    "    :sort(\n",
    "        numwait:desc,\n",
    "        name)\n",
    "    :take(100),\n",
    "    NATION=\"SAUDI ARABIA\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Global Sales Opportunity Query (Q22)\n",
    "*[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 66]*\n",
    "\n",
    "The Global Sales Opportunity Query identifies geographies where there are customers who may be likely to make a\n",
    "purchase.\n",
    "\n",
    "### Business Question\n",
    "This query counts how many customers within a specific range of country codes have not placed orders for 7 years\n",
    "but who have a greater than average “positive” account balance. It also reflects the magnitude of that balance.\n",
    "Country code is defined as the first two characters of c_phone.\n",
    "\n",
    "### Query Definition\n",
    "```\n",
    "select\n",
    "    cntrycode,\n",
    "    count(*) as numcust,\n",
    "    sum(c_acctbal) as totacctbal\n",
    "from (\n",
    "    select\n",
    "        substring(c_phone from 1 for 2) as cntrycode,\n",
    "        c_acctbal\n",
    "    from\n",
    "        customer\n",
    "    where\n",
    "        substring(c_phone from 1 for 2) in\n",
    "            ('[I1]','[I2]','[I3]','[I4]','[I5]','[I6]','[I7]')\n",
    "        and c_acctbal > (\n",
    "            select\n",
    "                avg(c_acctbal)\n",
    "            from\n",
    "                customer\n",
    "            where\n",
    "                c_acctbal > 0.00\n",
    "                and substring (c_phone from 1 for 2) in\n",
    "                    ('[I1]','[I2]','[I3]','[I4]','[I5]','[I6]','[I7]'))\n",
    "        and not exists (\n",
    "            select\n",
    "                *\n",
    "            from\n",
    "                orders\n",
    "            where\n",
    "                o_custkey = c_custkey)) as custsale\n",
    "group by\n",
    "    cntrycode\n",
    "order by\n",
    "    cntrycode;\n",
    "```\n",
    "\n",
    "### Substitution Parameters\n",
    "1. I1 = 13.\n",
    "2. I2 = 31.\n",
    "3. I3 = 23.\n",
    "4. I4 = 29.\n",
    "5. I5 = 30.\n",
    "6. I6 = 18.\n",
    "7. I7 = 17."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>cntrycode</th><th>numcust</th><th>totacctbal</th></tr><tr><th>1</th><td>13</td><td>8</td><td>59306.91</td></tr><tr><th>2</th><td>17</td><td>6</td><td>49853.61</td></tr><tr><th>3</th><td>18</td><td>11</td><td>89404.25</td></tr><tr><th>4</th><td>23</td><td>3</td><td>25939.81</td></tr><tr><th>5</th><td>29</td><td>11</td><td>88722.85</td></tr><tr><th>6</th><td>30</td><td>13</td><td>92703.39</td></tr><tr><th>7</th><td>31</td><td>9</td><td>69434.60</td></tr></table>"
      ],
      "text/plain": [
       "7x3 DataFrames.DataFrame\n",
       "| Row | cntrycode | numcust | totacctbal |\n",
       "|-----|-----------|---------|------------|\n",
       "| 1   | \"13\"      | 8       | 59306.91   |\n",
       "⋮\n",
       "| 6   | \"30\"      | 13      | 92703.39   |\n",
       "| 7   | \"31\"      | 9       | 69434.60   |"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@query(\n",
    "    customer\n",
    "    :define(\n",
    "        cntrycode => substr(phone, 1, 2))\n",
    "    :filter(\n",
    "        cntrycode in IS &&\n",
    "        acctbal > 0_usd)\n",
    "    :filter(\n",
    "        !exists(order) &&\n",
    "        acctbal > mean(and_around.acctbal))\n",
    "    :group(cntrycode)\n",
    "    :select(\n",
    "        cntrycode,\n",
    "        numcust => count(customer),\n",
    "        totacctbal => sum(customer.acctbal)),\n",
    "    IS=[\"13\", \"31\", \"23\", \"29\", \"30\", \"18\", \"17\"])"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Julia 0.5.0-dev",
   "language": "julia",
   "name": "julia-0.5"
  },
  "language_info": {
   "file_extension": ".jl",
   "mimetype": "application/julia",
   "name": "julia",
   "version": "0.5.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}