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

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
}