blob: cea12dd3402910895549b9af9416aa45d51ce9f7 [file] [log] [blame]
/*
* Licensed to the Apache Software Foundation (ASF) under one
* or more contributor license agreements. See the NOTICE file
* distributed with this work for additional information
* regarding copyright ownership. The ASF licenses this file
* to you under the Apache License, Version 2.0 (the
* "License"); you may not use this file except in compliance
* with the License. You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing,
* software distributed under the License is distributed on an
* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
* KIND, either express or implied. See the License for the
* specific language governing permissions and limitations
* under the License.
*/
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)
}