write [%0->$$21, %0->$$24, %0->$$23]
-- SINK_WRITE  |PARTITIONED|
  project ([$$21, $$24, $$23])
  -- STREAM_PROJECT  |PARTITIONED|
    assign [$$24] <- [function-call: hive:org.apache.hadoop.hive.ql.udf.UDFToInteger, Args:[%0->$$22]]
    -- ASSIGN  |PARTITIONED|
      exchange 
      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
        group by ([$$21 := %0->$$17]) decor ([]) {
                  aggregate [$$22, $$23] <- [function-call: hive:count(COMPLETE), Args:[%0->$$18], function-call: hive:max(FINAL), Args:[%0->$$20]]
                  -- AGGREGATE  |LOCAL|
                    nested tuple source
                    -- NESTED_TUPLE_SOURCE  |LOCAL|
               }
        -- PRE_CLUSTERED_GROUP_BY[$$17]  |PARTITIONED|
          exchange 
          -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$17(ASC)] HASH:[$$17]  |PARTITIONED|
            group by ([$$17 := %0->$$1; $$18 := %0->$$3]) decor ([]) {
                      aggregate [$$20] <- [function-call: hive:max(PARTIAL1), Args:[%0->$$3]]
                      -- AGGREGATE  |LOCAL|
                        nested tuple source
                        -- NESTED_TUPLE_SOURCE  |LOCAL|
                   }
            -- PRE_CLUSTERED_GROUP_BY[$$1, $$3]  |PARTITIONED|
              exchange 
              -- ONE_TO_ONE_EXCHANGE  |LOCAL|
                order (ASC, %0->$$1) (ASC, %0->$$3) 
                -- STABLE_SORT [$$1(ASC), $$3(ASC)]  |LOCAL|
                  exchange 
                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                    data-scan [$$1, $$3]<-[$$1, $$2, $$3, $$4, $$5, $$6, $$7, $$8, $$9, $$10, $$11, $$12, $$13, $$14, $$15, $$16] <- default.lineitem
                    -- DATASOURCE_SCAN  |PARTITIONED|
                      exchange 
                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                        empty-tuple-source
                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
write [%0->$$21, %0->$$24, %0->$$23]
-- SINK_WRITE  |PARTITIONED|
  project ([$$21, $$24, $$23])
  -- STREAM_PROJECT  |PARTITIONED|
    assign [$$24] <- [function-call: hive:org.apache.hadoop.hive.ql.udf.UDFToInteger, Args:[%0->$$22]]
    -- ASSIGN  |PARTITIONED|
      exchange 
      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
        group by ([$$21 := %0->$$17]) decor ([]) {
                  aggregate [$$22, $$23] <- [function-call: hive:count(COMPLETE), Args:[%0->$$18], function-call: hive:max(FINAL), Args:[%0->$$20]]
                  -- AGGREGATE  |LOCAL|
                    nested tuple source
                    -- NESTED_TUPLE_SOURCE  |LOCAL|
               }
        -- PRE_CLUSTERED_GROUP_BY[$$17]  |PARTITIONED|
          exchange 
          -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$17(ASC)] HASH:[$$17]  |PARTITIONED|
            group by ([$$17 := %0->$$1; $$18 := %0->$$3]) decor ([]) {
                      aggregate [$$20] <- [function-call: hive:max(PARTIAL1), Args:[%0->$$3]]
                      -- AGGREGATE  |LOCAL|
                        nested tuple source
                        -- NESTED_TUPLE_SOURCE  |LOCAL|
                   }
            -- PRE_CLUSTERED_GROUP_BY[$$1, $$3]  |PARTITIONED|
              exchange 
              -- ONE_TO_ONE_EXCHANGE  |LOCAL|
                order (ASC, %0->$$1) (ASC, %0->$$3) 
                -- STABLE_SORT [$$1(ASC), $$3(ASC)]  |LOCAL|
                  exchange 
                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                    project ([$$1, $$3])
                    -- STREAM_PROJECT  |PARTITIONED|
                      select (function-call: algebricks:gt, Args:[%0->$$13, %0->$$12])
                      -- STREAM_SELECT  |PARTITIONED|
                        exchange 
                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                          data-scan [$$1, $$3, $$12, $$13]<-[$$1, $$2, $$3, $$4, $$5, $$6, $$7, $$8, $$9, $$10, $$11, $$12, $$13, $$14, $$15, $$16] <- default.lineitem
                          -- DATASOURCE_SCAN  |PARTITIONED|
                            exchange 
                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                              empty-tuple-source
                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
write [%0->$$43, %0->$$45]
-- SINK_WRITE  |UNPARTITIONED|
  project ([$$43, $$45])
  -- STREAM_PROJECT  |UNPARTITIONED|
    assign [$$45] <- [function-call: hive:org.apache.hadoop.hive.ql.udf.UDFToInteger, Args:[%0->$$44]]
    -- ASSIGN  |UNPARTITIONED|
      limit 100
      -- STREAM_LIMIT  |UNPARTITIONED|
        limit 100
        -- STREAM_LIMIT  |UNPARTITIONED|
          exchange 
          -- SORT_MERGE_EXCHANGE [$$44(DESC), $$43(ASC) ]  |PARTITIONED|
            limit 100
            -- STREAM_LIMIT  |LOCAL|
              exchange 
              -- ONE_TO_ONE_EXCHANGE  |LOCAL|
                order (DESC, %0->$$44) (ASC, %0->$$43) 
                -- STABLE_SORT [$$44(DESC), $$43(ASC)]  |LOCAL|
                  exchange 
                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                    group by ([$$43 := %0->$$48]) decor ([]) {
                              aggregate [$$44] <- [function-call: hive:count(FINAL), Args:[%0->$$47]]
                              -- AGGREGATE  |LOCAL|
                                nested tuple source
                                -- NESTED_TUPLE_SOURCE  |LOCAL|
                           }
                    -- EXTERNAL_GROUP_BY[$$48]  |PARTITIONED|
                      exchange 
                      -- HASH_PARTITION_EXCHANGE [$$48]  |PARTITIONED|
                        group by ([$$48 := %0->$$37]) decor ([]) {
                                  aggregate [$$47] <- [function-call: hive:count(PARTIAL1), Args:[1]]
                                  -- AGGREGATE  |LOCAL|
                                    nested tuple source
                                    -- NESTED_TUPLE_SOURCE  |LOCAL|
                               }
                        -- EXTERNAL_GROUP_BY[$$37]  |LOCAL|
                          exchange 
                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                            project ([$$37])
                            -- STREAM_PROJECT  |PARTITIONED|
                              select (function-call: algebricks:or, Args:[function-call: hive:org.apache.hadoop.hive.ql.udf.generic.GenericUDFOPNull, Args:[%0->$$2], function-call: algebricks:and, Args:[function-call: algebricks:eq, Args:[%0->$$2, 1], function-call: algebricks:eq, Args:[%0->$$18, %0->$$3]]])
                              -- STREAM_SELECT  |PARTITIONED|
                                exchange 
                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                  left outer join (function-call: algebricks:eq, Args:[%0->$$4, %0->$$1])
                                  -- HYBRID_HASH_JOIN [$$4][$$1]  |PARTITIONED|
                                    exchange 
                                    -- HASH_PARTITION_EXCHANGE [$$4]  |PARTITIONED|
                                      project ([$$37, $$4, $$18])
                                      -- STREAM_PROJECT  |PARTITIONED|
                                        select (function-call: algebricks:or, Args:[function-call: algebricks:gt, Args:[%0->$$5, 1], function-call: algebricks:and, Args:[function-call: algebricks:eq, Args:[%0->$$5, 1], function-call: algebricks:neq, Args:[%0->$$18, %0->$$6]]])
                                        -- STREAM_SELECT  |UNPARTITIONED|
                                          exchange 
                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                            join (function-call: algebricks:eq, Args:[%0->$$16, %0->$$4])
                                            -- HYBRID_HASH_JOIN [$$16][$$4]  |PARTITIONED|
                                              exchange 
                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                project ([$$37, $$16, $$18])
                                                -- STREAM_PROJECT  |PARTITIONED|
                                                  exchange 
                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                    join (function-call: algebricks:eq, Args:[%0->$$16, %0->$$7])
                                                    -- HYBRID_HASH_JOIN [$$16][$$7]  |PARTITIONED|
                                                      exchange 
                                                      -- HASH_PARTITION_EXCHANGE [$$16]  |PARTITIONED|
                                                        project ([$$37, $$16, $$18])
                                                        -- STREAM_PROJECT  |PARTITIONED|
                                                          exchange 
                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                            join (function-call: algebricks:eq, Args:[%0->$$18, %0->$$36])
                                                            -- HYBRID_HASH_JOIN [$$18][$$36]  |PARTITIONED|
                                                              exchange 
                                                              -- HASH_PARTITION_EXCHANGE [$$18]  |PARTITIONED|
                                                                project ([$$16, $$18])
                                                                -- STREAM_PROJECT  |PARTITIONED|
                                                                  select (function-call: algebricks:and, Args:[function-call: algebricks:gt, Args:[%0->$$28, %0->$$27], function-call: algebricks:gt, Args:[%0->$$28, %0->$$27]])
                                                                  -- STREAM_SELECT  |PARTITIONED|
                                                                    exchange 
                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                      data-scan [$$16, $$18, $$27, $$28]<-[$$16, $$17, $$18, $$19, $$20, $$21, $$22, $$23, $$24, $$25, $$26, $$27, $$28, $$29, $$30, $$31] <- default.lineitem
                                                                      -- DATASOURCE_SCAN  |PARTITIONED|
                                                                        exchange 
                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                          empty-tuple-source
                                                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
                                                              exchange 
                                                              -- HASH_PARTITION_EXCHANGE [$$36]  |PARTITIONED|
                                                                project ([$$36, $$37])
                                                                -- STREAM_PROJECT  |PARTITIONED|
                                                                  exchange 
                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                    join (function-call: algebricks:eq, Args:[%0->$$39, %0->$$32])
                                                                    -- HYBRID_HASH_JOIN [$$39][$$32]  |PARTITIONED|
                                                                      exchange 
                                                                      -- HASH_PARTITION_EXCHANGE [$$39]  |PARTITIONED|
                                                                        data-scan [$$39, $$36, $$37]<-[$$36, $$37, $$38, $$39, $$40, $$41, $$42] <- default.supplier
                                                                        -- DATASOURCE_SCAN  |PARTITIONED|
                                                                          exchange 
                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                            empty-tuple-source
                                                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
                                                                      exchange 
                                                                      -- HASH_PARTITION_EXCHANGE [$$32]  |PARTITIONED|
                                                                        project ([$$32])
                                                                        -- STREAM_PROJECT  |PARTITIONED|
                                                                          select (function-call: algebricks:eq, Args:[%0->$$33, SAUDI ARABIA])
                                                                          -- STREAM_SELECT  |PARTITIONED|
                                                                            exchange 
                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                              data-scan [$$32, $$33]<-[$$32, $$33, $$34, $$35] <- default.nation
                                                                              -- DATASOURCE_SCAN  |PARTITIONED|
                                                                                exchange 
                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                  empty-tuple-source
                                                                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
                                                      exchange 
                                                      -- HASH_PARTITION_EXCHANGE [$$7]  |PARTITIONED|
                                                        project ([$$7])
                                                        -- STREAM_PROJECT  |PARTITIONED|
                                                          select (function-call: algebricks:eq, Args:[%0->$$9, F])
                                                          -- STREAM_SELECT  |PARTITIONED|
                                                            exchange 
                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                              data-scan [$$7, $$9]<-[$$7, $$8, $$9, $$10, $$11, $$12, $$13, $$14, $$15] <- default.orders
                                                              -- DATASOURCE_SCAN  |PARTITIONED|
                                                                exchange 
                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                  empty-tuple-source
                                                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
                                              exchange 
                                              -- HASH_PARTITION_EXCHANGE [$$4]  |PARTITIONED|
                                                data-scan [$$4, $$5, $$6]<-[$$4, $$5, $$6] <- default.q21_tmp1
                                                -- DATASOURCE_SCAN  |PARTITIONED|
                                                  exchange 
                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                    empty-tuple-source
                                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
                                    exchange 
                                    -- HASH_PARTITION_EXCHANGE [$$1]  |PARTITIONED|
                                      data-scan [$$1, $$2, $$3]<-[$$1, $$2, $$3] <- default.q21_tmp2
                                      -- DATASOURCE_SCAN  |PARTITIONED|
                                        exchange 
                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                          empty-tuple-source
                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|