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

use dataverse tpch;

for $t in (
  for $slnrcop in (
    for $s in dataset("Supplier")
    for $lnrcop in (
      for $lnrco in (
        for $l in dataset('LineItem')
        for $nrco in (
          for $o in dataset('Orders')
          for $nrc in (
            for $c in dataset('Customer')
            for $nr in (
              for $n1 in dataset('Nation')
              for $r1 in dataset('Region')
              where $n1.n_regionkey = $r1.r_regionkey and $r1.r_name = 'AMERICA'
              return { "n_nationkey": $n1.n_nationkey }
            )
            where $c.c_nationkey = $nr.n_nationkey
            return { "c_custkey": $c.c_custkey }
          )
          where $nrc.c_custkey = $o.o_custkey
          return {
            "o_orderdate" : $o.o_orderdate, 
            "o_orderkey": $o.o_orderkey 
          }
        )
        where $l.l_orderkey = $nrco.o_orderkey
          and $nrco.o_orderdate >= '1995-01-01' 
          and $nrco.o_orderdate <= '1996-12-31'
        return {
          "o_orderdate": $nrco.o_orderdate, 
          "l_partkey": $l.l_partkey, 
          "l_discount": $l.l_discount, 
          "l_extendedprice": $l.l_extendedprice, 
          "l_suppkey": $l.l_suppkey
        }
      )
      for $p in dataset("Part")
      where $p.p_partkey = $lnrco.l_partkey and $p.p_type = 'ECONOMY ANODIZED STEEL'
      return {
        "o_orderdate": $lnrco.o_orderdate, 
        "l_discount": $lnrco.l_discount, 
        "l_extendedprice": $lnrco.l_extendedprice, 
        "l_suppkey": $lnrco.l_suppkey 
      }
    )
    where $s.s_suppkey = $lnrcop.l_suppkey
    return {
      "o_orderdate": $lnrcop.o_orderdate, 
      "l_discount": $lnrcop.l_discount, 
      "l_extendedprice": $lnrcop.l_extendedprice, 
      "l_suppkey": $lnrcop.l_suppkey, 
      "s_nationkey": $s.s_nationkey
    }
  )
  for $n2 in dataset('Nation')
  where $slnrcop.s_nationkey = $n2.n_nationkey
  let $o_year := get-year($slnrcop.o_orderdate)
  return {
    "year": $o_year,
    "revenue": $slnrcop.l_extendedprice *(1-$slnrcop.l_discount),
    "s_name": $n2.n_name
  }
)
group by $year := $t.year with $t
order by $year
return {
  "year": $year,
  "mkt_share": sum(for $i in $t return switch-case($i.s_name='BRAZIL', true, $i.revenue, false, 0.0))/ 
        sum(for $i in $t return $i.revenue)
}