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

use dataverse tpch;

declare function tmp1() {
  for $p in dataset('Part')
  for $pssrn in (
    for $ps in dataset('Partsupp')
    for $srn in (
      for $s in dataset('Supplier')
      for $rn in (
        for $r in dataset('Region')
        for $n in dataset('Nation')
        where $n.n_regionkey = $r.r_regionkey and $r.r_name = 'EUROPE' 
        return {
          "n_nationkey": $n.n_nationkey,
          "n_name": $n.n_name
        }
      )
      where $s.s_nationkey = $rn.n_nationkey
      return {
        "s_suppkey": $s.s_suppkey,
        "n_name": $rn.n_name,
        "s_name": $s.s_name,
        "s_acctbal": $s.s_acctbal,
        "s_address": $s.s_address, 
        "s_phone": $s.s_phone, 
        "s_comment": $s.s_comment
      }
    )
    where $srn.s_suppkey = $ps.ps_suppkey
    return {
      "n_name": $srn.n_name,
      "p_partkey": $ps.ps_partkey,
      "ps_supplycost": $ps.ps_supplycost,
      "s_name": $srn.s_name,
      "s_acctbal": $srn.s_acctbal,
      "s_address":  $srn.s_address, 
      "s_phone":  $srn.s_phone, 
      "s_comment":  $srn.s_comment
    }
  ) 
  where $p.p_partkey = $pssrn.p_partkey and like($p.p_type, '%BRASS') and $p.p_size = 15
  return {
    "s_acctbal": $pssrn.s_acctbal,
    "s_name": $pssrn.s_name, 
    "n_name": $pssrn.n_name, 
    "p_partkey": $p.p_partkey, 
    "ps_supplycost": $pssrn.ps_supplycost, 
    "p_mfgr": $p.p_mfgr, 
    "s_address":  $pssrn.s_address, 
    "s_phone":  $pssrn.s_phone, 
    "s_comment":  $pssrn.s_comment
  }
}

declare function tmp2(){
  for $p in dataset('Part')
  for $pssrn in (
    for $ps in dataset('Partsupp')
    for $srn in (
      for $s in dataset('Supplier')
      for $rn in (
        for $r in dataset('Region')
        for $n in dataset('Nation')
        where $n.n_regionkey = $r.r_regionkey and $r.r_name = 'EUROPE' 
        return {
          "n_nationkey": $n.n_nationkey,
          "n_name": $n.n_name
        }
      )
      where $s.s_nationkey = $rn.n_nationkey
      return {
        "s_suppkey": $s.s_suppkey,
        "n_name": $rn.n_name,
        "s_name": $s.s_name,
        "s_acctbal": $s.s_acctbal,
        "s_address": $s.s_address, 
        "s_phone": $s.s_phone, 
        "s_comment": $s.s_comment
      }
    )
    where $srn.s_suppkey = $ps.ps_suppkey
    return {
      "n_name": $srn.n_name,
      "p_partkey": $ps.ps_partkey,
      "ps_supplycost": $ps.ps_supplycost,
      "s_name": $srn.s_name,
      "s_acctbal": $srn.s_acctbal,
      "s_address":  $srn.s_address, 
      "s_phone":  $srn.s_phone, 
      "s_comment":  $srn.s_comment
    }
  )
  where $p.p_partkey = $pssrn.p_partkey and like($p.p_type, '%BRASS') and $p.p_size = 15
  /*+ hash*/
  group by $p_partkey := $pssrn.p_partkey with $pssrn
  return {
    "p_partkey": $p_partkey, 
    "ps_min_supplycost": min(for $i in $pssrn return $i.ps_supplycost)
  }
}

for $t2 in tmp2()
for $t1 in tmp1()
where $t1.p_partkey = $t2.p_partkey and $t1.ps_supplycost = $t2.ps_min_supplycost
order by $t1.s_acctbal desc, $t1.n_name, $t1.s_name, $t1.p_partkey
limit 100
return 
{
  "s_acctbal": $t1.s_acctbal, 
  "s_name": $t1.s_name, 
  "n_name": $t1.n_name, 
  "p_partkey": $t1.p_partkey, 
  "p_mfgr": $t1.p_mfgr, 
  "s_address": $t1.s_address, 
  "s_phone": $t1.s_phone, 
  "s_comment": $t1.s_comment
}