asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpch-sql-like/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.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;


declare function tmp1() {
(
    select element {'l_orderkey':l_orderkey,'count_suppkey':COLL_COUNT((
            select element i.l_suppkey
            from  l2 as i
        )),'max_suppkey':tpch.coll_max((
            select element i.l_suppkey
            from  l2 as i
        ))}
    from  (
        select element {'l_orderkey':l_orderkey1,'l_suppkey':l_suppkey1}
        from  LineItem as l
        group by l.l_orderkey as l_orderkey1,l.l_suppkey as l_suppkey1
    ) as l2
    group by l2.l_orderkey as l_orderkey
)
};
declare function tmp2() {
(
    select element {'l_orderkey':l_orderkey,'count_suppkey':COLL_COUNT((
            select element i.l_suppkey
            from  l2 as i
        )),'max_suppkey':COLL_MAX((
            select element i.l_suppkey
            from  l2 as i
        ))}
    from  (
        select element {'l_orderkey':l_orderkey1,'l_suppkey':l_suppkey1}
        from  LineItem as l
        where (l.l_receiptdate > l.l_commitdate)
        group by l.l_orderkey as l_orderkey1,l.l_suppkey as l_suppkey1
    ) as l2
    group by l2.l_orderkey as l_orderkey
)
};
select element {'s_name':s_name,'numwait':numwait}
from  (
    select element {'s_name':t3.s_name,'l_suppkey':t3.l_suppkey,'l_orderkey':t2.l_orderkey,'count_suppkey':t2.count_suppkey,'max_suppkey':t2.max_suppkey}
    from  (
        select element {'s_name':ns.s_name,'l_orderkey':t1.l_orderkey,'l_suppkey':l.l_suppkey}
        from  LineItem as l,
              (
            select element {'s_name':s.s_name,'s_suppkey':s.s_suppkey}
            from  Nation as n,
                  Supplier as s
            where (s.s_nationkey = n.n_nationkey)
        ) as ns,
              Orders as o,
              tpch.tmp1() as t1
        where (((ns.s_suppkey = l.l_suppkey) and (l.l_receiptdate > l.l_commitdate)) and (o.o_orderkey = l.l_orderkey) and (l.l_orderkey = t1.l_orderkey))
    ) as t3,
          tpch.tmp2() as t2
    where ((t2.count_suppkey >= 0) and (t3.l_orderkey = t2.l_orderkey))
) as t4
group by t4.s_name as s_name
with  numwait as COLL_COUNT(t4)
order by numwait desc,s_name
;