# Licensed to the Apache Software Foundation (ASF) under one
# or more contributor license agreements.  See the NOTICE file
# distributed with this work for additional information
# regarding copyright ownership.  The ASF licenses this file
# to you under the Apache License, Version 2.0 (the
# "License"); you may not use this file except in compliance
# with the License.  You may obtain a copy of the License at
#
#   http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing,
# software distributed under the License is distributed on an
# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
# KIND, either express or implied.  See the License for the
# specific language governing permissions and limitations
# under the License.

# Query 1
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 '96 day'
GROUP BY
  l_returnflag,
  l_linestatus
ORDER BY
  l_returnflag,
  l_linestatus
--
SetOperationStatement
+-set_operation_query=SetOperation[set_operation_type=Select]
  +-children=
    +-Select
      +-select_clause=SelectList
      | +-SelectListItem
      | | +-AttributeReference[attribute_name=l_returnflag]
      | +-SelectListItem
      | | +-AttributeReference[attribute_name=l_linestatus]
      | +-SelectListItem[alias=sum_qty]
      | | +-FunctionCall[name=SUM]
      | |   +-AttributeReference[attribute_name=l_quantity]
      | +-SelectListItem[alias=sum_base_price]
      | | +-FunctionCall[name=SUM]
      | |   +-AttributeReference[attribute_name=l_extendedprice]
      | +-SelectListItem[alias=sum_disc_price]
      | | +-FunctionCall[name=SUM]
      | |   +-Multiply
      | |     +-left_operand=AttributeReference[attribute_name=l_extendedprice]
      | |     +-right_operand=Subtract
      | |       +-left_operand=Literal
      | |       | +-NumericLiteral[numeric_string=1,float_like=false]
      | |       +-right_operand=AttributeReference[attribute_name=l_discount]
      | +-SelectListItem[alias=sum_charge]
      | | +-FunctionCall[name=SUM]
      | |   +-Multiply
      | |     +-left_operand=Multiply
      | |     | +-left_operand=AttributeReference[attribute_name=l_extendedprice]
      | |     | +-right_operand=Subtract
      | |     |   +-left_operand=Literal
      | |     |   | +-NumericLiteral[numeric_string=1,float_like=false]
      | |     |   +-right_operand=AttributeReference[attribute_name=l_discount]
      | |     +-right_operand=Add
      | |       +-left_operand=Literal
      | |       | +-NumericLiteral[numeric_string=1,float_like=false]
      | |       +-right_operand=AttributeReference[attribute_name=l_tax]
      | +-SelectListItem[alias=avg_qty]
      | | +-FunctionCall[name=AVG]
      | |   +-AttributeReference[attribute_name=l_quantity]
      | +-SelectListItem[alias=avg_price]
      | | +-FunctionCall[name=AVG]
      | |   +-AttributeReference[attribute_name=l_extendedprice]
      | +-SelectListItem[alias=avg_disc]
      | | +-FunctionCall[name=AVG]
      | |   +-AttributeReference[attribute_name=l_discount]
      | +-SelectListItem[alias=count_order]
      |   +-FunctionCall[name=COUNT,is_star=true]
      +-where_clause=LessOrEqual
      | +-left_operand=AttributeReference[attribute_name=l_shipdate]
      | +-right_operand=Subtract
      |   +-left_operand=Literal
      |   | +-StringLiteral[value=1998-12-01,explicit_type=Date]
      |   +-right_operand=Literal
      |     +-StringLiteral[value=96 day,explicit_type=DatetimeInterval]
      +-group_by=GroupBy
      | +-AttributeReference[attribute_name=l_returnflag]
      | +-AttributeReference[attribute_name=l_linestatus]
      +-order_by=OrderBy
      | +-OrderByItem[is_asc=true,nulls_first=false]
      | | +-AttributeReference[attribute_name=l_returnflag]
      | +-OrderByItem[is_asc=true,nulls_first=false]
      |   +-AttributeReference[attribute_name=l_linestatus]
      +-from_clause=
        +-TableReference[table=lineitem]
==

# Query 2
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 = 48
  AND p_type LIKE '%NICKEL'
  AND s_nationkey = n_nationkey
  AND n_regionkey = r_regionkey
  AND r_name = 'ASIA'
  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 = 'ASIA'
  )
ORDER BY
  s_acctbal desc,
  n_name,
  s_name,
  p_partkey
LIMIT 100
--
SetOperationStatement
+-set_operation_query=SetOperation[set_operation_type=Select]
  +-children=
    +-Select
      +-select_clause=SelectList
      | +-SelectListItem
      | | +-AttributeReference[attribute_name=s_acctbal]
      | +-SelectListItem
      | | +-AttributeReference[attribute_name=s_name]
      | +-SelectListItem
      | | +-AttributeReference[attribute_name=n_name]
      | +-SelectListItem
      | | +-AttributeReference[attribute_name=p_partkey]
      | +-SelectListItem
      | | +-AttributeReference[attribute_name=p_mfgr]
      | +-SelectListItem
      | | +-AttributeReference[attribute_name=s_address]
      | +-SelectListItem
      | | +-AttributeReference[attribute_name=s_phone]
      | +-SelectListItem
      |   +-AttributeReference[attribute_name=s_comment]
      +-where_clause=And
      | +-Equal
      | | +-left_operand=AttributeReference[attribute_name=p_partkey]
      | | +-right_operand=AttributeReference[attribute_name=ps_partkey]
      | +-Equal
      | | +-left_operand=AttributeReference[attribute_name=s_suppkey]
      | | +-right_operand=AttributeReference[attribute_name=ps_suppkey]
      | +-Equal
      | | +-left_operand=AttributeReference[attribute_name=p_size]
      | | +-right_operand=Literal
      | |   +-NumericLiteral[numeric_string=48,float_like=false]
      | +-Like
      | | +-left_operand=AttributeReference[attribute_name=p_type]
      | | +-right_operand=Literal
      | |   +-StringLiteral[value=%NICKEL]
      | +-Equal
      | | +-left_operand=AttributeReference[attribute_name=s_nationkey]
      | | +-right_operand=AttributeReference[attribute_name=n_nationkey]
      | +-Equal
      | | +-left_operand=AttributeReference[attribute_name=n_regionkey]
      | | +-right_operand=AttributeReference[attribute_name=r_regionkey]
      | +-Equal
      | | +-left_operand=AttributeReference[attribute_name=r_name]
      | | +-right_operand=Literal
      | |   +-StringLiteral[value=ASIA]
      | +-Equal
      |   +-left_operand=AttributeReference[attribute_name=ps_supplycost]
      |   +-right_operand=SubqueryExpression
      |     +-SetOperation[set_operation_type=Select]
      |       +-children=
      |         +-Select
      |           +-select_clause=SelectList
      |           | +-SelectListItem
      |           |   +-FunctionCall[name=MIN]
      |           |     +-AttributeReference[attribute_name=ps_supplycost]
      |           +-where_clause=And
      |           | +-Equal
      |           | | +-left_operand=AttributeReference[attribute_name=p_partkey]
      |           | | +-right_operand=AttributeReference[
      |           | |   attribute_name=ps_partkey]
      |           | +-Equal
      |           | | +-left_operand=AttributeReference[attribute_name=s_suppkey]
      |           | | +-right_operand=AttributeReference[
      |           | |   attribute_name=ps_suppkey]
      |           | +-Equal
      |           | | +-left_operand=AttributeReference[
      |           | | | attribute_name=s_nationkey]
      |           | | +-right_operand=AttributeReference[
      |           | |   attribute_name=n_nationkey]
      |           | +-Equal
      |           | | +-left_operand=AttributeReference[
      |           | | | attribute_name=n_regionkey]
      |           | | +-right_operand=AttributeReference[
      |           | |   attribute_name=r_regionkey]
      |           | +-Equal
      |           |   +-left_operand=AttributeReference[attribute_name=r_name]
      |           |   +-right_operand=Literal
      |           |     +-StringLiteral[value=ASIA]
      |           +-from_clause=
      |             +-TableReference[table=partsupp]
      |             +-TableReference[table=supplier]
      |             +-TableReference[table=nation]
      |             +-TableReference[table=region]
      +-order_by=OrderBy
      | +-OrderByItem[is_asc=false,nulls_first=true]
      | | +-AttributeReference[attribute_name=s_acctbal]
      | +-OrderByItem[is_asc=true,nulls_first=false]
      | | +-AttributeReference[attribute_name=n_name]
      | +-OrderByItem[is_asc=true,nulls_first=false]
      | | +-AttributeReference[attribute_name=s_name]
      | +-OrderByItem[is_asc=true,nulls_first=false]
      |   +-AttributeReference[attribute_name=p_partkey]
      +-limit=LIMIT
      | +-NumericLiteral[numeric_string=100,float_like=false]
      +-from_clause=
        +-TableReference[table=part]
        +-TableReference[table=supplier]
        +-TableReference[table=partsupp]
        +-TableReference[table=nation]
        +-TableReference[table=region]
==

# Query 3
SELECT
  l_orderkey,
  SUM(l_extendedprice * (1 - l_discount)) AS revenue,
  o_orderdate,
  o_shippriority
FROM
  customer,
  orders,
  lineitem
WHERE
  c_mktsegment = 'AUTOMOBILE'
  AND c_custkey = o_custkey
  AND l_orderkey = o_orderkey
  AND o_orderdate < DATE '1995-03-17'
  AND l_shipdate > DATE '1995-03-17'
GROUP BY
  l_orderkey,
  o_orderdate,
  o_shippriority
ORDER BY
  revenue desc,
  o_orderdate
LIMIT 10
--
SetOperationStatement
+-set_operation_query=SetOperation[set_operation_type=Select]
  +-children=
    +-Select
      +-select_clause=SelectList
      | +-SelectListItem
      | | +-AttributeReference[attribute_name=l_orderkey]
      | +-SelectListItem[alias=revenue]
      | | +-FunctionCall[name=SUM]
      | |   +-Multiply
      | |     +-left_operand=AttributeReference[attribute_name=l_extendedprice]
      | |     +-right_operand=Subtract
      | |       +-left_operand=Literal
      | |       | +-NumericLiteral[numeric_string=1,float_like=false]
      | |       +-right_operand=AttributeReference[attribute_name=l_discount]
      | +-SelectListItem
      | | +-AttributeReference[attribute_name=o_orderdate]
      | +-SelectListItem
      |   +-AttributeReference[attribute_name=o_shippriority]
      +-where_clause=And
      | +-Equal
      | | +-left_operand=AttributeReference[attribute_name=c_mktsegment]
      | | +-right_operand=Literal
      | |   +-StringLiteral[value=AUTOMOBILE]
      | +-Equal
      | | +-left_operand=AttributeReference[attribute_name=c_custkey]
      | | +-right_operand=AttributeReference[attribute_name=o_custkey]
      | +-Equal
      | | +-left_operand=AttributeReference[attribute_name=l_orderkey]
      | | +-right_operand=AttributeReference[attribute_name=o_orderkey]
      | +-Less
      | | +-left_operand=AttributeReference[attribute_name=o_orderdate]
      | | +-right_operand=Literal
      | |   +-StringLiteral[value=1995-03-17,explicit_type=Date]
      | +-Greater
      |   +-left_operand=AttributeReference[attribute_name=l_shipdate]
      |   +-right_operand=Literal
      |     +-StringLiteral[value=1995-03-17,explicit_type=Date]
      +-group_by=GroupBy
      | +-AttributeReference[attribute_name=l_orderkey]
      | +-AttributeReference[attribute_name=o_orderdate]
      | +-AttributeReference[attribute_name=o_shippriority]
      +-order_by=OrderBy
      | +-OrderByItem[is_asc=false,nulls_first=true]
      | | +-AttributeReference[attribute_name=revenue]
      | +-OrderByItem[is_asc=true,nulls_first=false]
      |   +-AttributeReference[attribute_name=o_orderdate]
      +-limit=LIMIT
      | +-NumericLiteral[numeric_string=10,float_like=false]
      +-from_clause=
        +-TableReference[table=customer]
        +-TableReference[table=orders]
        +-TableReference[table=lineitem]
==

# Query 4
SELECT
  o_orderpriority,
  COUNT(*) AS order_count
FROM
  orders
WHERE
  o_orderdate >= DATE '1995-08-01'
  AND o_orderdate < DATE '1995-08-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
--
SetOperationStatement
+-set_operation_query=SetOperation[set_operation_type=Select]
  +-children=
    +-Select
      +-select_clause=SelectList
      | +-SelectListItem
      | | +-AttributeReference[attribute_name=o_orderpriority]
      | +-SelectListItem[alias=order_count]
      |   +-FunctionCall[name=COUNT,is_star=true]
      +-where_clause=And
      | +-GreaterOrEqual
      | | +-left_operand=AttributeReference[attribute_name=o_orderdate]
      | | +-right_operand=Literal
      | |   +-StringLiteral[value=1995-08-01,explicit_type=Date]
      | +-Less
      | | +-left_operand=AttributeReference[attribute_name=o_orderdate]
      | | +-right_operand=Add
      | |   +-left_operand=Literal
      | |   | +-StringLiteral[value=1995-08-01,explicit_type=Date]
      | |   +-right_operand=Literal
      | |     +-StringLiteral[value=3 month,explicit_type=YearMonthInterval]
      | +-Exists
      |   +-subquery=SubqueryExpression
      |     +-SetOperation[set_operation_type=Select]
      |       +-children=
      |         +-Select
      |           +-select_clause=SelectStar
      |           +-where_clause=And
      |           | +-Equal
      |           | | +-left_operand=AttributeReference[
      |           | | | attribute_name=l_orderkey]
      |           | | +-right_operand=AttributeReference[
      |           | |   attribute_name=o_orderkey]
      |           | +-Less
      |           |   +-left_operand=AttributeReference[
      |           |   | attribute_name=l_commitdate]
      |           |   +-right_operand=AttributeReference[
      |           |     attribute_name=l_receiptdate]
      |           +-from_clause=
      |             +-TableReference[table=lineitem]
      +-group_by=GroupBy
      | +-AttributeReference[attribute_name=o_orderpriority]
      +-order_by=OrderBy
      | +-OrderByItem[is_asc=true,nulls_first=false]
      |   +-AttributeReference[attribute_name=o_orderpriority]
      +-from_clause=
        +-TableReference[table=orders]
==

# Query 5
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 = 'AMERICA'
  AND o_orderdate >= DATE '1997-01-01'
  AND o_orderdate < DATE '1997-01-01' + INTERVAL '1 year'
GROUP BY
  n_name
ORDER BY
  revenue DESC
--
SetOperationStatement
+-set_operation_query=SetOperation[set_operation_type=Select]
  +-children=
    +-Select
      +-select_clause=SelectList
      | +-SelectListItem
      | | +-AttributeReference[attribute_name=n_name]
      | +-SelectListItem[alias=revenue]
      |   +-FunctionCall[name=SUM]
      |     +-Multiply
      |       +-left_operand=AttributeReference[attribute_name=l_extendedprice]
      |       +-right_operand=Subtract
      |         +-left_operand=Literal
      |         | +-NumericLiteral[numeric_string=1,float_like=false]
      |         +-right_operand=AttributeReference[attribute_name=l_discount]
      +-where_clause=And
      | +-Equal
      | | +-left_operand=AttributeReference[attribute_name=c_custkey]
      | | +-right_operand=AttributeReference[attribute_name=o_custkey]
      | +-Equal
      | | +-left_operand=AttributeReference[attribute_name=l_orderkey]
      | | +-right_operand=AttributeReference[attribute_name=o_orderkey]
      | +-Equal
      | | +-left_operand=AttributeReference[attribute_name=l_suppkey]
      | | +-right_operand=AttributeReference[attribute_name=s_suppkey]
      | +-Equal
      | | +-left_operand=AttributeReference[attribute_name=c_nationkey]
      | | +-right_operand=AttributeReference[attribute_name=s_nationkey]
      | +-Equal
      | | +-left_operand=AttributeReference[attribute_name=s_nationkey]
      | | +-right_operand=AttributeReference[attribute_name=n_nationkey]
      | +-Equal
      | | +-left_operand=AttributeReference[attribute_name=n_regionkey]
      | | +-right_operand=AttributeReference[attribute_name=r_regionkey]
      | +-Equal
      | | +-left_operand=AttributeReference[attribute_name=r_name]
      | | +-right_operand=Literal
      | |   +-StringLiteral[value=AMERICA]
      | +-GreaterOrEqual
      | | +-left_operand=AttributeReference[attribute_name=o_orderdate]
      | | +-right_operand=Literal
      | |   +-StringLiteral[value=1997-01-01,explicit_type=Date]
      | +-Less
      |   +-left_operand=AttributeReference[attribute_name=o_orderdate]
      |   +-right_operand=Add
      |     +-left_operand=Literal
      |     | +-StringLiteral[value=1997-01-01,explicit_type=Date]
      |     +-right_operand=Literal
      |       +-StringLiteral[value=1 year,explicit_type=YearMonthInterval]
      +-group_by=GroupBy
      | +-AttributeReference[attribute_name=n_name]
      +-order_by=OrderBy
      | +-OrderByItem[is_asc=false,nulls_first=true]
      |   +-AttributeReference[attribute_name=revenue]
      +-from_clause=
        +-TableReference[table=customer]
        +-TableReference[table=orders]
        +-TableReference[table=lineitem]
        +-TableReference[table=supplier]
        +-TableReference[table=nation]
        +-TableReference[table=region]
==

# Query 6
SELECT
  SUM(l_extendedprice * l_discount) AS revenue
FROM
  lineitem
WHERE
  l_shipdate >= DATE '1997-01-01'
  AND l_shipdate < DATE '1997-01-01' + INTERVAL '1 year'
  AND l_discount BETWEEN 0.03 - 0.01 AND 0.03 + 0.01
  AND l_quantity < 25
--
SetOperationStatement
+-set_operation_query=SetOperation[set_operation_type=Select]
  +-children=
    +-Select
      +-select_clause=SelectList
      | +-SelectListItem[alias=revenue]
      |   +-FunctionCall[name=SUM]
      |     +-Multiply
      |       +-left_operand=AttributeReference[attribute_name=l_extendedprice]
      |       +-right_operand=AttributeReference[attribute_name=l_discount]
      +-where_clause=And
      | +-GreaterOrEqual
      | | +-left_operand=AttributeReference[attribute_name=l_shipdate]
      | | +-right_operand=Literal
      | |   +-StringLiteral[value=1997-01-01,explicit_type=Date]
      | +-Less
      | | +-left_operand=AttributeReference[attribute_name=l_shipdate]
      | | +-right_operand=Add
      | |   +-left_operand=Literal
      | |   | +-StringLiteral[value=1997-01-01,explicit_type=Date]
      | |   +-right_operand=Literal
      | |     +-StringLiteral[value=1 year,explicit_type=YearMonthInterval]
      | +-Between
      | | +-check_operand=AttributeReference[attribute_name=l_discount]
      | | +-lower_bound_operand=Subtract
      | | | +-left_operand=Literal
      | | | | +-NumericLiteral[numeric_string=0.03,float_like=true]
      | | | +-right_operand=Literal
      | | |   +-NumericLiteral[numeric_string=0.01,float_like=true]
      | | +-upper_bound_operand=Add
      | |   +-left_operand=Literal
      | |   | +-NumericLiteral[numeric_string=0.03,float_like=true]
      | |   +-right_operand=Literal
      | |     +-NumericLiteral[numeric_string=0.01,float_like=true]
      | +-Less
      |   +-left_operand=AttributeReference[attribute_name=l_quantity]
      |   +-right_operand=Literal
      |     +-NumericLiteral[numeric_string=25,float_like=false]
      +-from_clause=
        +-TableReference[table=lineitem]
==

# Query 7
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 = 'ETHIOPIA' AND n2.n_name = 'UNITED STATES')
        OR (n1.n_name = 'UNITED STATES' AND n2.n_name = 'ETHIOPIA')
      )
      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
--
SetOperationStatement
+-set_operation_query=SetOperation[set_operation_type=Select]
  +-children=
    +-Select
      +-select_clause=SelectList
      | +-SelectListItem
      | | +-AttributeReference[attribute_name=supp_nation]
      | +-SelectListItem
      | | +-AttributeReference[attribute_name=cust_nation]
      | +-SelectListItem
      | | +-AttributeReference[attribute_name=l_year]
      | +-SelectListItem[alias=revenue]
      |   +-FunctionCall[name=SUM]
      |     +-AttributeReference[attribute_name=volume]
      +-group_by=GroupBy
      | +-AttributeReference[attribute_name=supp_nation]
      | +-AttributeReference[attribute_name=cust_nation]
      | +-AttributeReference[attribute_name=l_year]
      +-order_by=OrderBy
      | +-OrderByItem[is_asc=true,nulls_first=false]
      | | +-AttributeReference[attribute_name=supp_nation]
      | +-OrderByItem[is_asc=true,nulls_first=false]
      | | +-AttributeReference[attribute_name=cust_nation]
      | +-OrderByItem[is_asc=true,nulls_first=false]
      |   +-AttributeReference[attribute_name=l_year]
      +-from_clause=
        +-SubqueryTable
          +-table_signature=TableSignature[table_alias=shipping]
          +-SubqueryExpression
            +-SetOperation[set_operation_type=Select]
              +-children=
                +-Select
                  +-select_clause=SelectList
                  | +-SelectListItem[alias=supp_nation]
                  | | +-AttributeReference[attribute_name=n_name,
                  | |   relation_name=n1]
                  | +-SelectListItem[alias=cust_nation]
                  | | +-AttributeReference[attribute_name=n_name,
                  | |   relation_name=n2]
                  | +-SelectListItem[alias=l_year]
                  | | +-Extract[unit=YEAR]
                  | |   +-date_expression=AttributeReference[
                  | |     attribute_name=l_shipdate]
                  | +-SelectListItem[alias=volume]
                  |   +-Multiply
                  |     +-left_operand=AttributeReference[
                  |     | attribute_name=l_extendedprice]
                  |     +-right_operand=Subtract
                  |       +-left_operand=Literal
                  |       | +-NumericLiteral[numeric_string=1,float_like=false]
                  |       +-right_operand=AttributeReference[
                  |         attribute_name=l_discount]
                  +-where_clause=And
                  | +-Equal
                  | | +-left_operand=AttributeReference[attribute_name=s_suppkey]
                  | | +-right_operand=AttributeReference[
                  | |   attribute_name=l_suppkey]
                  | +-Equal
                  | | +-left_operand=AttributeReference[
                  | | | attribute_name=o_orderkey]
                  | | +-right_operand=AttributeReference[
                  | |   attribute_name=l_orderkey]
                  | +-Equal
                  | | +-left_operand=AttributeReference[attribute_name=c_custkey]
                  | | +-right_operand=AttributeReference[
                  | |   attribute_name=o_custkey]
                  | +-Equal
                  | | +-left_operand=AttributeReference[
                  | | | attribute_name=s_nationkey]
                  | | +-right_operand=AttributeReference[
                  | |   attribute_name=n_nationkey,relation_name=n1]
                  | +-Equal
                  | | +-left_operand=AttributeReference[
                  | | | attribute_name=c_nationkey]
                  | | +-right_operand=AttributeReference[
                  | |   attribute_name=n_nationkey,relation_name=n2]
                  | +-Or
                  | | +-And
                  | | | +-Equal
                  | | | | +-left_operand=AttributeReference[
                  | | | | | attribute_name=n_name,relation_name=n1]
                  | | | | +-right_operand=Literal
                  | | | |   +-StringLiteral[value=ETHIOPIA]
                  | | | +-Equal
                  | | |   +-left_operand=AttributeReference[
                  | | |   | attribute_name=n_name,relation_name=n2]
                  | | |   +-right_operand=Literal
                  | | |     +-StringLiteral[value=UNITED STATES]
                  | | +-And
                  | |   +-Equal
                  | |   | +-left_operand=AttributeReference[
                  | |   | | attribute_name=n_name,relation_name=n1]
                  | |   | +-right_operand=Literal
                  | |   |   +-StringLiteral[value=UNITED STATES]
                  | |   +-Equal
                  | |     +-left_operand=AttributeReference[
                  | |     | attribute_name=n_name,relation_name=n2]
                  | |     +-right_operand=Literal
                  | |       +-StringLiteral[value=ETHIOPIA]
                  | +-Between
                  |   +-check_operand=AttributeReference[
                  |   | attribute_name=l_shipdate]
                  |   +-lower_bound_operand=Literal
                  |   | +-StringLiteral[value=1995-01-01,explicit_type=Date]
                  |   +-upper_bound_operand=Literal
                  |     +-StringLiteral[value=1996-12-31,explicit_type=Date]
                  +-from_clause=
                    +-TableReference[table=supplier]
                    +-TableReference[table=lineitem]
                    +-TableReference[table=orders]
                    +-TableReference[table=customer]
                    +-TableReference[table=nation]
                    | +-table_signature=TableSignature[table_alias=n1]
                    +-TableReference[table=nation]
                      +-table_signature=TableSignature[table_alias=n2]
==

# Query 8
SELECT
  o_year,
  SUM(CASE
    WHEN nation = 'UNITED STATES' 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 = 'MEDIUM ANODIZED NICKEL'
  ) AS all_nations
GROUP BY
  o_year
ORDER BY
  o_year
--
SetOperationStatement
+-set_operation_query=SetOperation[set_operation_type=Select]
  +-children=
    +-Select
      +-select_clause=SelectList
      | +-SelectListItem
      | | +-AttributeReference[attribute_name=o_year]
      | +-SelectListItem[alias=mkt_share]
      |   +-Divide
      |     +-left_operand=FunctionCall[name=SUM]
      |     | +-SearchedCaseExpression
      |     |   +-else_result_expression=Literal
      |     |   | +-NumericLiteral[numeric_string=0,float_like=false]
      |     |   +-when_clauses=
      |     |     +-SearchedWhenClause
      |     |       +-condition_predicate=Equal
      |     |       | +-left_operand=AttributeReference[attribute_name=nation]
      |     |       | +-right_operand=Literal
      |     |       |   +-StringLiteral[value=UNITED STATES]
      |     |       +-result_expression=AttributeReference[attribute_name=volume]
      |     +-right_operand=FunctionCall[name=SUM]
      |       +-AttributeReference[attribute_name=volume]
      +-group_by=GroupBy
      | +-AttributeReference[attribute_name=o_year]
      +-order_by=OrderBy
      | +-OrderByItem[is_asc=true,nulls_first=false]
      |   +-AttributeReference[attribute_name=o_year]
      +-from_clause=
        +-SubqueryTable
          +-table_signature=TableSignature[table_alias=all_nations]
          +-SubqueryExpression
            +-SetOperation[set_operation_type=Select]
              +-children=
                +-Select
                  +-select_clause=SelectList
                  | +-SelectListItem[alias=o_year]
                  | | +-Extract[unit=YEAR]
                  | |   +-date_expression=AttributeReference[
                  | |     attribute_name=o_orderdate]
                  | +-SelectListItem[alias=volume]
                  | | +-Multiply
                  | |   +-left_operand=AttributeReference[
                  | |   | attribute_name=l_extendedprice]
                  | |   +-right_operand=Subtract
                  | |     +-left_operand=Literal
                  | |     | +-NumericLiteral[numeric_string=1,float_like=false]
                  | |     +-right_operand=AttributeReference[
                  | |       attribute_name=l_discount]
                  | +-SelectListItem[alias=nation]
                  |   +-AttributeReference[attribute_name=n_name,
                  |     relation_name=n2]
                  +-where_clause=And
                  | +-Equal
                  | | +-left_operand=AttributeReference[attribute_name=p_partkey]
                  | | +-right_operand=AttributeReference[
                  | |   attribute_name=l_partkey]
                  | +-Equal
                  | | +-left_operand=AttributeReference[attribute_name=s_suppkey]
                  | | +-right_operand=AttributeReference[
                  | |   attribute_name=l_suppkey]
                  | +-Equal
                  | | +-left_operand=AttributeReference[
                  | | | attribute_name=l_orderkey]
                  | | +-right_operand=AttributeReference[
                  | |   attribute_name=o_orderkey]
                  | +-Equal
                  | | +-left_operand=AttributeReference[attribute_name=o_custkey]
                  | | +-right_operand=AttributeReference[
                  | |   attribute_name=c_custkey]
                  | +-Equal
                  | | +-left_operand=AttributeReference[
                  | | | attribute_name=c_nationkey]
                  | | +-right_operand=AttributeReference[
                  | |   attribute_name=n_nationkey,relation_name=n1]
                  | +-Equal
                  | | +-left_operand=AttributeReference[
                  | | | attribute_name=n_regionkey,relation_name=n1]
                  | | +-right_operand=AttributeReference[
                  | |   attribute_name=r_regionkey]
                  | +-Equal
                  | | +-left_operand=AttributeReference[attribute_name=r_name]
                  | | +-right_operand=Literal
                  | |   +-StringLiteral[value=AMERICA]
                  | +-Equal
                  | | +-left_operand=AttributeReference[
                  | | | attribute_name=s_nationkey]
                  | | +-right_operand=AttributeReference[
                  | |   attribute_name=n_nationkey,relation_name=n2]
                  | +-Between
                  | | +-check_operand=AttributeReference[
                  | | | attribute_name=o_orderdate]
                  | | +-lower_bound_operand=Literal
                  | | | +-StringLiteral[value=1995-01-01,explicit_type=Date]
                  | | +-upper_bound_operand=Literal
                  | |   +-StringLiteral[value=1996-12-31,explicit_type=Date]
                  | +-Equal
                  |   +-left_operand=AttributeReference[attribute_name=p_type]
                  |   +-right_operand=Literal
                  |     +-StringLiteral[value=MEDIUM ANODIZED NICKEL]
                  +-from_clause=
                    +-TableReference[table=part]
                    +-TableReference[table=supplier]
                    +-TableReference[table=lineitem]
                    +-TableReference[table=orders]
                    +-TableReference[table=customer]
                    +-TableReference[table=nation]
                    | +-table_signature=TableSignature[table_alias=n1]
                    +-TableReference[table=nation]
                    | +-table_signature=TableSignature[table_alias=n2]
                    +-TableReference[table=region]
==

# Query 9
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 '%ghost%'
  ) AS profit
GROUP BY
  nation,
  o_year
ORDER BY
  nation,
  o_year DESC
--
SetOperationStatement
+-set_operation_query=SetOperation[set_operation_type=Select]
  +-children=
    +-Select
      +-select_clause=SelectList
      | +-SelectListItem
      | | +-AttributeReference[attribute_name=nation]
      | +-SelectListItem
      | | +-AttributeReference[attribute_name=o_year]
      | +-SelectListItem[alias=sum_profit]
      |   +-FunctionCall[name=SUM]
      |     +-AttributeReference[attribute_name=amount]
      +-group_by=GroupBy
      | +-AttributeReference[attribute_name=nation]
      | +-AttributeReference[attribute_name=o_year]
      +-order_by=OrderBy
      | +-OrderByItem[is_asc=true,nulls_first=false]
      | | +-AttributeReference[attribute_name=nation]
      | +-OrderByItem[is_asc=false,nulls_first=true]
      |   +-AttributeReference[attribute_name=o_year]
      +-from_clause=
        +-SubqueryTable
          +-table_signature=TableSignature[table_alias=profit]
          +-SubqueryExpression
            +-SetOperation[set_operation_type=Select]
              +-children=
                +-Select
                  +-select_clause=SelectList
                  | +-SelectListItem[alias=nation]
                  | | +-AttributeReference[attribute_name=n_name]
                  | +-SelectListItem[alias=o_year]
                  | | +-Extract[unit=YEAR]
                  | |   +-date_expression=AttributeReference[
                  | |     attribute_name=o_orderdate]
                  | +-SelectListItem[alias=amount]
                  |   +-Subtract
                  |     +-left_operand=Multiply
                  |     | +-left_operand=AttributeReference[
                  |     | | attribute_name=l_extendedprice]
                  |     | +-right_operand=Subtract
                  |     |   +-left_operand=Literal
                  |     |   | +-NumericLiteral[numeric_string=1,float_like=false]
                  |     |   +-right_operand=AttributeReference[
                  |     |     attribute_name=l_discount]
                  |     +-right_operand=Multiply
                  |       +-left_operand=AttributeReference[
                  |       | attribute_name=ps_supplycost]
                  |       +-right_operand=AttributeReference[
                  |         attribute_name=l_quantity]
                  +-where_clause=And
                  | +-Equal
                  | | +-left_operand=AttributeReference[attribute_name=s_suppkey]
                  | | +-right_operand=AttributeReference[
                  | |   attribute_name=l_suppkey]
                  | +-Equal
                  | | +-left_operand=AttributeReference[
                  | | | attribute_name=ps_suppkey]
                  | | +-right_operand=AttributeReference[
                  | |   attribute_name=l_suppkey]
                  | +-Equal
                  | | +-left_operand=AttributeReference[
                  | | | attribute_name=ps_partkey]
                  | | +-right_operand=AttributeReference[
                  | |   attribute_name=l_partkey]
                  | +-Equal
                  | | +-left_operand=AttributeReference[attribute_name=p_partkey]
                  | | +-right_operand=AttributeReference[
                  | |   attribute_name=l_partkey]
                  | +-Equal
                  | | +-left_operand=AttributeReference[
                  | | | attribute_name=o_orderkey]
                  | | +-right_operand=AttributeReference[
                  | |   attribute_name=l_orderkey]
                  | +-Equal
                  | | +-left_operand=AttributeReference[
                  | | | attribute_name=s_nationkey]
                  | | +-right_operand=AttributeReference[
                  | |   attribute_name=n_nationkey]
                  | +-Like
                  |   +-left_operand=AttributeReference[attribute_name=p_name]
                  |   +-right_operand=Literal
                  |     +-StringLiteral[value=%ghost%]
                  +-from_clause=
                    +-TableReference[table=part]
                    +-TableReference[table=supplier]
                    +-TableReference[table=lineitem]
                    +-TableReference[table=partsupp]
                    +-TableReference[table=orders]
                    +-TableReference[table=nation]
==

# Query 10
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 '1994-03-01'
  AND o_orderdate < DATE '1994-03-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
--
SetOperationStatement
+-set_operation_query=SetOperation[set_operation_type=Select]
  +-children=
    +-Select
      +-select_clause=SelectList
      | +-SelectListItem
      | | +-AttributeReference[attribute_name=c_custkey]
      | +-SelectListItem
      | | +-AttributeReference[attribute_name=c_name]
      | +-SelectListItem[alias=revenue]
      | | +-FunctionCall[name=SUM]
      | |   +-Multiply
      | |     +-left_operand=AttributeReference[attribute_name=l_extendedprice]
      | |     +-right_operand=Subtract
      | |       +-left_operand=Literal
      | |       | +-NumericLiteral[numeric_string=1,float_like=false]
      | |       +-right_operand=AttributeReference[attribute_name=l_discount]
      | +-SelectListItem
      | | +-AttributeReference[attribute_name=c_acctbal]
      | +-SelectListItem
      | | +-AttributeReference[attribute_name=n_name]
      | +-SelectListItem
      | | +-AttributeReference[attribute_name=c_address]
      | +-SelectListItem
      | | +-AttributeReference[attribute_name=c_phone]
      | +-SelectListItem
      |   +-AttributeReference[attribute_name=c_comment]
      +-where_clause=And
      | +-Equal
      | | +-left_operand=AttributeReference[attribute_name=c_custkey]
      | | +-right_operand=AttributeReference[attribute_name=o_custkey]
      | +-Equal
      | | +-left_operand=AttributeReference[attribute_name=l_orderkey]
      | | +-right_operand=AttributeReference[attribute_name=o_orderkey]
      | +-GreaterOrEqual
      | | +-left_operand=AttributeReference[attribute_name=o_orderdate]
      | | +-right_operand=Literal
      | |   +-StringLiteral[value=1994-03-01,explicit_type=Date]
      | +-Less
      | | +-left_operand=AttributeReference[attribute_name=o_orderdate]
      | | +-right_operand=Add
      | |   +-left_operand=Literal
      | |   | +-StringLiteral[value=1994-03-01,explicit_type=Date]
      | |   +-right_operand=Literal
      | |     +-StringLiteral[value=3 month,explicit_type=YearMonthInterval]
      | +-Equal
      | | +-left_operand=AttributeReference[attribute_name=l_returnflag]
      | | +-right_operand=Literal
      | |   +-StringLiteral[value=R]
      | +-Equal
      |   +-left_operand=AttributeReference[attribute_name=c_nationkey]
      |   +-right_operand=AttributeReference[attribute_name=n_nationkey]
      +-group_by=GroupBy
      | +-AttributeReference[attribute_name=c_custkey]
      | +-AttributeReference[attribute_name=c_name]
      | +-AttributeReference[attribute_name=c_acctbal]
      | +-AttributeReference[attribute_name=c_phone]
      | +-AttributeReference[attribute_name=n_name]
      | +-AttributeReference[attribute_name=c_address]
      | +-AttributeReference[attribute_name=c_comment]
      +-order_by=OrderBy
      | +-OrderByItem[is_asc=false,nulls_first=true]
      |   +-AttributeReference[attribute_name=revenue]
      +-limit=LIMIT
      | +-NumericLiteral[numeric_string=20,float_like=false]
      +-from_clause=
        +-TableReference[table=customer]
        +-TableReference[table=orders]
        +-TableReference[table=lineitem]
        +-TableReference[table=nation]
==

# Query 11
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 = 'INDONESIA'
GROUP BY
  ps_partkey HAVING
    SUM(ps_supplycost * ps_availqty) > (
      SELECT
        SUM(ps_supplycost * ps_availqty) * 0.0000010000
      FROM
        partsupp,
        supplier,
        nation
      WHERE
        ps_suppkey = s_suppkey
        AND s_nationkey = n_nationkey
        AND n_name = 'INDONESIA'
    )
ORDER BY
  value DESC
--
SetOperationStatement
+-set_operation_query=SetOperation[set_operation_type=Select]
  +-children=
    +-Select
      +-select_clause=SelectList
      | +-SelectListItem
      | | +-AttributeReference[attribute_name=ps_partkey]
      | +-SelectListItem[alias=value]
      |   +-FunctionCall[name=SUM]
      |     +-Multiply
      |       +-left_operand=AttributeReference[attribute_name=ps_supplycost]
      |       +-right_operand=AttributeReference[attribute_name=ps_availqty]
      +-where_clause=And
      | +-Equal
      | | +-left_operand=AttributeReference[attribute_name=ps_suppkey]
      | | +-right_operand=AttributeReference[attribute_name=s_suppkey]
      | +-Equal
      | | +-left_operand=AttributeReference[attribute_name=s_nationkey]
      | | +-right_operand=AttributeReference[attribute_name=n_nationkey]
      | +-Equal
      |   +-left_operand=AttributeReference[attribute_name=n_name]
      |   +-right_operand=Literal
      |     +-StringLiteral[value=INDONESIA]
      +-group_by=GroupBy
      | +-AttributeReference[attribute_name=ps_partkey]
      +-having=HAVING
      | +-Greater
      |   +-left_operand=FunctionCall[name=SUM]
      |   | +-Multiply
      |   |   +-left_operand=AttributeReference[attribute_name=ps_supplycost]
      |   |   +-right_operand=AttributeReference[attribute_name=ps_availqty]
      |   +-right_operand=SubqueryExpression
      |     +-SetOperation[set_operation_type=Select]
      |       +-children=
      |         +-Select
      |           +-select_clause=SelectList
      |           | +-SelectListItem
      |           |   +-Multiply
      |           |     +-left_operand=FunctionCall[name=SUM]
      |           |     | +-Multiply
      |           |     |   +-left_operand=AttributeReference[
      |           |     |   | attribute_name=ps_supplycost]
      |           |     |   +-right_operand=AttributeReference[
      |           |     |     attribute_name=ps_availqty]
      |           |     +-right_operand=Literal
      |           |       +-NumericLiteral[numeric_string=0.0000010000,
      |           |         float_like=true]
      |           +-where_clause=And
      |           | +-Equal
      |           | | +-left_operand=AttributeReference[
      |           | | | attribute_name=ps_suppkey]
      |           | | +-right_operand=AttributeReference[
      |           | |   attribute_name=s_suppkey]
      |           | +-Equal
      |           | | +-left_operand=AttributeReference[
      |           | | | attribute_name=s_nationkey]
      |           | | +-right_operand=AttributeReference[
      |           | |   attribute_name=n_nationkey]
      |           | +-Equal
      |           |   +-left_operand=AttributeReference[attribute_name=n_name]
      |           |   +-right_operand=Literal
      |           |     +-StringLiteral[value=INDONESIA]
      |           +-from_clause=
      |             +-TableReference[table=partsupp]
      |             +-TableReference[table=supplier]
      |             +-TableReference[table=nation]
      +-order_by=OrderBy
      | +-OrderByItem[is_asc=false,nulls_first=true]
      |   +-AttributeReference[attribute_name=value]
      +-from_clause=
        +-TableReference[table=partsupp]
        +-TableReference[table=supplier]
        +-TableReference[table=nation]
==

# Query 12
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 ('REG AIR', 'RAIL')
  AND l_commitdate < l_receiptdate
  AND l_shipdate < l_commitdate
  AND l_receiptdate >= DATE '1997-01-01'
  AND l_receiptdate < DATE '1997-01-01' + INTERVAL '1 year'
GROUP BY
  l_shipmode
ORDER BY
  l_shipmode
--
SetOperationStatement
+-set_operation_query=SetOperation[set_operation_type=Select]
  +-children=
    +-Select
      +-select_clause=SelectList
      | +-SelectListItem
      | | +-AttributeReference[attribute_name=l_shipmode]
      | +-SelectListItem[alias=high_line_count]
      | | +-FunctionCall[name=SUM]
      | |   +-SearchedCaseExpression
      | |     +-else_result_expression=Literal
      | |     | +-NumericLiteral[numeric_string=0,float_like=false]
      | |     +-when_clauses=
      | |       +-SearchedWhenClause
      | |         +-condition_predicate=Or
      | |         | +-Equal
      | |         | | +-left_operand=AttributeReference[
      | |         | | | attribute_name=o_orderpriority]
      | |         | | +-right_operand=Literal
      | |         | |   +-StringLiteral[value=1-URGENT]
      | |         | +-Equal
      | |         |   +-left_operand=AttributeReference[
      | |         |   | attribute_name=o_orderpriority]
      | |         |   +-right_operand=Literal
      | |         |     +-StringLiteral[value=2-HIGH]
      | |         +-result_expression=Literal
      | |           +-NumericLiteral[numeric_string=1,float_like=false]
      | +-SelectListItem[alias=low_line_count]
      |   +-FunctionCall[name=SUM]
      |     +-SearchedCaseExpression
      |       +-else_result_expression=Literal
      |       | +-NumericLiteral[numeric_string=0,float_like=false]
      |       +-when_clauses=
      |         +-SearchedWhenClause
      |           +-condition_predicate=And
      |           | +-NotEqual
      |           | | +-left_operand=AttributeReference[
      |           | | | attribute_name=o_orderpriority]
      |           | | +-right_operand=Literal
      |           | |   +-StringLiteral[value=1-URGENT]
      |           | +-NotEqual
      |           |   +-left_operand=AttributeReference[
      |           |   | attribute_name=o_orderpriority]
      |           |   +-right_operand=Literal
      |           |     +-StringLiteral[value=2-HIGH]
      |           +-result_expression=Literal
      |             +-NumericLiteral[numeric_string=1,float_like=false]
      +-where_clause=And
      | +-Equal
      | | +-left_operand=AttributeReference[attribute_name=o_orderkey]
      | | +-right_operand=AttributeReference[attribute_name=l_orderkey]
      | +-InValueList
      | | +-test_expression=AttributeReference[attribute_name=l_shipmode]
      | | +-value_list=
      | |   +-Literal
      | |   | +-StringLiteral[value=REG AIR]
      | |   +-Literal
      | |     +-StringLiteral[value=RAIL]
      | +-Less
      | | +-left_operand=AttributeReference[attribute_name=l_commitdate]
      | | +-right_operand=AttributeReference[attribute_name=l_receiptdate]
      | +-Less
      | | +-left_operand=AttributeReference[attribute_name=l_shipdate]
      | | +-right_operand=AttributeReference[attribute_name=l_commitdate]
      | +-GreaterOrEqual
      | | +-left_operand=AttributeReference[attribute_name=l_receiptdate]
      | | +-right_operand=Literal
      | |   +-StringLiteral[value=1997-01-01,explicit_type=Date]
      | +-Less
      |   +-left_operand=AttributeReference[attribute_name=l_receiptdate]
      |   +-right_operand=Add
      |     +-left_operand=Literal
      |     | +-StringLiteral[value=1997-01-01,explicit_type=Date]
      |     +-right_operand=Literal
      |       +-StringLiteral[value=1 year,explicit_type=YearMonthInterval]
      +-group_by=GroupBy
      | +-AttributeReference[attribute_name=l_shipmode]
      +-order_by=OrderBy
      | +-OrderByItem[is_asc=true,nulls_first=false]
      |   +-AttributeReference[attribute_name=l_shipmode]
      +-from_clause=
        +-TableReference[table=orders]
        +-TableReference[table=lineitem]
==

# Query 13
SELECT
  c_count,
  COUNT(*) AS custdist
FROM
  (
    SELECT
      c_custkey,
      COUNT(o_orderkey)
    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)
GROUP BY
  c_count
ORDER BY
  custdist desc,
  c_count DESC
--
SetOperationStatement
+-set_operation_query=SetOperation[set_operation_type=Select]
  +-children=
    +-Select
      +-select_clause=SelectList
      | +-SelectListItem
      | | +-AttributeReference[attribute_name=c_count]
      | +-SelectListItem[alias=custdist]
      |   +-FunctionCall[name=COUNT,is_star=true]
      +-group_by=GroupBy
      | +-AttributeReference[attribute_name=c_count]
      +-order_by=OrderBy
      | +-OrderByItem[is_asc=false,nulls_first=true]
      | | +-AttributeReference[attribute_name=custdist]
      | +-OrderByItem[is_asc=false,nulls_first=true]
      |   +-AttributeReference[attribute_name=c_count]
      +-from_clause=
        +-SubqueryTable
          +-table_signature=TableSignature[table_alias=c_orders,
          | columns=(c_custkey, c_count)]
          +-SubqueryExpression
            +-SetOperation[set_operation_type=Select]
              +-children=
                +-Select
                  +-select_clause=SelectList
                  | +-SelectListItem
                  | | +-AttributeReference[attribute_name=c_custkey]
                  | +-SelectListItem
                  |   +-FunctionCall[name=COUNT]
                  |     +-AttributeReference[attribute_name=o_orderkey]
                  +-group_by=GroupBy
                  | +-AttributeReference[attribute_name=c_custkey]
                  +-from_clause=
                    +-JoinedTable[join_type=LeftOuterJoin]
                      +-left_table=TableReference[table=customer]
                      +-right_table=TableReference[table=orders]
                      +-join_predicate=And
                        +-Equal
                        | +-left_operand=AttributeReference[
                        | | attribute_name=c_custkey]
                        | +-right_operand=AttributeReference[
                        |   attribute_name=o_custkey]
                        +-NotLike
                          +-left_operand=AttributeReference[
                          | attribute_name=o_comment]
                          +-right_operand=Literal
                            +-StringLiteral[value=%special%requests%]
==

# Query 14
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 '1994-11-01'
  AND l_shipdate < DATE '1994-11-01' + INTERVAL '1 month'
--
SetOperationStatement
+-set_operation_query=SetOperation[set_operation_type=Select]
  +-children=
    +-Select
      +-select_clause=SelectList
      | +-SelectListItem[alias=promo_revenue]
      |   +-Divide
      |     +-left_operand=Multiply
      |     | +-left_operand=Literal
      |     | | +-NumericLiteral[numeric_string=100.00,float_like=true]
      |     | +-right_operand=FunctionCall[name=SUM]
      |     |   +-SearchedCaseExpression
      |     |     +-else_result_expression=Literal
      |     |     | +-NumericLiteral[numeric_string=0,float_like=false]
      |     |     +-when_clauses=
      |     |       +-SearchedWhenClause
      |     |         +-condition_predicate=Like
      |     |         | +-left_operand=AttributeReference[attribute_name=p_type]
      |     |         | +-right_operand=Literal
      |     |         |   +-StringLiteral[value=PROMO%]
      |     |         +-result_expression=Multiply
      |     |           +-left_operand=AttributeReference[
      |     |           | attribute_name=l_extendedprice]
      |     |           +-right_operand=Subtract
      |     |             +-left_operand=Literal
      |     |             | +-NumericLiteral[numeric_string=1,float_like=false]
      |     |             +-right_operand=AttributeReference[
      |     |               attribute_name=l_discount]
      |     +-right_operand=FunctionCall[name=SUM]
      |       +-Multiply
      |         +-left_operand=AttributeReference[attribute_name=l_extendedprice]
      |         +-right_operand=Subtract
      |           +-left_operand=Literal
      |           | +-NumericLiteral[numeric_string=1,float_like=false]
      |           +-right_operand=AttributeReference[attribute_name=l_discount]
      +-where_clause=And
      | +-Equal
      | | +-left_operand=AttributeReference[attribute_name=l_partkey]
      | | +-right_operand=AttributeReference[attribute_name=p_partkey]
      | +-GreaterOrEqual
      | | +-left_operand=AttributeReference[attribute_name=l_shipdate]
      | | +-right_operand=Literal
      | |   +-StringLiteral[value=1994-11-01,explicit_type=Date]
      | +-Less
      |   +-left_operand=AttributeReference[attribute_name=l_shipdate]
      |   +-right_operand=Add
      |     +-left_operand=Literal
      |     | +-StringLiteral[value=1994-11-01,explicit_type=Date]
      |     +-right_operand=Literal
      |       +-StringLiteral[value=1 month,explicit_type=YearMonthInterval]
      +-from_clause=
        +-TableReference[table=lineitem]
        +-TableReference[table=part]
==

# Query 15
WITH revenue (supplier_no, total_revenue) AS (
SELECT
  l_suppkey,
  sum(l_extendedprice * (1-l_discount))
FROM
  lineitem
  WHERE
  l_shipdate >= DATE '1996-11-01'
  AND l_shipdate < DATE '1996-11-01' + INTERVAL '3 month'
GROUP BY
  l_suppkey
) 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
--
SetOperationStatement
+-set_operation_query=SetOperation[set_operation_type=Select]
| +-children=
|   +-Select
|     +-select_clause=SelectList
|     | +-SelectListItem
|     | | +-AttributeReference[attribute_name=s_suppkey]
|     | +-SelectListItem
|     | | +-AttributeReference[attribute_name=s_name]
|     | +-SelectListItem
|     | | +-AttributeReference[attribute_name=s_address]
|     | +-SelectListItem
|     | | +-AttributeReference[attribute_name=s_phone]
|     | +-SelectListItem
|     |   +-AttributeReference[attribute_name=total_revenue]
|     +-where_clause=And
|     | +-Equal
|     | | +-left_operand=AttributeReference[attribute_name=s_suppkey]
|     | | +-right_operand=AttributeReference[attribute_name=supplier_no]
|     | +-Equal
|     |   +-left_operand=AttributeReference[attribute_name=total_revenue]
|     |   +-right_operand=SubqueryExpression
|     |     +-SetOperation[set_operation_type=Select]
|     |       +-children=
|     |         +-Select
|     |           +-select_clause=SelectList
|     |           | +-SelectListItem
|     |           |   +-FunctionCall[name=MAX]
|     |           |     +-AttributeReference[attribute_name=total_revenue]
|     |           +-from_clause=
|     |             +-TableReference[table=revenue]
|     +-order_by=OrderBy
|     | +-OrderByItem[is_asc=true,nulls_first=false]
|     |   +-AttributeReference[attribute_name=s_suppkey]
|     +-from_clause=
|       +-TableReference[table=supplier]
|       +-TableReference[table=revenue]
+-with_clause=
  +-SubqueryTable
    +-table_signature=TableSignature[table_alias=revenue,
    | columns=(supplier_no, total_revenue)]
    +-SubqueryExpression
      +-SetOperation[set_operation_type=Select]
        +-children=
          +-Select
            +-select_clause=SelectList
            | +-SelectListItem
            | | +-AttributeReference[attribute_name=l_suppkey]
            | +-SelectListItem
            |   +-FunctionCall[name=sum]
            |     +-Multiply
            |       +-left_operand=AttributeReference[
            |       | attribute_name=l_extendedprice]
            |       +-right_operand=Subtract
            |         +-left_operand=Literal
            |         | +-NumericLiteral[numeric_string=1,float_like=false]
            |         +-right_operand=AttributeReference[
            |           attribute_name=l_discount]
            +-where_clause=And
            | +-GreaterOrEqual
            | | +-left_operand=AttributeReference[attribute_name=l_shipdate]
            | | +-right_operand=Literal
            | |   +-StringLiteral[value=1996-11-01,explicit_type=Date]
            | +-Less
            |   +-left_operand=AttributeReference[attribute_name=l_shipdate]
            |   +-right_operand=Add
            |     +-left_operand=Literal
            |     | +-StringLiteral[value=1996-11-01,explicit_type=Date]
            |     +-right_operand=Literal
            |       +-StringLiteral[value=3 month,
            |         explicit_type=YearMonthInterval]
            +-group_by=GroupBy
            | +-AttributeReference[attribute_name=l_suppkey]
            +-from_clause=
              +-TableReference[table=lineitem]
==

# Query 16
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#22'
  AND p_type NOT LIKE 'ECONOMY BURNISHED%'
  AND p_size IN (32, 42, 9, 18, 50, 30, 12, 21)
  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
--
SetOperationStatement
+-set_operation_query=SetOperation[set_operation_type=Select]
  +-children=
    +-Select
      +-select_clause=SelectList
      | +-SelectListItem
      | | +-AttributeReference[attribute_name=p_brand]
      | +-SelectListItem
      | | +-AttributeReference[attribute_name=p_type]
      | +-SelectListItem
      | | +-AttributeReference[attribute_name=p_size]
      | +-SelectListItem[alias=supplier_cnt]
      |   +-FunctionCall[name=COUNT,is_distinct=true]
      |     +-AttributeReference[attribute_name=ps_suppkey]
      +-where_clause=And
      | +-Equal
      | | +-left_operand=AttributeReference[attribute_name=p_partkey]
      | | +-right_operand=AttributeReference[attribute_name=ps_partkey]
      | +-NotEqual
      | | +-left_operand=AttributeReference[attribute_name=p_brand]
      | | +-right_operand=Literal
      | |   +-StringLiteral[value=Brand#22]
      | +-NotLike
      | | +-left_operand=AttributeReference[attribute_name=p_type]
      | | +-right_operand=Literal
      | |   +-StringLiteral[value=ECONOMY BURNISHED%]
      | +-InValueList
      | | +-test_expression=AttributeReference[attribute_name=p_size]
      | | +-value_list=
      | |   +-Literal
      | |   | +-NumericLiteral[numeric_string=32,float_like=false]
      | |   +-Literal
      | |   | +-NumericLiteral[numeric_string=42,float_like=false]
      | |   +-Literal
      | |   | +-NumericLiteral[numeric_string=9,float_like=false]
      | |   +-Literal
      | |   | +-NumericLiteral[numeric_string=18,float_like=false]
      | |   +-Literal
      | |   | +-NumericLiteral[numeric_string=50,float_like=false]
      | |   +-Literal
      | |   | +-NumericLiteral[numeric_string=30,float_like=false]
      | |   +-Literal
      | |   | +-NumericLiteral[numeric_string=12,float_like=false]
      | |   +-Literal
      | |     +-NumericLiteral[numeric_string=21,float_like=false]
      | +-Not
      |   +-InTableQuery
      |     +-test_expression=AttributeReference[attribute_name=ps_suppkey]
      |     +-table_query=SubqueryExpression
      |       +-SetOperation[set_operation_type=Select]
      |         +-children=
      |           +-Select
      |             +-select_clause=SelectList
      |             | +-SelectListItem
      |             |   +-AttributeReference[attribute_name=s_suppkey]
      |             +-where_clause=Like
      |             | +-left_operand=AttributeReference[attribute_name=s_comment]
      |             | +-right_operand=Literal
      |             |   +-StringLiteral[value=%Customer%Complaints%]
      |             +-from_clause=
      |               +-TableReference[table=supplier]
      +-group_by=GroupBy
      | +-AttributeReference[attribute_name=p_brand]
      | +-AttributeReference[attribute_name=p_type]
      | +-AttributeReference[attribute_name=p_size]
      +-order_by=OrderBy
      | +-OrderByItem[is_asc=false,nulls_first=true]
      | | +-AttributeReference[attribute_name=supplier_cnt]
      | +-OrderByItem[is_asc=true,nulls_first=false]
      | | +-AttributeReference[attribute_name=p_brand]
      | +-OrderByItem[is_asc=true,nulls_first=false]
      | | +-AttributeReference[attribute_name=p_type]
      | +-OrderByItem[is_asc=true,nulls_first=false]
      |   +-AttributeReference[attribute_name=p_size]
      +-from_clause=
        +-TableReference[table=partsupp]
        +-TableReference[table=part]
==

# Query 17
SELECT
  SUM(l_extendedprice) / 7.0 AS avg_yearly
FROM
  lineitem,
  part
WHERE
  p_partkey = l_partkey
  AND p_brand = 'Brand#24'
  AND p_container = 'JUMBO BOX'
  AND l_quantity < (
    SELECT
      0.2 * AVG(l_quantity)
    FROM
      lineitem
    WHERE
      l_partkey = p_partkey
  )
--
SetOperationStatement
+-set_operation_query=SetOperation[set_operation_type=Select]
  +-children=
    +-Select
      +-select_clause=SelectList
      | +-SelectListItem[alias=avg_yearly]
      |   +-Divide
      |     +-left_operand=FunctionCall[name=SUM]
      |     | +-AttributeReference[attribute_name=l_extendedprice]
      |     +-right_operand=Literal
      |       +-NumericLiteral[numeric_string=7.0,float_like=true]
      +-where_clause=And
      | +-Equal
      | | +-left_operand=AttributeReference[attribute_name=p_partkey]
      | | +-right_operand=AttributeReference[attribute_name=l_partkey]
      | +-Equal
      | | +-left_operand=AttributeReference[attribute_name=p_brand]
      | | +-right_operand=Literal
      | |   +-StringLiteral[value=Brand#24]
      | +-Equal
      | | +-left_operand=AttributeReference[attribute_name=p_container]
      | | +-right_operand=Literal
      | |   +-StringLiteral[value=JUMBO BOX]
      | +-Less
      |   +-left_operand=AttributeReference[attribute_name=l_quantity]
      |   +-right_operand=SubqueryExpression
      |     +-SetOperation[set_operation_type=Select]
      |       +-children=
      |         +-Select
      |           +-select_clause=SelectList
      |           | +-SelectListItem
      |           |   +-Multiply
      |           |     +-left_operand=Literal
      |           |     | +-NumericLiteral[numeric_string=0.2,float_like=true]
      |           |     +-right_operand=FunctionCall[name=AVG]
      |           |       +-AttributeReference[attribute_name=l_quantity]
      |           +-where_clause=Equal
      |           | +-left_operand=AttributeReference[attribute_name=l_partkey]
      |           | +-right_operand=AttributeReference[attribute_name=p_partkey]
      |           +-from_clause=
      |             +-TableReference[table=lineitem]
      +-from_clause=
        +-TableReference[table=lineitem]
        +-TableReference[table=part]
==

# Query 18
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) > 314
  )
  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
LIMIT 100
--
SetOperationStatement
+-set_operation_query=SetOperation[set_operation_type=Select]
  +-children=
    +-Select
      +-select_clause=SelectList
      | +-SelectListItem
      | | +-AttributeReference[attribute_name=c_name]
      | +-SelectListItem
      | | +-AttributeReference[attribute_name=c_custkey]
      | +-SelectListItem
      | | +-AttributeReference[attribute_name=o_orderkey]
      | +-SelectListItem
      | | +-AttributeReference[attribute_name=o_orderdate]
      | +-SelectListItem
      | | +-AttributeReference[attribute_name=o_totalprice]
      | +-SelectListItem
      |   +-FunctionCall[name=sum]
      |     +-AttributeReference[attribute_name=l_quantity]
      +-where_clause=And
      | +-InTableQuery
      | | +-test_expression=AttributeReference[attribute_name=o_orderkey]
      | | +-table_query=SubqueryExpression
      | |   +-SetOperation[set_operation_type=Select]
      | |     +-children=
      | |       +-Select
      | |         +-select_clause=SelectList
      | |         | +-SelectListItem
      | |         |   +-AttributeReference[attribute_name=l_orderkey]
      | |         +-group_by=GroupBy
      | |         | +-AttributeReference[attribute_name=l_orderkey]
      | |         +-having=HAVING
      | |         | +-Greater
      | |         |   +-left_operand=FunctionCall[name=SUM]
      | |         |   | +-AttributeReference[attribute_name=l_quantity]
      | |         |   +-right_operand=Literal
      | |         |     +-NumericLiteral[numeric_string=314,float_like=false]
      | |         +-from_clause=
      | |           +-TableReference[table=lineitem]
      | +-Equal
      | | +-left_operand=AttributeReference[attribute_name=c_custkey]
      | | +-right_operand=AttributeReference[attribute_name=o_custkey]
      | +-Equal
      |   +-left_operand=AttributeReference[attribute_name=o_orderkey]
      |   +-right_operand=AttributeReference[attribute_name=l_orderkey]
      +-group_by=GroupBy
      | +-AttributeReference[attribute_name=c_name]
      | +-AttributeReference[attribute_name=c_custkey]
      | +-AttributeReference[attribute_name=o_orderkey]
      | +-AttributeReference[attribute_name=o_orderdate]
      | +-AttributeReference[attribute_name=o_totalprice]
      +-order_by=OrderBy
      | +-OrderByItem[is_asc=false,nulls_first=true]
      | | +-AttributeReference[attribute_name=o_totalprice]
      | +-OrderByItem[is_asc=true,nulls_first=false]
      |   +-AttributeReference[attribute_name=o_orderdate]
      +-limit=LIMIT
      | +-NumericLiteral[numeric_string=100,float_like=false]
      +-from_clause=
        +-TableReference[table=customer]
        +-TableReference[table=orders]
        +-TableReference[table=lineitem]
==

# Query 19
SELECT
  SUM(l_extendedprice* (1 - l_discount)) AS revenue
FROM
  lineitem,
  part
WHERE
  (
    p_partkey = l_partkey
    AND p_brand = 'Brand#45'
    AND p_container IN ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
    AND l_quantity >= 2 AND l_quantity <= 2 + 10
    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#12'
    AND p_container IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
    AND l_quantity >= 13 AND l_quantity <= 13 + 10
    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#53'
    AND p_container IN ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
    AND l_quantity >= 24 AND l_quantity <= 24 + 10
    AND p_size BETWEEN 1 AND 15
    AND l_shipmode IN ('AIR', 'AIR REG')
    AND l_shipinstruct = 'DELIVER IN PERSON'
  )
--
SetOperationStatement
+-set_operation_query=SetOperation[set_operation_type=Select]
  +-children=
    +-Select
      +-select_clause=SelectList
      | +-SelectListItem[alias=revenue]
      |   +-FunctionCall[name=SUM]
      |     +-Multiply
      |       +-left_operand=AttributeReference[attribute_name=l_extendedprice]
      |       +-right_operand=Subtract
      |         +-left_operand=Literal
      |         | +-NumericLiteral[numeric_string=1,float_like=false]
      |         +-right_operand=AttributeReference[attribute_name=l_discount]
      +-where_clause=Or
      | +-And
      | | +-Equal
      | | | +-left_operand=AttributeReference[attribute_name=p_partkey]
      | | | +-right_operand=AttributeReference[attribute_name=l_partkey]
      | | +-Equal
      | | | +-left_operand=AttributeReference[attribute_name=p_brand]
      | | | +-right_operand=Literal
      | | |   +-StringLiteral[value=Brand#45]
      | | +-InValueList
      | | | +-test_expression=AttributeReference[attribute_name=p_container]
      | | | +-value_list=
      | | |   +-Literal
      | | |   | +-StringLiteral[value=SM CASE]
      | | |   +-Literal
      | | |   | +-StringLiteral[value=SM BOX]
      | | |   +-Literal
      | | |   | +-StringLiteral[value=SM PACK]
      | | |   +-Literal
      | | |     +-StringLiteral[value=SM PKG]
      | | +-GreaterOrEqual
      | | | +-left_operand=AttributeReference[attribute_name=l_quantity]
      | | | +-right_operand=Literal
      | | |   +-NumericLiteral[numeric_string=2,float_like=false]
      | | +-LessOrEqual
      | | | +-left_operand=AttributeReference[attribute_name=l_quantity]
      | | | +-right_operand=Add
      | | |   +-left_operand=Literal
      | | |   | +-NumericLiteral[numeric_string=2,float_like=false]
      | | |   +-right_operand=Literal
      | | |     +-NumericLiteral[numeric_string=10,float_like=false]
      | | +-Between
      | | | +-check_operand=AttributeReference[attribute_name=p_size]
      | | | +-lower_bound_operand=Literal
      | | | | +-NumericLiteral[numeric_string=1,float_like=false]
      | | | +-upper_bound_operand=Literal
      | | |   +-NumericLiteral[numeric_string=5,float_like=false]
      | | +-InValueList
      | | | +-test_expression=AttributeReference[attribute_name=l_shipmode]
      | | | +-value_list=
      | | |   +-Literal
      | | |   | +-StringLiteral[value=AIR]
      | | |   +-Literal
      | | |     +-StringLiteral[value=AIR REG]
      | | +-Equal
      | |   +-left_operand=AttributeReference[attribute_name=l_shipinstruct]
      | |   +-right_operand=Literal
      | |     +-StringLiteral[value=DELIVER IN PERSON]
      | +-And
      | | +-Equal
      | | | +-left_operand=AttributeReference[attribute_name=p_partkey]
      | | | +-right_operand=AttributeReference[attribute_name=l_partkey]
      | | +-Equal
      | | | +-left_operand=AttributeReference[attribute_name=p_brand]
      | | | +-right_operand=Literal
      | | |   +-StringLiteral[value=Brand#12]
      | | +-InValueList
      | | | +-test_expression=AttributeReference[attribute_name=p_container]
      | | | +-value_list=
      | | |   +-Literal
      | | |   | +-StringLiteral[value=MED BAG]
      | | |   +-Literal
      | | |   | +-StringLiteral[value=MED BOX]
      | | |   +-Literal
      | | |   | +-StringLiteral[value=MED PKG]
      | | |   +-Literal
      | | |     +-StringLiteral[value=MED PACK]
      | | +-GreaterOrEqual
      | | | +-left_operand=AttributeReference[attribute_name=l_quantity]
      | | | +-right_operand=Literal
      | | |   +-NumericLiteral[numeric_string=13,float_like=false]
      | | +-LessOrEqual
      | | | +-left_operand=AttributeReference[attribute_name=l_quantity]
      | | | +-right_operand=Add
      | | |   +-left_operand=Literal
      | | |   | +-NumericLiteral[numeric_string=13,float_like=false]
      | | |   +-right_operand=Literal
      | | |     +-NumericLiteral[numeric_string=10,float_like=false]
      | | +-Between
      | | | +-check_operand=AttributeReference[attribute_name=p_size]
      | | | +-lower_bound_operand=Literal
      | | | | +-NumericLiteral[numeric_string=1,float_like=false]
      | | | +-upper_bound_operand=Literal
      | | |   +-NumericLiteral[numeric_string=10,float_like=false]
      | | +-InValueList
      | | | +-test_expression=AttributeReference[attribute_name=l_shipmode]
      | | | +-value_list=
      | | |   +-Literal
      | | |   | +-StringLiteral[value=AIR]
      | | |   +-Literal
      | | |     +-StringLiteral[value=AIR REG]
      | | +-Equal
      | |   +-left_operand=AttributeReference[attribute_name=l_shipinstruct]
      | |   +-right_operand=Literal
      | |     +-StringLiteral[value=DELIVER IN PERSON]
      | +-And
      |   +-Equal
      |   | +-left_operand=AttributeReference[attribute_name=p_partkey]
      |   | +-right_operand=AttributeReference[attribute_name=l_partkey]
      |   +-Equal
      |   | +-left_operand=AttributeReference[attribute_name=p_brand]
      |   | +-right_operand=Literal
      |   |   +-StringLiteral[value=Brand#53]
      |   +-InValueList
      |   | +-test_expression=AttributeReference[attribute_name=p_container]
      |   | +-value_list=
      |   |   +-Literal
      |   |   | +-StringLiteral[value=LG CASE]
      |   |   +-Literal
      |   |   | +-StringLiteral[value=LG BOX]
      |   |   +-Literal
      |   |   | +-StringLiteral[value=LG PACK]
      |   |   +-Literal
      |   |     +-StringLiteral[value=LG PKG]
      |   +-GreaterOrEqual
      |   | +-left_operand=AttributeReference[attribute_name=l_quantity]
      |   | +-right_operand=Literal
      |   |   +-NumericLiteral[numeric_string=24,float_like=false]
      |   +-LessOrEqual
      |   | +-left_operand=AttributeReference[attribute_name=l_quantity]
      |   | +-right_operand=Add
      |   |   +-left_operand=Literal
      |   |   | +-NumericLiteral[numeric_string=24,float_like=false]
      |   |   +-right_operand=Literal
      |   |     +-NumericLiteral[numeric_string=10,float_like=false]
      |   +-Between
      |   | +-check_operand=AttributeReference[attribute_name=p_size]
      |   | +-lower_bound_operand=Literal
      |   | | +-NumericLiteral[numeric_string=1,float_like=false]
      |   | +-upper_bound_operand=Literal
      |   |   +-NumericLiteral[numeric_string=15,float_like=false]
      |   +-InValueList
      |   | +-test_expression=AttributeReference[attribute_name=l_shipmode]
      |   | +-value_list=
      |   |   +-Literal
      |   |   | +-StringLiteral[value=AIR]
      |   |   +-Literal
      |   |     +-StringLiteral[value=AIR REG]
      |   +-Equal
      |     +-left_operand=AttributeReference[attribute_name=l_shipinstruct]
      |     +-right_operand=Literal
      |       +-StringLiteral[value=DELIVER IN PERSON]
      +-from_clause=
        +-TableReference[table=lineitem]
        +-TableReference[table=part]
==

# Query 20
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 'sandy%'
      )
      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 '1993-01-01'
          AND l_shipdate < DATE '1993-01-01' + INTERVAL '1 year'
      )
  )
  AND s_nationkey = n_nationkey
  AND n_name = 'GERMANY'
ORDER BY
  s_name
--
SetOperationStatement
+-set_operation_query=SetOperation[set_operation_type=Select]
  +-children=
    +-Select
      +-select_clause=SelectList
      | +-SelectListItem
      | | +-AttributeReference[attribute_name=s_name]
      | +-SelectListItem
      |   +-AttributeReference[attribute_name=s_address]
      +-where_clause=And
      | +-InTableQuery
      | | +-test_expression=AttributeReference[attribute_name=s_suppkey]
      | | +-table_query=SubqueryExpression
      | |   +-SetOperation[set_operation_type=Select]
      | |     +-children=
      | |       +-Select
      | |         +-select_clause=SelectList
      | |         | +-SelectListItem
      | |         |   +-AttributeReference[attribute_name=ps_suppkey]
      | |         +-where_clause=And
      | |         | +-InTableQuery
      | |         | | +-test_expression=AttributeReference[
      | |         | | | attribute_name=ps_partkey]
      | |         | | +-table_query=SubqueryExpression
      | |         | |   +-SetOperation[set_operation_type=Select]
      | |         | |     +-children=
      | |         | |       +-Select
      | |         | |         +-select_clause=SelectList
      | |         | |         | +-SelectListItem
      | |         | |         |   +-AttributeReference[attribute_name=p_partkey]
      | |         | |         +-where_clause=Like
      | |         | |         | +-left_operand=AttributeReference[
      | |         | |         | | attribute_name=p_name]
      | |         | |         | +-right_operand=Literal
      | |         | |         |   +-StringLiteral[value=sandy%]
      | |         | |         +-from_clause=
      | |         | |           +-TableReference[table=part]
      | |         | +-Greater
      | |         |   +-left_operand=AttributeReference[
      | |         |   | attribute_name=ps_availqty]
      | |         |   +-right_operand=SubqueryExpression
      | |         |     +-SetOperation[set_operation_type=Select]
      | |         |       +-children=
      | |         |         +-Select
      | |         |           +-select_clause=SelectList
      | |         |           | +-SelectListItem
      | |         |           |   +-Multiply
      | |         |           |     +-left_operand=Literal
      | |         |           |     | +-NumericLiteral[numeric_string=0.5,
      | |         |           |     |   float_like=true]
      | |         |           |     +-right_operand=FunctionCall[name=SUM]
      | |         |           |       +-AttributeReference[
      | |         |           |         attribute_name=l_quantity]
      | |         |           +-where_clause=And
      | |         |           | +-Equal
      | |         |           | | +-left_operand=AttributeReference[
      | |         |           | | | attribute_name=l_partkey]
      | |         |           | | +-right_operand=AttributeReference[
      | |         |           | |   attribute_name=ps_partkey]
      | |         |           | +-Equal
      | |         |           | | +-left_operand=AttributeReference[
      | |         |           | | | attribute_name=l_suppkey]
      | |         |           | | +-right_operand=AttributeReference[
      | |         |           | |   attribute_name=ps_suppkey]
      | |         |           | +-GreaterOrEqual
      | |         |           | | +-left_operand=AttributeReference[
      | |         |           | | | attribute_name=l_shipdate]
      | |         |           | | +-right_operand=Literal
      | |         |           | |   +-StringLiteral[value=1993-01-01,
      | |         |           | |     explicit_type=Date]
      | |         |           | +-Less
      | |         |           |   +-left_operand=AttributeReference[
      | |         |           |   | attribute_name=l_shipdate]
      | |         |           |   +-right_operand=Add
      | |         |           |     +-left_operand=Literal
      | |         |           |     | +-StringLiteral[value=1993-01-01,
      | |         |           |     |   explicit_type=Date]
      | |         |           |     +-right_operand=Literal
      | |         |           |       +-StringLiteral[value=1 year,
      | |         |           |         explicit_type=YearMonthInterval]
      | |         |           +-from_clause=
      | |         |             +-TableReference[table=lineitem]
      | |         +-from_clause=
      | |           +-TableReference[table=partsupp]
      | +-Equal
      | | +-left_operand=AttributeReference[attribute_name=s_nationkey]
      | | +-right_operand=AttributeReference[attribute_name=n_nationkey]
      | +-Equal
      |   +-left_operand=AttributeReference[attribute_name=n_name]
      |   +-right_operand=Literal
      |     +-StringLiteral[value=GERMANY]
      +-order_by=OrderBy
      | +-OrderByItem[is_asc=true,nulls_first=false]
      |   +-AttributeReference[attribute_name=s_name]
      +-from_clause=
        +-TableReference[table=supplier]
        +-TableReference[table=nation]
==

# Query 21
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 = 'CANADA'
GROUP BY
  s_name
ORDER BY
  numwait desc,
  s_name
LIMIT 100
--
SetOperationStatement
+-set_operation_query=SetOperation[set_operation_type=Select]
  +-children=
    +-Select
      +-select_clause=SelectList
      | +-SelectListItem
      | | +-AttributeReference[attribute_name=s_name]
      | +-SelectListItem[alias=numwait]
      |   +-FunctionCall[name=count,is_star=true]
      +-where_clause=And
      | +-Equal
      | | +-left_operand=AttributeReference[attribute_name=s_suppkey]
      | | +-right_operand=AttributeReference[attribute_name=l_suppkey,
      | |   relation_name=l1]
      | +-Equal
      | | +-left_operand=AttributeReference[attribute_name=o_orderkey]
      | | +-right_operand=AttributeReference[attribute_name=l_orderkey,
      | |   relation_name=l1]
      | +-Equal
      | | +-left_operand=AttributeReference[attribute_name=o_orderstatus]
      | | +-right_operand=Literal
      | |   +-StringLiteral[value=F]
      | +-Greater
      | | +-left_operand=AttributeReference[attribute_name=l_receiptdate,
      | | | relation_name=l1]
      | | +-right_operand=AttributeReference[attribute_name=l_commitdate,
      | |   relation_name=l1]
      | +-Exists
      | | +-subquery=SubqueryExpression
      | |   +-SetOperation[set_operation_type=Select]
      | |     +-children=
      | |       +-Select
      | |         +-select_clause=SelectStar
      | |         +-where_clause=And
      | |         | +-Equal
      | |         | | +-left_operand=AttributeReference[
      | |         | | | attribute_name=l_orderkey,relation_name=l2]
      | |         | | +-right_operand=AttributeReference[
      | |         | |   attribute_name=l_orderkey,relation_name=l1]
      | |         | +-NotEqual
      | |         |   +-left_operand=AttributeReference[
      | |         |   | attribute_name=l_suppkey,relation_name=l2]
      | |         |   +-right_operand=AttributeReference[
      | |         |     attribute_name=l_suppkey,relation_name=l1]
      | |         +-from_clause=
      | |           +-TableReference[table=lineitem]
      | |             +-table_signature=TableSignature[table_alias=l2]
      | +-Not
      | | +-Exists
      | |   +-subquery=SubqueryExpression
      | |     +-SetOperation[set_operation_type=Select]
      | |       +-children=
      | |         +-Select
      | |           +-select_clause=SelectStar
      | |           +-where_clause=And
      | |           | +-Equal
      | |           | | +-left_operand=AttributeReference[
      | |           | | | attribute_name=l_orderkey,relation_name=l3]
      | |           | | +-right_operand=AttributeReference[
      | |           | |   attribute_name=l_orderkey,relation_name=l1]
      | |           | +-NotEqual
      | |           | | +-left_operand=AttributeReference[
      | |           | | | attribute_name=l_suppkey,relation_name=l3]
      | |           | | +-right_operand=AttributeReference[
      | |           | |   attribute_name=l_suppkey,relation_name=l1]
      | |           | +-Greater
      | |           |   +-left_operand=AttributeReference[
      | |           |   | attribute_name=l_receiptdate,relation_name=l3]
      | |           |   +-right_operand=AttributeReference[
      | |           |     attribute_name=l_commitdate,relation_name=l3]
      | |           +-from_clause=
      | |             +-TableReference[table=lineitem]
      | |               +-table_signature=TableSignature[table_alias=l3]
      | +-Equal
      | | +-left_operand=AttributeReference[attribute_name=s_nationkey]
      | | +-right_operand=AttributeReference[attribute_name=n_nationkey]
      | +-Equal
      |   +-left_operand=AttributeReference[attribute_name=n_name]
      |   +-right_operand=Literal
      |     +-StringLiteral[value=CANADA]
      +-group_by=GroupBy
      | +-AttributeReference[attribute_name=s_name]
      +-order_by=OrderBy
      | +-OrderByItem[is_asc=false,nulls_first=true]
      | | +-AttributeReference[attribute_name=numwait]
      | +-OrderByItem[is_asc=true,nulls_first=false]
      |   +-AttributeReference[attribute_name=s_name]
      +-limit=LIMIT
      | +-NumericLiteral[numeric_string=100,float_like=false]
      +-from_clause=
        +-TableReference[table=supplier]
        +-TableReference[table=lineitem]
        | +-table_signature=TableSignature[table_alias=l1]
        +-TableReference[table=orders]
        +-TableReference[table=nation]
==

# Query 22
SELECT
  cntrycode,
  COUNT(*) AS numcust,
  SUM(c_acctbal) AS totacctbal
FROM
  (
    SELECT
      SUBSTR(c_phone, 1, 2) AS cntrycode,
      c_acctbal
    FROM
      customer
    WHERE
      SUBSTR(c_phone, 1, 2) IN
        ('27', '44', '34', '25', '30', '33', '23')
      AND c_acctbal > (
        SELECT
          AVG(c_acctbal)
        FROM
          customer
        WHERE
          c_acctbal > 0.00
          AND SUBSTR(c_phone, 1, 2) IN
            ('27', '44', '34', '25', '30', '33', '23')
      )
      AND NOT EXISTS (
        SELECT *
        FROM
          orders
        WHERE
          o_custkey = c_custkey
      )
  ) AS custsale
GROUP BY
  cntrycode
ORDER BY
  cntrycode
--
SetOperationStatement
+-set_operation_query=SetOperation[set_operation_type=Select]
  +-children=
    +-Select
      +-select_clause=SelectList
      | +-SelectListItem
      | | +-AttributeReference[attribute_name=cntrycode]
      | +-SelectListItem[alias=numcust]
      | | +-FunctionCall[name=COUNT,is_star=true]
      | +-SelectListItem[alias=totacctbal]
      |   +-FunctionCall[name=SUM]
      |     +-AttributeReference[attribute_name=c_acctbal]
      +-group_by=GroupBy
      | +-AttributeReference[attribute_name=cntrycode]
      +-order_by=OrderBy
      | +-OrderByItem[is_asc=true,nulls_first=false]
      |   +-AttributeReference[attribute_name=cntrycode]
      +-from_clause=
        +-SubqueryTable
          +-table_signature=TableSignature[table_alias=custsale]
          +-SubqueryExpression
            +-SetOperation[set_operation_type=Select]
              +-children=
                +-Select
                  +-select_clause=SelectList
                  | +-SelectListItem[alias=cntrycode]
                  | | +-FunctionCall[name=SUBSTR]
                  | |   +-AttributeReference[attribute_name=c_phone]
                  | |   +-Literal
                  | |   | +-NumericLiteral[numeric_string=1,float_like=false]
                  | |   +-Literal
                  | |     +-NumericLiteral[numeric_string=2,float_like=false]
                  | +-SelectListItem
                  |   +-AttributeReference[attribute_name=c_acctbal]
                  +-where_clause=And
                  | +-InValueList
                  | | +-test_expression=FunctionCall[name=SUBSTR]
                  | | | +-AttributeReference[attribute_name=c_phone]
                  | | | +-Literal
                  | | | | +-NumericLiteral[numeric_string=1,float_like=false]
                  | | | +-Literal
                  | | |   +-NumericLiteral[numeric_string=2,float_like=false]
                  | | +-value_list=
                  | |   +-Literal
                  | |   | +-StringLiteral[value=27]
                  | |   +-Literal
                  | |   | +-StringLiteral[value=44]
                  | |   +-Literal
                  | |   | +-StringLiteral[value=34]
                  | |   +-Literal
                  | |   | +-StringLiteral[value=25]
                  | |   +-Literal
                  | |   | +-StringLiteral[value=30]
                  | |   +-Literal
                  | |   | +-StringLiteral[value=33]
                  | |   +-Literal
                  | |     +-StringLiteral[value=23]
                  | +-Greater
                  | | +-left_operand=AttributeReference[attribute_name=c_acctbal]
                  | | +-right_operand=SubqueryExpression
                  | |   +-SetOperation[set_operation_type=Select]
                  | |     +-children=
                  | |       +-Select
                  | |         +-select_clause=SelectList
                  | |         | +-SelectListItem
                  | |         |   +-FunctionCall[name=AVG]
                  | |         |     +-AttributeReference[
                  | |         |       attribute_name=c_acctbal]
                  | |         +-where_clause=And
                  | |         | +-Greater
                  | |         | | +-left_operand=AttributeReference[
                  | |         | | | attribute_name=c_acctbal]
                  | |         | | +-right_operand=Literal
                  | |         | |   +-NumericLiteral[numeric_string=0.00,
                  | |         | |     float_like=true]
                  | |         | +-InValueList
                  | |         |   +-test_expression=FunctionCall[name=SUBSTR]
                  | |         |   | +-AttributeReference[attribute_name=c_phone]
                  | |         |   | +-Literal
                  | |         |   | | +-NumericLiteral[numeric_string=1,
                  | |         |   | |   float_like=false]
                  | |         |   | +-Literal
                  | |         |   |   +-NumericLiteral[numeric_string=2,
                  | |         |   |     float_like=false]
                  | |         |   +-value_list=
                  | |         |     +-Literal
                  | |         |     | +-StringLiteral[value=27]
                  | |         |     +-Literal
                  | |         |     | +-StringLiteral[value=44]
                  | |         |     +-Literal
                  | |         |     | +-StringLiteral[value=34]
                  | |         |     +-Literal
                  | |         |     | +-StringLiteral[value=25]
                  | |         |     +-Literal
                  | |         |     | +-StringLiteral[value=30]
                  | |         |     +-Literal
                  | |         |     | +-StringLiteral[value=33]
                  | |         |     +-Literal
                  | |         |       +-StringLiteral[value=23]
                  | |         +-from_clause=
                  | |           +-TableReference[table=customer]
                  | +-Not
                  |   +-Exists
                  |     +-subquery=SubqueryExpression
                  |       +-SetOperation[set_operation_type=Select]
                  |         +-children=
                  |           +-Select
                  |             +-select_clause=SelectStar
                  |             +-where_clause=Equal
                  |             | +-left_operand=AttributeReference[
                  |             | | attribute_name=o_custkey]
                  |             | +-right_operand=AttributeReference[
                  |             |   attribute_name=c_custkey]
                  |             +-from_clause=
                  |               +-TableReference[table=orders]
                  +-from_clause=
                    +-TableReference[table=customer]
==