- 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
        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 '1998-12-01' - interval '90' day
    GROUP BY
        l_returnflag, l_linestatus
    ORDER BY
        l_returnflag, l_linestatus;
  desc: TPC-H Q1
  tasks:
    - explain:logical_optd,physical_optd
- sql: |
    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 = 4
    and p_type like '%TIN'
            and s_nationkey = n_nationkey
            and n_regionkey = r_regionkey
            and r_name = 'AFRICA'
            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 = 'AFRICA'
            )
    order by
        s_acctbal desc,
        n_name,
        s_name,
        p_partkey
    limit 100;
  desc: TPC-H Q2
  tasks:
    - explain:logical_optd,physical_optd
- sql: |
    SELECT
        l_orderkey,
        SUM(l_extendedprice * (1 - l_discount)) AS revenue,
        o_orderdate,
        o_shippriority 
    FROM
        customer,
        orders,
        lineitem 
    WHERE
        c_mktsegment = 'FURNITURE' 
        AND c_custkey = o_custkey 
        AND l_orderkey = o_orderkey 
        AND o_orderdate < DATE '1995-03-29' 
        AND l_shipdate > DATE '1995-03-29' 
    GROUP BY
        l_orderkey,
        o_orderdate,
        o_shippriority 
    ORDER BY
        revenue DESC,
        o_orderdate LIMIT 10;
  desc: TPC-H Q3
  tasks:
    - explain:logical_optd,physical_optd
- sql: |
    SELECT
        n_name AS nation,
        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 = 'Asia' -- Specified region
        AND o_orderdate >= DATE '2023-01-01'
        AND o_orderdate < DATE '2024-01-01'
    GROUP BY
        n_name
    ORDER BY
        revenue DESC;
  desc: TPC-H Q5
  tasks:
    - explain:logical_optd,physical_optd