TPC-H Queries

In this notebook, we explore how to reimplement SQL queries from the well-known TPC-H benchmark in Rabbit.

We start with loading the dataset.

In [1]:
include("../tpch.jl")

using RBT
setdb(tpch)
Out[1]:
Region:
  name :: ASCIIString
  comment :: ASCIIString
  nation (inverse of Nation.region) :: Array{Nation,1} # unique, covering
Nation:
  name :: ASCIIString
  region :: Region
  comment :: ASCIIString
  customer (inverse of Customer.nation) :: Array{Customer,1} # unique, covering
  supplier (inverse of Supplier.nation) :: Array{Supplier,1} # unique, covering
Customer:
  name :: ASCIIString
  address :: ASCIIString
  nation :: Nation
  phone :: ASCIIString
  acctbal :: RBT.Monetary{:USD}
  mktsegment :: ASCIIString
  comment :: ASCIIString
  order (inverse of Order.customer) :: Array{Order,1} # unique, covering
Supplier:
  name :: ASCIIString
  address :: ASCIIString
  nation :: Nation
  phone :: ASCIIString
  acctbal :: RBT.Monetary{:USD}
  comment :: ASCIIString
  partsupp (inverse of Partsupp.supplier) :: Array{Partsupp,1} # unique, covering
  lineitem (inverse of Lineitem.supplier) :: Array{Lineitem,1} # unique, covering
Part:
  name :: ASCIIString
  mfgr :: ASCIIString
  brand :: ASCIIString
  type_ :: ASCIIString
  size :: Int64
  container :: ASCIIString
  retailprice :: RBT.Monetary{:USD}
  comment :: ASCIIString
  partsupp (inverse of Partsupp.part) :: Array{Partsupp,1} # unique, covering
  lineitem (inverse of Lineitem.part) :: Array{Lineitem,1} # unique, covering
Partsupp:
  part :: Part
  supplier :: Supplier
  availqty :: Int64
  supplycost :: RBT.Monetary{:USD}
  comment :: ASCIIString
  lineitem (inverse of Lineitem.partsupp) :: Array{Lineitem,1} # unique, covering
Order:
  customer :: Customer
  orderstatus :: ASCIIString
  totalprice :: RBT.Monetary{:USD}
  orderdate :: Date
  orderpriority :: ASCIIString
  clerk :: ASCIIString
  shippriority :: Int64
  comment :: ASCIIString
  lineitem (inverse of Lineitem.order) :: Array{Lineitem,1} # unique, covering
Lineitem:
  order :: Order
  part :: Part
  supplier :: Supplier
  partsupp :: Partsupp
  linenumber :: Int64
  quantity :: Int64
  extendedprice :: RBT.Monetary{:USD}
  discount :: Float64
  tax :: Float64
  returnflag :: ASCIIString
  linestatus :: ASCIIString
  shipdate :: Date
  commitdate :: Date
  receiptdate :: Date
  shipinstruct :: ASCIIString
  shipmode :: ASCIIString
  comment :: ASCIIString

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.

Table order lists all orders made by customers.

In [2]:
@query(
    order
    :select(
        customer.name, totalprice, orderdate))
Out[2]:
nametotalpriceorderdate
1Customer#000000296159877.961996-01-02
2Customer#00000062569858.911996-12-01
3Customer#000000988183718.981993-10-14
4Customer#00000109650463.931995-10-11
5Customer#000000356133409.481994-07-30
6Customer#00000044535731.211992-02-21
7Customer#000000314229881.301996-01-10
8Customer#000001042173503.971995-07-16
9Customer#000000536135080.421993-10-27
10Customer#00000049064891.671998-07-21

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.

In [3]:
@query(
    lineitem
    :select(
        order.customer, linenumber, part, supplier, quantity, extendedprice, discount, tax, shipdate))
Out[3]:
customerlinenumberpartsupplierquantityextendedpricediscounttaxshipdate
1Customer#0000002961dodger brown ivory blue pinkSupplier#0000000781719435.080.040.021996-03-13
2Customer#0000002962dodger midnight salmon drab saddleSupplier#0000000603651823.080.090.061996-04-12
3Customer#0000002963blush thistle orchid red laceSupplier#000000031811284.080.10.021996-01-29
4Customer#0000002964turquoise indian lemon lavender mistySupplier#0000000392825704.280.090.061996-04-21
5Customer#0000002965turquoise lime royal metallic azureSupplier#0000000202426236.560.10.041996-03-30
6Customer#0000002966burnished black blue metallic orchidSupplier#0000000093232835.840.070.021996-01-30
7Customer#0000006251peach goldenrod honeydew moccasin siennaSupplier#0000000313866532.300.00.051997-01-28
8Customer#0000009881green blush tomato burlywood seashellSupplier#0000000164542076.350.060.01994-02-02
9Customer#0000009882linen frosted slate coral peruSupplier#0000000574951604.350.10.01993-11-09
10Customer#0000009883blue drab lime khaki siennaSupplier#0000000532725083.540.060.071994-01-16

For each customer and supplier, we track their country of origin. Table nation contains a list of all countries partitioned by region.

In [4]:
@query(
    nation
    :select(
        name,
        region,
        cust_count => count(customer),
        supp_count => count(supplier)))
Out[4]:
nameregioncust_countsupp_count
1ALGERIAAFRICA513
2ARGENTINAAMERICA503
3BRAZILAMERICA631
4CANADAAMERICA482
5EGYPTMIDDLE EAST564
6ETHIOPIAAFRICA443
7FRANCEEUROPE321
8GERMANYEUROPE414
9INDIAASIA525
10INDONESIAASIA564

The following query definitions are taken directly from TPC-H documentation. We will show how to incrementally construct these queries in Rabbit.

Pricing Summary Report Query (Q1)

[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 29]

This query reports the amount of business that was billed, shipped, and returned.

Business Question

The Pricing Summary Report Query provides a summary pricing report for all lineitems shipped as of a given date. The date is within 60 - 120 days of the greatest ship date contained in the database. The query lists totals for extended price, discounted extended price, discounted extended price plus tax, average quantity, average extended price, and average discount. These aggregates are grouped by RETURNFLAG and LINESTATUS, and listed in ascending order of RETURNFLAG and LINESTATUS. A count of the number of lineitems in each group is included.

Query Definition

select
    l_returnflag,
    l_linestatus,
    sum(l_quantity) as sum_qty,
    sum(l_extendedprice) as sum_base_price,
    sum(l_extendedprice*(1-l_discount)) as sum_disc_price,
    sum(l_extendedprice*(1-l_discount)*(1+l_tax)) as sum_charge,
    avg(l_quantity) as avg_qty,
    avg(l_extendedprice) as avg_price,
    avg(l_discount) as avg_disc,
    count(*) as count_order
from
    lineitem
where
    l_shipdate <= date '1998-12-01' - interval '[DELTA]' day
group by
    l_returnflag,
    l_linestatus
order by
    l_returnflag,
    l_linestatus;

Substitution Parameters

  1. DELTA = 90.

To generate this report, we use the data from the lineitem table.

In [5]:
@query(
    lineitem
    :select(order, linenumber, shipdate, quantity, extendedprice, discount, tax, returnflag, linestatus))
Out[5]:
orderlinenumbershipdatequantityextendedpricediscounttaxreturnflaglinestatus
1Customer#00000029611996-03-131719435.080.040.02NO
2Customer#00000029621996-04-123651823.080.090.06NO
3Customer#00000029631996-01-29811284.080.10.02NO
4Customer#00000029641996-04-212825704.280.090.06NO
5Customer#00000029651996-03-302426236.560.10.04NO
6Customer#00000029661996-01-303232835.840.070.02NO
7Customer#00000062511997-01-283866532.300.00.05NO
8Customer#00000098811994-02-024542076.350.060.0RF
9Customer#00000098821993-11-094951604.350.10.0RF
10Customer#00000098831994-01-162725083.540.060.07AF

We start with extracting all lineitems in the selected date range.

In [6]:
@query(
    lineitem
    :filter(shipdate <= date("1998-12-01") - DELTA*days),
    DELTA=90)
Out[6]:
orderpartsupplierlinenumberquantityextendedpricediscounttaxreturnflaglinestatusshipdatecommitdatereceiptdateshipinstructshipmodecomment
1Customer#000000296dodger brown ivory blue pinkSupplier#00000007811719435.080.040.02NO1996-03-131996-02-121996-03-22DELIVER IN PERSONTRUCKegular courts above the
2Customer#000000296dodger midnight salmon drab saddleSupplier#00000006023651823.080.090.06NO1996-04-121996-02-281996-04-20TAKE BACK RETURNMAILly final dependencies: slyly bold
3Customer#000000296blush thistle orchid red laceSupplier#0000000313811284.080.10.02NO1996-01-291996-03-051996-01-31TAKE BACK RETURNREG AIRriously. regular, express dep
4Customer#000000296turquoise indian lemon lavender mistySupplier#00000003942825704.280.090.06NO1996-04-211996-03-301996-05-16NONEAIRlites. fluffily even de
5Customer#000000296turquoise lime royal metallic azureSupplier#00000002052426236.560.10.04NO1996-03-301996-03-141996-04-01NONEFOB pending foxes. slyly re
6Customer#000000296burnished black blue metallic orchidSupplier#00000000963232835.840.070.02NO1996-01-301996-02-071996-02-03DELIVER IN PERSONMAILarefully slyly ex
7Customer#000000625peach goldenrod honeydew moccasin siennaSupplier#00000003113866532.300.00.05NO1997-01-281997-01-141997-02-02TAKE BACK RETURNRAILven requests. deposits breach a
8Customer#000000988green blush tomato burlywood seashellSupplier#00000001614542076.350.060.0RF1994-02-021994-01-041994-02-23NONEAIRongside of the furiously brave acco
9Customer#000000988linen frosted slate coral peruSupplier#00000005724951604.350.10.0RF1993-11-091993-12-201993-11-24TAKE BACK RETURNRAIL unusual accounts. eve
10Customer#000000988blue drab lime khaki siennaSupplier#00000005332725083.540.060.07AF1994-01-161993-11-221994-01-23DELIVER IN PERSONSHIPnal foxes wake.

Next, we group the lineitems by values of returnflag and linestatus attributes.

In [7]:
@query(
    lineitem
    :filter(shipdate <= date("1998-12-01") - DELTA*days)
    :group(returnflag, linestatus),
    DELTA=90)
Out[7]:
returnflaglinestatuslineitem
1AF
orderpartsupplierlinenumberquantityextendedpricediscounttaxreturnflaglinestatusshipdatecommitdatereceiptdateshipinstructshipmodecomment
1Customer#000000988blue drab lime khaki siennaSupplier#00000005332725083.540.060.07AF1994-01-161993-11-221994-01-23DELIVER IN PERSONSHIPnal foxes wake.
2Customer#000000988salmon antique burlywood linen peachSupplier#000000019422272.460.010.06AF1993-12-041994-01-071994-01-01NONETRUCKy. fluffily pending d
3Customer#000000988sandy sky gainsboro peach cornflowerSupplier#00000001762636360.740.10.02AF1993-10-291993-12-181993-11-04TAKE BACK RETURNRAILges sleep after the caref
4Customer#000000356misty snow lace burnished linenSupplier#00000000535060065.000.080.03AF1994-08-081994-10-131994-08-26DELIVER IN PERSONAIReodolites. fluffily unusual
2NF
orderpartsupplierlinenumberquantityextendedpricediscounttaxreturnflaglinestatusshipdatecommitdatereceiptdateshipinstructshipmodecomment
1Customer#000000262hot midnight orchid dim steelSupplier#00000002742526103.500.040.01NF1995-06-131995-05-231995-06-24TAKE BACK RETURNFOBs-- quickly final accounts
2Customer#000000760tan thistle frosted indian lawnSupplier#00000001113132523.340.00.03NF1995-06-051995-06-181995-06-26COLLECT CODFOBfinal theodolites. fluffil
3Customer#000000380pale navajo royal papaya thistleSupplier#00000004214250160.180.030.0NF1995-06-071995-05-291995-06-23TAKE BACK RETURNSHIPy asymptotes. regular depen
4Customer#000001066navy light red royal oliveSupplier#00000006111618468.000.10.06NF1995-05-231995-05-071995-06-19NONETRUCKun quickly slyly
3NO
orderpartsupplierlinenumberquantityextendedpricediscounttaxreturnflaglinestatusshipdatecommitdatereceiptdateshipinstructshipmodecomment
1Customer#000000296dodger brown ivory blue pinkSupplier#00000007811719435.080.040.02NO1996-03-131996-02-121996-03-22DELIVER IN PERSONTRUCKegular courts above the
2Customer#000000296dodger midnight salmon drab saddleSupplier#00000006023651823.080.090.06NO1996-04-121996-02-281996-04-20TAKE BACK RETURNMAILly final dependencies: slyly bold
3Customer#000000296blush thistle orchid red laceSupplier#0000000313811284.080.10.02NO1996-01-291996-03-051996-01-31TAKE BACK RETURNREG AIRriously. regular, express dep
4Customer#000000296turquoise indian lemon lavender mistySupplier#00000003942825704.280.090.06NO1996-04-211996-03-301996-05-16NONEAIRlites. fluffily even de
4RF
orderpartsupplierlinenumberquantityextendedpricediscounttaxreturnflaglinestatusshipdatecommitdatereceiptdateshipinstructshipmodecomment
1Customer#000000988green blush tomato burlywood seashellSupplier#00000001614542076.350.060.0RF1994-02-021994-01-041994-02-23NONEAIRongside of the furiously brave acco
2Customer#000000988linen frosted slate coral peruSupplier#00000005724951604.350.10.0RF1993-11-091993-12-201993-11-24TAKE BACK RETURNRAIL unusual accounts. eve
3Customer#000000988bisque saddle cyan rose wheatSupplier#00000006052838260.880.040.0RF1993-12-141994-01-101994-01-01TAKE BACK RETURNFOBages nag slyly pending
4Customer#000000356snow blush violet lace ghostSupplier#00000007011526547.900.020.04RF1994-10-311994-08-311994-11-20NONEAIRts wake furiously

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.

For example, to find the total number of matching lineitems, we can use count aggregate.

In [8]:
@query(
    lineitem
    :filter(shipdate <= date("1998-12-01") - DELTA*days)
    :group(returnflag, linestatus)
    :select(returnflag, linestatus, count(lineitem)),
    DELTA=90)
Out[8]:
returnflaglinestatus
1AF11958
2NF288
3NO23326
4RF11963

The query output now has the shape of the report. We only need to calculate the respective fields.

In [9]:
@query(
    lineitem
    :filter(shipdate <= date("1998-12-01") - DELTA*days)
    :group(returnflag, linestatus)
    :select(
        returnflag,
        linestatus,
        sum_qty => sum(lineitem.quantity),
        sum_base_price => sum(lineitem.extendedprice),
        sum_disc_price => sum(lineitem.(extendedprice*(1-discount))),
        sum_charge => sum(lineitem.(extendedprice*(1-discount)*(1+tax))),
        avg_qty => mean(lineitem.quantity),
        avg_price => mean(lineitem.extendedprice),
        avg_disc => mean(lineitem.discount),
        count_order => count(lineitem)),
    DELTA=90)
Out[9]:
returnflaglinestatussum_qtysum_base_pricesum_disc_pricesum_chargeavg_qtyavg_priceavg_disccount_order
1AF303771401515429.00381465877.43396851569.6425.4031610637230333577.140.0501195852149188211958
2NF73549721375.749247800.689616674.4325.5347222222222233754.780.04809027777777776288
3NO593340787065026.86748034019.78777970207.0525.43685158192574733741.960.0498851067478349723326
4RF306623406030555.01385792652.67401431970.2225.63094541502967533940.530.0497308367466354111963

Minimum Cost Supplier Query (Q2)

[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 30]

This query finds which supplier should be selected to place an order for a given part in a given region.

Business Question

The Minimum Cost Supplier Query finds, in a given region, for each part of a certain type and size, the supplier who can supply it at minimum cost. If several suppliers in that region offer the desired part type and size at the same (minimum) cost, the query lists the parts from suppliers with the 100 highest account balances. For each supplier, the query lists the supplier's account balance, name and nation; the part's number and manufacturer; the supplier's address, phone number and comment information.

Query Definition

select
    s_acctbal,
    s_name,
    n_name,
    p_partkey,
    p_mfgr,
    s_address,
    s_phone,
    s_comment
from
    part,
    supplier,
    partsupp,
    nation,
    region
where
    p_partkey = ps_partkey
    and s_suppkey = ps_suppkey
    and p_size = [SIZE]
    and p_type like '%[TYPE]'
    and s_nationkey = n_nationkey
    and n_regionkey = r_regionkey
    and r_name = '[REGION]'
    and ps_supplycost = (
        select
            min(ps_supplycost)
        from
            partsupp, supplier,
            nation, region
        where
            p_partkey = ps_partkey
            and s_suppkey = ps_suppkey
            and s_nationkey = n_nationkey
            and n_regionkey = r_regionkey
            and r_name = '[REGION]')
order by
    s_acctbal desc,
    n_name,
    s_name,
    p_partkey
limit 100;

Substitution Parameters

  1. SIZE = 8;
  2. TYPE = BRASS;
  3. REGION = EUROPE.

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.

In [10]:
@query(
    partsupp
    :filter(
        (part.size == SIZE) &
        contains(part.type_, TYPE)),
    SIZE=8, TYPE="BRASS")
Out[10]:
partsupplieravailqtysupplycostcomment
1drab aquamarine red papaya paleSupplier#000000009773165.54ronic accounts haggle blithely across the blithely special platelets. furiously final ideas haggle carefully after the fluffily unusual dep
2drab aquamarine red papaya paleSupplier#0000000328847935.64ular, regular requests affix ironically enticing ideas. fur
3drab aquamarine red papaya paleSupplier#0000000556329148.79enticingly final ideas sleep fluffily. blithely final ideas boost fluffily ironic asymptote
4drab aquamarine red papaya paleSupplier#0000000788054425.78the express accounts. even, ironic pinto beans wake furious
5blue gainsboro sky burnished puffSupplier#0000000148029765.35ous packages sleep slyly across the quickly ironic accounts. regular packages thrash above the furiously final theodolites. furi
6blue gainsboro sky burnished puffSupplier#0000000401481319.99coys 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
7blue gainsboro sky burnished puffSupplier#000000066718634.58furiously slyly ironic packages. pending requests use ruthlessly across the blithely bold deposits. furiously fin
8blue gainsboro sky burnished puffSupplier#0000000122818237.16 the carefully special frays. final excuses haggle slyly. furiously regular deposits across t
9plum indian cornflower frosted purpleSupplier#0000000731076844.74y ironic instructions. even, final requests are. bold instructions use silent, regular accounts-- slyly final excuses doubt fluffily after the quickly p
10plum indian cornflower frosted purpleSupplier#0000000229272904.83ly regular packages. ruthless instructions haggle carefully final, special courts. silently ruthless courts hinder slyly regular packages; regular, regular pac

Among all suppliers, we are only interested in those who are situated in the given REGION.

In [11]:
@query(
    partsupp
    :filter(
        (part.size == SIZE) &
        contains(part.type_, TYPE) &
        (supplier.nation.region.name == REGION)),
    SIZE=8, TYPE="BRASS", REGION="EUROPE")
Out[11]:
partsupplieravailqtysupplycostcomment
1drab aquamarine red papaya paleSupplier#0000000328847935.64ular, regular requests affix ironically enticing ideas. fur
2blue gainsboro sky burnished puffSupplier#0000000401481319.99coys 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
3blue gainsboro sky burnished puffSupplier#000000066718634.58furiously slyly ironic packages. pending requests use ruthlessly across the blithely bold deposits. furiously fin
4tan ghost cyan salmon goldenrodSupplier#000000066322104.62g the quickly express dolphins. slyly even instructions nag furiously. carefully final ideas are idly above the ironic asymptotes. unusual requests haggle about t
5tan ghost cyan salmon goldenrodSupplier#0000000166830298.77ss orbits. furiously regular deposits haggle blithely carefully final pinto beans. ironic, silent accounts thrash quickly about the accounts. iron
6frosted blue ghost purple coralSupplier#000000025290774.80r pinto beans-- quickly final instructions integrate alongside of the sly, silent th
7burlywood seashell slate white creamSupplier#0000000409737799.94ns cajole idly. deposits are quickly blithely even deposits. quickly regular packages boost carefully. blithely ironic asympto
8burlywood seashell slate white creamSupplier#0000000779194240.78l requests cajole carefully. blithely express gifts

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.

In [12]:
@query(
    partsupp
    :filter(
        (part.size == SIZE) &
        contains(part.type_, TYPE) &
        (supplier.nation.region.name == REGION))
    :select(part, supplier, supplycost, othersupplycost => and_around(part).supplycost),
    SIZE=8, TYPE="BRASS", REGION="EUROPE")
Out[12]:
partsuppliersupplycostothersupplycost
1drab aquamarine red papaya paleSupplier#000000032935.64[935.64]
2blue gainsboro sky burnished puffSupplier#000000040319.99[319.99,634.58]
3blue gainsboro sky burnished puffSupplier#000000066634.58[319.99,634.58]
4tan ghost cyan salmon goldenrodSupplier#000000066104.62[104.62,298.77]
5tan ghost cyan salmon goldenrodSupplier#000000016298.77[104.62,298.77]
6frosted blue ghost purple coralSupplier#000000025774.80[774.80]
7burlywood seashell slate white creamSupplier#000000040799.94[799.94,240.78]
8burlywood seashell slate white creamSupplier#000000077240.78[799.94,240.78]

Similarly, we can select rows that provide their part at the minimal cost.

In [13]:
@query(
    partsupp
    :filter(
        (part.size == SIZE) &
        contains(part.type_, TYPE) &
        (supplier.nation.region.name == REGION))
    :filter(supplycost == min(and_around(part).supplycost)),
    SIZE=8, TYPE="BRASS", REGION="EUROPE")
Out[13]:
partsupplieravailqtysupplycostcomment
1drab aquamarine red papaya paleSupplier#0000000328847935.64ular, regular requests affix ironically enticing ideas. fur
2blue gainsboro sky burnished puffSupplier#0000000401481319.99coys 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
3tan ghost cyan salmon goldenrodSupplier#000000066322104.62g the quickly express dolphins. slyly even instructions nag furiously. carefully final ideas are idly above the ironic asymptotes. unusual requests haggle about t
4frosted blue ghost purple coralSupplier#000000025290774.80r pinto beans-- quickly final instructions integrate alongside of the sly, silent th
5burlywood seashell slate white creamSupplier#0000000779194240.78l requests cajole carefully. blithely express gifts

We have the rows that we need in the requested report. We only need to sort them and show the requested columns.

In [14]:
@query(
    partsupp
    :filter(
        (part.size == SIZE) &
        contains(part.type_, TYPE) &
        (supplier.nation.region.name == REGION))
    :filter(supplycost == min(and_around(part).supplycost))
    :sort(
        supplier.acctbal:desc,
        supplier.nation.name,
        part.name,
        part.id)
    :select(
        supplier.acctbal,
        supplier.name,
        supplier.nation,
        part.id,
        part.mfgr,
        supplier.address,
        supplier.phone,
        supplier.comment)
    :take(100),
    SIZE=8, TYPE="BRASS", REGION="EUROPE")
Out[14]:
acctbalnamenationidmfgraddressphonecomment
19198.31Supplier#000000025RUSSIA1156Manufacturer#5RCQKONXMFnrodzz6w7fObFVV6CUm2q32-431-945-3541ely regular deposits. carefully regular sauternes engage furiously above the regular accounts. idly
24186.95Supplier#000000077GERMANY1362Manufacturer#1wVtcr0uH3CyrSiWMLsqnB09Syo,UuZxPMeBghlY17-281-345-4863the slyly final asymptotes. blithely pending theodoli
33556.47Supplier#000000032UNITED KINGDOM248Manufacturer#1yvoD3TtZSx1skQNCK8agk5bZlZLug33-484-637-7873usly even depths. quickly ironic theodolites s
42455.98Supplier#000000066UNITED KINGDOM805Manufacturer#5qYdruFJQJYYiKvnNVmYfCVydVB8bcW,AW,U6SOV333-300-836-9529ar requests. express orbits de
5-290.06Supplier#000000040RUSSIA493Manufacturer#4zyIeWzbbpkTV37vm1nmSGBxSgd2Kp32-231-247-6991 final patterns. accounts haggle idly pas

Shipping Priority Query (Q3)

[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 33]

This query retrieves the 10 unshipped orders with the highest value.

Business Question

The Shipping Priority Query retrieves the shipping priority and potential revenue, defined as the sum of 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.

Query Definition

select
    l_orderkey,
    sum(l_extendedprice*(1-l_discount)) as revenue,
    o_orderdate,
    o_shippriority
from
    customer,
    orders,
    lineitem
where
    c_mktsegment = '[SEGMENT]'
    and c_custkey = o_custkey
    and l_orderkey = o_orderkey
    and o_orderdate < date '[DATE]'
    and l_shipdate > date '[DATE]'
group by
    l_orderkey,
    o_orderdate,
    o_shippriority
order by
    revenue desc,
    o_orderdate
limit 10;

Substitution Parameters

  1. SEGMENT = BUILDING;
  2. DATE = 1995-03-15.
In [15]:
@query(
    order
    :filter(
        (customer.mktsegment == SEGMENT) &
        (orderdate < DATE))
    :select(
        id,
        revenue =>
            lineitem:filter(shipdate > DATE).(extendedprice*(1-discount)):sum,
        orderdate,
        shippriority)
    :sort(revenue:desc, orderdate)
    :take(10),
    SEGMENT="BUILDING", DATE=Date("1995-03-15"))
Out[15]:
idrevenueorderdateshippriority
16240245018.101995-01-280
25822234486.931995-03-140
39974231804.681995-03-060
44608197118.761995-03-130
54672182178.381995-03-060
67197179200.591995-02-110
7114173955.471995-03-050
811089172336.481995-02-040
91870170114.811995-02-210
107189165761.651995-03-010

Order Priority Checking Query (Q4)

[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 35]

This query determines how well the order priority system is working and gives an assessment of customer satisfaction.

Business Question

The Order Priority Checking Query counts the number of orders ordered in a given quarter of a given year in which at least one lineitem was received by the customer later than its committed date. The query lists the count of such orders for each order priority sorted in ascending priority order.

Query Definition

select
    o_orderpriority,
    count(*) as order_count
from
    orders
where
    o_orderdate >= date '[DATE]'
    and o_orderdate < date '[DATE]' + interval '3' month
    and exists (
        select
            *
        from
            lineitem
        where
            l_orderkey = o_orderkey
            and l_commitdate < l_receiptdate)
group by
    o_orderpriority
order by
    o_orderpriority;

Substitution Parameters

  1. DATE = 1993-07-01.
In [16]:
@query(
    order
    :filter(
        DATE <= orderdate < DATE + 3_months &&
        any(lineitem.(commitdate < receiptdate)))
    :group(orderpriority)
    :select(
        orderpriority,
        order_count => count(order)),
    DATE=Date("1993-07-01"))
Out[16]:
orderpriorityorder_count
11-URGENT78
22-HIGH80
33-MEDIUM89
44-NOT SPECIFIED85
55-LOW105

Local Supplier Volume Query (Q5)

[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 36]

This query lists the revenue volume done through local suppliers.

Business Question

The Local Supplier Volume Query lists for each nation in a region the revenue volume that resulted from lineitem transactions in which the customer ordering parts and the supplier filling them were both within that nation. The 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)).

Query Definition

select
    n_name,
    sum(l_extendedprice * (1 - l_discount)) as revenue
from
    customer,
    orders,
    lineitem,
    supplier,
    nation,
    region
where
    c_custkey = o_custkey
    and l_orderkey = o_orderkey
    and l_suppkey = s_suppkey
    and c_nationkey = s_nationkey
    and s_nationkey = n_nationkey
    and n_regionkey = r_regionkey
    and r_name = '[REGION]'
    and o_orderdate >= date '[DATE]'
    and o_orderdate < date '[DATE]' + interval '1' year
group by
    n_name
order by
    revenue desc;

Substitution Parameters

  1. REGION = ASIA;
  2. DATE = 1994-01-01.
In [17]:
@query(
    lineitem
    :filter(
        DATE <= order.orderdate < DATE + 1_year &&
        supplier.nation == order.customer.nation &&
        supplier.nation.region.name == REGION)
    :group(supplier.nation)
    :select(
        nation,
        revenue => sum(lineitem.(extendedprice*(1-discount))))
    :sort(revenue:desc),
    REGION="ASIA", DATE=Date("1994-01-01"))
Out[17]:
nationrevenue
1VIETNAM807082.63
2INDIA697063.03
3INDONESIA463882.68
4CHINA440134.00
5JAPAN237479.43

Forecasting Revenue Change Query (Q6)

[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 38]

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 for ways to increase revenues.

Business Question

The Forecasting Revenue Change Query considers all the lineitems shipped in a given year with discounts between DISCOUNT-0.01 and DISCOUNT+0.01. The query lists the amount by which the total revenue would have increased if these discounts had been eliminated for lineitems with l_quantity less than quantity. Note that the potential revenue increase is equal to the sum of [l_extendedprice * l_discount] for all lineitems with discounts and quantities in the qualifying range.

Query Definition

select
    sum(l_extendedprice*l_discount) as revenue
from
    lineitem
where
    l_shipdate >= date '[DATE]'
    and l_shipdate < date '[DATE]' + interval '1' year
    and l_discount between [DISCOUNT] - 0.01 and [DISCOUNT] + 0.01
    and l_quantity < [QUANTITY];

Substitution Parameters

  1. DATE = 1994-01-01;
  2. DISCOUNT = 0.06;
  3. QUANTITY = 24.
In [18]:
@query(
    lineitem
    :filter(
        DATE <= shipdate < DATE + 1_year &&
        DISCOUNT-0.011 <= discount <= DISCOUNT+0.011 &&
        quantity < QUANTITY)
    :group
    :select(
        revenue => sum(lineitem.(extendedprice*discount))),
    DATE=Date("1994-01-01"), DISCOUNT=0.06, QUANTITY=24)
Out[18]:
revenue
1905922.82

Volume Shipping Query (Q7)

[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 39]

This query determines the value of goods shipped between certain nations to help in the re-negotiation of shipping contracts.

Business Question

The Volume Shipping Query finds, for two given nations, the gross discounted revenues derived from lineitems in which parts were shipped from a supplier in either nation to a customer in the other nation during 1995 and 1996. 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).

Query Definition

select
    supp_nation,
    cust_nation,
    l_year,
    sum(volume) as revenue
from (
    select
        n1.n_name as supp_nation,
        n2.n_name as cust_nation,
        extract(year from l_shipdate) as l_year,
        l_extendedprice * (1 - l_discount) as volume
    from
        supplier,
        lineitem,
        orders,
        customer,
        nation n1,
        nation n2
    where
        s_suppkey = l_suppkey
        and o_orderkey = l_orderkey
        and c_custkey = o_custkey
        and s_nationkey = n1.n_nationkey
        and c_nationkey = n2.n_nationkey
        and (
            (n1.n_name = '[NATION1]' and n2.n_name = '[NATION2]')
            or (n1.n_name = '[NATION2]' and n2.n_name = '[NATION1]'))
        and l_shipdate between date '1995-01-01' and date '1996-12-31') as shipping
group by
    supp_nation,
    cust_nation,
    l_year
order by
    supp_nation,
    cust_nation,
    l_year;

Substitution Parameters

  1. NATION1 = FRANCE;
  2. NATION2 = GERMANY.
In [19]:
@query(
    lineitem
    :define(
        supp_nation => supplier.nation.name,
        cust_nation => order.customer.nation.name)
    :filter(
        date("1995-01-01") <= shipdate <= date("1996-12-31") && (
            (supp_nation == NATION1 && cust_nation == NATION2) ||
            (supp_nation == NATION2 && cust_nation == NATION1)))
    :group(
        supp_nation,
        cust_nation,
        year => year(shipdate))
    :select(
        supp_nation,
        cust_nation,
        year,
        revenue => sum(lineitem.(extendedprice*(1-discount)))),
    NATION1="FRANCE", NATION2="GERMANY")
Out[19]:
supp_nationcust_nationyearrevenue
1FRANCEGERMANY1995263047.87
2FRANCEGERMANY1996154119.14
3GERMANYFRANCE1995205237.67
4GERMANYFRANCE1996407967.21

National Market Share Query (Q8)

[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 41]

This query determines how the market share of a given nation within a given region has changed over two years for a given part type.

Business Question

The market share for a given nation within a given region is defined as the fraction of the revenue, the sum of [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.

Functional Query Definition

select
    o_year,
    sum(case
            when nation = '[NATION]'
            then volume
            else 0
        end) / sum(volume) as mkt_share
from (
    select
        extract(year from o_orderdate) as o_year,
        l_extendedprice * (1-l_discount) as volume,
        n2.n_name as nation
    from
        part,
        supplier,
        lineitem,
        orders,
        customer,
        nation n1,
        nation n2,
        region
    where
        p_partkey = l_partkey
        and s_suppkey = l_suppkey
        and l_orderkey = o_orderkey
        and o_custkey = c_custkey
        and c_nationkey = n1.n_nationkey
        and n1.n_regionkey = r_regionkey
        and r_name = '[REGION]'
        and s_nationkey = n2.n_nationkey
        and o_orderdate between date '1995-01-01' and date '1996-12-31'
        and p_type = '[TYPE]') as all_nations
group by
    o_year
order by
    o_year;

Substitution Parameters

  1. NATION = CANADA;
  2. REGION = AMERICA;
  3. TYPE = ECONOMY ANODIZED STEEL.
In [20]:
@query(
    lineitem
    :define(
        year => year(order.orderdate),
        supp_nation => supplier.nation.name,
        volume => extendedprice*(1-discount))
    :filter(
        part.type_ == TYPE &&
        order.customer.nation.region.name == REGION &&
        1995 <= year <= 1996)
    :group(year)
    :select(
        year,
        mkt_share =>
            sum(lineitem:filter(supp_nation == NATION).volume) / sum(lineitem.volume)),
    NATION="CANADA", REGION="AMERICA", TYPE="ECONOMY ANODIZED STEEL")
Out[20]:
yearmkt_share
119950.1379404076847995
219960.2615672520944461

Product Type Profit Measure Query (Q9)

[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 43]

This query determines how much profit is made on a given line of parts, broken out by supplier nation and year.

Business Question

The Product Type Profit Measure Query finds, for each nation and each year, the profit for all parts ordered in that 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).

Query Definition

select
    nation,
    o_year,
    sum(amount) as sum_profit
from (
    select
        n_name as nation,
        extract(year from o_orderdate) as o_year,
        l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
    from
        part,
        supplier,
        lineitem,
        partsupp,
        orders,
        nation
    where
        s_suppkey = l_suppkey
        and ps_suppkey = l_suppkey
        and ps_partkey = l_partkey
        and p_partkey = l_partkey
        and o_orderkey = l_orderkey
        and s_nationkey = n_nationkey
        and p_name like '%[COLOR]%') as profit
group by
    nation,
    o_year
order by
    nation,
    o_year desc;

Substitution Parameters

  1. COLOR = green.
In [21]:
@query(
    lineitem
    :define(
        amount =>
            extendedprice*(1-discount) - partsupp.supplycost*quantity)
    :filter(contains(part.name, COLOR))
    :group(
        nation => supplier.nation.name,
        year => year(order.orderdate))
    :sort(nation, year:desc)
    :select(
        nation,
        year,
        sum_profit => sum(lineitem.amount)),
    COLOR="green")
Out[21]:
nationyearsum_profit
1ALGERIA1998197990.07
2ALGERIA1997209363.95
3ALGERIA1996508610.09
4ALGERIA1995321224.39
5ALGERIA1994323614.10
6ALGERIA1993429217.36
7ALGERIA1992313931.42
8ARGENTINA1998207703.71
9ARGENTINA1997404879.36
10ARGENTINA1996277287.30

Returned Item Reporting Query (Q10)

[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 45]

The query identifies customers who might be having problems with the parts that are shipped to them.

Business Question

The Returned Item Reporting Query finds the top 20 customers, in terms of their effect on lost revenue for a given quarter, who have returned parts. The query considers only parts that were ordered in the specified quarter. The query lists the customer's name, address, nation, phone number, account balance, comment information and revenue lost. The customers are listed in descending order of lost revenue. Revenue lost is defined as sum(l_extendedprice*(1-l_discount)) for all qualifying lineitems.

Functional Query Definition

select
    c_custkey,
    c_name,
    sum(l_extendedprice * (1 - l_discount)) as revenue,
    c_acctbal,
    n_name,
    c_address,
    c_phone,
    c_comment
from
    customer,
    orders,
    lineitem,
    nation
where
    c_custkey = o_custkey
    and l_orderkey = o_orderkey
    and o_orderdate >= date '[DATE]'
    and o_orderdate < date '[DATE]' + interval '3' month
    and l_returnflag = 'R'
    and c_nationkey = n_nationkey
group by
    c_custkey,
    c_name,
    c_acctbal,
    c_phone,
    n_name,
    c_address,
    c_comment
order by
    revenue desc
limit 20;

Substitution Parameters

  1. DATE = 1993-10-01.
In [22]:
@query(
    customer
    :define(
        returned =>
            order:filter(DATE <= orderdate < DATE+3_months).lineitem:filter(returnflag == "R"))
    :select(
        id,
        name,
        revenue => sum(returned.(extendedprice*(1-discount))),
        acctbal,
        nation,
        address,
        phone,
        comment)
    :sort(revenue:desc)
    :take(20),
    DATE=Date("1993-10-01"))
Out[22]:
idnamerevenueacctbalnationaddressphonecomment
1544Customer#000000544391580.054974.68ETHIOPIAJv7vcm,oE,HEyxekXKia1V5H1up2315-572-651-1323bout the packages integrate above the regular instructions. regular ideas hinder s
21105Customer#000001105375872.309491.46RUSSIAcZhhOUzv6,Vbaa2bFT32-885-298-6750y final packages. furiously ironic packages was. fluffily ironic instructions integrate
3961Customer#000000961372764.616963.68JAPAN5,81YDLFuRR47KKzv8GXdmi3zyP37PlPn22-989-463-6089e final requests: busily final accounts believe a
4266Customer#000000266347106.765481.00ALGERIAVSIEruiMdDvjDaTQxkuK60Yw3AGxO10-474-243-3974ccounts. quickly ironic excuses after the regular foxes wake along the ironic, fina
5683Customer#000000683328973.739120.93FRANCEG0, q8c6vBykpiLvcuSJLYvqE16-566-251-5446 somas use-- slyly express foxes wake quickly blithely iro
6415Customer#000000415327104.672317.93UNITED KINGDOM334jCRiUb,gx333-346-876-2972egular deposits. blithely ironic inst
7746Customer#000000746311870.961164.46SAUDI ARABIAJOKj8N2QKUm8Gi,F4qX0fLVy30-154-354-9928 final theodolites. final pinto beans haggle-- furiously
8421Customer#000000421311164.577073.17JORDANit3mUlkZAe9J8gmy23-918-228-2560lithely final deposits haggle furiously above the
9643Customer#000000643281356.655184.70ALGERIA9T 2avhfyF PQ10-978-597-2747fily along the quickly ironic ideas. final, final
101051Customer#000001051275867.469776.39BRAZILiHS,UFudVOOe12-869-221-1428cuses boost furiously silent deposits. quickly silent requests integrate quickly bold asymptotes; slyly regular ide

Important Stock Identification Query (Q11)

[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 47]

This query finds the most important subset of suppliers' stock in a given nation.

Business Question

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.

Query Definition

select
    ps_partkey,
    sum(ps_supplycost * ps_availqty) as value
from
    partsupp,
    supplier,
    nation
where
    ps_suppkey = s_suppkey
    and s_nationkey = n_nationkey
    and n_name = '[NATION]'
group by
    ps_partkey
having
    sum(ps_supplycost * ps_availqty) > (
        select
            sum(ps_supplycost * ps_availqty) * [FRACTION]
        from
            partsupp,
            supplier,
            nation
        where
            ps_suppkey = s_suppkey
            and s_nationkey = n_nationkey
            and n_name = '[NATION]')
order by
    value desc;

Substitution Parameters

  1. NATION = GERMANY;
  2. FRACTION = 0.0001.
In [23]:
@query(
    partsupp
    :filter(supplier.nation.name == NATION)
    :group(part)
    :define(value => sum(partsupp.(supplycost * availqty)))
    :filter(value > sum(and_around.value)*FRACTION)
    :select(part.id, value)
    :sort(value:desc),
    NATION="GERMANY", FRACTION=0.0001)
Out[23]:
idvalue
1108313092535.78
2135611542206.53
37169945808.42
44489941036.40
51119382317.55
614549081368.05
712188999420.40
83248901344.93
913128834080.10
1014728609013.30

Shipping Modes and Order Priority Query (Q12)

[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 49]

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.

Business Question

The Shipping Modes and Order Priority Query counts, by ship mode, for lineitems actually received by customers in 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 priority other than URGENT or HIGH.

Query Definition

select
    l_shipmode,
    sum(case
            when o_orderpriority ='1-URGENT' or o_orderpriority ='2-HIGH'
            then 1
            else 0
        end) as high_line_count,
    sum(case
            when o_orderpriority <> '1-URGENT' and o_orderpriority <> '2-HIGH'
            then 1
            else 0
        end) as low_line_count
from
    orders,
    lineitem
where
    o_orderkey = l_orderkey
    and l_shipmode in ('[SHIPMODE1]', '[SHIPMODE2]')
    and l_commitdate < l_receiptdate
    and l_shipdate < l_commitdate
    and l_receiptdate >= date '[DATE]'
    and l_receiptdate < date '[DATE]' + interval '1' year
group by
    l_shipmode
order by
    l_shipmode;

Substitution Parameters

  1. SHIPMODE1 = MAIL;
  2. SHIPMODE2 = SHIP;
  3. DATE = 1994-01-01.
In [24]:
@query(
    lineitem
    :define(
        high => order.orderpriority in ["1-URGENT", "2-HIGH"])
    :filter(
        shipmode in SHIPMODES &&
        shipdate < commitdate < receiptdate &&
        DATE <= receiptdate < DATE + 1_year)
    :group(shipmode)
    :select(
        shipmode,
        high_line_count => count(lineitem:filter(high)),
        low_line_count => count(lineitem:filter(!high))),
    SHIPMODES=["MAIL", "SHIP"], DATE=Date("1994-01-01"))
Out[24]:
shipmodehigh_line_countlow_line_count
1MAIL4567
2SHIP4569

Customer Distribution Query (Q13)

[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 51]

This query seeks relationships between customers and the size of their orders.

Business Question

This query determines the distribution of customers by the number of orders they have made, including customers who have no record of orders, past or present. It counts and reports how many customers have no orders, how many 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.

Query Definition

select
    c_count,
    count(*) as custdist
from (
    select
        c_custkey,
        count(o_orderkey)
    from
        customer left outer join orders
            on c_custkey = o_custkey and o_comment not like '%[WORD1]%[WORD2]%'
    group by
        c_custkey) as c_orders (c_custkey, c_count)
group by
    c_count
order by
    custdist desc,
    c_count desc;

Substitution Parameters

  1. WORD1 = special.
  2. WORD2 = requests.
In [25]:
@query(
    customer
    :group(
        c_count => count(order:filter(!contains(comment, WORDS))))
    :select(
        c_count,
        custdist => count(customer))
    :sort(
        custdist:desc,
        c_count:desc),
    WORDS=r".*special.*requests.*")
Out[25]:
c_countcustdist
10400
21157
31255
41053
5851
6948
71743
81339
92237
102137

Promotion Effect Query (Q14)

[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 52]

This query monitors the market response to a promotion such as TV advertisements or a special campaign.

Business Question

The Promotion Effect Query determines what percentage of the revenue in a given year and month was derived from promotional parts. The query considers only parts actually shipped in that month and gives the percentage. Revenue is defined as (l_extendedprice * (1-l_discount)).

Query Definition

select
    100.00 * sum(
        case
            when p_type like 'PROMO%'
            then l_extendedprice*(1-l_discount)
            else 0
        end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue
from
    lineitem,
    part
where
    l_partkey = p_partkey
    and l_shipdate >= date '[DATE]'
    and l_shipdate < date '[DATE]' + interval '1' month;

Substitution Parameters

  1. DATE = 1995-09-01.
In [26]:
@query(
    lineitem
    :define(
        volume => extendedprice*(1-discount),
        promo => startswith(part.type_, "PROMO"))
    :filter(DATE <= shipdate < DATE + 1_month)
    :group
    :select(
        promo_revenue =>
            100*sum(lineitem:filter(promo).volume)/sum(lineitem.volume)),
    DATE=Date("1995-09-01"))
Out[26]:
promo_revenue
112.409568669150199

Top Supplier Query (Q15)

[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 53]

This query determines the top supplier so it can be rewarded, given more business, or identified for special recognition.

Business Question

The Top Supplier Query finds the supplier who contributed the most to the overall revenue for parts shipped during a given quarter of a given year. In case of a tie, the query lists all suppliers whose contribution was equal to the maximum, presented in supplier number order.

Query Definition

create view revenue (supplier_no, total_revenue) as
    select
        l_suppkey,
        sum(l_extendedprice * (1 - l_discount))
    from
        lineitem
    where
        l_shipdate >= date '[DATE]'
        and l_shipdate < date '[DATE]' + interval '3' month
    group by
        l_suppkey;

select
    s_suppkey,
    s_name,
    s_address,
    s_phone,
    total_revenue
from
    supplier,
    revenue
where
    s_suppkey = supplier_no
    and total_revenue = (
        select
            max(total_revenue)
        from
            revenue)
order by
    s_suppkey;

drop view revenue;

Substitution Parameters

  1. DATE = 1996-01-01.
In [27]:
@query(
    supplier
    :define(
        total_revenue =>
            sum(lineitem:filter(DATE <= shipdate < DATE+3_months).(extendedprice*(1-discount))))
    :first(total_revenue)
    :select(
        id,
        name,
        address,
        phone,
        total_revenue),
    DATE=Date("1996-01-01"))
Out[27]:
idnameaddressphonetotal_revenue
117Supplier#000000017c2d,ESHRSkK3WYnxpgw6aOqN0q29-601-884-92191113801.53

Parts/Supplier Relationship Query (Q16)

[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 55]

This query finds out how many suppliers can supply parts with given attributes. It might be used, for example, to determine whether there is a sufficient number of suppliers for heavily ordered parts.

Business Question

The Parts/Supplier Relationship Query counts the number of suppliers who can supply parts that satisfy a particular 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.

Query Definition

select
    p_brand,
    p_type,
    p_size,
    count(distinct ps_suppkey) as supplier_cnt
from
    partsupp,
    part
where
    p_partkey = ps_partkey
    and p_brand <> '[BRAND]'
    and p_type not like '[TYPE]%'
    and p_size in ([SIZE1], [SIZE2], [SIZE3], [SIZE4], [SIZE5], [SIZE6], [SIZE7], [SIZE8])
    and ps_suppkey not in (
        select
            s_suppkey
        from
            supplier
        where
            s_comment like '%Customer%Complaints%')
group by
    p_brand,
    p_type,
    p_size
order by
    supplier_cnt desc,
    p_brand,
    p_type,
    p_size;

Substitution Parameters

  1. BRAND = Brand#45.
  2. TYPE = MEDIUM POLISHED.
  3. SIZE1 = 49
  4. SIZE2 = 14
  5. SIZE3 = 23
  6. SIZE4 = 45
  7. SIZE5 = 19
  8. SIZE6 = 3
  9. SIZE7 = 36
  10. SIZE8 = 9.
In [28]:
@query(
    partsupp
    :filter(
        part.brand != BRAND &&
        !startswith(part.type_, TYPE) &&
        part.size in SIZES &&
        !contains(supplier.comment, r".*Customer.*Complaints.*"))
    :group(
        part.brand,
        part.type_,
        part.size)
    :select(
        brand,
        type_,
        size,
        supplier_cnt => count(unique(partsupp.supplier)))
    :sort(supplier_cnt:desc),
    BRAND="Brand#45", TYPE="MEDIUM POLISHED", SIZES=[49, 14, 23, 45, 19, 3, 36, 9])
Out[28]:
brandtype_sizesupplier_cnt
1Brand#14PROMO BRUSHED STEEL98
2Brand#35SMALL POLISHED COPPER148
3Brand#11LARGE PLATED TIN234
4Brand#11MEDIUM ANODIZED BRASS454
5Brand#11MEDIUM BRUSHED BRASS454
6Brand#11PROMO ANODIZED BRASS494
7Brand#11PROMO ANODIZED TIN454
8Brand#11PROMO BURNISHED BRASS364
9Brand#11SMALL ANODIZED TIN454
10Brand#11SMALL PLATED COPPER454

Small-Quantity-Order Revenue Query (Q17)

[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 57]

This query determines how much average yearly revenue would be lost if orders were no longer filled for small quantities of certain parts. This may reduce overhead expenses by concentrating sales on larger shipments.

Business Question

The Small-Quantity-Order Revenue Query considers parts of a given brand and with a given container type and 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?

Functional Query Definition

select
    sum(l_extendedprice) / 7.0 as avg_yearly
from
    lineitem,
    part
where
    p_partkey = l_partkey
    and p_brand = '[BRAND]'
    and p_container = '[CONTAINER]'
    and l_quantity < (
        select
            0.2 * avg(l_quantity)
        from
            lineitem
        where
            l_partkey = p_partkey);

Substitution Parameters

  1. BRAND = Brand#23;
  2. CONTAINER = MED JAR.
In [29]:
@query(
    lineitem
    :filter(part.brand == BRAND && part.container == CONTAINER)
    :filter(quantity < 0.2 * mean(and_around(part).quantity))
    :group
    :select(
        avg_yearly => sum(lineitem.extendedprice)/7),
    BRAND="Brand#23", CONTAINER="MED JAR")
Out[29]:
avg_yearly
11217.62

Large Volume Customer Query (Q18)

[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 58]

The Large Volume Customer Query ranks customers based on their having placed a large quantity order. Large quantity orders are defined as those orders whose total quantity is above a certain level.

Business Question

The Large Volume Customer Query finds a list of the top 100 customers who have ever placed large quantity orders. The query lists the customer name, customer key, the order key, date and total price and the quantity for the order.

Functional Query Definition

select
    c_name,
    c_custkey,
    o_orderkey,
    o_orderdate,
    o_totalprice,
    sum(l_quantity)
from
    customer,
    orders,
    lineitem
where
    o_orderkey in (
        select
            l_orderkey
        from
            lineitem
        group by
            l_orderkey
        having
            sum(l_quantity) > [QUANTITY])
    and c_custkey = o_custkey
    and o_orderkey = l_orderkey
group by
    c_name,
    c_custkey,
    o_orderkey,
    o_orderdate,
    o_totalprice
order by
    o_totalprice desc,
    o_orderdate
limit 100;

Substitution Parameters

  1. QUANTITY = 300
In [30]:
@query(
    order
    :select(
        customer.name,
        custkey => customer.id,
        orderkey => id,
        orderdate,
        totalprice,
        quantity => sum(lineitem.quantity))
    :filter(quantity > QUANTITY)
    :sort(
        totalprice:desc,
        orderdate)
    :take(100),
    QUANTITY=300)
Out[30]:
namecustkeyorderkeyorderdatetotalpricequantity
1Customer#00000014214217221997-04-09423609.13303
2Customer#00000053353372941995-10-21372230.14305

Discounted Revenue Query (Q19)

[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 60]

The Discounted Revenue Query reports the gross discounted revenue attributed to the sale of selected parts handled in a particular manner. This query is an example of code such as might be produced programmatically by a data mining tool.

Business Question

The Discounted Revenue query finds the gross discounted revenue for all orders for three different types of parts that were shipped by air and delivered in person. Parts are selected based on the combination of specific brands, a list of containers, and a range of sizes.

Query Definition

select
    sum(l_extendedprice * (1 - l_discount) ) as revenue
from
    lineitem,
    part
where
    (
        p_partkey = l_partkey
        and p_brand = '[BRAND1]'
        and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
        and l_quantity >= [QUANTITY1] and l_quantity <= [QUANTITY1] + 10
        and p_size between 1 and 5
        and l_shipmode in ('AIR', 'AIR REG')
        and l_shipinstruct = 'DELIVER IN PERSON'
    )
    or
    (
        p_partkey = l_partkey
        and p_brand = '[BRAND2]'
        and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
        and l_quantity >= [QUANTITY2] and l_quantity <= [QUANTITY2] + 10
        and p_size between 1 and 10
        and l_shipmode in ('AIR', 'AIR REG')
        and l_shipinstruct = 'DELIVER IN PERSON'
    )
    or
    (
        p_partkey = l_partkey
        and p_brand = '[BRAND3]'
        and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
        and l_quantity >= [QUANTITY3] and l_quantity <= [QUANTITY3] + 10
        and p_size between 1 and 15
        and l_shipmode in ('AIR', 'AIR REG')
        and l_shipinstruct = 'DELIVER IN PERSON'
    );

Substitution Parameters

  1. QUANTITY1 = 1.
  2. QUANTITY2 = 10.
  3. QUANTITY3 = 20.
  4. BRAND1 = Brand#53.
  5. BRAND2 = Brand#35.
  6. BRAND3 = Brand#32.
In [31]:
@query(
    lineitem
    :filter(
        (
            part.brand == BRAND1 &&
            part.container in ["SM CASE", "SM BOX", "SM PACK", "SM PKG"] &&
            QUANTITY1 <= quantity <= QUANTITY1+10 &&
            1 <= part.size <= 5 &&
            shipmode in ["AIR", "AIR REG"] &&
            shipinstruct == "DELIVER IN PERSON"
        )
        ||
        (
            part.brand == BRAND2 && 
            part.container in ["MED BAG", "MED BOX", "MED PKG", "MED PACK"] &&
            QUANTITY2 <= quantity <= QUANTITY2+10 &&
            1 <= part.size <= 10 &&
            shipmode in ["AIR", "AIR REG"] &&
            shipinstruct == "DELIVER IN PERSON"
        )
        ||
        (
            part.brand == BRAND3 &&
            part.container in ["LG CASE", "LG BOX", "LG PACK", "LG PKG"] &&
            QUANTITY3 <= quantity <= QUANTITY3+10 &&
            1 <= part.size <= 15 &&
            shipmode in ["AIR", "AIR REG"] &&
            shipinstruct == "DELIVER IN PERSON"
        ))
    :group
    :select(
        revenue => sum(lineitem.(extendedprice*(1-discount)))),
    QUANTITY1=1, QUANTITY2=10, QUANTITY3=20,
    BRAND1="Brand#53", BRAND2="Brand#35", BRAND3="Brand#32")
Out[31]:
revenue
140908.53

Potential Part Promotion Query (Q20)

[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 62]

The Potential Part Promotion Query identifies suppliers in a particular nation having selected parts that may be candidates for a promotional offer.

Business Question

The Potential Part Promotion query identifies suppliers who have an excess of a given part available; an excess is defined to be more than 50% of the parts like the given part that the supplier shipped in a given year for a given nation. Only parts whose names share a certain naming convention are considered.

Query Definition

select
    s_name,
    s_address
from
    supplier,
    nation
where
    s_suppkey in (
        select
            ps_suppkey
        from
            partsupp
        where
            ps_partkey in (
                select
                    p_partkey
                from
                    part
                where
                    p_name like '[COLOR]%')
            and ps_availqty > (
                select
                    0.5 * sum(l_quantity)
                from
                    lineitem
                where
                    l_partkey = ps_partkey
                    and l_suppkey = ps_suppkey
                    and l_shipdate >= date('[DATE]')
                    and l_shipdate < date('[DATE]') + interval '1' year))
    and s_nationkey = n_nationkey
    and n_name = '[NATION]'
order by
    s_name;

Substitution Parameters

  1. COLOR = forest.
  2. DATE = 1994-01-01.
  3. NATION = CANADA.
In [32]:
@query(
    partsupp
    :define(
        expectedqty =>
            sum(lineitem:filter(DATE <= shipdate < DATE + 1_year).quantity))
    :filter(
        supplier.nation.name == NATION &&
        startswith(part.name, COLOR) &&
        availqty > 0.5*expectedqty)
    :group(supplier)
    :select(
        supplier.name,
        supplier.address)
    :sort(name),
    COLOR="forest", DATE=Date("1994-01-01"), NATION="CANADA")
Out[32]:
nameaddress
1Supplier#000000020iybAE,RmTymrZVYaFZva2SH,j

Suppliers Who Kept Orders Waiting Query (Q21)

[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 64]

This query identifies certain suppliers who were not able to ship required parts in a timely manner.

Business Question

The Suppliers Who Kept Orders Waiting query identifies suppliers, for a given nation, whose product was part of a multi-supplier order (with current status of 'F') where they were the only supplier who failed to meet the committed delivery date.

Query Definition

select
    s_name,
    count(*) as numwait
from
    supplier,
    lineitem l1,
    orders,
    nation
where
    s_suppkey = l1.l_suppkey
    and o_orderkey = l1.l_orderkey
    and o_orderstatus = 'F'
    and l1.l_receiptdate > l1.l_commitdate
    and exists (
        select
            *
        from
            lineitem l2
        where
            l2.l_orderkey = l1.l_orderkey
            and l2.l_suppkey <> l1.l_suppkey)
    and not exists (
        select
            *
        from
            lineitem l3
        where
            l3.l_orderkey = l1.l_orderkey
            and l3.l_suppkey <> l1.l_suppkey
            and l3.l_receiptdate > l3.l_commitdate)
    and s_nationkey = n_nationkey
    and n_name = '[NATION]'
group by
    s_name
order by
    numwait desc,
    s_name
limit 100;

Substitution Parameters

  1. NATION = SAUDI ARABIA.
In [33]:
@query(
    lineitem
    :filter((
        order.orderstatus == "F" &&
        supplier.nation.name == NATION &&
        receiptdate > commitdate &&
        any(order.lineitem.supplier != SUPP) &&
        !any(order.lineitem.(supplier != SUPP && receiptdate > commitdate)))
        :given(SUPP => supplier))
    :group(supplier)
    :select(
        supplier.name,
        numwait => count(lineitem))
    :sort(
        numwait:desc,
        name)
    :take(100),
    NATION="SAUDI ARABIA")
Out[33]:
namenumwait
1Supplier#00000007410

Global Sales Opportunity Query (Q22)

[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 66]

The Global Sales Opportunity Query identifies geographies where there are customers who may be likely to make a purchase.

Business Question

This query counts how many customers within a specific range of country codes have not placed orders for 7 years but who have a greater than average “positive” account balance. It also reflects the magnitude of that balance. Country code is defined as the first two characters of c_phone.

Query Definition

select
    cntrycode,
    count(*) as numcust,
    sum(c_acctbal) as totacctbal
from (
    select
        substring(c_phone from 1 for 2) as cntrycode,
        c_acctbal
    from
        customer
    where
        substring(c_phone from 1 for 2) in
            ('[I1]','[I2]','[I3]','[I4]','[I5]','[I6]','[I7]')
        and c_acctbal > (
            select
                avg(c_acctbal)
            from
                customer
            where
                c_acctbal > 0.00
                and substring (c_phone from 1 for 2) in
                    ('[I1]','[I2]','[I3]','[I4]','[I5]','[I6]','[I7]'))
        and not exists (
            select
                *
            from
                orders
            where
                o_custkey = c_custkey)) as custsale
group by
    cntrycode
order by
    cntrycode;

Substitution Parameters

  1. I1 = 13.
  2. I2 = 31.
  3. I3 = 23.
  4. I4 = 29.
  5. I5 = 30.
  6. I6 = 18.
  7. I7 = 17.
In [34]:
@query(
    customer
    :define(
        cntrycode => substr(phone, 1, 2))
    :filter(
        cntrycode in IS &&
        acctbal > 0_usd)
    :filter(
        !exists(order) &&
        acctbal > mean(and_around.acctbal))
    :group(cntrycode)
    :select(
        cntrycode,
        numcust => count(customer),
        totacctbal => sum(customer.acctbal)),
    IS=["13", "31", "23", "29", "30", "18", "17"])
Out[34]:
cntrycodenumcusttotacctbal
113859306.91
217649853.61
3181189404.25
423325939.81
5291188722.85
6301392703.39
731969434.60