- 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
        ROUND(SUM(l_extendedprice) / 7.0, 16) AS avg_yearly 
    FROM
        lineitem,
        part 
    WHERE
        p_partkey = l_partkey 
        AND p_brand = 'Brand#13' 
        AND p_container = 'JUMBO PKG' 
        AND l_quantity < ( 
            SELECT
                0.2 * AVG(l_quantity) 
            FROM
                lineitem 
            WHERE
                l_partkey = p_partkey 
        );
  desc: TPC-H Q17
  tasks:
    - explain[use_df_logical]:logical_optd,physical_optd
- sql: |
    SELECT
        sum(l_extendedprice* (1 - l_discount)) as revenue
    FROM
        lineitem,
        part
    WHERE
        (
            p_partkey = l_partkey
            AND p_brand = 'Brand#12'
            AND p_container IN ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
            AND l_quantity >= 1 AND l_quantity <= 11
            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#23'
            AND p_container IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
            AND l_quantity >= 10 AND l_quantity <= 20
            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 >= 20 AND l_quantity <= 30
            AND p_size BETWEEN 1 AND 15
            AND l_shipmode IN ('AIR', 'AIR REG')
            AND l_shipinstruct = 'DELIVER IN PERSON'
        )
  desc: TPC-H Q19
  tasks:
    - explain:logical_optd,physical_optd