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

use dataverse tpch;

for $c in dataset('Customer')
for $o in dataset('Orders')
where $c.c_custkey = $o.o_custkey
for $t in (
  for $l in dataset('LineItem')
  group by $l_orderkey := $l.l_orderkey with $l
  return {
    "l_orderkey": $l_orderkey, 
    "t_sum_quantity": sum(for $i in $l return $i.l_quantity)
  }
) 
where $o.o_orderkey = $t.l_orderkey and $t.t_sum_quantity > 300
for $l in dataset('LineItem')
where $l.l_orderkey = $o.o_orderkey 
group by $c_name := $c.c_name, $c_custkey := $c.c_custkey, $o_orderkey := $o.o_orderkey, 
         $o_orderdate := $o.o_orderdate, $o_totalprice := $o.o_totalprice with $l
order by $o_totalprice desc, $o_orderdate 
limit 100
return {  
  "c_name": $c_name,
  "c_custkey": $c_custkey,
  "o_orderkey": $o_orderkey,
  "o_orderdate": $o_orderdate,
  "o_totalprice": $o_totalprice,
  "sum_quantity": sum(for $j in $l return $j.l_quantity)  
}