text: -- Copyright Materialize, Inc. and contributors. All rights reserved.
  --
  -- Use of this software is governed by the Business Source License
  -- included in the LICENSE file at the root of this repository.
  --
  -- As of the Change Date specified in that file, in accordance with
  -- the Business Source License, use of this software will be governed
  -- by the Apache License, Version 2.0.
  
  -- Queries for to workload of the `TPCH` benchmarking scenario.
  
  -- name: Q01
  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 '60' day (fails with an error)
  GROUP BY
  	l_returnflag,
  	l_linestatus
  ORDER BY
  	l_returnflag,
  	l_linestatus;
  
  
  -- name: Q02
  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 = CAST (15 AS smallint)
      AND p_type LIKE '%BRASS'
      AND s_nationkey = n_nationkey
      AND n_regionkey = r_regionkey
      AND r_name = 'EUROPE'
      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 = 'EUROPE'
              )
  ORDER BY
      s_acctbal DESC, n_name, s_name, p_partkey;
  
  -- name: Q03
  SELECT
      l_orderkey,
      sum(l_extendedprice * (1 - l_discount)) AS revenue,
      o_orderdate,
      o_shippriority
  FROM
      customer,
      orders,
      lineitem
  WHERE
      c_mktsegment = 'BUILDING'
      AND c_custkey = o_custkey
      AND l_orderkey = o_orderkey
      AND o_orderdate < DATE '1995-03-15'
      AND l_shipdate > DATE '1995-03-15'
  GROUP BY
      l_orderkey,
      o_orderdate,
      o_shippriority
  ORDER BY
      revenue DESC,
      o_orderdate;
  
  -- name: Q04
  SELECT
      o_orderpriority,
      count(*) AS order_count
  FROM
      orders
  WHERE
      o_orderdate >= DATE '1993-07-01'
      AND o_orderdate < DATE '1993-07-01' + INTERVAL '3' month
      AND EXISTS (
          SELECT
              *
          FROM
              lineitem
          WHERE
              l_orderkey = o_orderkey
              AND l_commitdate < l_receiptdate
      )
  GROUP BY
      o_orderpriority
  ORDER BY
      o_orderpriority;
  
  
  -- name: Q05
  SELECT
      n_name,
      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'
      AND o_orderdate >= DATE '1994-01-01'
      AND o_orderdate < DATE '1995-01-01'
  GROUP BY
      n_name
  ORDER BY
      revenue DESC;
  
  -- name: Q06
  SELECT
      sum(l_extendedprice * l_discount) AS revenue
  FROM
      lineitem
  WHERE
      l_quantity < 24
      AND l_shipdate >= DATE '1994-01-01'
      AND l_shipdate < DATE '1994-01-01' + INTERVAL '1' year
      AND l_discount BETWEEN 0.06 - 0.01 AND 0.07;
  
  -- name: Q07
  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;
  
  -- name: Q08
  SELECT
      o_year,
      sum(case
          when nation = 'BRAZIL' 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;
  
  -- name: Q09
  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;
  
  -- name: Q10
  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-10-01'
      AND o_orderdate < DATE '1994-01-01'
      AND o_orderdate < DATE '1993-10-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;
  
  -- name: Q11
  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 = 'GERMANY'
  GROUP BY
      ps_partkey having
          sum(ps_supplycost * ps_availqty) > (
              SELECT
                  sum(ps_supplycost * ps_availqty) * 0.0001
              FROM
                  partsupp,
                  supplier,
                  nation
              WHERE
                  ps_suppkey = s_suppkey
                  AND s_nationkey = n_nationkey
                  AND n_name = 'GERMANY'
          )
  ORDER BY
      value DESC;
  
  -- name: Q12
  SELECT
      l_shipmode,
      sum(case
          when o_orderpriority = '1-URGENT'
              or o_orderpriority = '2-HIGH'
              then 1
          else 0
      end) AS high_line_count,
      sum(case
          when o_orderpriority <> '1-URGENT'
              AND o_orderpriority <> '2-HIGH'
              then 1
          else 0
      end) AS low_line_count
  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 '1994-01-01' + INTERVAL '1' year
  GROUP BY
      l_shipmode
  ORDER BY
      l_shipmode;
  
  -- name: Q13
  SELECT
      c_count,
      count(*) AS custdist
  FROM
      (
          SELECT
              c_custkey,
              count(o_orderkey) c_count -- workaround for no column aliases
          FROM
              customer LEFT OUTER JOIN orders ON
                  c_custkey = o_custkey
                  AND o_comment NOT LIKE '%special%requests%'
          GROUP BY
              c_custkey
      ) AS c_orders -- (c_custkey, c_count) -- no column aliases yet
  GROUP BY
      c_count
  ORDER BY
      custdist DESC,
      c_count DESC;
  
  -- name: Q14
  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;
  
  -- name: Q15
  SELECT
      s_suppkey,
      s_name,
      s_address,
      s_phone,
      total_revenue
  FROM
      supplier,
      revenue
  WHERE
      s_suppkey = supplier_no
      AND total_revenue = (
          SELECT
              max(total_revenue)
          FROM
              revenue
      )
  ORDER BY
      s_suppkey;
  
  -- name: Q16
  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;
  
  -- name: Q17
  SELECT
    sum(l_extendedprice) / 7.0 AS avg_yearly
  FROM
    lineitem,
    part
  WHERE
    p_partkey = l_partkey
    AND p_brand = 'Brand#23'
    AND p_container = 'MED BOX'
    AND l_quantity < (
      SELECT
        0.2 * avg(l_quantity)
      FROM
        lineitem
      WHERE
        l_partkey = p_partkey
    );
  
  -- name: Q18
  SELECT
      c_name,
      c_custkey,
      o_orderkey,
      o_orderdate,
      o_totalprice,
      sum(l_quantity)
  FROM
      customer,
      orders,
      lineitem
  WHERE
      o_orderkey IN (
          SELECT
              l_orderkey
          FROM
              lineitem
          GROUP BY
              l_orderkey having
                  sum(l_quantity) > 300
      )
      AND c_custkey = o_custkey
      AND o_orderkey = l_orderkey
  GROUP BY
      c_name,
      c_custkey,
      o_orderkey,
      o_orderdate,
      o_totalprice
  ORDER BY
      o_totalprice DESC,
      o_orderdate;
  
  -- name: Q19
  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 >= CAST (1 AS smallint) AND l_quantity <= CAST (1 + 10 AS smallint)
          AND p_size BETWEEN CAST (1 AS smallint) AND CAST (5 AS smallint)
          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 >= CAST (10 AS smallint) AND l_quantity <= CAST (10 + 10 AS smallint)
          AND p_size BETWEEN CAST (1 AS smallint) AND CAST (10 AS smallint)
          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 >= CAST (20 AS smallint) AND l_quantity <= CAST (20 + 10 AS smallint)
          AND p_size BETWEEN CAST (1 AS smallint) AND CAST (15 AS smallint)
          AND l_shipmode IN ('AIR', 'AIR REG')
          AND l_shipinstruct = 'DELIVER IN PERSON'
      );
  
  -- name: Q20
  SELECT
      s_name,
      s_address
  FROM
      supplier,
      nation
  WHERE
      s_suppkey IN (
          SELECT
              ps_suppkey
          FROM
              partsupp
          WHERE
              ps_partkey IN (
                  SELECT
                      p_partkey
                  FROM
                      part
                  WHERE
                      p_name like 'forest%'
              )
              AND ps_availqty > (
                  SELECT
                      0.5 * sum(l_quantity)
                  FROM
                      lineitem
                  WHERE
                      l_partkey = ps_partkey
                      AND l_suppkey = ps_suppkey
                      AND l_shipdate >= DATE '1995-01-01'
                      AND l_shipdate < DATE '1995-01-01' + INTERVAL '1' year
              )
      )
      AND s_nationkey = n_nationkey
      AND n_name = 'CANADA'
  ORDER BY
      s_name;
  
  -- name: Q21
  SELECT
      s_name,
      count(*) AS numwait
  FROM
      supplier,
      lineitem l1,
      orders,
      nation
  WHERE
      s_suppkey = l1.l_suppkey
      AND o_orderkey = l1.l_orderkey
      AND o_orderstatus = 'F'
      AND l1.l_receiptdate > l1.l_commitdate
      AND EXISTS (
          SELECT
              *
          FROM
              lineitem l2
          WHERE
              l2.l_orderkey = l1.l_orderkey
              AND l2.l_suppkey <> l1.l_suppkey
      )
      AND not EXISTS (
          SELECT
              *
          FROM
              lineitem l3
          WHERE
              l3.l_orderkey = l1.l_orderkey
              AND l3.l_suppkey <> l1.l_suppkey
              AND l3.l_receiptdate > l3.l_commitdate
      )
      AND s_nationkey = n_nationkey
      AND n_name = 'SAUDI ARABIA'
  GROUP BY
      s_name
  ORDER BY
      numwait DESC,
      s_name;
  
  -- name: Q22
  SELECT
      cntrycode,
      count(*) AS numcust,
      sum(c_acctbal) AS totacctbal
  FROM
      (
          SELECT
              substring(c_phone, 1, 2) AS cntrycode, c_acctbal
          FROM
              customer
          WHERE
              substring(c_phone, 1, 2)
              IN ('13', '31', '23', '29', '30', '18', '17')
              AND c_acctbal
                  > (
                          SELECT
                              avg(c_acctbal)
                          FROM
                              customer
                          WHERE
                              c_acctbal > 0.00
                              AND substring(c_phone, 1, 2)
                                  IN (
                                          '13',
                                          '31',
                                          '23',
                                          '29',
                                          '30',
                                          '18',
                                          '17'
                                      )
                      )
              AND NOT
                      EXISTS(
                          SELECT
                              *
                          FROM
                              orders
                          WHERE
                              o_custkey = c_custkey
                      )
      )
          AS custsale
  GROUP BY
      cntrycode
  ORDER BY
      cntrycode;
  