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

use dataverse tpch;

declare function tmp1() {
  for $l2 in (
    for $l in dataset('LineItem')
    group by $l_orderkey1 := $l.l_orderkey, $l_suppkey1 := $l.l_suppkey with $l
    return {
      "l_orderkey": $l_orderkey1,
      "l_suppkey": $l_suppkey1
    } 
  )
  group by $l_orderkey := $l2.l_orderkey with $l2
  return {
    "l_orderkey": $l_orderkey, 
    "count_suppkey": count(for $i in $l2 return $i.l_suppkey), 
    "max_suppkey": max(for $i in $l2 return $i.l_suppkey)
  }
}

declare function tmp2() {
  for $l2 in (
    for $l in dataset('LineItem')
    group by $l_orderkey1 := $l.l_orderkey, $l_suppkey1 := $l.l_suppkey with $l
    return {
      "l_orderkey": $l_orderkey1,
      "l_suppkey": $l_suppkey1,
      "count": count(for $i in $l return $i.l_suppkey)
    } 
  )
  for $l3 in (
    for $l in dataset('LineItem')
    where $l.l_receiptdate <= $l.l_commitdate
    group by $l_orderkey1 := $l.l_orderkey, $l_suppkey1 := $l.l_suppkey with $l
    return {
      "l_orderkey": $l_orderkey1,
      "l_suppkey": $l_suppkey1,
      "count": count(for $i in $l return $i.l_suppkey)
    }
  )
  where $l2.l_orderkey = $l3.l_orderkey
    and $l2.l_suppkey = $l3.l_suppkey
    and $l2.count = $l3.count
  group by $l_orderkey := $l2.l_orderkey with $l2
  return {
    "l_orderkey": $l_orderkey,     
    "count_suppkey": count(for $i in $l2 return $i.l_suppkey), 
    "max_suppkey": max(for $i in $l2 return $i.l_suppkey)
  }
}

for $t4 in (
  for $t3 in (
    for $l in dataset('LineItem')
    for $ns in (
      for $n in dataset('Nation')
      for $s in dataset('Supplier')
      where $s.s_nationkey = $n.n_nationkey
        and $n.n_name = 'SAUDI ARABIA'
      return {
        "s_name": $s.s_name,
        "s_suppkey": $s.s_suppkey
      }
    )
    where $ns.s_suppkey = $l.l_suppkey and $l.l_receiptdate > $l.l_commitdate
    for $o in dataset('Orders')
    where $o.o_orderkey = $l.l_orderkey
      and $o.o_orderstatus = 'F'
    for $t1 in tmp1()
    where $l.l_orderkey = $t1.l_orderkey
      and $t1.count_suppkey > 1
    return {
      "s_name": $ns.s_name,
      "l_orderkey": $t1.l_orderkey,
      "l_suppkey": $l.l_suppkey,
      "t1_count_suppkey": $t1.count_suppkey
    }
  )
  for $t2 in tmp2() 
  where $t3.l_orderkey = $t2.l_orderkey
    and $t2.count_suppkey = $t3.t1_count_suppkey - 1
  return {
    "s_name": $t3.s_name,
    "l_suppkey": $t3.l_suppkey,
    "l_orderkey": $t2.l_orderkey,
    "count_suppkey": $t2.count_suppkey, 
    "max_suppkey": $t2.max_suppkey
  }
)
group by $s_name := $t4.s_name with $t4
let $numwait := count($t4)
order by $numwait desc, $s_name
limit 100
return {
  "s_name": $s_name,
  "numwait": $numwait
}