Q01, "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 TPCR.LINEITEM WHERE L_SHIPDATE <= timestampadd(SQL_TSI_DAY, -90, {ts'1998-12-01 00:00:00.0'}) GROUP BY L_RETURNFLAG, L_LINESTATUS", "TPCR query 1"
Q02, "SELECT S_ACCTBAL, S_NAME, N_NAME, P_PARTKEY, P_MFGR, S_ADDRESS, S_PHONE, S_COMMENT FROM TPCR.PART, TPCR.SUPPLIER, TPCR.PARTSUPP, TPCR.NATION, TPCR.REGION WHERE 		(P_PARTKEY = PS_PARTKEY) AND (S_SUPPKEY = PS_SUPPKEY) AND (P_SIZE = 15) AND (P_TYPE LIKE '%BRASS') AND (S_NATIONKEY = N_NATIONKEY) AND (N_REGIONKEY = R_REGIONKEY) AND (R_NAME = 'EUROPE') AND (PS_SUPPLYCOST = (SELECT MIN(PS_SUPPLYCOST) FROM TPCR.PARTSUPP, TPCR.SUPPLIER, TPCR.NATION, TPCR.REGION WHERE (P_PARTKEY = PS_PARTKEY) AND (S_SUPPKEY = PS_SUPPKEY) AND (S_NATIONKEY = N_NATIONKEY) AND (N_REGIONKEY = R_REGIONKEY) AND (R_NAME = 'EUROPE')))", "TPCR Query 2"
Q03, "SELECT L_ORDERKEY, SUM((L_EXTENDEDPRICE * (1 - L_DISCOUNT))) AS revenue, O_ORDERDATE, O_SHIPPRIORITY FROM TPCR.CUSTOMER, TPCR.ORDERS, TPCR.LINEITEM WHERE (C_MKTSEGMENT = 'BUILDING') AND (C_CUSTKEY = O_CUSTKEY) AND (L_ORDERKEY = O_ORDERKEY) AND (O_ORDERDATE < {ts'1995-03-15 00:00:00.0'}) AND (L_SHIPDATE > {ts'1995-03-15 00:00:00.0'}) GROUP BY L_ORDERKEY, O_ORDERDATE, O_SHIPPRIORITY" , "Query 3"
Q04, "SELECT O_ORDERPRIORITY, COUNT(*) AS order_count FROM TPCR.ORDERS WHERE (O_ORDERDATE >= {ts'1993-07-01 00:00:00.0'}) AND (O_ORDERDATE < timestampadd(SQL_TSI_MONTH, 3, {ts'1993-07-01 00:00:00.0'})) AND (EXISTS (SELECT * FROM TPCR.LINEITEM WHERE (L_ORDERKEY = O_ORDERKEY) AND (L_COMMITDATE < L_RECEIPTDATE))) GROUP BY O_ORDERPRIORITY", "Query 4"
Q05, "SELECT N_NAME, SUM((L_EXTENDEDPRICE * (1 - L_DISCOUNT))) AS revenue FROM TPCR.CUSTOMER, TPCR.ORDERS, TPCR.LINEITEM, TPCR.SUPPLIER, TPCR.NATION, TPCR.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 = 'ASIA') AND (O_ORDERDATE >= {ts'1994-01-01 00:00:00.0'}) AND (O_ORDERDATE < timestampadd(SQL_TSI_YEAR, 1, {ts'1994-01-01 00:00:00.0'})) GROUP BY N_NAME", "Query 5"
Q06, "SELECT SUM((L_EXTENDEDPRICE * L_DISCOUNT)) AS revenue FROM TPCR.LINEITEM WHERE (L_SHIPDATE >= {ts'1994-01-01 00:00:00.0'}) AND (L_SHIPDATE < timestampadd(SQL_TSI_YEAR, 1, {ts'1994-01-01 00:00:00.0'})) AND (L_DISCOUNT BETWEEN (convert(0.06, bigdecimal) - convert(0.01, bigdecimal)) AND (convert(0.06, bigdecimal) + convert(0.01, bigdecimal))) AND (L_QUANTITY < 24)", "Query 6"
Q07, "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, YEAR(L_SHIPDATE) AS l_year, (L_EXTENDEDPRICE * (1 - L_DISCOUNT)) AS volume FROM TPCR.SUPPLIER, TPCR.LINEITEM, TPCR.ORDERS, TPCR.CUSTOMER, TPCR.NATION AS n1, TPCR.NATION AS 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 = 'FRANCE') AND (n2.N_NAME = 'GERMANY')) OR ((n1.N_NAME = 'GERMANY') AND (n2.N_NAME = 'FRANCE'))) AND (L_SHIPDATE BETWEEN {ts'1995-01-01 00:00:00.0'} AND {ts'1996-12-31 00:00:00.0'})) AS shipping GROUP BY supp_nation, cust_nation, l_year", "Query 7"
Q08, "SELECT o_year, (SUM(CASE WHEN nation = 'BRAZIL' THEN volume ELSE 0 END) / SUM(volume)) AS mkt_share FROM (SELECT year(O_ORDERDATE) AS o_year, (L_EXTENDEDPRICE * (1 - L_DISCOUNT)) AS volume, n2.N_NAME AS nation FROM TPCR.PART, TPCR.SUPPLIER, TPCR.LINEITEM, TPCR.ORDERS, TPCR.CUSTOMER, TPCR.NATION AS n1, TPCR.NATION AS n2, TPCR.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 = 'AMERICA') AND (S_NATIONKEY = n2.N_NATIONKEY) AND (O_ORDERDATE BETWEEN {ts'1995-01-01 00:00:00.0'} AND {ts'1996-12-31 00:00:00.0'}) AND (P_TYPE = 'ECONOMY ANODIZED STEEL')) AS all_nations GROUP BY o_year", "Query 8"
Q09, "SELECT nation, o_year, SUM(amount) AS sum_profit FROM (SELECT N_NAME AS nation, year(O_ORDERDATE) AS o_year, ((L_EXTENDEDPRICE * (1 - L_DISCOUNT)) - (PS_SUPPLYCOST * L_QUANTITY)) AS amount FROM TPCR.PART, TPCR.SUPPLIER, TPCR.LINEITEM, TPCR.PARTSUPP, TPCR.ORDERS, TPCR.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 '%green%')) AS profit GROUP BY nation, o_year", "Query 9"
Q10, "SELECT C_CUSTKEY, C_NAME, SUM((L_EXTENDEDPRICE * (1 - L_DISCOUNT))) AS revenue, C_ACCTBAL, N_NAME, C_ADDRESS, C_PHONE, C_COMMENT FROM TPCR.CUSTOMER, TPCR.ORDERS, TPCR.LINEITEM, TPCR.NATION WHERE (C_CUSTKEY = O_CUSTKEY) AND (L_ORDERKEY = O_ORDERKEY) AND (O_ORDERDATE >= convert('1993-10-01 00:00:00', timestamp)) AND (O_ORDERDATE < timestampadd(SQL_TSI_MONTH, 3, convert('1993-10-01 00:00:00', timestamp))) 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", "Query 10"
Q11, "SELECT PS_PARTKEY, SUM((PS_SUPPLYCOST * PS_AVAILQTY)) AS value FROM TPCR.PARTSUPP, TPCR.SUPPLIER, TPCR.NATION WHERE (PS_SUPPKEY = S_SUPPKEY) AND (S_NATIONKEY = N_NATIONKEY) AND (N_NAME = 'GERMANY') GROUP BY PS_PARTKEY HAVING SUM((PS_SUPPLYCOST * PS_AVAILQTY)) > (SELECT (SUM((PS_SUPPLYCOST * PS_AVAILQTY)) * 1.0E-4) FROM TPCR.PARTSUPP, TPCR.SUPPLIER, TPCR.NATION WHERE (PS_SUPPKEY = S_SUPPKEY) AND (S_NATIONKEY = N_NATIONKEY) AND (N_NAME = 'GERMANY'))", "TPCR Query 11"
Q12, "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 TPCR.ORDERS, TPCR.LINEITEM WHERE (O_ORDERKEY = L_ORDERKEY) AND (L_SHIPMODE IN ('MAIL', 'SHIP')) AND (L_COMMITDATE < L_RECEIPTDATE) AND (L_SHIPDATE < L_COMMITDATE) AND (L_RECEIPTDATE >= convert('1994-01-01 00:00:00', timestamp)) AND (L_RECEIPTDATE < timestampadd(SQL_TSI_YEAR, 1, convert('1994-01-01 00:00:00', timestamp))) GROUP BY L_SHIPMODE", "Query 12"
Q13, "SELECT c_count, COUNT(*) AS custdist FROM (SELECT TPCR.CUSTOMER.C_CUSTKEY, COUNT(O_ORDERKEY) AS c_count FROM TPCR.CUSTOMER LEFT OUTER JOIN TPCR.ORDERS ON TPCR.CUSTOMER.C_CUSTKEY = O_CUSTKEY AND O_COMMENT NOT LIKE '%special%requests%' GROUP BY TPCR.CUSTOMER.C_CUSTKEY) AS c_orders GROUP BY c_count", "Query 13"
Q14, "SELECT ((100.0 * 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 TPCR.LINEITEM, TPCR.PART WHERE (L_PARTKEY = P_PARTKEY) AND (L_SHIPDATE >= convert('1995-09-01 00:00:00', timestamp)) AND (L_SHIPDATE < convert('1995-10-01 00:00:00', timestamp))", "Query 14"
Q15, "SELECT 'We don''t create views' AS TheReason", "Fake query 15"
Q16, "SELECT P_BRAND, P_TYPE, P_SIZE, COUNT(DISTINCT PS_SUPPKEY) AS supplier_cnt FROM TPCR.PARTSUPP, TPCR.PART WHERE (P_PARTKEY = PS_PARTKEY) AND (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 TPCR.SUPPLIER WHERE S_COMMENT LIKE '%Customer%Complaints%')) GROUP BY P_BRAND, P_TYPE, P_SIZE", "Query 16"
Q17, "SELECT (SUM(L_EXTENDEDPRICE) / 7.0) AS avg_yearly FROM TPCR.LINEITEM, TPCR.PART WHERE (P_PARTKEY = L_PARTKEY) AND (P_BRAND = 'Brand#23') AND (P_CONTAINER = 'MED BOX') AND (L_QUANTITY < (SELECT (0.2 * AVG(L_QUANTITY)) FROM TPCR.LINEITEM WHERE L_PARTKEY = P_PARTKEY))", "Query 17"
Q18, "SELECT C_NAME, C_CUSTKEY, O_ORDERKEY, O_ORDERDATE, O_TOTALPRICE, SUM(L_QUANTITY) FROM TPCR.CUSTOMER, TPCR.ORDERS, TPCR.LINEITEM WHERE (O_ORDERKEY IN (SELECT L_ORDERKEY FROM TPCR.LINEITEM GROUP BY L_ORDERKEY HAVING SUM(L_QUANTITY) > 300)) AND (C_CUSTKEY = O_CUSTKEY) AND (O_ORDERKEY = L_ORDERKEY) GROUP BY C_NAME, C_CUSTKEY, O_ORDERKEY, O_ORDERDATE, O_TOTALPRICE", "Query 18"
Q19, "SELECT SUM((L_EXTENDEDPRICE * (1 - L_DISCOUNT))) AS revenue FROM TPCR.LINEITEM, TPCR.PART WHERE ((P_PARTKEY = L_PARTKEY) AND (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_PARTKEY = L_PARTKEY) AND (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_PARTKEY = L_PARTKEY) AND (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'))", "Query 19"
Q20, "SELECT S_NAME, S_ADDRESS FROM TPCR.SUPPLIER, TPCR.NATION WHERE (S_SUPPKEY IN (SELECT PS_SUPPKEY FROM TPCR.PARTSUPP WHERE (PS_PARTKEY IN (SELECT P_PARTKEY FROM TPCR.PART WHERE P_NAME LIKE 'forest%')) AND (PS_AVAILQTY > (SELECT (0.5 * SUM(L_QUANTITY)) FROM TPCR.LINEITEM WHERE (L_PARTKEY = PS_PARTKEY) AND (L_SUPPKEY = PS_SUPPKEY) AND (L_SHIPDATE >= convert('1994-01-01 00:00:00', timestamp)) AND (L_SHIPDATE < timestampadd(SQL_TSI_YEAR, 1, convert('1994-01-01 00:00:00', timestamp))))))) AND (S_NATIONKEY = N_NATIONKEY) AND (N_NAME = 'CANADA')", "Query 20"
Q21, "SELECT S_NAME, COUNT(*) AS numwait FROM TPCR.SUPPLIER, TPCR.LINEITEM AS l1, TPCR.ORDERS, TPCR.NATION WHERE (S_SUPPKEY = L_SUPPKEY) AND (O_ORDERKEY = L_ORDERKEY) AND (O_ORDERSTATUS = 'F') AND (L_RECEIPTDATE > L_COMMITDATE) AND (EXISTS (SELECT * FROM TPCR.LINEITEM AS l2 WHERE (l2.L_ORDERKEY = l1.l_orderkey) AND (l2.L_SUPPKEY <> l1.l_suppkey))) AND (NOT (EXISTS (SELECT * FROM TPCR.LINEITEM AS 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 = 'SAUDI ARABIA') GROUP BY S_NAME", "Query 21"
Q22, "SELECT cntrycode, COUNT(*) AS numcust, SUM(c_acctbal) AS totacctbal FROM (SELECT left(C_PHONE, 2) AS cntrycode, TPCR.CUSTOMER.C_ACCTBAL FROM TPCR.CUSTOMER WHERE (left(C_PHONE, 2) IN ('13', '31', '23', '29', '30', '18', '17')) AND (TPCR.CUSTOMER.C_ACCTBAL > (SELECT AVG(TPCR.CUSTOMER.C_ACCTBAL) FROM TPCR.CUSTOMER WHERE (TPCR.CUSTOMER.C_ACCTBAL > 0.0) AND (left(C_PHONE, 2) IN ('13', '31', '23', '29', '30', '18', '17')))) AND (NOT (EXISTS (SELECT * FROM TPCR.ORDERS WHERE O_CUSTKEY = C_CUSTKEY)))) AS custsale GROUP BY cntrycode", "Query 22"