move hivesterix codebase into hyracks fullstack
git-svn-id: https://hyracks.googlecode.com/svn/branches/fullstack_staging@2420 123451ca-8445-de46-9d55-352943316053
diff --git a/hivesterix/resource/tpch/q10_returned_item.hive b/hivesterix/resource/tpch/q10_returned_item.hive
new file mode 100644
index 0000000..b6535cb
--- /dev/null
+++ b/hivesterix/resource/tpch/q10_returned_item.hive
@@ -0,0 +1,37 @@
+DROP TABLE lineitem;
+DROP TABLE orders;
+DROP TABLE customer;
+DROP TABLE nation;
+DROP TABLE q10_returned_item;
+
+-- create the tables and load the data
+Create external table lineitem (L_ORDERKEY INT, L_PARTKEY INT, L_SUPPKEY INT, L_LINENUMBER INT, L_QUANTITY DOUBLE, L_EXTENDEDPRICE DOUBLE, L_DISCOUNT DOUBLE, L_TAX DOUBLE, L_RETURNFLAG STRING, L_LINESTATUS STRING, L_SHIPDATE STRING, L_COMMITDATE STRING, L_RECEIPTDATE STRING, L_SHIPINSTRUCT STRING, L_SHIPMODE STRING, L_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/lineitem';
+create external table orders (O_ORDERKEY INT, O_CUSTKEY INT, O_ORDERSTATUS STRING, O_TOTALPRICE DOUBLE, O_ORDERDATE STRING, O_ORDERPRIORITY STRING, O_CLERK STRING, O_SHIPPRIORITY INT, O_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/orders';
+create external table customer (C_CUSTKEY INT, C_NAME STRING, C_ADDRESS STRING, C_NATIONKEY INT, C_PHONE STRING, C_ACCTBAL DOUBLE, C_MKTSEGMENT STRING, C_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/customer';
+create external table nation (N_NATIONKEY INT, N_NAME STRING, N_REGIONKEY INT, N_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/nation';
+
+-- create the result table
+create table q10_returned_item (c_custkey int, c_name string, revenue double, c_acctbal string, n_name string, c_address string, c_phone string, c_comment string);
+
+set mapred.min.split.size=536870912;
+set hive.exec.reducers.bytes.per.reducer=1024000000;
+
+-- the query
+insert overwrite table q10_returned_item
+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 c join orders o
+ on
+ c.c_custkey = o.o_custkey and o.o_orderdate >= '1993-10-01' and o.o_orderdate < '1994-01-01'
+ join nation n
+ on
+ c.c_nationkey = n.n_nationkey
+ join lineitem l
+ on
+ l.l_orderkey = o.o_orderkey and l.l_returnflag = 'R'
+group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment
+order by revenue desc
+limit 20;
+
diff --git a/hivesterix/resource/tpch/q11_important_stock.hive b/hivesterix/resource/tpch/q11_important_stock.hive
new file mode 100644
index 0000000..bfa3743
--- /dev/null
+++ b/hivesterix/resource/tpch/q11_important_stock.hive
@@ -0,0 +1,47 @@
+DROP TABLE partsupp;
+DROP TABLE supplier;
+DROP TABLE nation;
+DROP TABLE q11_important_stock;
+DROP TABLE q11_part_tmp;
+DROP TABLE q11_sum_tmp;
+
+-- create tables and load data
+create external table supplier (S_SUPPKEY INT, S_NAME STRING, S_ADDRESS STRING, S_NATIONKEY INT, S_PHONE STRING, S_ACCTBAL DOUBLE, S_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/supplier';
+create external table nation (N_NATIONKEY INT, N_NAME STRING, N_REGIONKEY INT, N_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/nation';
+create external table partsupp (PS_PARTKEY INT, PS_SUPPKEY INT, PS_AVAILQTY INT, PS_SUPPLYCOST DOUBLE, PS_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION'/tpch/10/partsupp';
+
+-- create the target table
+create table q11_important_stock(ps_partkey INT, value DOUBLE);
+create table q11_part_tmp(ps_partkey int, part_value double);
+create table q11_sum_tmp(total_value double);
+
+-- the query
+insert overwrite table q11_part_tmp
+select
+ ps_partkey, sum(ps_supplycost * ps_availqty) as part_value
+from
+ nation n join supplier s
+ on
+ s.s_nationkey = n.n_nationkey and n.n_name = 'GERMANY'
+ join partsupp ps
+ on
+ ps.ps_suppkey = s.s_suppkey
+group by ps_partkey;
+
+insert overwrite table q11_sum_tmp
+select
+ sum(part_value) as total_value
+from
+ q11_part_tmp;
+
+insert overwrite table q11_important_stock
+select
+ ps_partkey, part_value as value
+from
+ (
+ select ps_partkey, part_value, total_value
+ from q11_part_tmp join q11_sum_tmp
+ ) a
+where part_value > total_value * 0.0001
+order by value desc;
+
diff --git a/hivesterix/resource/tpch/q12_shipping.hive b/hivesterix/resource/tpch/q12_shipping.hive
new file mode 100644
index 0000000..0ae896c
--- /dev/null
+++ b/hivesterix/resource/tpch/q12_shipping.hive
@@ -0,0 +1,42 @@
+DROP TABLE lineitem;
+DROP TABLE orders;
+DROP TABLE q12_shipping;
+
+-- create the tables and load the data
+create external table lineitem (L_ORDERKEY INT, L_PARTKEY INT, L_SUPPKEY INT, L_LINENUMBER INT, L_QUANTITY DOUBLE, L_EXTENDEDPRICE DOUBLE, L_DISCOUNT DOUBLE, L_TAX DOUBLE, L_RETURNFLAG STRING, L_LINESTATUS STRING, L_SHIPDATE STRING, L_COMMITDATE STRING, L_RECEIPTDATE STRING, L_SHIPINSTRUCT STRING, L_SHIPMODE STRING, L_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/lineitem';
+create external table orders (O_ORDERKEY INT, O_CUSTKEY INT, O_ORDERSTATUS STRING, O_TOTALPRICE DOUBLE, O_ORDERDATE STRING, O_ORDERPRIORITY STRING, O_CLERK STRING, O_SHIPPRIORITY INT, O_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/orders';
+
+-- create the result table
+create table q12_shipping(l_shipmode string, high_line_count double, low_line_count double);
+
+set mapred.min.split.size=536870912;
+set hive.exec.reducers.bytes.per.reducer=1225000000;
+
+-- the query
+insert overwrite table q12_shipping
+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 o join lineitem l
+ on
+ o.o_orderkey = l.l_orderkey and l.l_commitdate < l.l_receiptdate
+and l.l_shipdate < l.l_commitdate and l.l_receiptdate >= '1994-01-01'
+and l.l_receiptdate < '1995-01-01'
+where
+ l.l_shipmode = 'MAIL' or l.l_shipmode = 'SHIP'
+group by l_shipmode
+order by l_shipmode;
diff --git a/hivesterix/resource/tpch/q13_customer_distribution.hive b/hivesterix/resource/tpch/q13_customer_distribution.hive
new file mode 100644
index 0000000..dd3674d
--- /dev/null
+++ b/hivesterix/resource/tpch/q13_customer_distribution.hive
@@ -0,0 +1,27 @@
+DROP TABLE customer;
+DROP TABLE orders;
+DROP TABLE q13_customer_distribution;
+
+-- create the tables and load the data
+create external table customer (C_CUSTKEY INT, C_NAME STRING, C_ADDRESS STRING, C_NATIONKEY INT, C_PHONE STRING, C_ACCTBAL DOUBLE, C_MKTSEGMENT STRING, C_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/customer';
+create external table orders (O_ORDERKEY INT, O_CUSTKEY INT, O_ORDERSTATUS STRING, O_TOTALPRICE DOUBLE, O_ORDERDATE STRING, O_ORDERPRIORITY STRING, O_CLERK STRING, O_SHIPPRIORITY INT, O_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/orders';
+
+-- create the result table
+create table q13_customer_distribution (c_count int, custdist int);
+
+-- the query
+insert overwrite table q13_customer_distribution
+select
+ c_count, count(1) as custdist
+from
+ (select
+ c_custkey, count(o_orderkey) as c_count
+ from
+ customer c left outer join orders o
+ on
+ c.c_custkey = o.o_custkey and not o.o_comment like '%special%requests%'
+ group by c_custkey
+ ) c_orders
+group by c_count
+order by custdist desc, c_count desc;
+
diff --git a/hivesterix/resource/tpch/q14_promotion_effect.hive b/hivesterix/resource/tpch/q14_promotion_effect.hive
new file mode 100644
index 0000000..a7ea773
--- /dev/null
+++ b/hivesterix/resource/tpch/q14_promotion_effect.hive
@@ -0,0 +1,28 @@
+DROP TABLE lineitem;
+DROP TABLE part;
+DROP TABLE q14_promotion_effect;
+
+-- create the tables and load the data
+create external table lineitem (L_ORDERKEY INT, L_PARTKEY INT, L_SUPPKEY INT, L_LINENUMBER INT, L_QUANTITY DOUBLE, L_EXTENDEDPRICE DOUBLE, L_DISCOUNT DOUBLE, L_TAX DOUBLE, L_RETURNFLAG STRING, L_LINESTATUS STRING, L_SHIPDATE STRING, L_COMMITDATE STRING, L_RECEIPTDATE STRING, L_SHIPINSTRUCT STRING, L_SHIPMODE STRING, L_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/lineitem';
+create external table part (P_PARTKEY INT, P_NAME STRING, P_MFGR STRING, P_BRAND STRING, P_TYPE STRING, P_SIZE INT, P_CONTAINER STRING, P_RETAILPRICE DOUBLE, P_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/part';
+
+-- create the result table
+create table q14_promotion_effect(promo_revenue double);
+
+set mapred.min.split.size=536870912;
+set hive.exec.reducers.bytes.per.reducer=1040000000;
+
+-- the query
+insert overwrite table q14_promotion_effect
+select
+ 100.00 * sum(case
+ when p_type like 'PROMO%'
+ then l_extendedprice*(1-l_discount)
+ else 0.0
+ end
+ ) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue
+from
+ part p join lineitem l
+ on
+ l.l_partkey = p.p_partkey and l.l_shipdate >= '1995-09-01' and l.l_shipdate < '1995-10-01';
+
diff --git a/hivesterix/resource/tpch/q15_top_supplier.hive b/hivesterix/resource/tpch/q15_top_supplier.hive
new file mode 100644
index 0000000..b38ba2c
--- /dev/null
+++ b/hivesterix/resource/tpch/q15_top_supplier.hive
@@ -0,0 +1,45 @@
+DROP TABLE lineitem;
+DROP TABLE supplier;
+DROP TABLE revenue;
+DROP TABLE max_revenue;
+DROP TABLE q15_top_supplier;
+
+-- create the tables and load the data
+create external table lineitem (L_ORDERKEY INT, L_PARTKEY INT, L_SUPPKEY INT, L_LINENUMBER INT, L_QUANTITY DOUBLE, L_EXTENDEDPRICE DOUBLE, L_DISCOUNT DOUBLE, L_TAX DOUBLE, L_RETURNFLAG STRING, L_LINESTATUS STRING, L_SHIPDATE STRING, L_COMMITDATE STRING, L_RECEIPTDATE STRING, L_SHIPINSTRUCT STRING, L_SHIPMODE STRING, L_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/lineitem';
+create external table supplier (S_SUPPKEY INT, S_NAME STRING, S_ADDRESS STRING, S_NATIONKEY INT, S_PHONE STRING, S_ACCTBAL DOUBLE, S_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/supplier';
+
+-- create result tables
+create table revenue(supplier_no int, total_revenue double);
+create table max_revenue(max_revenue double);
+create table q15_top_supplier(s_suppkey int, s_name string, s_address string, s_phone string, total_revenue double);
+
+
+set mapred.min.split.size=536870912;
+
+-- the query
+insert overwrite table revenue
+select
+ l_suppkey as supplier_no, sum(l_extendedprice * (1 - l_discount)) as total_revenue
+from
+ lineitem
+where
+ l_shipdate >= '1996-01-01' and l_shipdate < '1996-04-01'
+group by l_suppkey;
+
+insert overwrite table max_revenue
+select
+ max(total_revenue)
+from
+ revenue;
+
+insert overwrite table q15_top_supplier
+select
+ s_suppkey, s_name, s_address, s_phone, total_revenue
+from supplier s join revenue r
+ on
+ s.s_suppkey = r.supplier_no
+ join max_revenue m
+ on
+ r.total_revenue = m.max_revenue
+order by s_suppkey;
+
diff --git a/hivesterix/resource/tpch/q16_parts_supplier_relationship.hive b/hivesterix/resource/tpch/q16_parts_supplier_relationship.hive
new file mode 100644
index 0000000..495a5ea
--- /dev/null
+++ b/hivesterix/resource/tpch/q16_parts_supplier_relationship.hive
@@ -0,0 +1,53 @@
+DROP TABLE partsupp;
+DROP TABLE part;
+DROP TABLE supplier;
+DROP TABLE q16_parts_supplier_relationship;
+DROP TABLE q16_tmp;
+DROP TABLE supplier_tmp;
+
+-- create the tables and load the data
+create external table part (P_PARTKEY INT, P_NAME STRING, P_MFGR STRING, P_BRAND STRING, P_TYPE STRING, P_SIZE INT, P_CONTAINER STRING, P_RETAILPRICE DOUBLE, P_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/part';
+create external table partsupp (PS_PARTKEY INT, PS_SUPPKEY INT, PS_AVAILQTY INT, PS_SUPPLYCOST DOUBLE, PS_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION'/tpch/10/partsupp';
+create external table supplier (S_SUPPKEY INT, S_NAME STRING, S_ADDRESS STRING, S_NATIONKEY INT, S_PHONE STRING, S_ACCTBAL DOUBLE, S_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/supplier';
+
+-- create the result table
+create table q16_parts_supplier_relationship(p_brand string, p_type string, p_size int, supplier_cnt int);
+create table q16_tmp(p_brand string, p_type string, p_size int, ps_suppkey int);
+create table supplier_tmp(s_suppkey int);
+
+-- the query
+insert overwrite table supplier_tmp
+select
+ s_suppkey
+from
+ supplier
+where
+ not s_comment like '%Customer%Complaints%';
+
+insert overwrite table q16_tmp
+select
+ p_brand, p_type, p_size, ps_suppkey
+from
+ partsupp ps join part p
+ on
+ p.p_partkey = ps.ps_partkey and p.p_brand <> 'Brand#45'
+ and not p.p_type like 'MEDIUM POLISHED%'
+ join supplier_tmp s
+ on
+ ps.ps_suppkey = s.s_suppkey;
+
+insert overwrite table q16_parts_supplier_relationship
+select
+ p_brand, p_type, p_size, count(distinct ps_suppkey) as supplier_cnt
+from
+ (select
+ *
+ from
+ q16_tmp
+ where p_size = 49 or p_size = 14 or p_size = 23 or
+ p_size = 45 or p_size = 19 or p_size = 3 or
+ p_size = 36 or p_size = 9
+) q16_all
+group by p_brand, p_type, p_size
+order by supplier_cnt desc, p_brand, p_type, p_size;
+
diff --git a/hivesterix/resource/tpch/q17_small_quantity_order_revenue.hive b/hivesterix/resource/tpch/q17_small_quantity_order_revenue.hive
new file mode 100644
index 0000000..448b8f3
--- /dev/null
+++ b/hivesterix/resource/tpch/q17_small_quantity_order_revenue.hive
@@ -0,0 +1,38 @@
+DROP TABLE lineitem;
+DROP TABLE part;
+DROP TABLE q17_small_quantity_order_revenue;
+DROP TABLE lineitem_tmp;
+
+-- create the tables and load the data
+create external table lineitem (L_ORDERKEY INT, L_PARTKEY INT, L_SUPPKEY INT, L_LINENUMBER INT, L_QUANTITY DOUBLE, L_EXTENDEDPRICE DOUBLE, L_DISCOUNT DOUBLE, L_TAX DOUBLE, L_RETURNFLAG STRING, L_LINESTATUS STRING, L_SHIPDATE STRING, L_COMMITDATE STRING, L_RECEIPTDATE STRING, L_SHIPINSTRUCT STRING, L_SHIPMODE STRING, L_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/lineitem';
+create external table part (P_PARTKEY INT, P_NAME STRING, P_MFGR STRING, P_BRAND STRING, P_TYPE STRING, P_SIZE INT, P_CONTAINER STRING, P_RETAILPRICE DOUBLE, P_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/part';
+
+-- create the result table
+create table q17_small_quantity_order_revenue (avg_yearly double);
+create table lineitem_tmp (t_partkey int, t_avg_quantity double);
+
+-- the query
+insert overwrite table lineitem_tmp
+select
+ l_partkey as t_partkey, 0.2 * avg(l_quantity) as t_avg_quantity
+from
+ lineitem
+group by l_partkey;
+
+insert overwrite table q17_small_quantity_order_revenue
+select
+ sum(l_extendedprice) / 7.0 as avg_yearly
+from
+ (select l_quantity, l_extendedprice, t_avg_quantity from
+ lineitem_tmp t join
+ (select
+ l_quantity, l_partkey, l_extendedprice
+ from
+ part p join lineitem l
+ on
+ p.p_partkey = l.l_partkey
+ and p.p_brand = 'Brand#23'
+ and p.p_container = 'MED BOX'
+ ) l1 on l1.l_partkey = t.t_partkey
+ ) a
+where l_quantity < t_avg_quantity;
diff --git a/hivesterix/resource/tpch/q18_large_volume_customer.hive b/hivesterix/resource/tpch/q18_large_volume_customer.hive
new file mode 100644
index 0000000..04081ad
--- /dev/null
+++ b/hivesterix/resource/tpch/q18_large_volume_customer.hive
@@ -0,0 +1,43 @@
+DROP TABLE lineitem;
+DROP TABLE orders;
+DROP TABLE customer;
+DROP TABLE q18_tmp;
+DROP TABLE q18_large_volume_customer;
+
+-- create the tables and load the data
+create external table lineitem (L_ORDERKEY INT, L_PARTKEY INT, L_SUPPKEY INT, L_LINENUMBER INT, L_QUANTITY DOUBLE, L_EXTENDEDPRICE DOUBLE, L_DISCOUNT DOUBLE, L_TAX DOUBLE, L_RETURNFLAG STRING, L_LINESTATUS STRING, L_SHIPDATE STRING, L_COMMITDATE STRING, L_RECEIPTDATE STRING, L_SHIPINSTRUCT STRING, L_SHIPMODE STRING, L_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/lineitem';
+create external table orders (O_ORDERKEY INT, O_CUSTKEY INT, O_ORDERSTATUS STRING, O_TOTALPRICE DOUBLE, O_ORDERDATE STRING, O_ORDERPRIORITY STRING, O_CLERK STRING, O_SHIPPRIORITY INT, O_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/orders';
+create external table customer (C_CUSTKEY INT, C_NAME STRING, C_ADDRESS STRING, C_NATIONKEY INT, C_PHONE STRING, C_ACCTBAL DOUBLE, C_MKTSEGMENT STRING, C_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/customer';
+
+-- create the result tables
+create table q18_tmp(l_orderkey int, t_sum_quantity double);
+create table q18_large_volume_customer(c_name string, c_custkey int, o_orderkey int, o_orderdate string, o_totalprice double, sum_quantity double);
+
+set mapred.min.split.size=268435456;
+set hive.exec.reducers.bytes.per.reducer=1164000000;
+
+-- the query
+insert overwrite table q18_tmp
+select
+ l_orderkey, sum(l_quantity) as t_sum_quantity
+from
+ lineitem
+group by l_orderkey;
+
+insert overwrite table q18_large_volume_customer
+select
+ c_name,c_custkey,o_orderkey,o_orderdate,o_totalprice,sum(l_quantity)
+from
+ customer c join orders o
+ on
+ c.c_custkey = o.o_custkey
+ join q18_tmp t
+ on
+ o.o_orderkey = t.l_orderkey and t.t_sum_quantity > 300
+ join lineitem l
+ on
+ o.o_orderkey = l.l_orderkey
+group by c_name,c_custkey,o_orderkey,o_orderdate,o_totalprice
+order by o_totalprice desc,o_orderdate
+limit 100;
+
diff --git a/hivesterix/resource/tpch/q19_discounted_revenue.hive b/hivesterix/resource/tpch/q19_discounted_revenue.hive
new file mode 100644
index 0000000..1e821ca
--- /dev/null
+++ b/hivesterix/resource/tpch/q19_discounted_revenue.hive
@@ -0,0 +1,49 @@
+DROP TABLE lineitem;
+DROP TABLE part;
+DROP TABLE q19_discounted_revenue;
+
+-- create the tables and load the data
+create external table lineitem (L_ORDERKEY INT, L_PARTKEY INT, L_SUPPKEY INT, L_LINENUMBER INT, L_QUANTITY DOUBLE, L_EXTENDEDPRICE DOUBLE, L_DISCOUNT DOUBLE, L_TAX DOUBLE, L_RETURNFLAG STRING, L_LINESTATUS STRING, L_SHIPDATE STRING, L_COMMITDATE STRING, L_RECEIPTDATE STRING, L_SHIPINSTRUCT STRING, L_SHIPMODE STRING, L_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/lineitem';
+create external table part (P_PARTKEY INT, P_NAME STRING, P_MFGR STRING, P_BRAND STRING, P_TYPE STRING, P_SIZE INT, P_CONTAINER STRING, P_RETAILPRICE DOUBLE, P_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/part';
+
+-- create the result table
+create table q19_discounted_revenue(revenue double);
+
+set mapred.min.split.size=268435456;
+set hive.exec.reducers.bytes.per.reducer=1040000000;
+
+-- the query
+insert overwrite table q19_discounted_revenue
+select
+ sum(l_extendedprice * (1 - l_discount) ) as revenue
+from
+ lineitem l join part p
+ on
+ p.p_partkey = l.l_partkey
+where
+ (
+ p_brand = 'Brand#12'
+ and p_container REGEXP 'SM CASE||SM BOX||SM PACK||SM PKG'
+ and l_quantity >= 1 and l_quantity <= 11
+ and p_size >= 1 and p_size <= 5
+ and l_shipmode REGEXP 'AIR||AIR REG'
+ and l_shipinstruct = 'DELIVER IN PERSON'
+ )
+ or
+ (
+ p_brand = 'Brand#23'
+ and p_container REGEXP 'MED BAG||MED BOX||MED PKG||MED PACK'
+ and l_quantity >= 10 and l_quantity <= 20
+ and p_size >= 1 and p_size <= 10
+ and l_shipmode REGEXP 'AIR||AIR REG'
+ and l_shipinstruct = 'DELIVER IN PERSON'
+ )
+ or
+ (
+ p_brand = 'Brand#34'
+ and p_container REGEXP 'LG CASE||LG BOX||LG PACK||LG PKG'
+ and l_quantity >= 20 and l_quantity <= 30
+ and p_size >= 1 and p_size <= 15
+ and l_shipmode REGEXP 'AIR||AIR REG'
+ and l_shipinstruct = 'DELIVER IN PERSON'
+ );
diff --git a/hivesterix/resource/tpch/q1_pricing_summary_report.hive b/hivesterix/resource/tpch/q1_pricing_summary_report.hive
new file mode 100644
index 0000000..94b2913
--- /dev/null
+++ b/hivesterix/resource/tpch/q1_pricing_summary_report.hive
@@ -0,0 +1,21 @@
+DROP TABLE lineitem;
+DROP TABLE q1_pricing_summary_report;
+
+-- create tables and load data
+Create external table lineitem (L_ORDERKEY INT, L_PARTKEY INT, L_SUPPKEY INT, L_LINENUMBER INT, L_QUANTITY DOUBLE, L_EXTENDEDPRICE DOUBLE, L_DISCOUNT DOUBLE, L_TAX DOUBLE, L_RETURNFLAG STRING, L_LINESTATUS STRING, L_SHIPDATE STRING, L_COMMITDATE STRING, L_RECEIPTDATE STRING, L_SHIPINSTRUCT STRING, L_SHIPMODE STRING, L_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/lineitem';
+
+-- create the target table
+CREATE TABLE q1_pricing_summary_report ( L_RETURNFLAG STRING, L_LINESTATUS STRING, SUM_QTY DOUBLE, SUM_BASE_PRICE DOUBLE, SUM_DISC_PRICE DOUBLE, SUM_CHARGE DOUBLE, AVE_QTY DOUBLE, AVE_PRICE DOUBLE, AVE_DISC DOUBLE, COUNT_ORDER INT);
+
+set mapred.min.split.size=536870912;
+
+-- the query
+-- INSERT OVERWRITE TABLE q1_pricing_summary_report
+SELECT
+ L_RETURNFLAG, L_LINESTATUS, SUM(L_QUANTITY), SUM(L_EXTENDEDPRICE), SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)), SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)), AVG(L_QUANTITY), AVG(L_EXTENDEDPRICE), AVG(L_DISCOUNT), COUNT(1)
+FROM
+ lineitem
+WHERE
+ L_SHIPDATE<='1998-09-02'
+GROUP BY L_RETURNFLAG, L_LINESTATUS
+ORDER BY L_RETURNFLAG, L_LINESTATUS;
diff --git a/hivesterix/resource/tpch/q20_potential_part_promotion.hive b/hivesterix/resource/tpch/q20_potential_part_promotion.hive
new file mode 100644
index 0000000..40ae423
--- /dev/null
+++ b/hivesterix/resource/tpch/q20_potential_part_promotion.hive
@@ -0,0 +1,77 @@
+DROP TABLE partsupp;
+DROP TABLE lineitem;
+DROP TABLE supplier;
+DROP TABLE nation;
+DROP TABLE q20_tmp1;
+DROP TABLE q20_tmp2;
+DROP TABLE q20_tmp3;
+DROP TABLE q20_tmp4;
+DROP TABLE q20_potential_part_promotion;
+
+-- create tables and load data
+create external table lineitem (L_ORDERKEY INT, L_PARTKEY INT, L_SUPPKEY INT, L_LINENUMBER INT, L_QUANTITY DOUBLE, L_EXTENDEDPRICE DOUBLE, L_DISCOUNT DOUBLE, L_TAX DOUBLE, L_RETURNFLAG STRING, L_LINESTATUS STRING, L_SHIPDATE STRING, L_COMMITDATE STRING, L_RECEIPTDATE STRING, L_SHIPINSTRUCT STRING, L_SHIPMODE STRING, L_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/lineitem';
+create external table supplier (S_SUPPKEY INT, S_NAME STRING, S_ADDRESS STRING, S_NATIONKEY INT, S_PHONE STRING, S_ACCTBAL DOUBLE, S_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/supplier';
+create external table nation (N_NATIONKEY INT, N_NAME STRING, N_REGIONKEY INT, N_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/nation';
+create external table partsupp (PS_PARTKEY INT, PS_SUPPKEY INT, PS_AVAILQTY INT, PS_SUPPLYCOST DOUBLE, PS_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION'/tpch/10/partsupp';
+
+-- create the target table
+create table q20_tmp1(p_partkey int);
+create table q20_tmp2(l_partkey int, l_suppkey int, sum_quantity double);
+create table q20_tmp3(ps_suppkey int, ps_availqty int, sum_quantity double);
+create table q20_tmp4(ps_suppkey int);
+create table q20_potential_part_promotion(s_name string, s_address string);
+
+set mapred.min.split.size=536870912;
+
+-- the query
+insert overwrite table q20_tmp1
+select distinct p_partkey
+from
+ part
+where
+ p_name like 'forest%';
+
+insert overwrite table q20_tmp2
+select
+ l_partkey, l_suppkey, 0.5 * sum(l_quantity)
+from
+ lineitem
+where
+ l_shipdate >= '1994-01-01'
+ and l_shipdate < '1995-01-01'
+group by l_partkey, l_suppkey;
+
+insert overwrite table q20_tmp3
+select
+ ps_suppkey, ps_availqty, sum_quantity
+from
+ partsupp ps join q20_tmp1 t1
+ on
+ ps.ps_partkey = t1.p_partkey
+ join q20_tmp2 t2
+ on
+ ps.ps_partkey = t2.l_partkey and ps.ps_suppkey = t2.l_suppkey;
+
+insert overwrite table q20_tmp4
+select
+ ps_suppkey
+from
+ q20_tmp3
+where
+ ps_availqty > sum_quantity
+group by ps_suppkey;
+
+insert overwrite table q20_potential_part_promotion
+select
+ s_name, s_address
+from
+ supplier s join nation n
+ on
+ s.s_nationkey = n.n_nationkey
+ and n.n_name = 'CANADA'
+ join q20_tmp4 t4
+ on
+ s.s_suppkey = t4.ps_suppkey
+order by s_name;
+
+
diff --git a/hivesterix/resource/tpch/q21_suppliers_who_kept_orders_waiting.hive b/hivesterix/resource/tpch/q21_suppliers_who_kept_orders_waiting.hive
new file mode 100644
index 0000000..0418540
--- /dev/null
+++ b/hivesterix/resource/tpch/q21_suppliers_who_kept_orders_waiting.hive
@@ -0,0 +1,74 @@
+DROP TABLE orders;
+DROP TABLE lineitem;
+DROP TABLE supplier;
+DROP TABLE nation;
+DROP TABLE q21_tmp1;
+DROP TABLE q21_tmp2;
+DROP TABLE q21_suppliers_who_kept_orders_waiting;
+
+-- create tables and load data
+create external table lineitem (L_ORDERKEY INT, L_PARTKEY INT, L_SUPPKEY INT, L_LINENUMBER INT, L_QUANTITY DOUBLE, L_EXTENDEDPRICE DOUBLE, L_DISCOUNT DOUBLE, L_TAX DOUBLE, L_RETURNFLAG STRING, L_LINESTATUS STRING, L_SHIPDATE STRING, L_COMMITDATE STRING, L_RECEIPTDATE STRING, L_SHIPINSTRUCT STRING, L_SHIPMODE STRING, L_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/lineitem';
+create external table orders (O_ORDERKEY INT, O_CUSTKEY INT, O_ORDERSTATUS STRING, O_TOTALPRICE DOUBLE, O_ORDERDATE STRING, O_ORDERPRIORITY STRING, O_CLERK STRING, O_SHIPPRIORITY INT, O_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/orders';
+create external table supplier (S_SUPPKEY INT, S_NAME STRING, S_ADDRESS STRING, S_NATIONKEY INT, S_PHONE STRING, S_ACCTBAL DOUBLE, S_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/supplier';
+create external table nation (N_NATIONKEY INT, N_NAME STRING, N_REGIONKEY INT, N_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/nation';
+
+-- create target tables
+create table q21_tmp1(l_orderkey int, count_suppkey int, max_suppkey int);
+create table q21_tmp2(l_orderkey int, count_suppkey int, max_suppkey int);
+create table q21_suppliers_who_kept_orders_waiting(s_name string, numwait int);
+
+-- the query
+insert overwrite table q21_tmp1
+select
+ l_orderkey, count(distinct l_suppkey), max(l_suppkey) as max_suppkey
+from
+ lineitem
+group by l_orderkey;
+
+insert overwrite table q21_tmp2
+select
+ l_orderkey, count(distinct l_suppkey), max(l_suppkey) as max_suppkey
+from
+ lineitem
+where
+ l_receiptdate > l_commitdate
+group by l_orderkey;
+
+insert overwrite table q21_suppliers_who_kept_orders_waiting
+select
+ s_name, count(1) as numwait
+from
+ (select s_name from
+(select s_name, t2.l_orderkey, l_suppkey, count_suppkey, max_suppkey
+ from q21_tmp2 t2 right outer join
+ (select s_name, l_orderkey, l_suppkey from
+ (select s_name, t1.l_orderkey, l_suppkey, count_suppkey, max_suppkey
+ from
+ q21_tmp1 t1 join
+ (select s_name, l_orderkey, l_suppkey
+ from
+ orders o join
+ (select s_name, l_orderkey, l_suppkey
+ from
+ nation n join supplier s
+ on
+ s.s_nationkey = n.n_nationkey
+ and n.n_name = 'SAUDI ARABIA'
+ join lineitem l
+ on
+ s.s_suppkey = l.l_suppkey
+ where
+ l.l_receiptdate > l.l_commitdate
+ ) l1 on o.o_orderkey = l1.l_orderkey and o.o_orderstatus = 'F'
+ ) l2 on l2.l_orderkey = t1.l_orderkey
+ ) a
+ where
+ (count_suppkey > 1) or ((count_suppkey=1) and (l_suppkey <> max_suppkey))
+ ) l3 on l3.l_orderkey = t2.l_orderkey
+ ) b
+ where
+ (count_suppkey is null) or ((count_suppkey=1) and (l_suppkey = max_suppkey))
+ )c
+group by s_name
+order by numwait desc, s_name
+limit 100;
diff --git a/hivesterix/resource/tpch/q22_global_sales_opportunity.hive b/hivesterix/resource/tpch/q22_global_sales_opportunity.hive
new file mode 100644
index 0000000..379cfc9
--- /dev/null
+++ b/hivesterix/resource/tpch/q22_global_sales_opportunity.hive
@@ -0,0 +1,70 @@
+DROP TABLE customer;
+DROP TABLE orders;
+DROP TABLE q22_customer_tmp;
+DROP TABLE q22_customer_tmp1;
+DROP TABLE q22_orders_tmp;
+DROP TABLE q22_global_sales_opportunity;
+
+-- create tables and load data
+create external table customer (C_CUSTKEY INT, C_NAME STRING, C_ADDRESS STRING, C_NATIONKEY INT, C_PHONE STRING, C_ACCTBAL DOUBLE, C_MKTSEGMENT STRING, C_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/customer';
+create external table orders (O_ORDERKEY INT, O_CUSTKEY INT, O_ORDERSTATUS STRING, O_TOTALPRICE DOUBLE, O_ORDERDATE STRING, O_ORDERPRIORITY STRING, O_CLERK STRING, O_SHIPPRIORITY INT, O_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/orders';
+
+-- create target tables
+create table q22_customer_tmp(c_acctbal double, c_custkey int, cntrycode string);
+create table q22_customer_tmp1(avg_acctbal double);
+create table q22_orders_tmp(o_custkey int);
+create table q22_global_sales_opportunity(cntrycode string, numcust int, totacctbal double);
+
+-- the query
+insert overwrite table q22_customer_tmp
+select
+ c_acctbal, c_custkey, substr(c_phone, 1, 2) as cntrycode
+from
+ customer
+where
+ substr(c_phone, 1, 2) = '13' or
+ substr(c_phone, 1, 2) = '31' or
+ substr(c_phone, 1, 2) = '23' or
+ substr(c_phone, 1, 2) = '29' or
+ substr(c_phone, 1, 2) = '30' or
+ substr(c_phone, 1, 2) = '18' or
+ substr(c_phone, 1, 2) = '17';
+
+insert overwrite table q22_customer_tmp1
+select
+ avg(c_acctbal)
+from
+ q22_customer_tmp
+where
+ c_acctbal > 0.00;
+
+insert overwrite table q22_orders_tmp
+select
+ o_custkey
+from
+ orders
+group by
+ o_custkey;
+
+insert overwrite table q22_global_sales_opportunity
+select
+ cntrycode, count(1) as numcust, sum(c_acctbal) as totacctbal
+from
+(
+ select cntrycode, c_acctbal, avg_acctbal from
+ q22_customer_tmp1 ct1 join
+ (
+ select cntrycode, c_acctbal from
+ q22_orders_tmp ot
+ right outer join q22_customer_tmp ct
+ on
+ ct.c_custkey = ot.o_custkey
+ where
+ o_custkey is null
+ ) ct2
+) a
+where
+ c_acctbal > avg_acctbal
+group by cntrycode
+order by cntrycode;
+
diff --git a/hivesterix/resource/tpch/q2_copy.hive b/hivesterix/resource/tpch/q2_copy.hive
new file mode 100644
index 0000000..647d500
--- /dev/null
+++ b/hivesterix/resource/tpch/q2_copy.hive
@@ -0,0 +1,46 @@
+DROP TABLE part;
+DROP TABLE supplier;
+DROP TABLE partsupp;
+DROP TABLE nation;
+DROP TABLE region;
+DROP TABLE q2_minimum_cost_supplier;
+DROP TABLE q2_minimum_cost_supplier_tmp1;
+DROP TABLE q2_minimum_cost_supplier_tmp2;
+
+-- create the tables and load the data
+create external table part (P_PARTKEY INT, P_NAME STRING, P_MFGR STRING, P_BRAND STRING, P_TYPE STRING, P_SIZE INT, P_CONTAINER STRING, P_RETAILPRICE DOUBLE, P_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/part';
+create external table supplier (S_SUPPKEY INT, S_NAME STRING, S_ADDRESS STRING, S_NATIONKEY INT, S_PHONE STRING, S_ACCTBAL DOUBLE, S_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/supplier';
+create external table partsupp (PS_PARTKEY INT, PS_SUPPKEY INT, PS_AVAILQTY INT, PS_SUPPLYCOST DOUBLE, PS_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION'/tpch/10/partsupp';
+create external table nation (N_NATIONKEY INT, N_NAME STRING, N_REGIONKEY INT, N_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/nation';
+create external table region (R_REGIONKEY INT, R_NAME STRING, R_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/region';
+
+-- create result tables
+create table q2_minimum_cost_supplier_tmp1 (s_acctbal double, s_name string, n_name string, p_partkey int, ps_supplycost double, p_mfgr string, s_address string, s_phone string, s_comment string);
+create table q2_minimum_cost_supplier_tmp2 (p_partkey int, ps_min_supplycost double);
+create table q2_minimum_cost_supplier (s_acctbal double, s_name string, n_name string, p_partkey int, p_mfgr string, s_address string, s_phone string, s_comment string);
+
+-- the query
+insert overwrite table q2_minimum_cost_supplier_tmp1
+select
+ s.s_acctbal, s.s_name, n.n_name, p.p_partkey, ps.ps_supplycost, p.p_mfgr, s.s_address, s.s_phone, s.s_comment
+from
+ nation n join region r
+ on
+ n.n_regionkey = r.r_regionkey and r.r_name = 'EUROPE'
+ join supplier s
+ on
+s.s_nationkey = n.n_nationkey
+ join partsupp ps
+ on
+s.s_suppkey = ps.ps_suppkey
+ join part p
+ on
+ p.p_partkey = ps.ps_partkey and p.p_size = 15 ;
+
+-- explain insert overwrite table q2_minimum_cost_supplier_tmp2
+-- select
+-- p_partkey, min(ps_supplycost)
+-- from
+-- q2_minimum_cost_supplier_tmp1
+-- group by p_partkey
+
diff --git a/hivesterix/resource/tpch/q2_minimum_cost_supplier.hive b/hivesterix/resource/tpch/q2_minimum_cost_supplier.hive
new file mode 100644
index 0000000..7a68ee2
--- /dev/null
+++ b/hivesterix/resource/tpch/q2_minimum_cost_supplier.hive
@@ -0,0 +1,56 @@
+DROP TABLE part;
+DROP TABLE supplier;
+DROP TABLE partsupp;
+DROP TABLE nation;
+DROP TABLE region;
+DROP TABLE q2_minimum_cost_supplier;
+DROP TABLE q2_minimum_cost_supplier_tmp1;
+DROP TABLE q2_minimum_cost_supplier_tmp2;
+
+-- create the tables and load the data
+create external table part (P_PARTKEY INT, P_NAME STRING, P_MFGR STRING, P_BRAND STRING, P_TYPE STRING, P_SIZE INT, P_CONTAINER STRING, P_RETAILPRICE DOUBLE, P_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/part';
+create external table supplier (S_SUPPKEY INT, S_NAME STRING, S_ADDRESS STRING, S_NATIONKEY INT, S_PHONE STRING, S_ACCTBAL DOUBLE, S_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/supplier';
+create external table partsupp (PS_PARTKEY INT, PS_SUPPKEY INT, PS_AVAILQTY INT, PS_SUPPLYCOST DOUBLE, PS_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION'/tpch/10/partsupp';
+create external table nation (N_NATIONKEY INT, N_NAME STRING, N_REGIONKEY INT, N_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/nation';
+create external table region (R_REGIONKEY INT, R_NAME STRING, R_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/region';
+
+-- create result tables
+create table q2_minimum_cost_supplier_tmp1 (s_acctbal double, s_name string, n_name string, p_partkey int, ps_supplycost double, p_mfgr string, s_address string, s_phone string, s_comment string);
+create table q2_minimum_cost_supplier_tmp2 (p_partkey int, ps_min_supplycost double);
+create table q2_minimum_cost_supplier (s_acctbal double, s_name string, n_name string, p_partkey int, p_mfgr string, s_address string, s_phone string, s_comment string);
+
+-- the query
+insert overwrite table q2_minimum_cost_supplier_tmp1
+select
+ s.s_acctbal, s.s_name, n.n_name, p.p_partkey, ps.ps_supplycost, p.p_mfgr, s.s_address, s.s_phone, s.s_comment
+from
+ nation n join region r
+ on
+ n.n_regionkey = r.r_regionkey and r.r_name = 'EUROPE'
+ join supplier s
+ on
+s.s_nationkey = n.n_nationkey
+ join partsupp ps
+ on
+s.s_suppkey = ps.ps_suppkey
+ join part p
+ on
+ p.p_partkey = ps.ps_partkey and p.p_size = 15 and p.p_type like '%BRASS' ;
+
+insert overwrite table q2_minimum_cost_supplier_tmp2
+select
+ p_partkey, min(ps_supplycost)
+from
+ q2_minimum_cost_supplier_tmp1
+group by p_partkey;
+
+insert overwrite table q2_minimum_cost_supplier
+select
+ t1.s_acctbal, t1.s_name, t1.n_name, t1.p_partkey, t1.p_mfgr, t1.s_address, t1.s_phone, t1.s_comment
+from
+ q2_minimum_cost_supplier_tmp1 t1 join q2_minimum_cost_supplier_tmp2 t2
+on
+ t1.p_partkey = t2.p_partkey and t1.ps_supplycost=t2.ps_min_supplycost
+order by s_acctbal desc, n_name, s_name, p_partkey
+limit 100;
+
diff --git a/hivesterix/resource/tpch/q3_shipping_priority.hive b/hivesterix/resource/tpch/q3_shipping_priority.hive
new file mode 100644
index 0000000..888775e
--- /dev/null
+++ b/hivesterix/resource/tpch/q3_shipping_priority.hive
@@ -0,0 +1,30 @@
+DROP TABLE orders;
+DROP TABLE lineitem;
+DROP TABLE customer;
+DROP TABLE q3_shipping_priority;
+
+-- create tables and load data
+Create external table lineitem (L_ORDERKEY INT, L_PARTKEY INT, L_SUPPKEY INT, L_LINENUMBER INT, L_QUANTITY DOUBLE, L_EXTENDEDPRICE DOUBLE, L_DISCOUNT DOUBLE, L_TAX DOUBLE, L_RETURNFLAG STRING, L_LINESTATUS STRING, L_SHIPDATE STRING, L_COMMITDATE STRING, L_RECEIPTDATE STRING, L_SHIPINSTRUCT STRING, L_SHIPMODE STRING, L_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/lineitem';
+create external table orders (O_ORDERKEY INT, O_CUSTKEY INT, O_ORDERSTATUS STRING, O_TOTALPRICE DOUBLE, O_ORDERDATE STRING, O_ORDERPRIORITY STRING, O_CLERK STRING, O_SHIPPRIORITY INT, O_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/orders';
+create external table customer (C_CUSTKEY INT, C_NAME STRING, C_ADDRESS STRING, C_NATIONKEY INT, C_PHONE STRING, C_ACCTBAL DOUBLE, C_MKTSEGMENT STRING, C_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/customer';
+
+-- create the target table
+create table q3_shipping_priority (l_orderkey int, revenue double, o_orderdate string, o_shippriority int);
+
+set mapred.min.split.size=536870912;
+set hive.exec.reducers.bytes.per.reducer=1024000000;
+
+-- the query
+Insert overwrite table q3_shipping_priority
+select
+ l_orderkey, sum(l_extendedprice*(1-l_discount)) as revenue, o_orderdate, o_shippriority
+from
+ customer c join orders o
+ on c.c_mktsegment = 'BUILDING' and c.c_custkey = o.o_custkey
+ join lineitem l
+ on l.l_orderkey = o.o_orderkey
+where
+ o_orderdate < '1995-03-15' and l_shipdate > '1995-03-15'
+group by l_orderkey, o_orderdate, o_shippriority
+order by revenue desc, o_orderdate
+limit 10;
diff --git a/hivesterix/resource/tpch/q4_order_priority.hive b/hivesterix/resource/tpch/q4_order_priority.hive
new file mode 100644
index 0000000..18c8d9d
--- /dev/null
+++ b/hivesterix/resource/tpch/q4_order_priority.hive
@@ -0,0 +1,30 @@
+DROP TABLE orders;
+DROP TABLE lineitem;
+DROP TABLE q4_order_priority_tmp;
+DROP TABLE q4_order_priority;
+
+-- create tables and load data
+create external table orders (O_ORDERKEY INT, O_CUSTKEY INT, O_ORDERSTATUS STRING, O_TOTALPRICE DOUBLE, O_ORDERDATE STRING, O_ORDERPRIORITY STRING, O_CLERK STRING, O_SHIPPRIORITY INT, O_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/orders';
+Create external table lineitem (L_ORDERKEY INT, L_PARTKEY INT, L_SUPPKEY INT, L_LINENUMBER INT, L_QUANTITY DOUBLE, L_EXTENDEDPRICE DOUBLE, L_DISCOUNT DOUBLE, L_TAX DOUBLE, L_RETURNFLAG STRING, L_LINESTATUS STRING, L_SHIPDATE STRING, L_COMMITDATE STRING, L_RECEIPTDATE STRING, L_SHIPINSTRUCT STRING, L_SHIPMODE STRING, L_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/lineitem';
+
+-- create the target table
+CREATE TABLE q4_order_priority_tmp (O_ORDERKEY INT);
+CREATE TABLE q4_order_priority (O_ORDERPRIORITY STRING, ORDER_COUNT INT);
+
+set mapred.min.split.size=536870912;
+-- the query
+INSERT OVERWRITE TABLE q4_order_priority_tmp
+select
+ DISTINCT l_orderkey
+from
+ lineitem
+where
+ l_commitdate < l_receiptdate;
+INSERT OVERWRITE TABLE q4_order_priority
+select o_orderpriority, count(1) as order_count
+from
+ orders o join q4_order_priority_tmp t
+ on
+o.o_orderkey = t.o_orderkey and o.o_orderdate >= '1993-07-01' and o.o_orderdate < '1993-10-01'
+group by o_orderpriority
+order by o_orderpriority;
diff --git a/hivesterix/resource/tpch/q5_local_supplier_volume.hive b/hivesterix/resource/tpch/q5_local_supplier_volume.hive
new file mode 100644
index 0000000..f5b10d8
--- /dev/null
+++ b/hivesterix/resource/tpch/q5_local_supplier_volume.hive
@@ -0,0 +1,42 @@
+DROP TABLE customer;
+DROP TABLE orders;
+DROP TABLE lineitem;
+DROP TABLE supplier;
+DROP TABLE nation;
+DROP TABLE region;
+DROP TABLE q5_local_supplier_volume;
+
+-- create tables and load data
+create external table customer (C_CUSTKEY INT, C_NAME STRING, C_ADDRESS STRING, C_NATIONKEY INT, C_PHONE STRING, C_ACCTBAL DOUBLE, C_MKTSEGMENT STRING, C_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/customer';
+Create external table lineitem (L_ORDERKEY INT, L_PARTKEY INT, L_SUPPKEY INT, L_LINENUMBER INT, L_QUANTITY DOUBLE, L_EXTENDEDPRICE DOUBLE, L_DISCOUNT DOUBLE, L_TAX DOUBLE, L_RETURNFLAG STRING, L_LINESTATUS STRING, L_SHIPDATE STRING, L_COMMITDATE STRING, L_RECEIPTDATE STRING, L_SHIPINSTRUCT STRING, L_SHIPMODE STRING, L_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/lineitem';
+create external table orders (O_ORDERKEY INT, O_CUSTKEY INT, O_ORDERSTATUS STRING, O_TOTALPRICE DOUBLE, O_ORDERDATE STRING, O_ORDERPRIORITY STRING, O_CLERK STRING, O_SHIPPRIORITY INT, O_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/orders';
+create external table supplier (S_SUPPKEY INT, S_NAME STRING, S_ADDRESS STRING, S_NATIONKEY INT, S_PHONE STRING, S_ACCTBAL DOUBLE, S_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/supplier';
+create external table nation (N_NATIONKEY INT, N_NAME STRING, N_REGIONKEY INT, N_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/nation';
+create external table region (R_REGIONKEY INT, R_NAME STRING, R_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/region';
+
+-- create the target table
+create table q5_local_supplier_volume (N_NAME STRING, REVENUE DOUBLE);
+
+set mapred.min.split.size=536870912;
+
+-- the query
+insert overwrite table q5_local_supplier_volume
+select
+ n_name, sum(l_extendedprice * (1 - l_discount)) as revenue
+from
+ customer c join
+ ( select n_name, l_extendedprice, l_discount, s_nationkey, o_custkey from orders o join
+ ( select n_name, l_extendedprice, l_discount, l_orderkey, s_nationkey from lineitem l join
+ ( select n_name, s_suppkey, s_nationkey from supplier s join
+ ( select n_name, n_nationkey
+ from nation n join region r
+ on n.n_regionkey = r.r_regionkey and r.r_name = 'ASIA'
+ ) n1 on s.s_nationkey = n1.n_nationkey
+ ) s1 on l.l_suppkey = s1.s_suppkey
+ ) l1 on l1.l_orderkey = o.o_orderkey and o.o_orderdate >= '1994-01-01'
+ and o.o_orderdate < '1995-01-01'
+) o1
+on c.c_nationkey = o1.s_nationkey and c.c_custkey = o1.o_custkey
+group by n_name
+order by revenue desc;
+
diff --git a/hivesterix/resource/tpch/q6_forecast_revenue_change.hive b/hivesterix/resource/tpch/q6_forecast_revenue_change.hive
new file mode 100644
index 0000000..72900c7
--- /dev/null
+++ b/hivesterix/resource/tpch/q6_forecast_revenue_change.hive
@@ -0,0 +1,21 @@
+DROP TABLE lineitem;
+DROP TABLE q6_forecast_revenue_change;
+
+-- create tables and load data
+create external table lineitem (L_ORDERKEY INT, L_PARTKEY INT, L_SUPPKEY INT, L_LINENUMBER INT, L_QUANTITY DOUBLE, L_EXTENDEDPRICE DOUBLE, L_DISCOUNT DOUBLE, L_TAX DOUBLE, L_RETURNFLAG STRING, L_LINESTATUS STRING, L_SHIPDATE STRING, L_COMMITDATE STRING, L_RECEIPTDATE STRING, L_SHIPINSTRUCT STRING, L_SHIPMODE STRING, L_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/lineitem';
+
+-- create the target table
+create table q6_forecast_revenue_change (revenue double);
+
+-- the query
+insert overwrite table q6_forecast_revenue_change
+select
+ sum(l_extendedprice*l_discount) as revenue
+from
+ lineitem
+where
+ l_shipdate >= '1994-01-01'
+ and l_shipdate < '1995-01-01'
+ and l_discount >= 0.05 and l_discount <= 0.07
+ and l_quantity < 24;
+
diff --git a/hivesterix/resource/tpch/q7_volume_shipping.hive b/hivesterix/resource/tpch/q7_volume_shipping.hive
new file mode 100644
index 0000000..da6eab2
--- /dev/null
+++ b/hivesterix/resource/tpch/q7_volume_shipping.hive
@@ -0,0 +1,71 @@
+DROP TABLE customer;
+DROP TABLE orders;
+DROP TABLE lineitem;
+DROP TABLE supplier;
+DROP TABLE nation;
+DROP TABLE q7_volume_shipping;
+DROP TABLE q7_volume_shipping_tmp;
+
+-- create tables and load data
+create external table customer (C_CUSTKEY INT, C_NAME STRING, C_ADDRESS STRING, C_NATIONKEY INT, C_PHONE STRING, C_ACCTBAL DOUBLE, C_MKTSEGMENT STRING, C_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/customer';
+Create external table lineitem (L_ORDERKEY INT, L_PARTKEY INT, L_SUPPKEY INT, L_LINENUMBER INT, L_QUANTITY DOUBLE, L_EXTENDEDPRICE DOUBLE, L_DISCOUNT DOUBLE, L_TAX DOUBLE, L_RETURNFLAG STRING, L_LINESTATUS STRING, L_SHIPDATE STRING, L_COMMITDATE STRING, L_RECEIPTDATE STRING, L_SHIPINSTRUCT STRING, L_SHIPMODE STRING, L_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/lineitem';
+create external table orders (O_ORDERKEY INT, O_CUSTKEY INT, O_ORDERSTATUS STRING, O_TOTALPRICE DOUBLE, O_ORDERDATE STRING, O_ORDERPRIORITY STRING, O_CLERK STRING, O_SHIPPRIORITY INT, O_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/orders';
+create external table supplier (S_SUPPKEY INT, S_NAME STRING, S_ADDRESS STRING, S_NATIONKEY INT, S_PHONE STRING, S_ACCTBAL DOUBLE, S_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/supplier';
+create external table nation (N_NATIONKEY INT, N_NAME STRING, N_REGIONKEY INT, N_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/nation';
+
+-- create the target table
+create table q7_volume_shipping (supp_nation string, cust_nation string, l_year int, revenue double);
+create table q7_volume_shipping_tmp(supp_nation string, cust_nation string, s_nationkey int, c_nationkey int);
+
+set mapred.min.split.size=536870912;
+set hive.exec.reducers.bytes.per.reducer=1225000000;
+
+-- the query
+insert overwrite table q7_volume_shipping_tmp
+select
+ *
+from
+ (
+ select
+ n1.n_name as supp_nation, n2.n_name as cust_nation, n1.n_nationkey as s_nationkey,
+ n2.n_nationkey as c_nationkey
+from
+ nation n1 join nation n2
+ on
+ n1.n_name = 'FRANCE' and n2.n_name = 'GERMANY'
+ UNION ALL
+select
+ n1.n_name as supp_nation, n2.n_name as cust_nation, n1.n_nationkey as s_nationkey,
+ n2.n_nationkey as c_nationkey
+from
+ nation n1 join nation n2
+ on
+ n2.n_name = 'FRANCE' and n1.n_name = 'GERMANY'
+) a;
+
+insert overwrite table q7_volume_shipping
+select
+ supp_nation, cust_nation, l_year, sum(volume) as revenue
+from
+ (
+ select
+ supp_nation, cust_nation, year(l_shipdate) as l_year,
+ l_extendedprice * (1 - l_discount) as volume
+ from
+ q7_volume_shipping_tmp t join
+ (select l_shipdate, l_extendedprice, l_discount, c_nationkey, s_nationkey
+ from supplier s join
+ (select l_shipdate, l_extendedprice, l_discount, l_suppkey, c_nationkey
+ from customer c join
+ (select l_shipdate, l_extendedprice, l_discount, l_suppkey, o_custkey
+ from orders o join lineitem l
+ on
+ o.o_orderkey = l.l_orderkey and l.l_shipdate >= '1995-01-01'
+ and l.l_shipdate <= '1996-12-31'
+ ) l1 on c.c_custkey = l1.o_custkey
+ ) l2 on s.s_suppkey = l2.l_suppkey
+ ) l3 on l3.c_nationkey = t.c_nationkey and l3.s_nationkey = t.s_nationkey
+ ) shipping
+group by supp_nation, cust_nation, l_year
+order by supp_nation, cust_nation, l_year;
+
diff --git a/hivesterix/resource/tpch/q8_national_market_share.hive b/hivesterix/resource/tpch/q8_national_market_share.hive
new file mode 100644
index 0000000..ae2abec
--- /dev/null
+++ b/hivesterix/resource/tpch/q8_national_market_share.hive
@@ -0,0 +1,56 @@
+DROP TABLE customer;
+DROP TABLE orders;
+DROP TABLE lineitem;
+DROP TABLE supplier;
+DROP TABLE nation;
+DROP TABLE region;
+DROP TABLE part;
+DROP TABLE q8_national_market_share;
+
+-- create the tables and load the data
+create external table part (P_PARTKEY INT, P_NAME STRING, P_MFGR STRING, P_BRAND STRING, P_TYPE STRING, P_SIZE INT, P_CONTAINER STRING, P_RETAILPRICE DOUBLE, P_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/part';
+create external table customer (C_CUSTKEY INT, C_NAME STRING, C_ADDRESS STRING, C_NATIONKEY INT, C_PHONE STRING, C_ACCTBAL DOUBLE, C_MKTSEGMENT STRING, C_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/customer';
+Create external table lineitem (L_ORDERKEY INT, L_PARTKEY INT, L_SUPPKEY INT, L_LINENUMBER INT, L_QUANTITY DOUBLE, L_EXTENDEDPRICE DOUBLE, L_DISCOUNT DOUBLE, L_TAX DOUBLE, L_RETURNFLAG STRING, L_LINESTATUS STRING, L_SHIPDATE STRING, L_COMMITDATE STRING, L_RECEIPTDATE STRING, L_SHIPINSTRUCT STRING, L_SHIPMODE STRING, L_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/lineitem';
+create external table orders (O_ORDERKEY INT, O_CUSTKEY INT, O_ORDERSTATUS STRING, O_TOTALPRICE DOUBLE, O_ORDERDATE STRING, O_ORDERPRIORITY STRING, O_CLERK STRING, O_SHIPPRIORITY INT, O_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/orders';
+create external table supplier (S_SUPPKEY INT, S_NAME STRING, S_ADDRESS STRING, S_NATIONKEY INT, S_PHONE STRING, S_ACCTBAL DOUBLE, S_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/supplier';
+create external table nation (N_NATIONKEY INT, N_NAME STRING, N_REGIONKEY INT, N_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/nation';
+create external table region (R_REGIONKEY INT, R_NAME STRING, R_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/region';
+
+-- create the result table
+create table q8_national_market_share(o_year string, mkt_share double);
+
+-- the query
+insert overwrite table q8_national_market_share
+select
+ o_year, sum(case when nation = 'BRAZIL' then volume else 0.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
+ nation n2 join
+ (select o_orderdate, l_discount, l_extendedprice, s_nationkey
+ from supplier s join
+ (select o_orderdate, l_discount, l_extendedprice, l_suppkey
+ from part p join
+ (select o_orderdate, l_partkey, l_discount, l_extendedprice, l_suppkey
+ from lineitem l join
+ (select o_orderdate, o_orderkey
+ from orders o join
+ (select c.c_custkey
+ from customer c join
+ (select n1.n_nationkey
+ from nation n1 join region r
+ on n1.n_regionkey = r.r_regionkey and r.r_name = 'AMERICA'
+ ) n11 on c.c_nationkey = n11.n_nationkey
+ ) c1 on c1.c_custkey = o.o_custkey
+ ) o1 on l.l_orderkey = o1.o_orderkey and o1.o_orderdate >= '1995-01-01'
+ and o1.o_orderdate < '1996-12-31'
+ ) l1 on p.p_partkey = l1.l_partkey and p.p_type = 'ECONOMY ANODIZED STEEL'
+ ) p1 on s.s_suppkey = p1.l_suppkey
+ ) s1 on s1.s_nationkey = n2.n_nationkey
+ ) all_nation
+group by o_year
+order by o_year;
+
diff --git a/hivesterix/resource/tpch/q9_product_type_profit.hive b/hivesterix/resource/tpch/q9_product_type_profit.hive
new file mode 100644
index 0000000..bc8ba3f
--- /dev/null
+++ b/hivesterix/resource/tpch/q9_product_type_profit.hive
@@ -0,0 +1,51 @@
+DROP TABLE part;
+DROP TABLE lineitem;
+DROP TABLE supplier;
+DROP TABLE orders;
+DROP TABLE partsupp;
+DROP TABLE nation;
+DROP TABLE q9_product_type_profit;
+
+-- create the tables and load the data
+create external table part (P_PARTKEY INT, P_NAME STRING, P_MFGR STRING, P_BRAND STRING, P_TYPE STRING, P_SIZE INT, P_CONTAINER STRING, P_RETAILPRICE DOUBLE, P_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/part';
+Create external table lineitem (L_ORDERKEY INT, L_PARTKEY INT, L_SUPPKEY INT, L_LINENUMBER INT, L_QUANTITY DOUBLE, L_EXTENDEDPRICE DOUBLE, L_DISCOUNT DOUBLE, L_TAX DOUBLE, L_RETURNFLAG STRING, L_LINESTATUS STRING, L_SHIPDATE STRING, L_COMMITDATE STRING, L_RECEIPTDATE STRING, L_SHIPINSTRUCT STRING, L_SHIPMODE STRING, L_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/lineitem';
+create external table orders (O_ORDERKEY INT, O_CUSTKEY INT, O_ORDERSTATUS STRING, O_TOTALPRICE DOUBLE, O_ORDERDATE STRING, O_ORDERPRIORITY STRING, O_CLERK STRING, O_SHIPPRIORITY INT, O_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/orders';
+create external table supplier (S_SUPPKEY INT, S_NAME STRING, S_ADDRESS STRING, S_NATIONKEY INT, S_PHONE STRING, S_ACCTBAL DOUBLE, S_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/supplier';
+create external table partsupp (PS_PARTKEY INT, PS_SUPPKEY INT, PS_AVAILQTY INT, PS_SUPPLYCOST DOUBLE, PS_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION'/tpch/10/partsupp';
+create external table nation (N_NATIONKEY INT, N_NAME STRING, N_REGIONKEY INT, N_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/nation';
+
+-- create the result table
+create table q9_product_type_profit (nation string, o_year string, sum_profit double);
+
+set mapred.min.split.size=536870912;
+set hive.exec.reducers.bytes.per.reducer=1024000000;
+
+-- the query
+insert overwrite table q9_product_type_profit
+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
+ orders o join
+ (select l_extendedprice, l_discount, l_quantity, l_orderkey, n_name, ps_supplycost
+ from part p join
+ (select l_extendedprice, l_discount, l_quantity, l_partkey, l_orderkey,
+ n_name, ps_supplycost
+ from partsupp ps join
+ (select l_suppkey, l_extendedprice, l_discount, l_quantity, l_partkey,
+ l_orderkey, n_name
+ from
+ (select s_suppkey, n_name
+ from nation n join supplier s on n.n_nationkey = s.s_nationkey
+ ) s1 join lineitem l on s1.s_suppkey = l.l_suppkey
+ ) l1 on ps.ps_suppkey = l1.l_suppkey and ps.ps_partkey = l1.l_partkey
+ ) l2 on p.p_name like '%green%' and p.p_partkey = l2.l_partkey
+ ) l3 on o.o_orderkey = l3.l_orderkey
+ )profit
+group by nation, o_year
+order by nation, o_year desc;
+