rules.tpch.spark.txt

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_sub('1998-12-01',61) group by l_returnflag,  l_linestatus order by l_returnflag,  l_linestatus
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 (select l_quantity,  l_extendedprice,  l_discount,  l_tax,  l_returnflag,  l_linestatus  from lineitem  where l_shipdate IS NOT NULL  and l_shipdate <= date_sub('1998-12-01',61)  ) AS SA 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  part,  supplier,  partsupp,  nation,  region  WHERE  p_partkey = ps_partkey  AND s_suppkey = ps_suppkey  AND p_size = 3  AND p_type LIKE '%COPPER'  AND s_nationkey = n_nationkey  AND n_regionkey = r_regionkey  AND r_name = 'EUROPE'  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 = 'EUROPE'  )  ORDER BY  s_acctbal DESC,  n_name,  s_name,  p_partkey LIMIT 100
SELECT s_acctbal,  s_name,  n_name,  p_partkey,  p_mfgr,  s_address,  s_phone,  s_comment FROM (SELECT p_partkey,  p_mfgr,  s_name,  s_address,  s_phone,  s_acctbal,  s_comment,  n_name,  n_regionkey  FROM (SELECT p_partkey,  p_mfgr,  s_name,  s_address,  s_nationkey,  s_phone,  s_acctbal,  s_comment  FROM (SELECT p_partkey,  p_mfgr,  ps_suppkey  FROM (SELECT p_partkey,  p_mfgr,  ps_suppkey,  ps_supplycost  FROM (SELECT p_partkey,  p_mfgr  FROM part  WHERE (p_size IS NOT NULL AND p_type IS NOT NULL) AND  (p_size = 3 AND p_type LIKE '%COPPER') AND p_partkey IS NOT NULL  ) AS SJ  JOIN (SELECT ps_partkey,  ps_suppkey,  ps_supplycost  FROM partsupp  WHERE ps_partkey IS NOT NULL AND ps_supplycost IS NOT NULL   AND ps_suppkey IS NOT NULL  ) AS SK  ON SJ.p_partkey = SK.ps_partkey  ) AS SG  JOIN  (SELECT ps_partkey, MIN(ps_supplycost) AS T  FROM (SELECT ps_partkey,  ps_supplycost  FROM (SELECT ps_partkey,  ps_supplycost,  n_regionkey  FROM (SELECT ps_partkey,  ps_supplycost,  s_nationkey  FROM (SELECT ps_partkey,  ps_suppkey,  ps_supplycost  FROM partsupp  WHERE ps_suppkey IS NOT NULL AND ps_partkey IS NOT NULL   ) AS SP  JOIN (SELECT s_suppkey,  s_nationkey  FROM supplier  WHERE s_suppkey IS NOT NULL AND s_nationkey IS NOT NULL  ) AS SQ  ON SQ.s_suppkey = SP.ps_suppkey   ) AS SN  JOIN (SELECT n_nationkey,  n_regionkey  FROM nation  WHERE n_nationkey IS NOT NULL AND n_regionkey IS NOT NULL  ) AS SO  ON SN.s_nationkey = SO.n_nationkey  ) AS SL  JOIN (SELECT r_regionkey  FROM region  WHERE (r_name IS NOT NULL AND r_name = 'EUROPE') AND r_regionkey IS NOT NULL  ) AS SM  ON SL.n_regionkey = SM.r_regionkey   )  GROUP BY ps_partkey  HAVING MIN(ps_supplycost) IS NOT NULL  ) AS SI  ON SG.p_partkey = SI.ps_partkey AND (SG.ps_supplycost = SI.T)  ) AS SE  JOIN  (SELECT *  FROM supplier  WHERE (s_suppkey IS NOT NULL AND s_nationkey IS NOT NULL)  ) AS SF  ON SF.s_suppkey = SE.ps_suppkey  ) AS SC  JOIN  (SELECT n_nationkey,  n_name,  n_regionkey  FROM nation  WHERE (n_nationkey IS NOT NULL AND n_regionkey IS NOT NULL)  ) AS SD  ON SC.s_nationkey = SD.n_nationkey  ) AS SA  JOIN  (SELECT r_regionkey  FROM region  WHERE (r_name IS NOT NULL AND r_name = 'EUROPE') AND r_regionkey IS NOT NULL) AS SB  ON SA.n_regionkey = SB.r_regionkey ORDER BY s_acctbal DESC,  n_name,  s_name,  p_partkey LIMIT 100
select l_orderkey,  sum(l_extendedprice * (1 - l_discount)) as revenue,  o_orderdate,  o_shippriority from customer,  orders,  lineitem where c_mktsegment = 'BUILDING'  and c_custkey = o_custkey  and l_orderkey = o_orderkey  and o_orderdate < date('1995-03-21 +08')  and l_shipdate > date('1995-03-21 +08') group by l_orderkey,  o_orderdate,  o_shippriority order by revenue desc,  o_orderdate limit 10
select l_orderkey,  sum(l_extendedprice * (1 - l_discount)) as revenue,  o_orderdate,  o_shippriority from (select o_orderdate,  o_shippriority,  l_orderkey,  l_extendedprice,  l_discount  from (select o_orderkey,  o_orderdate,  o_shippriority  from (select c_custkey  from customer  where c_mktsegment IS NOT NULL and c_mktsegment = 'BUILDING' and c_custkey IS NOT NULL  ) as SC  join (select o_orderkey,  o_custkey,  o_orderdate,  o_shippriority  from orders  where o_orderdate IS NOT NULL AND o_orderdate < date('1995-03-21 +08') and o_custkey IS NOT NULL and o_orderkey IS NOT NULL  ) as SD  on SC.c_custkey = SD.o_custkey  ) as SA  join (select l_orderkey,  l_extendedprice,  l_discount  from lineitem  where l_shipdate IS NOT NULL and l_shipdate > date('1995-03-21 +08') and l_orderkey IS NOT NULL  ) as SB  on SB.l_orderkey = SA.o_orderkey  ) as T group by l_orderkey,  o_orderdate,  o_shippriority order by revenue desc,  o_orderdate limit 10
select o_orderpriority,  count(*) as order_count from orders where o_orderdate >= date('1994-09-01 +08')  and o_orderdate < date('1994-09-01 +08') + 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
select o_orderpriority,  count(*) as order_count from (select o_orderpriority  from (select o_orderkey,  o_orderpriority  from orders  where o_orderdate IS NOT NULL  and o_orderdate >= date('1994-09-01 +08')  and o_orderdate < date('1994-12-01 +08')  ) AS SB  left semi join (select l_orderkey  from lineitem  where l_commitdate IS NOT NULL  and l_receiptdate IS NOT NULL  and l_commitdate < l_receiptdate  ) AS SC  on SC.l_orderkey = SB.o_orderkey  ) AS SA group by o_orderpriority order by o_orderpriority
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 = 'AMERICA'  and o_orderdate >= date('1994-01-01 +08')  and o_orderdate < date('1994-01-01 +08') + interval '1' year group by n_name order by revenue desc
select n_name,  sum(l_extendedprice * (1 - l_discount)) as revenue from (select l_extendedprice,  l_discount,  n_name  from (select l_extendedprice,  l_discount,  n_name,  n_regionkey  from (select l_extendedprice,  l_discount,  s_nationkey  from (select c_nationkey,  l_suppkey,  l_extendedprice,  l_discount  from (select c_nationkey,  o_orderkey  from (select c_custkey,  c_nationkey  from customer  where c_custkey IS NOT NULL and c_nationkey IS NOT NULL  ) as SJ  join (select o_orderkey,  o_custkey  from orders  where o_orderdate IS NOT NULL and o_orderdate >= date('1994-01-01 +08') and o_orderdate < date('1995-01-01 +08')  and o_orderkey IS NOT NULL and o_custkey IS NOT NULL  ) as SK   on SJ.c_custkey = SK.o_custkey  ) as SH  join (select l_orderkey,  l_suppkey,  l_extendedprice,  l_discount  from lineitem  where l_orderkey IS NOT NULL and l_suppkey IS NOT NULL  ) as SI  on SI.l_orderkey = SH.o_orderkey  ) as SF  join (select s_suppkey,  s_nationkey  from supplier  where s_suppkey IS NOT NULL and s_nationkey IS NOT NULL  ) as SG  on SF.l_suppkey = SG.s_suppkey and SF.c_nationkey = SG.s_nationkey  ) as SD  join (select n_nationkey,  n_name,  n_regionkey  from nation  where n_nationkey IS NOT NULL and n_regionkey IS NOT NULL  ) as SE   on SD.s_nationkey = SE.n_nationkey  ) as SB  join (select r_regionkey  from region  where (r_name IS NOT NULL and r_name = 'AMERICA') and r_regionkey IS NOT NULL  ) as SC  on SB.n_regionkey = SC.r_regionkey  ) as SA group by n_name order by revenue desc
select sum(l_extendedprice * l_discount) as revenue from lineitem where l_shipdate >= date('1994-01-01 +08')  and l_shipdate < date('1994-01-01 +08') + interval '1' year  and l_discount between 0.06 - 0.01 and 0.06 + 0.01  and l_quantity < 24
select sum(l_extendedprice * l_discount) as revenue from (select l_extendedprice,  l_discount  from lineitem  where l_shipdate IS NOT NULL   and l_discount IS NOT NULL  and l_quantity IS NOT NULL  and l_shipdate >= date('1994-01-01 +08')  and l_shipdate < date('1995-01-01 +08')  and l_discount >= 0.05  and l_discount <= 0.07  and l_quantity < 24  ) as SA
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 = 'RUSSIA' AND n2.n_name = 'KENYA')  OR  (n1.n_name = 'KENYA' AND n2.n_name = 'RUSSIA')  )  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 supp_nation,  cust_nation,  l_year,  SUM(volume) AS revenue FROM  (SELECT SA.n_name AS supp_nation,  SB.n_name AS cust_nation,  year(l_shipdate) AS l_year,  l_extendedprice * (1 - l_discount) AS volume  FROM (SELECT l_extendedprice,  l_discount,  l_shipdate,  c_nationkey,  n_name  FROM (SELECT s_nationkey,  l_extendedprice,  l_discount,  l_shipdate,  c_nationkey  FROM (SELECT s_nationkey,  l_extendedprice,  l_discount,  l_shipdate,  o_custkey  FROM (SELECT s_nationkey,  l_orderkey,  l_extendedprice,  l_discount,  l_shipdate  FROM (SELECT s_suppkey,  s_nationkey  FROM supplier  WHERE s_suppkey IS NOT NULL AND s_nationkey IS NOT NULL  ) AS SI  JOIN (SELECT l_orderkey,  l_suppkey,  l_extendedprice,  l_discount,  l_shipdate  FROM lineitem  WHERE l_shipdate IS NOT NULL AND (l_shipdate >= DATE '1995-01-01' AND l_shipdate <= DATE '1996-12-31'   AND l_suppkey IS NOT NULL AND l_orderkey IS NOT NULL)  ) AS SJ  ON SI.s_suppkey = SJ.l_suppkey  ) AS SG  JOIN (SELECT o_orderkey,  o_custkey  FROM orders  WHERE o_orderkey IS NOT NULL AND o_custkey IS NOT NULL  ) AS SH  ON SH.o_orderkey = SG.l_orderkey  ) AS SE  JOIN (SELECT c_custkey,  c_nationkey  FROM customer  WHERE c_custkey IS NOT NULL AND c_nationkey IS NOT NULL  ) AS SF  ON c_custkey = o_custkey   ) AS SC  JOIN (SELECT n_nationkey,  n_name  FROM nation  WHERE n_nationkey IS NOT NULL AND (n_name = 'RUSSIA' OR n_name = 'KENYA')  ) AS SD  ON SC.s_nationkey = SD.n_nationkey   ) AS SA  JOIN  (SELECT n_nationkey,  n_name  FROM nation  WHERE n_nationkey IS NOT NULL AND (n_name = 'KENYA' OR n_name = 'RUSSIA')  ) AS SB  ON SA.c_nationkey = SB.n_nationkey AND ((SA.n_name = 'RUSSIA' AND SB.n_name = 'KENYA')  OR (SA.n_name = 'KENYA' AND SB.n_name = 'RUSSIA'))  ) 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 = 'UNITED KINGDOM'  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 = 'EUROPE'  AND s_nationkey = n2.n_nationkey  AND o_orderdate BETWEEN DATE '1995-01-01' AND DATE '1996-12-31'  AND p_type = 'ECONOMY POLISHED BRASS'  )  AS all_nations GROUP BY  o_year ORDER BY  o_year
SELECT o_year,  SUM(CASE  WHEN nation = 'UNITED KINGDOM' 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,  SA.n_name AS nation  FROM (SELECT l_extendedprice,  l_discount,  o_orderdate,  n_regionkey,  n_name  FROM (SELECT l_extendedprice,  l_discount,  s_nationkey,  o_orderdate,  n_regionkey  FROM (SELECT l_extendedprice,  l_discount,  s_nationkey,  o_orderdate,  c_nationkey  FROM (SELECT l_extendedprice,  l_discount,  s_nationkey,  o_custkey,  o_orderdate  FROM (SELECT l_orderkey,  l_extendedprice,  l_discount,  s_nationkey  FROM (SELECT l_orderkey,  l_suppkey,  l_extendedprice,  l_discount  FROM (SELECT p_partkey  FROM part  WHERE p_type IS NOT NULL AND p_type = 'ECONOMY POLISHED BRASS' AND p_partkey IS NOT NULL  ) AS SM  JOIN (SELECT l_orderkey,  l_partkey,  l_suppkey,  l_extendedprice,  l_discount  FROM lineitem  WHERE l_partkey IS NOT NULL AND l_suppkey IS NOT NULL AND l_orderkey IS NOT NULL  ) AS SN  ON SM.p_partkey = SN.l_partkey  ) AS SK  JOIN (SELECT s_suppkey,  s_nationkey  FROM supplier  WHERE s_suppkey IS NOT NULL AND s_nationkey IS NOT NULL  ) AS SL  ON SL.s_suppkey = SK.l_suppkey  ) AS SI  JOIN (SELECT o_orderkey,  o_custkey,  o_orderdate  FROM orders  WHERE o_orderdate IS NOT NULL AND (o_orderdate >= DATE '1995-01-01' AND o_orderdate <= DATE '1996-12-31')  AND o_orderkey IS NOT NULL AND o_custkey IS NOT NULL  ) AS SJ  ON SI.l_orderkey = SJ.o_orderkey   ) AS SG  JOIN (SELECT c_custkey,  c_nationkey  FROM customer  WHERE c_custkey IS NOT NULL AND c_nationkey IS NOT NULL  ) AS SH  ON SG.o_custkey = SH.c_custkey  ) AS SE  JOIN (SELECt n_nationkey,  n_regionkey  FROM nation  WHERE n_nationkey IS NOT NULL AND n_regionkey IS NOT NULL  ) AS SF  ON SE.c_nationkey = SF.n_nationkey  ) AS SC  JOIN (SELECT n_nationkey,  n_name  FROM nation  WHERE n_nationkey IS NOT NULL   ) AS SD  ON SC.s_nationkey = SD.n_nationkey  ) AS SA  JOIN (SELECT r_regionkey  FROM region  WHERE r_name IS NOT NULL AND r_name = 'EUROPE' AND r_regionkey IS NOT NULL  ) AS SB  ON SA.n_regionkey = SB.r_regionkey  ) 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  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 '%honeydew%'  )  AS profit GROUP BY  nation,  o_year ORDER BY  nation,  o_year DESC
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 (SELECT l_quantity,  l_extendedprice,  l_discount,  s_nationkey,  ps_supplycost,  o_orderdate  FROM (SELECT l_orderkey,  l_quantity,  l_extendedprice,  l_discount,  s_nationkey,  ps_supplycost  FROM (SELECT l_orderkey,  l_partkey,  l_suppkey,  l_quantity,  l_extendedprice,  l_discount,  s_nationkey  FROM (SELECT l_orderkey,  l_partkey,  l_suppkey,  l_quantity,  l_extendedprice,  l_discount  FROM (SELECT p_partkey  FROM part  WHERE p_name IS NOT NULL AND p_name LIKE '%honeydew%' AND p_partkey IS NOT NULL  ) AS SI  JOIN (SELECT l_orderkey,  l_partkey,  l_suppkey,  l_quantity,  l_extendedprice,  l_discount  FROM lineitem  WHERE l_partkey IS NOT NULL AND l_suppkey IS NOT NULL AND l_orderkey IS NOT NULL  ) AS SJ  ON SI.p_partkey = SJ.l_partkey  ) AS SG  JOIN (SELECT s_suppkey,  s_nationkey  FROM supplier  WHERE s_suppkey IS NOT NULL AND s_nationkey IS NOT NULL  ) AS SH  ON SH.s_suppkey = SG.l_suppkey   ) AS SE  JOIN (SELECT ps_partkey,  ps_suppkey,  ps_supplycost  FROM partsupp  WHERE ps_suppkey IS NOT NULL AND ps_partkey IS NOT NULL  ) AS SF  ON SF.ps_suppkey = SE.l_suppkey AND SF.ps_partkey = SE.l_partkey    ) AS SC  JOIN (SELECT o_orderkey,  o_orderdate  FROM orders  WHERE o_orderkey IS NOT NULL  ) AS SD  ON SD.o_orderkey = SC.l_orderkey  ) AS SA  JOIN (SELECT n_nationkey,  n_name  FROM nation  WHERE n_nationkey IS NOT NULL  ) AS SB  ON SA.s_nationkey = SB.n_nationkey  ) 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 customer,  orders,  lineitem,  nation where c_custkey = o_custkey  and l_orderkey = o_orderkey  and o_orderdate >= date('1994-09-01 +08')  and o_orderdate < date('1994-09-01 +08') + 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
select c_custkey,  c_name,  sum(l_extendedprice * (1 - l_discount)) as revenue,  c_acctbal,  n_name,  c_address,  c_phone,  c_comment from (select c_custkey,  c_name,  c_address,  c_phone,  c_acctbal,  c_comment,  l_extendedprice,  l_discount,  n_name  from (select c_custkey,  c_name,  c_address,  c_nationkey,  c_phone,  c_acctbal,  c_comment,  l_extendedprice,  l_discount  from (select c_custkey,  c_name,  c_address,  c_nationkey,  c_phone,  c_acctbal,  c_comment,  o_orderkey  from (select c_custkey,  c_name,  c_address,  c_nationkey,  c_phone,  c_acctbal,  c_comment  from customer  where c_custkey IS NOT NULL and c_nationkey IS NOT NULL  ) AS SF  join (select o_orderkey,  o_custkey  from orders  where o_orderdate IS NOT NULL and o_orderdate >= date('1994-09-01 +08') and o_orderdate < date('1994-12-01 +08')  and o_custkey IS NOT NULL and o_orderkey IS NOT NULL  ) AS SG  on SF.c_custkey = SG.o_custkey  ) as SD  join (select l_orderkey,  l_extendedprice,  l_discount  from lineitem  where l_returnflag IS NOT NULL and l_returnflag = 'R' and l_orderkey IS NOT NULL  ) as SE  on SE.l_orderkey = SD.o_orderkey  ) as SB  join (select n_nationkey,  n_name  from nation  where n_nationkey IS NOT NULL  ) as SC  on SB.c_nationkey = SC.n_nationkey  ) as SA group by c_custkey,  c_name,  c_acctbal,  c_phone,  n_name,  c_address,  c_comment order by revenue desc limit 20
SELECT  ps_partkey,  SUM(ps_supplycost * ps_availqty) AS V FROM  partsupp,  supplier,  nation WHERE  ps_suppkey = s_suppkey  AND s_nationkey = n_nationkey  AND n_name = 'ARGENTINA' GROUP BY  ps_partkey HAVING  SUM(ps_supplycost * ps_availqty) > (  SELECT  SUM(ps_supplycost * ps_availqty) * 0.001  FROM  partsupp, supplier, nation  WHERE  ps_suppkey = s_suppkey  AND s_nationkey = n_nationkey  AND n_name = 'ARGENTINA' )  ORDER BY  V DESC
SELECT ps_partkey, SUM(ps_supplycost * ps_availqty) AS V FROM ( SELECT ps_partkey, ps_availqty, ps_supplycost FROM ( SELECT ps_partkey, ps_availqty, ps_supplycost, s_nationkey FROM ( SELECT ps_partkey, ps_suppkey, ps_availqty, ps_supplycost FROM partsupp WHERE ps_suppkey IS NOT NULL ) AS SG JOIN ( SELECT s_suppkey, s_nationkey FROM supplier WHERE s_suppkey IS NOT NULL AND s_nationkey IS NOT NULL ) AS SH ON SG.ps_suppkey = SH.s_suppkey ) AS SE JOIN ( SELECT n_nationkey FROM nation WHERE n_name IS NOT NULL AND n_name = 'ARGENTINA' AND n_nationkey IS NOT NULL ) AS SF ON SE.s_nationkey = SF.n_nationkey ) AS ta1 GROUP BY ps_partkey HAVING SUM(ps_supplycost * ps_availqty) IS NOT NULL AND SUM(ps_supplycost * ps_availqty) > ( SELECT SUM(ps_supplycost * ps_availqty) * 0.001 FROM ( SELECT ps_availqty, ps_supplycost FROM ( SELECT ps_availqty, ps_supplycost, s_nationkey FROM ( SELECT ps_suppkey, ps_availqty, ps_supplycost FROM partsupp WHERE ps_suppkey IS NOT NULL ) AS SC JOIN ( SELECT s_suppkey, s_nationkey FROM supplier WHERE s_suppkey IS NOT NULL AND s_nationkey IS NOT NULL ) AS SD ON SC.ps_suppkey = SD.s_suppkey ) AS SA JOIN ( SELECT n_nationkey FROM nation WHERE n_name IS NOT NULL AND n_name = 'ARGENTINA' AND n_nationkey IS NOT NULL ) AS SB ON SA.s_nationkey = SB.n_nationkey ) AS ta0 ) ORDER BY V 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 orders,  lineitem where o_orderkey = l_orderkey  and l_shipmode in ('AIR',  'TRUCK')  and l_commitdate < l_receiptdate  and l_shipdate < l_commitdate  and l_receiptdate >= date('1994-01-01 +08')  and l_receiptdate < date('1994-01-01 +08') + interval '1' year group by l_shipmode order by l_shipmode
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 (select o_orderpriority,  l_shipmode  from (select o_orderkey,  o_orderpriority  from orders  where o_orderkey IS NOT NULL  ) as SB  join (select l_orderkey,  l_shipmode  from lineitem  where (l_commitdate IS NOT NULL and l_receiptdate IS NOT NULL and l_shipdate IS NOT NULL)  and l_shipmode in ('AIR', 'TRUCK') and l_commitdate < l_receiptdate and l_shipdate < l_commitdate  and l_receiptdate >= date('1994-01-01 +08') and l_receiptdate < date('1995-01-01 +08') and l_orderkey IS NOT NULL  ) as SC  on SB.o_orderkey = SC.l_orderkey  ) as SA  group by l_shipmode order by l_shipmode
SELECT  c_count,  COUNT(*) AS custdist FROM  (  SELECT  c_custkey,  COUNT(o_orderkey) AS c_count  FROM  customer  LEFT OUTER JOIN  orders  ON c_custkey = o_custkey  AND o_comment NOT LIKE '%unusual%accounts%'  GROUP BY  c_custkey  )  AS c_orders GROUP BY  c_count ORDER BY  custdist DESC,  c_count DESC
SELECT c_count,  COUNT(*) AS custdist FROM  (SELECT c_custkey,  COUNT(o_orderkey) AS c_count  FROM (SELECT c_custkey,  o_orderkey  FROM (SELECT c_custkey  FROM customer  ) AS SB  LEFT OUTER JOIN (SELECT o_orderkey,  o_custkey  FROM orders  WHERE o_comment IS NOT NULL AND o_comment NOT LIKE '%unusual%accounts%' and o_custkey IS NOT NULL  ) AS SC   ON SB.c_custkey = SC.o_custkey  ) AS SA  GROUP BY c_custkey  ) AS c_orders 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 lineitem,  part where l_partkey = p_partkey  and l_shipdate >= date('1997-01-01 +08')  and l_shipdate < date('1997-01-01 +08') + interval '1' month
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 (select l_extendedprice,  l_discount,  p_type  from (select l_partkey,  l_extendedprice,  l_discount  from lineitem  where l_shipdate IS NOT NULL and l_shipdate >= date('1997-01-01 +08') and l_shipdate < date('1997-02-01 +08')  and l_partkey IS NOT NULL  ) as SB  join (select p_partkey,  p_type  from part  where p_partkey IS NOT NULL  ) as SC  on SB.l_partkey = SC.p_partkey  ) AS SA
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#31'  AND p_type NOT LIKE 'PROMO POLISHED%'  AND p_size IN (7, 19, 6, 28, 40, 35, 25, 16)  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
SELECT p_brand, p_type, p_size, COUNT(DISTINCT ps_suppkey) AS supplier_cnt FROM ( SELECT ps_suppkey, p_brand, p_type, p_size FROM ( SELECT * FROM ( SELECT ps_partkey, ps_suppkey FROM partsupp WHERE ps_partkey IS NOT NULL ) AS SD LEFT ANTI JOIN ( SELECT s_suppkey FROM supplier WHERE s_comment IS NOT NULL AND s_comment LIKE '%Customer%Complaints%' ) AS SE ON SE.s_suppkey = SD.ps_suppkey OR (SE.s_suppkey = SD.ps_suppkey) IS NULL ) AS SB JOIN ( SELECT p_partkey, p_brand, p_type, p_size FROM part WHERE p_brand IS NOT NULL AND p_type IS NOT NULL AND p_brand <> 'Brand#31' AND p_type NOT LIKE 'PROMO POLISHED%' AND p_size IN (7, 19, 6, 28, 40, 35, 25, 16) AND p_partkey IS NOT NULL ) AS SC ON SC.p_partkey = SB.ps_partkey ) AS SA 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  lineitem,  part WHERE  p_partkey = l_partkey  AND p_brand = 'Brand#25'  AND p_container = 'WRAP PACK'  AND l_quantity < (  SELECT  0.2 * AVG(l_quantity)  FROM  lineitem  WHERE  l_partkey = p_partkey )
SELECT SUM(l_extendedprice) / 7.0 AS avg_yearly FROM ( SELECT l_extendedprice FROM ( SELECT l_quantity, l_extendedprice, p_partkey FROM ( SELECT l_partkey, l_quantity, l_extendedprice FROM lineitem WHERE l_partkey IS NOT NULL AND l_quantity IS NOT NULL ) AS t3 INNER JOIN ( SELECT p_partkey FROM part WHERE ((p_brand IS NOT NULL AND p_container IS NOT NULL) AND ((p_brand = 'Brand#25') AND (p_container = 'WRAP PACK'))) AND p_partkey IS NOT NULL ) AS t4 ON p_partkey = l_partkey ) AS t1 INNER JOIN ( SELECT * FROM ( SELECT 0.2 * avg(l_quantity) AS pa, l_partkey FROM ( SELECT l_partkey, l_quantity FROM lineitem WHERE l_partkey IS NOT NULL ) AS t6 GROUP BY l_partkey ) AS t5 WHERE pa IS NOT NULL ) AS t2 ON (cast(l_quantity as decimal(21,7)) < pa) AND (l_partkey = p_partkey) ) AS t0
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) > 312  )  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
SELECT c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity) FROM ( SELECT c_custkey, c_name, o_orderkey, o_totalprice, o_orderdate, l_quantity FROM ( SELECT c_custkey, c_name, o_orderkey, o_totalprice, o_orderdate FROM ( SELECT c_custkey, c_name FROM customer WHERE c_custkey IS NOT NULL ) AS t3 INNER JOIN ( SELECT * FROM ( SELECT o_orderkey, o_custkey, o_totalprice, o_orderdate FROM orders WHERE o_custkey IS NOT NULL AND o_orderkey IS NOT NULL ) AS t5 LEFT SEMI JOIN ( SELECT l_orderkey FROM ( SELECT l_orderkey, sum(l_quantity) AS s FROM ( SELECT l_orderkey, l_quantity FROM lineitem ) AS t8 GROUP BY l_orderkey ) AS t7 WHERE s IS NOT NULL AND (s > 312.00) ) AS t6 ON o_orderkey = l_orderkey ) AS t4 ON c_custkey = o_custkey ) AS t1 INNER JOIN ( SELECT * FROM ( SELECT l_orderkey, l_quantity FROM lineitem WHERE l_orderkey IS NOT NULL ) AS t9 LEFT SEMI JOIN ( SELECT l_orderkey FROM ( SELECT l_orderkey, sum(l_quantity) AS s FROM ( SELECT l_orderkey, l_quantity FROM lineitem ) AS t12 GROUP BY l_orderkey ) AS t11 WHERE s IS NOT NULL AND (s > 312.00) ) AS t10 ON t9.l_orderkey = t10.l_orderkey ) AS t2 ON o_orderkey = l_orderkey ) AS t0 GROUP BY c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice ORDER BY o_totalprice DESC, o_orderdate LIMIT 100
SELECT  SUM(l_extendedprice* (1 - l_discount)) AS revenue FROM  lineitem,  part WHERE  (  p_partkey = l_partkey  AND p_brand = 'Brand#11'  AND p_container IN ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')  AND l_quantity >= 4  AND l_quantity <= 4 + 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#33'  AND p_container IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')  AND l_quantity >= 16  AND l_quantity <= 16 + 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 >= 30  AND l_quantity <= 30 + 10  AND p_size BETWEEN 1 AND 15  AND l_shipmode IN ('AIR', 'AIR REG')  AND l_shipinstruct = 'DELIVER IN PERSON'  )
SELECT SUM(l_extendedprice * (1 - l_discount)) AS revenue FROM ( SELECT l_extendedprice, l_discount FROM ( SELECT l_partkey, l_quantity, l_extendedprice, l_discount FROM lineitem WHERE ((l_shipinstruct IS NOT NULL AND (l_shipmode IN ('AIR', 'AIR REG') AND (l_shipinstruct = 'DELIVER IN PERSON'))) AND l_partkey IS NOT NULL) AND ((((l_quantity >= 4.00) AND (l_quantity <= 14.00)) OR ((l_quantity >= 16.00) AND (l_quantity <= 26.00))) OR ((l_quantity >= 30.00) AND (l_quantity <= 40.00))) ) AS t1 INNER JOIN ( SELECT p_partkey, p_brand, p_size, p_container FROM part WHERE ((p_size IS NOT NULL AND (p_size >= 1)) AND p_partkey IS NOT NULL) AND (((((p_brand = 'Brand#11') AND p_container IN ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')) AND (p_size <= 5)) OR (((p_brand = 'Brand#33') AND p_container IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')) AND (p_size <= 10))) OR (((p_brand = 'Brand#34') AND p_container IN ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')) AND (p_size <= 15))) ) AS t2 ON (p_partkey = l_partkey) AND (((((((p_brand = 'Brand#11') AND p_container IN ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')) AND (l_quantity >= 4.00)) AND (l_quantity <= 14.00)) AND (p_size <= 5)) OR (((((p_brand = 'Brand#33') AND p_container IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')) AND (l_quantity >= 16.00)) AND (l_quantity <= 26.00)) AND (p_size <= 10))) OR (((((p_brand = 'Brand#34') AND p_container IN ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')) AND (l_quantity >= 30.00)) AND (l_quantity <= 40.00)) AND (p_size <= 15))) ) AS t0
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 'rosy%' ) 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('1996-01-01 +08') and l_shipdate < date('1996-01-01 +08') + interval '1' year ) ) and s_nationkey = n_nationkey and n_name = 'INDIA' order by s_name
SELECT s_name, s_address FROM ( SELECT s_name, s_address, s_nationkey FROM ( SELECT s_suppkey, s_name, s_address, s_nationkey FROM supplier WHERE s_nationkey IS NOT NULL ) AS t2 LEFT SEMI JOIN ( SELECT ps_suppkey FROM ( SELECT * FROM ( SELECT ps_partkey, ps_suppkey, ps_availqty FROM partsupp WHERE (ps_availqty IS NOT NULL AND ps_partkey IS NOT NULL) AND ps_suppkey IS NOT NULL ) AS t6 LEFT SEMI JOIN ( SELECT p_partkey FROM part WHERE p_name IS NOT NULL AND p_name LIKE 'rosy%' ) AS t7 ON ps_partkey = p_partkey ) AS t4 INNER JOIN ( SELECT * FROM ( SELECT 0.5 * sum(l_quantity) AS hs, l_partkey, l_suppkey FROM ( SELECT * FROM ( SELECT l_partkey, l_suppkey, l_quantity FROM lineitem WHERE (((l_shipdate IS NOT NULL AND (l_shipdate >= '1996-01-01')) AND (l_shipdate < '1997-01-01')) AND l_partkey IS NOT NULL) AND l_suppkey IS NOT NULL ) AS t10 LEFT SEMI JOIN ( SELECT p_partkey FROM part WHERE p_name IS NOT NULL AND p_name LIKE 'rosy%' ) AS t11 ON l_partkey = p_partkey ) AS t9 GROUP BY l_partkey, l_suppkey ) AS t8 WHERE hs IS NOT NULL ) AS t5 ON ((cast(ps_availqty as decimal(27,3)) > hs) AND (l_partkey = ps_partkey)) AND (l_suppkey = ps_suppkey) ) AS t3 ON s_suppkey = ps_suppkey ) AS t0 INNER JOIN ( SELECT n_nationkey FROM nation WHERE (n_name IS NOT NULL AND n_name = 'INDIA') AND n_nationkey IS NOT NULL ) AS t1 ON s_nationkey = n_nationkey ORDER BY 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 customer where substring(c_phone from 1 for 2) in ('13', '10', '23', '21', '19', '34', '32') and c_acctbal > ( select avg(c_acctbal) from customer where c_acctbal > 0.00 and substring(c_phone from 1 for 2) in ('13', '10', '23', '21', '19', '34', '32') ) and not exists ( select * from orders where o_custkey = c_custkey ) ) as custsale group by cntrycode order by cntrycode
SELECT cntrycode, count(1) AS numcust, sum(c_acctbal) AS totacctbal FROM ( SELECT substring(c_phone, 1, 2) AS cntrycode, c_acctbal FROM ( SELECT c_custkey, c_phone, c_acctbal FROM customer WHERE c_acctbal IS NOT NULL AND (substring(c_phone from 1 for 2) IN ('13', '10', '23', '21', '19', '34', '32')) AND c_acctbal > ( SELECT avg(c_acctbal) FROM ( SELECT c_acctbal FROM customer WHERE c_acctbal IS NOT NULL AND ((c_acctbal > 0.00) AND substring(c_phone from 1 for 2) IN ('13', '10', '23', '21', '19', '34', '32')) ) AS t2 ) ) AS t1 LEFT ANTI JOIN ( SELECT o_custkey FROM orders ) AS t3 ON o_custkey = c_custkey ) AS t0 GROUP BY cntrycode ORDER BY cntrycode
SELECT s_suppkey, s_name, s_address, s_phone, total_revenue FROM supplier, ( SELECT l_suppkey AS supplier_no, SUM( l_extendedprice * (1 - l_discount) ) AS total_revenue FROM lineitem WHERE l_shipdate >= DATE '1993-01-01' AND l_shipdate < DATE '1993-01-01' + INTERVAL '3' MONTH GROUP BY l_suppkey ) AS revenue0 WHERE s_suppkey = supplier_no AND total_revenue = ( SELECT MAX(total_revenue) FROM ( SELECT l_suppkey AS supplier_no, SUM( l_extendedprice * (1 - l_discount) ) AS total_revenue FROM lineitem WHERE l_shipdate >= DATE '1993-01-01' AND l_shipdate < DATE '1993-01-01' + INTERVAL '3' MONTH GROUP BY l_suppkey ) AS revenue0 ) ORDER BY s_suppkey
SELECT s_suppkey, s_name, s_address, s_phone, total_revenue FROM ( SELECT s_suppkey, s_name, s_address, s_phone FROM supplier WHERE s_suppkey IS NOT NULL ) AS t0 INNER JOIN ( SELECT * FROM ( SELECT l_suppkey AS supplier_no, SUM(l_extendedprice * (1.00 - l_discount)) AS total_revenue FROM ( SELECT l_suppkey, l_extendedprice, l_discount FROM lineitem WHERE (l_shipdate IS NOT NULL AND ((l_shipdate >= DATE '1993-01-01') AND (l_shipdate < DATE '1993-04-01'))) AND l_suppkey IS NOT NULL ) AS t5 GROUP BY l_suppkey ) AS t2 WHERE total_revenue IS NOT NULL AND total_revenue = ( SELECT MAX(total_revenue) FROM ( SELECT SUM(l_extendedprice * (1.00 - l_discount)) AS total_revenue FROM ( SELECT l_suppkey, l_extendedprice, l_discount FROM lineitem WHERE l_shipdate IS NOT NULL AND ((l_shipdate >= DATE '1993-01-01') AND (l_shipdate < DATE '1993-04-01')) ) AS t4 GROUP BY l_suppkey ) AS t3 ) ) AS t1 ON s_suppkey = supplier_no ORDER BY s_suppkey
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 = 'ALGEBRA' GROUP BY s_name ORDER BY numwait DESC, s_name LIMIT 100
SELECT s_name, COUNT(*) AS numwait FROM ( SELECT s_name FROM ( SELECT s_name, s_nationkey FROM ( SELECT s_name, s_nationkey, l_orderkey FROM ( SELECT s_suppkey, s_name, s_nationkey FROM supplier WHERE s_suppkey IS NOT NULL AND s_nationkey IS NOT NULL ) AS SF JOIN ( SELECT * FROM ( SELECT l_orderkey, l_suppkey FROM lineitem WHERE (((l_receiptdate IS NOT NULL AND l_commitdate IS NOT NULL) AND (l_receiptdate > l_commitdate)) AND l_suppkey IS NOT NULL) AND l_orderkey IS NOT NULL ) AS SH LEFT SEMI JOIN ( SELECT l_orderkey, l_suppkey FROM lineitem ) AS SI ON (SI.l_orderkey = SH.l_orderkey) AND NOT (SI.l_suppkey = SH.l_suppkey) LEFT ANTI JOIN ( SELECT l_orderkey, l_suppkey FROM lineitem WHERE (l_receiptdate IS NOT NULL AND l_commitdate IS NOT NULL) AND (l_receiptdate > l_commitdate) ) AS SJ ON (SJ.l_orderkey = SH.l_orderkey) AND NOT (SJ.l_suppkey = SH.l_suppkey) ) AS SG ON SF.s_suppkey = SG.l_suppkey ) AS SD JOIN ( SELECT o_orderkey FROM orders WHERE o_orderstatus IS NOT NULL AND o_orderstatus = 'F' AND o_orderkey IS NOT NULL ) AS SE ON o_orderkey = l_orderkey ) AS SB JOIN ( SELECT n_nationkey FROM nation WHERE (n_name IS NOT NULL and n_name = 'ALGEBRA') AND n_nationkey IS NOT NULL ) AS SC ON SB.s_nationkey = SC.n_nationkey ) AS SA GROUP BY s_name ORDER BY numwait DESC, s_name LIMIT 100