drop table IF EXISTS lineitem;

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/lineitem';

select * from (select sum(abs(L_QUANTITY)), SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX))
FROM lineitem WHERE L_SHIPDATE<='1998-09-02'  GROUP BY L_RETURNFLAG) T;

drop table lineitem;