- sql: |
    CREATE TABLE NATION  (
        N_NATIONKEY  INT NOT NULL,
        N_NAME       CHAR(25) NOT NULL,
        N_REGIONKEY  INT NOT NULL,
        N_COMMENT    VARCHAR(152)
    );

    CREATE TABLE REGION  (
        R_REGIONKEY  INT NOT NULL,
        R_NAME       CHAR(25) NOT NULL,
        R_COMMENT    VARCHAR(152)
    );

    CREATE TABLE PART  (
        P_PARTKEY     INT NOT NULL,
        P_NAME        VARCHAR(55) NOT NULL,
        P_MFGR        CHAR(25) NOT NULL,
        P_BRAND       CHAR(10) NOT NULL,
        P_TYPE        VARCHAR(25) NOT NULL,
        P_SIZE        INT NOT NULL,
        P_CONTAINER   CHAR(10) NOT NULL,
        P_RETAILPRICE DECIMAL(15,2) NOT NULL,
        P_COMMENT     VARCHAR(23) NOT NULL
    );

    CREATE TABLE SUPPLIER (
        S_SUPPKEY     INT NOT NULL,
        S_NAME        CHAR(25) NOT NULL,
        S_ADDRESS     VARCHAR(40) NOT NULL,
        S_NATIONKEY   INT NOT NULL,
        S_PHONE       CHAR(15) NOT NULL,
        S_ACCTBAL     DECIMAL(15,2) NOT NULL,
        S_COMMENT     VARCHAR(101) NOT NULL
    );

    CREATE TABLE PARTSUPP (
        PS_PARTKEY     INT NOT NULL,
        PS_SUPPKEY     INT NOT NULL,
        PS_AVAILQTY    INT NOT NULL,
        PS_SUPPLYCOST  DECIMAL(15,2)  NOT NULL,
        PS_COMMENT     VARCHAR(199) NOT NULL
    );

    CREATE TABLE CUSTOMER (
        C_CUSTKEY     INT NOT NULL,
        C_NAME        VARCHAR(25) NOT NULL,
        C_ADDRESS     VARCHAR(40) NOT NULL,
        C_NATIONKEY   INT NOT NULL,
        C_PHONE       CHAR(15) NOT NULL,
        C_ACCTBAL     DECIMAL(15,2)   NOT NULL,
        C_MKTSEGMENT  CHAR(10) NOT NULL,
        C_COMMENT     VARCHAR(117) NOT NULL
    );

    CREATE TABLE ORDERS (
        O_ORDERKEY       INT NOT NULL,
        O_CUSTKEY        INT NOT NULL,
        O_ORDERSTATUS    CHAR(1) NOT NULL,
        O_TOTALPRICE     DECIMAL(15,2) NOT NULL,
        O_ORDERDATE      DATE NOT NULL,
        O_ORDERPRIORITY  CHAR(15) NOT NULL,  
        O_CLERK          CHAR(15) NOT NULL, 
        O_SHIPPRIORITY   INT NOT NULL,
        O_COMMENT        VARCHAR(79) NOT NULL
    );

    CREATE TABLE LINEITEM (
        L_ORDERKEY      INT NOT NULL,
        L_PARTKEY       INT NOT NULL,
        L_SUPPKEY       INT NOT NULL,
        L_LINENUMBER    INT 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
    );
  desc: TPC-H schema
  tasks:
    - execute
- sql: |
    select
        ps_partkey,
        sum(ps_supplycost * ps_availqty) as value
    from
        partsupp,
        supplier,
        nation
    where
        ps_suppkey = s_suppkey
        and s_nationkey = n_nationkey
        and n_name = 'CHINA'
    group by
        ps_partkey having
            sum(ps_supplycost * ps_availqty) > (
                select
                    sum(ps_supplycost * ps_availqty) * 0.0001000000
                from
                    partsupp,
                    supplier,
                    nation
                where
                    ps_suppkey = s_suppkey
                    and s_nationkey = n_nationkey
                    and n_name = 'CHINA'
            )
    order by
        value desc;
  desc: TPC-H Q11
  tasks:
    - explain[use_df_logical]:logical_optd,physical_optd
- sql: |
    SELECT
        l_shipmode,
        sum(case when o_orderpriority = '1-URGENT'
                 or o_orderpriority = '2-HIGH'
                 then 1 else 0 end) as high_priority_orders,
        sum(case when o_orderpriority <> '1-URGENT'
                 and o_orderpriority <> '2-HIGH'
                 then 1 else 0 end) as low_priority_orders
    FROM
        orders,
        lineitem
    WHERE
        o_orderkey = l_orderkey
        AND l_shipmode in ('MAIL', 'SHIP')
        AND l_commitdate < l_receiptdate
        AND l_shipdate < l_commitdate
        AND l_receiptdate >= DATE '1994-01-01'
        AND l_receiptdate < DATE '1995-01-01'
    GROUP BY
        l_shipmode
    ORDER BY
        l_shipmode;
  desc: TPC-H Q12
  tasks:
    - explain:logical_optd,physical_optd
- sql: |
    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 '1995-09-01'
        AND l_shipdate < DATE '1995-09-01' + INTERVAL '1' MONTH;
  desc: TPC-H Q14
  tasks:
    - explain:logical_optd,physical_optd
- sql: |
    WITH revenue0 (supplier_no, total_revenue) AS 
    (
        SELECT
            l_suppkey,
            SUM(l_extendedprice * (1 - l_discount)) 
        FROM
            lineitem 
        WHERE
            l_shipdate >= DATE '1993-01-01' 
            AND l_shipdate < DATE '1993-01-01' + INTERVAL '3' MONTH 
        GROUP BY
            l_suppkey 
    )
    SELECT
        s_suppkey,
        s_name,
        s_address,
        s_phone,
        total_revenue 
    FROM
        supplier,
        revenue0 
    WHERE
        s_suppkey = supplier_no 
        AND total_revenue = 
        (
            SELECT
                MAX(total_revenue) 
            FROM
                revenue0 
        )
    ORDER BY
        s_suppkey;
  desc: TPC-H Q15
  tasks:
    - explain[use_df_logical]:logical_optd,physical_optd