asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-with-index/q08_national_market_share/q08_national_market_share.3.query.sqlpp

/*
 * 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 tpch;


select element {'year':year,'mkt_share':(STRICT_SUM((
          select element case i.s_name = 'BRAZIL' when true then i.revenue when false then 0.0 end
          from  (from g select value t) as i
      )) / STRICT_SUM((
          select element i.revenue
          from  (from g select value t) as i
      )))}
from  (
    select element {'year':o_year,'revenue':(slnrcop.l_extendedprice * (1 - slnrcop.l_discount)),'s_name':n2.n_name}
    from  (
        select element {'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}
        from  Supplier as s,
              (
            select element {'o_orderdate':lnrco.o_orderdate,'l_discount':lnrco.l_discount,'l_extendedprice':lnrco.l_extendedprice,'l_suppkey':lnrco.l_suppkey}
            from  (
                select element {'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}
                from  LineItem as l,
                      (
                    select element {'o_orderdate':o.o_orderdate,'o_orderkey':o.o_orderkey}
                    from  Orders as o,
                          (
                        select element {'c_custkey':c.c_custkey}
                        from  Customer as c,
                              (
                            select element {'n_nationkey':n1.n_nationkey}
                            from  Nation as n1,
                                  Region as r1
                            where ((n1.n_regionkey = r1.r_regionkey) and (r1.r_name = 'AMERICA'))
                        ) as nr
                        where (c.c_nationkey = nr.n_nationkey)
                    ) as nrc
                    where (nrc.c_custkey = o.o_custkey)
                ) as nrco
                where ((l.l_orderkey = nrco.o_orderkey) and (nrco.o_orderdate >= '1995-01-01') and (nrco.o_orderdate < '1996-12-31'))
            ) as lnrco,
                  Part as p
            where ((p.p_partkey = lnrco.l_partkey) and (p.p_type = 'ECONOMY ANODIZED STEEL'))
        ) as lnrcop
        where (s.s_suppkey = lnrcop.l_suppkey)
    ) as slnrcop,
          Nation as n2
    with  o_year as tpch.`get-year`(slnrcop.o_orderdate)
    where (slnrcop.s_nationkey = n2.n_nationkey)
) as t
group by t.year as year
group as g
order by year
;