asterix-app/src/test/resources/runtimets/queries/tpch/q8_national_market_share.aql

drop dataverse tpch if exists;
create dataverse tpch;
  

use dataverse tpch;

create type LineItemType as closed {
  l_orderkey: int32, 
  l_partkey: int32, 
  l_suppkey: int32, 
  l_linenumber: int32, 
  l_quantity: int32, 
  l_extendedprice: double,
  l_discount: double, 
  l_tax: double,
  l_returnflag: string, 
  l_linestatus: string, 
  l_shipdate: string,
  l_commitdate: string, 
  l_receiptdate: string, 
  l_shipinstruct: string, 
  l_shipmode: string, 
  l_comment: string
}

create type OrderType as closed {
  o_orderkey: int32, 
  o_custkey: int32, 
  o_orderstatus: string, 
  o_totalprice: double, 
  o_orderdate: string, 
  o_orderpriority: string,
  o_clerk: string, 
  o_shippriority: int32, 
  o_comment: string
}

create type CustomerType as closed {
  c_custkey: int32, 
  c_name: string, 
  c_address: string, 
  c_nationkey: int32, 
  c_phone: string, 
  c_acctbal: double, 
  c_mktsegment: string,
  c_comment: string
}

create type SupplierType as closed {
  s_suppkey: int32, 
  s_name: string,
  s_address: string,
  s_nationkey: int32,
  s_phone: string,
  s_acctbal: double,
  s_comment: string
}

create type NationType as closed {
  n_nationkey: int32,
  n_name: string,
  n_regionkey: int32,
  n_comment: string
}

create type RegionType as closed {
	r_regionkey: int32, 
	r_name: string, 
	r_comment: string
} 

create type PartType as closed {
  p_partkey: int32, 
  p_name: string, 
  p_mfgr: string,
  p_brand: string,
  p_type: string,
  p_size: int32,
  p_container: string,
  p_retailprice: double,
  p_comment: string
}

create type PartSuppType as closed {
  ps_partkey: int32, 
  ps_suppkey: int32,
  ps_availqty: int32,
  ps_supplycost: double,
  ps_comment: string 
}

create dataset LineItem(LineItemType)
  partitioned by key l_orderkey, l_linenumber;
create dataset Orders(OrderType)
  partitioned by key o_orderkey;
create dataset Supplier(SupplierType)
  partitioned by key s_suppkey;
create dataset Region(RegionType) 
  partitioned by key r_regionkey;
create dataset Nation(NationType) 
  partitioned by key n_nationkey;
create dataset Part(PartType)
  partitioned by key p_partkey;
create dataset Partsupp(PartSuppType)
  partitioned by key ps_partkey, ps_suppkey;  
create dataset Customer(CustomerType) 
  partitioned by key c_custkey;

load dataset LineItem 
using "edu.uci.ics.asterix.external.dataset.adapter.NCFileSystemAdapter"
(("path"="nc1://data/tpch0.001/lineitem.tbl"),("format"="delimited-text"),("delimiter"="|")) pre-sorted;

load dataset Orders 
using "edu.uci.ics.asterix.external.dataset.adapter.NCFileSystemAdapter"
(("path"="nc1://data/tpch0.001/orders.tbl"),("format"="delimited-text"),("delimiter"="|")) pre-sorted;

load dataset Supplier 
using "edu.uci.ics.asterix.external.dataset.adapter.NCFileSystemAdapter"
(("path"="nc1://data/tpch0.001/supplier.tbl"),("format"="delimited-text"),("delimiter"="|")) pre-sorted;

load dataset Region 
using "edu.uci.ics.asterix.external.dataset.adapter.NCFileSystemAdapter"
(("path"="nc1://data/tpch0.001/region.tbl"),("format"="delimited-text"),("delimiter"="|")) pre-sorted;

load dataset Nation 
using "edu.uci.ics.asterix.external.dataset.adapter.NCFileSystemAdapter"
(("path"="nc1://data/tpch0.001/nation.tbl"),("format"="delimited-text"),("delimiter"="|")) pre-sorted;

load dataset Part 
using "edu.uci.ics.asterix.external.dataset.adapter.NCFileSystemAdapter"
(("path"="nc1://data/tpch0.001/part.tbl"),("format"="delimited-text"),("delimiter"="|")) pre-sorted;

load dataset Partsupp 
using "edu.uci.ics.asterix.external.dataset.adapter.NCFileSystemAdapter"
(("path"="nc1://data/tpch0.001/partsupp.tbl"),("format"="delimited-text"),("delimiter"="|")) pre-sorted;

load dataset Customer 
using "edu.uci.ics.asterix.external.dataset.adapter.NCFileSystemAdapter"
(("path"="nc1://data/tpch0.001/customer.tbl"),("format"="delimited-text"),("delimiter"="|")) pre-sorted;

write output to nc1:"rttest/tpch_q8_national_market_share.adm";


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 := 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)
}