use dataverse tpch;

declare function q7_volume_shipping_tmp() {
  for $n1 in dataset('Nation') 
  for $n2 in dataset('Nation')
  where ($n1.n_name='FRANCE' and $n2.n_name='GERMANY') or ($n1.n_name='GERMANY' and $n2.n_name='FRANCE')
  return {
    "supp_nation": $n1.n_name, 
    "cust_nation": $n2.n_name, 
    "s_nationkey": $n1.n_nationkey,      
    "c_nationkey": $n2.n_nationkey
  }
}

for $locs in (
  for $loc in (
    for $lo in (
      for $l in dataset('LineItem')
      for $o in dataset('Orders')
      where $o.o_orderkey = $l.l_orderkey and $l.l_shipdate >= '1995-01-01' 
        and $l.l_shipdate <= '1996-12-31'
      return {
        "l_shipdate": $l.l_shipdate, 
        "l_extendedprice": $l.l_extendedprice, 
        "l_discount": $l.l_discount, 
        "l_suppkey": $l.l_suppkey, 
        "o_custkey": $o.o_custkey 
      }
    )
    for $c in dataset('Customer')
    where $c.c_custkey = $lo.o_custkey
    return {
      "l_shipdate": $lo.l_shipdate, 
      "l_extendedprice": $lo.l_extendedprice, 
      "l_discount": $lo.l_discount, 
      "l_suppkey": $lo.l_suppkey, 
      "c_nationkey": $c.c_nationkey 
    }
  )
  for $s in dataset('Supplier')
  where $s.s_suppkey = $loc.l_suppkey
  return {
    "l_shipdate": $loc.l_shipdate, 
    "l_extendedprice": $loc.l_extendedprice, 
    "l_discount": $loc.l_discount, 
    "c_nationkey": $loc.c_nationkey, 
    "s_nationkey": $s.s_nationkey
  }
)
for $t in q7_volume_shipping_tmp()
where $locs.c_nationkey = $t.c_nationkey
  and $locs.s_nationkey = $t.s_nationkey
let $l_year0 := get-year($locs.l_shipdate)
group by $supp_nation := $t.supp_nation, $cust_nation := $t.cust_nation, $l_year := $l_year0
with $locs
let $revenue := sum(for $i in $locs return $i.l_extendedprice * (1 - $i.l_discount))
order by $supp_nation, $cust_nation, $l_year
return {
  "supp_nation": $supp_nation, 
  "cust_nation": $cust_nation, 
  "l_year": $l_year,
  "revenue": $revenue
}