set libra_etl_to_rw=on;
set libra_concurrent_etl=off;
set libra_etl_concurrency=1;

create table t1 (
    supp_nation VARCHAR(50),
    cust_nation VARCHAR(50),
    l_year INT,
    revenue DECIMAL
);

INSERT INTO t1 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 = 'JAPAN' and n2.n_name = 'INDIA') 
              or (n1.n_name = 'INDIA' and n2.n_name = 'JAPAN')) 
        and l_shipdate between '1995-01-01' and '1996-12-31') as shipping 
group by supp_nation, cust_nation, l_year 
order by supp_nation, cust_nation, l_year; 

set libra_etl_to_rw=on;
set libra_concurrent_etl=off;
set libra_etl_concurrency=1;

CREATE TABLE t2
 (
    p_brand VARCHAR(10),
    p_type VARCHAR(25),
    p_size INTEGER,
    supplier_cnt INTEGER
);

INSERT INTO t2 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#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
            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;

set libra_etl_to_rw=on;
set libra_concurrent_etl=on;
set libra_etl_concurrency=8;

CREATE TABLE IF NOT EXISTS lineitem ( L_ORDERKEY INTEGER NOT NULL,
L_PARTKEY INTEGER NOT NULL,
L_SUPPKEY INTEGER NOT NULL,
L_LINENUMBER INTEGER NOT NULL,
L_QUANTITY DECIMAL(15,2) NOT NULL,
L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
L_DISCOUNT DECIMAL(15,2) NOT NULL,
L_TAX DECIMAL(15,2) NOT NULL,
L_RETURNFLAG CHAR(1) NOT NULL,
L_LINESTATUS CHAR(1) NOT NULL,
L_SHIPDATE DATE NOT NULL,
L_COMMITDATE DATE NOT NULL,
L_RECEIPTDATE DATE NOT NULL,
L_SHIPINSTRUCT CHAR(25) NOT NULL,
L_SHIPMODE CHAR(10) NOT NULL,
L_COMMENT VARCHAR(44) NOT NULL , primary key(L_ORDERKEY, L_LINENUMBER));

INSERT INTO lineitem_t SELECT
    *
from
    lineitem;