- 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
        SUM(l_extendedprice * l_discount) AS revenue_loss
    FROM
        lineitem
    WHERE
        l_shipdate >= DATE '2023-01-01'
        AND l_shipdate < DATE '2024-01-01'
        AND l_discount BETWEEN 0.05 AND 0.07
        AND l_quantity < 24;
  desc: TPC-H Q6
  tasks:
    - explain:logical_optd,physical_optd
- sql: |
    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 = 'FRANCE' AND n2.n_name = 'GERMANY')
                    OR (n1.n_name = 'GERMANY' AND n2.n_name = 'FRANCE')
                )
                AND l_shipdate BETWEEN DATE '1995-01-01' AND DATE '1996-12-31'
        ) AS shipping
    GROUP BY
        supp_nation,
        cust_nation,
        l_year
    ORDER BY
        supp_nation,
        cust_nation,
        l_year;
  desc: TPC-H Q7
  tasks:
    - explain:logical_optd,physical_optd
- sql: |
    select
        o_year,
        sum(case
            when nation = 'IRAQ' then volume
            else 0
        end) / sum(volume) as mkt_share
    from
        (
            select
                extract(year from o_orderdate) as o_year,
                l_extendedprice * (1 - l_discount) as volume,
                n2.n_name as nation
            from
                part,
                supplier,
                lineitem,
                orders,
                customer,
                nation n1,
                nation n2,
                region
            where
                p_partkey = l_partkey
                and s_suppkey = l_suppkey
                and l_orderkey = o_orderkey
                and o_custkey = c_custkey
                and c_nationkey = n1.n_nationkey
                and n1.n_regionkey = r_regionkey
                and r_name = 'AMERICA'
                and s_nationkey = n2.n_nationkey
                and o_orderdate between date '1995-01-01' and date '1996-12-31'
                and p_type = 'ECONOMY ANODIZED STEEL'
        ) as all_nations
    group by
        o_year
    order by
        o_year;
  desc: TPC-H Q8 without top-most limit node
  tasks:
    - explain:logical_optd,physical_optd
- sql: |
    SELECT
        nation,
        o_year,
        SUM(amount) AS sum_profit
    FROM
        (
            SELECT
                n_name AS nation,
                EXTRACT(YEAR FROM o_orderdate) AS o_year,
                l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity AS amount
            FROM
                part,
                supplier,
                lineitem,
                partsupp,
                orders,
                nation
            WHERE
                s_suppkey = l_suppkey
                AND ps_suppkey = l_suppkey
                AND ps_partkey = l_partkey
                AND p_partkey = l_partkey
                AND o_orderkey = l_orderkey
                AND s_nationkey = n_nationkey
                AND p_name LIKE '%green%'
        ) AS profit
    GROUP BY
        nation,
        o_year
    ORDER BY
        nation,
        o_year DESC;
  desc: TPC-H Q9
  tasks:
    - explain:logical_optd,physical_optd
- sql: |
    SELECT
        nation,
        o_year,
        SUM(amount) AS sum_profit
    FROM
        (
            SELECT
                n_name AS nation,
                EXTRACT(YEAR FROM o_orderdate) AS o_year,
                l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity AS amount
            FROM
                part,
                supplier,
                lineitem,
                partsupp,
                orders,
                nation
            WHERE
                s_suppkey = l_suppkey
                AND ps_suppkey = l_suppkey
                AND ps_partkey = l_partkey
                AND p_partkey = l_partkey
                AND o_orderkey = l_orderkey
                AND s_nationkey = n_nationkey
                AND p_name LIKE '%green%'
        ) AS profit
    GROUP BY
        nation,
        o_year
    ORDER BY
        nation,
        o_year DESC;
  desc: TPC-H Q9
  tasks:
    - explain:logical_optd,physical_optd
- sql: |
    SELECT
        c_custkey,
        c_name,
        sum(l_extendedprice * (1 - l_discount)) as revenue,
        c_acctbal,
        n_name,
        c_address,
        c_phone,
        c_comment
    FROM
        customer,
        orders,
        lineitem,
        nation
    WHERE
        c_custkey = o_custkey
        AND l_orderkey = o_orderkey
        AND o_orderdate >= DATE '1993-07-01'
        AND o_orderdate < DATE '1993-07-01' + INTERVAL '3' MONTH
        AND l_returnflag = 'R'
        AND c_nationkey = n_nationkey
    GROUP BY
        c_custkey,
        c_name,
        c_acctbal,
        c_phone,
        n_name,
        c_address,
        c_comment
    ORDER BY
        revenue DESC
    LIMIT 20;
  desc: TPC-H Q10
  tasks:
    - explain:logical_optd,physical_optd