asterix-benchmarks/src/main/resources/tpc-h/queries/q20_potential_part_promotion.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;

for $t3 in (
  for $t2 in (
    for $l in dataset('LineItem')
    where $l.l_shipdate >= '1994-01-01' and $l.l_shipdate < '1995-01-01'
    group by $l_partkey:=$l.l_partkey, $l_suppkey:=$l.l_suppkey with $l
    return {
      "l_partkey": $l_partkey, 
      "l_suppkey": $l_suppkey,
      "sum_quantity": 0.5 * sum(for $i in $l return $i.l_quantity)
    }
  )
  for $pst1 in (
    for $ps in dataset('Partsupp')
    for $t1 in (
      for $p in dataset('Part')
      where like($p.p_name, 'forest%') 
      distinct by $p.p_partkey
      return { "p_partkey": $p.p_partkey }
    )
    where $ps.ps_partkey = $t1.p_partkey
    return {
      "ps_suppkey": $ps.ps_suppkey,
      "ps_partkey": $ps.ps_partkey,
      "ps_availqty": $ps.ps_availqty
    }
  )
  where $pst1.ps_partkey = $t2.l_partkey and $pst1.ps_suppkey = $t2.l_suppkey
    and $pst1.ps_availqty > $t2.sum_quantity
  distinct by $pst1.ps_suppkey
  return { "ps_suppkey": $pst1.ps_suppkey }
)
for $t4 in (
  for $n in dataset('Nation')
  for $s in dataset('Supplier')
  where  $s.s_nationkey = $n.n_nationkey and $n.n_name = 'CANADA'
  return {
    "s_name": $s.s_name,
    "s_address": $s.s_address,
    "s_suppkey": $s.s_suppkey
  }
)
where $t3.ps_suppkey = $t4.s_suppkey
order by $t4.s_name
return {
  "s_name": $t4.s_name,
  "s_address": $t4.s_address
}