asterix-benchmarks/src/main/resources/tpc-h/queries/q19_discounted_revenue.3.query.aql

/*
 * 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.
 */
use dataverse tpch;

set import-private-functions 'true';

sum( 
  for $l in dataset('LineItem')
  for $p in dataset('Part')
  where $p.p_partkey = $l.l_partkey
    and ( (
        $p.p_brand = 'Brand#12'
        and reg-exp($p.p_container,'SM CASE|SM BOX|SM PACK|SM PKG')
        and $l.l_quantity >= 1 and $l.l_quantity <= 11
        and $p.p_size >= 1 and $p.p_size <= 5
        and ($l.l_shipmode = 'AIR' or $l.l_shipmode = 'AIR REG')
        and $l.l_shipinstruct = 'DELIVER IN PERSON'
      ) or (
        $p.p_brand = 'Brand#23'
        and reg-exp($p.p_container, 'MED BAG|MED BOX|MED PKG|MED PACK')
        and $l.l_quantity >= 10 and $l.l_quantity <= 20
        and $p.p_size >= 1 and $p.p_size <= 10
        and ($l.l_shipmode = 'AIR' or $l.l_shipmode = 'AIR REG')
        and $l.l_shipinstruct = 'DELIVER IN PERSON'
      ) or (
        $p.p_brand = 'Brand#34'
        and reg-exp($p.p_container, 'LG CASE|LG BOX|LG PACK|LG PKG')
        and $l.l_quantity >= 20 and $l.l_quantity <= 30
        and $p.p_size >= 1 and $p.p_size <= 15
        and ($l.l_shipmode = 'AIR' or $l.l_shipmode = 'AIR REG')
        and $l.l_shipinstruct = 'DELIVER IN PERSON'
      )
    )
  return $l.l_extendedprice * (1 - $l.l_discount)
)