TPC-H Benchmark

            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 tpc.lineitem 
WHERE l_shipdate <= date '1998-12-01' - INTERVAL '90' day  
GROUP BY l_returnflag, l_linestatus  
ORDER BY l_returnflag, l_linestatus
        

            SELECT s_acctbal,  
s_name,  
n_name,  
p_partkey,  
p_mfgr,  
s_address,  
s_phone,  
s_comment  
FROM tpc.part
INNER JOIN tpc.partsupp ON p_partkey = ps_partkey
INNER JOIN tpc.supplier ON s_suppkey = ps_suppkey
INNER JOIN tpc.nation ON s_nationkey = n_nationkey
INNER JOIN tpc.region ON n_regionkey = r_regionkey
WHERE p_size = 15  
AND p_type like '%BRASS'  
AND r_name = 'EUROPE'  
AND ps_supplycost = (
  SELECT  min(ps_supplycost)  
  FROM tpc.partsupp
  INNER JOIN tpc.supplier ON s_suppkey = ps_suppkey
  INNER JOIN tpc.nation ON s_nationkey = n_nationkey
  INNER JOIN tpc.region ON n_regionkey = r_regionkey 
  WHERE p_partkey = ps_partkey  
  AND r_name = 'EUROPE'  
)  
ORDER BY s_acctbal DESC, n_name, s_name, p_partkey
        

            SELECT l_orderkey,  
SUM(l_extendedprice*(1-l_discount)) AS revenue,  
o_orderdate, 
o_shippriority  
FROM tpc.customer 
INNER JOIN tpc.`order` ON c_custkey = o_custkey 
INNER JOIN tpc.lineitem ON l_orderkey = o_orderkey 
WHERE c_mktsegment = 'BUILDING'  
AND o_orderdate < date '1995-03-15'  
AND l_shipdate > date '1995-03-15'  
GROUP BY l_orderkey, o_orderdate, o_shippriority  
ORDER BY revenue DESC, o_orderdate 
        

            SELECT o_orderpriority,  
COUNT(*) as order_count  
FROM tpc.`order`  
WHERE o_orderdate >= date '1993-07-01'  
AND o_orderdate < date '1993-07-01' + interval '3' month  
AND exists (select * 
from tpc.lineitem 
WHERE l_orderkey = o_orderkey 
AND l_commitdate < l_receiptdate)  
GROUP BY o_orderpriority  
ORDER BY o_orderpriority
        

            SELECT n_name,  
SUM(l_extendedprice * (1 - l_discount)) AS revenue  
FROM tpc.customer 
INNER JOIN tpc.`order` ON c_custkey = o_custkey
INNER JOIN tpc.lineitem ON l_orderkey = o_orderkey
INNER JOIN tpc.supplier ON l_suppkey = s_suppkey AND c_nationkey = s_nationkey
INNER JOIN tpc.nation ON s_nationkey = n_nationkey
INNER JOIN tpc.region ON n_regionkey = r_regionkey
WHERE r_name = 'ASIA'  
AND o_orderdate >= date '1994-01-01'  
AND o_orderdate < date '1994-01-01' + INTERVAL '1' year  
GROUP BY n_name 
ORDER BY revenue DESC
        

            SELECT SUM(l_extendedprice*l_discount) AS revenue  
FROM tpc.lineitem  
WHERE l_shipdate >= date '1994-01-01'  
AND l_shipdate < date '1994-01-01' + INTERVAL '1' year  
AND l_discount BETWEEN 0.06 - 0.01 AND 0.06 + 0.01  
AND l_quantity < 24
        

            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 tpc.supplier  
INNER JOIN tpc.lineitem ON s_suppkey = l_suppkey
INNER JOIN tpc.`order` ON o_orderkey = l_orderkey
INNER JOIN tpc.customer ON c_custkey = o_custkey
INNER JOIN tpc.nation n1 ON  s_nationkey = n1.n_nationkey
INNER JOIN tpc.nation n2 ON  c_nationkey = n2.n_nationkey
WHERE ((n1.n_name = 'FRANCE' AND n2.n_name = 'GERMANY')  
OR (n1.n_name = 'GERMANY' AND n2.n_name = 'FRANCE'))  
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
        

            SELECT o_year,  
SUM(CASE WHEN nation = 'BRAZIL' 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 tpc.part
  INNER JOIN tpc.lineitem ON p_partkey = l_partkey
  INNER JOIN tpc.supplier ON s_suppkey = l_suppkey
  INNER JOIN tpc.`order` ON l_orderkey = o_orderkey
  INNER JOIN tpc.customer ON o_custkey = c_custkey
  INNER JOIN tpc.nation n1 ON c_nationkey = n1.n_nationkey
  INNER JOIN tpc.nation n2 ON s_nationkey = n2.n_nationkey
  INNER JOIN tpc.region ON n1.n_regionkey = r_regionkey 
  WHERE r_name = 'AMERICA'  
  AND o_orderdate between date '1995-01-01' AND date '1996-12-31' 
  AND p_type = 'ECONOMY ANODIZED STEEL') AS all_nations  
GROUP BY o_year  
ORDER BY o_year
        

            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 tpc.part 
INNER JOIN tpc.lineitem ON p_partkey = l_partkey
INNER JOIN tpc.supplier ON s_suppkey = l_suppkey 
INNER JOIN tpc.partsupp ON ps_suppkey = l_suppkey and ps_partkey = l_partkey
INNER JOIN tpc.`order` ON o_orderkey = l_orderkey
INNER JOIN tpc.nation ON s_nationkey = n_nationkey 
WHERE p_name like '%green%') AS profit  
GROUP BY nation,  o_year  
ORDER BY nation,  o_year DESC
        

            SELECT c_custkey,  
c_name,  
SUM(l_extendedprice * (1 - l_discount)) AS revenue,  
c_acctbal,  
n_name,  
c_address,  
c_phone,  
c_comment  
FROM tpc.customer  
INNER JOIN tpc.`order` ON c_custkey = o_custkey
INNER JOIN tpc.lineitem ON l_orderkey = o_orderkey
INNER JOIN tpc.nation ON c_nationkey = n_nationkey
WHERE o_orderdate >= date '1993-10-01'  
AND o_orderdate < date '1993-10-01' + INTERVAL '3' month  
AND l_returnflag = 'R'  
GROUP BY c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment  
ORDER BY revenue DESC 
        

            SELECT ps_partkey, 
SUM(ps_supplycost*ps_availqty) AS `value`  
FROM tpc.partsupp
INNER JOIN tpc.supplier ON ps_suppkey = s_suppkey
INNER JOIN tpc.nation ON s_nationkey = n_nationkey 
WHERE n_name  = 'SAUDI ARABIA'  
GROUP BY ps_partkey  
HAVING SUM(ps_supplycost*ps_availqty) > ( 
  SELECT SUM(ps_supplycost*ps_availqty) * 0.0000000333     
  FROM tpc.partsupp,
  tpc.supplier,
  tpc.nation
  WHERE ps_suppkey = s_suppkey
  AND s_nationkey = n_nationkey 
  AND n_name  = 'SAUDI ARABIA'
)  
ORDER BY `value` DESC
        

            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 tpc.`order`  
INNER JOIN tpc.lineitem ON o_orderkey = l_orderkey
WHERE l_shipmode IN ('MAIL', 'SHIP')  
AND l_commitdate < l_receiptdate  
AND l_shipdate < l_commitdate  
AND l_receiptdate >= date '1994-01-01'  
AND l_receiptdate < date '1994-01-01' + INTERVAL '1' year  
GROUP BY l_shipmode  
ORDER BY l_shipmode
        

            SELECT c_count, 
COUNT(*) as custdist  
FROM (  
  SELECT c_custkey,  
  COUNT(o_orderkey)  
  FROM tpc.customer 
  LEFT OUTER JOIN tpc.`order` ON c_custkey = o_custkey AND o_comment NOT LIKE '%special%requests%'  
  GROUP BY c_custkey  
) AS c_orders (c_custkey, c_count)  
GROUP BY c_count  
ORDER BY custdist DESC, c_count DESC
        

            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 tpc.lineitem  
INNER JOIN tpc.part ON l_partkey = p_partkey 
WHERE l_shipdate >= date '1995-09-01'  
AND l_shipdate < date '1995-09-01' + INTERVAL '1' month
        

            SELECT s_suppkey,  
s_name,  
s_address,  
s_phone,  
total_revenue  
FROM tpc.supplier  
INNER JOIN (
  SELECT l_suppkey as supplier_no,  sum(l_extendedprice * (1 - l_discount)) AS total_revenue  
  FROM tpc.lineitem  
  WHERE l_shipdate >= date '1996-01-01'  
  AND l_shipdate < date '1996-01-01' + INTERVAL '3' month  
  GROUP BY l_suppkey
) AS revenue ON s_suppkey = supplier_no  
WHERE total_revenue IN (
  SELECT  MAX(total_revenue)  
  FROM (
    SELECT l_suppkey as supplier_no,  
    SUM(l_extendedprice * (1 - l_discount)) AS total_revenue  
    FROM tpc.lineitem  
    WHERE l_shipdate >= date '1996-01-01'  
    AND l_shipdate < date '1996-01-01' + INTERVAL '3' month  
    GROUP BY l_suppkey
  ) AS revenue
)  
order by s_suppkey
        

            SELECT p_brand,  
p_type,  
p_size,  
COUNT(distinct ps_suppkey) AS supplier_cnt  
FROM tpc.partsupp
INNER JOIN tpc.part ON p_partkey = ps_partkey
WHERE p_brand <> 'Brand#45' AND p_type NOT LIKE 'MEDIUM POLISHED%'  
AND p_size IN (49, 14, 23, 45, 19, 3, 36, 9)  
AND ps_suppkey NOT IN (SELECT s_suppkey FROM tpc.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
        

            SELECT SUM(`l_extendedprice`) / 7.0 AS avg_yearly  
FROM tpc.lineitem
INNER JOIN tpc.part ON `p_partkey` = `l_partkey`
WHERE `p_brand` = 'Brand#23'  
AND `p_container` = 'MED BOX'  
AND `l_quantity` < (
  SELECT  0.2 * AVG(`l_quantity`)  
  FROM tpc.lineitem  
  WHERE `l_partkey` = `p_partkey`
)
        

            SELECT c_name,  
c_custkey,  
o_orderkey,  
o_orderdate,  
o_totalprice,  
SUM(l_quantity) AS quantity 
FROM tpc.customer  
INNER JOIN tpc.`order` ON c_custkey = o_custkey
INNER JOIN tpc.lineitem ON o_orderkey = l_orderkey  
WHERE o_orderkey IN (SELECT l_orderkey FROM tpc.lineitem GROUP BY l_orderkey HAVING sum(l_quantity) > 300) 
GROUP BY c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice  
ORDER BY o_totalprice DESC, o_orderdate
        

            SELECT SUM(l_extendedprice * (1 - l_discount)) AS revenue  
FROM tpc.lineitem
INNER JOIN tpc.part ON p_partkey = l_partkey
WHERE (p_brand = 'Brand#12'  
AND p_container IN ( 'SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')  
AND l_quantity >= 1 AND l_quantity <= 1 + 10  
AND p_size BETWEEN 1 AND 5  
AND l_shipmode IN ('AIR', 'AIR REG')  
AND l_shipinstruct = 'DELIVER IN PERSON')  
OR  (p_brand = 'Brand#23'  
AND p_container IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')  
AND l_quantity >= 10 AND l_quantity <= 10 + 10  
AND p_size BETWEEN 1 AND 10  
AND l_shipmode IN ('AIR', 'AIR REG')  
AND l_shipinstruct = 'DELIVER IN PERSON')  
OR  (p_brand = 'Brand#34'  
AND p_container IN ( 'LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')  
AND l_quantity >= 20 AND l_quantity <= 20 + 10  
AND p_size BETWEEN 1 AND 15  
AND l_shipmode IN ('AIR', 'AIR REG')  
AND l_shipinstruct = 'DELIVER IN PERSON')
        

            SELECT s_name,  
s_address  
FROM tpc.supplier 
INNER JOIN tpc.nation ON s_nationkey = n_nationkey
WHERE s_suppkey in (
  SELECT ps_suppkey  
  FROM tpc.partsupp  
  WHERE ps_partkey in (
    SELECT p_partkey  
    FROM tpc.part  
    WHERE p_name like 'forest%')  
    AND ps_availqty > (
      SELECT 0.5 * sum(l_quantity)  
      FROM tpc.lineitem  
      WHERE l_partkey = ps_partkey  
      AND l_suppkey = ps_suppkey  
      AND l_shipdate >= date('1994-01-01')  
      AND l_shipdate < date('1994-01-01') + INTERVAL '1' year))  
AND n_name = 'CANADA'  
ORDER BY s_name
        

            SELECT s_name,  
COUNT(*) AS numwait  
FROM tpc.supplier  
INNER JOIN tpc.lineitem l1 ON s_suppkey = l1.l_suppkey
INNER JOIN tpc.`order` ON o_orderkey = l1.l_orderkey
INNER JOIN tpc.nation ON s_nationkey = n_nationkey
WHERE o_orderstatus = 'F'  
AND l1.l_receiptdate > l1.l_commitdate  
AND EXISTS (
  SELECT  *  
  FROM tpc.lineitem l2  
  WHERE l2.l_orderkey = l1.l_orderkey  
  AND l2.l_suppkey <> l1.l_suppkey
)  
AND NOT EXISTS (
  SELECT *  
  FROM tpc.lineitem l3  
  WHERE l3.l_orderkey = l1.l_orderkey  
  AND l3.l_suppkey <> l1.l_suppkey  
  AND l3.l_receiptdate > l3.l_commitdate
)  
AND n_name = 'SAUDI ARABIA'  
GROUP BY s_name  
ORDER BY numwait DESC, s_name
        

            SELECT cntrycode,  
COUNT(*) AS numcust,  
SUM(c_acctbal) AS totacctbal  
FROM (
  SELECT SUBSTRING(c_phone FROM 1 for 2) AS cntrycode,  
  c_acctbal  
  FROM tpc.customer  
  WHERE SUBSTRING(c_phone FROM 1 for 2) IN ('13','31','23','29','30','18','17')  
  AND c_acctbal > (  
    SELECT AVG(c_acctbal)  
    FROM tpc.customer  
    WHERE c_acctbal > 0.00  
    AND SUBSTRING (c_phone FROM 1 for 2) IN ('13','31','23','29','30','18','17'))  
  AND NOT exists (
    SELECT * 
    FROM tpc.`order` 
    WHERE o_custkey = c_custkey)
) AS custsale  
GROUP BY cntrycode  
ORDER BY cntrycode