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

let $sum := sum (
  for $ps in dataset('Partsupp')
  for $sn in (
    for $s in dataset('Supplier')
    for $n in dataset('Nation')
    where $s.s_nationkey = $n.n_nationkey
      and $n.n_name = 'GERMANY'
    return { "s_suppkey": $s.s_suppkey }
  )
  where $ps.ps_suppkey = $sn.s_suppkey
  return $ps.ps_supplycost * $ps.ps_availqty
)
for $t1 in (
  for $ps in dataset('Partsupp')
  for $sn in (
    for $s in dataset('Supplier')
    for $n in dataset('Nation')
    where $s.s_nationkey = $n.n_nationkey
      and $n.n_name = 'GERMANY'
    return { "s_suppkey": $s.s_suppkey }
  )
  where $ps.ps_suppkey = $sn.s_suppkey
  group by $ps_partkey := $ps.ps_partkey with $ps
  return {
    "ps_partkey": $ps_partkey, 
    "part_value": sum(for $i in $ps return $i.ps_supplycost * $i.ps_availqty)
  }
)
where $t1.part_value > $sum * 0.0001
order by $t1.part_value desc
return {
  "partkey": $t1.ps_partkey,
  "part_value": $t1.part_value
}