write [%0->$$6, %0->$$1, %0->$$9]
-- SINK_WRITE  |PARTITIONED|
  project ([$$6, $$1, $$9])
  -- STREAM_PROJECT  |PARTITIONED|
    assign [$$9] <- [function-call: hive:org.apache.hadoop.hive.ql.udf.UDFSubstr, Args:[%0->$$5, 1, 2]]
    -- ASSIGN  |PARTITIONED|
      select (function-call: algebricks:or, Args:[function-call: algebricks:or, Args:[function-call: algebricks:or, Args:[function-call: algebricks:or, Args:[function-call: algebricks:or, Args:[function-call: algebricks:or, Args:[function-call: algebricks:eq, Args:[function-call: hive:org.apache.hadoop.hive.ql.udf.UDFSubstr, Args:[%0->$$5, 1, 2], 13], function-call: algebricks:eq, Args:[function-call: hive:org.apache.hadoop.hive.ql.udf.UDFSubstr, Args:[%0->$$5, 1, 2], 31]], function-call: algebricks:eq, Args:[function-call: hive:org.apache.hadoop.hive.ql.udf.UDFSubstr, Args:[%0->$$5, 1, 2], 23]], function-call: algebricks:eq, Args:[function-call: hive:org.apache.hadoop.hive.ql.udf.UDFSubstr, Args:[%0->$$5, 1, 2], 29]], function-call: algebricks:eq, Args:[function-call: hive:org.apache.hadoop.hive.ql.udf.UDFSubstr, Args:[%0->$$5, 1, 2], 30]], function-call: algebricks:eq, Args:[function-call: hive:org.apache.hadoop.hive.ql.udf.UDFSubstr, Args:[%0->$$5, 1, 2], 18]], function-call: algebricks:eq, Args:[function-call: hive:org.apache.hadoop.hive.ql.udf.UDFSubstr, Args:[%0->$$5, 1, 2], 17]])
      -- STREAM_SELECT  |PARTITIONED|
        exchange 
        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
          data-scan []<-[$$1, $$2, $$3, $$4, $$5, $$6, $$7, $$8] <- default.customer
          -- DATASOURCE_SCAN  |PARTITIONED|
            exchange 
            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
              empty-tuple-source
              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
write [%0->$$4]
-- SINK_WRITE  |PARTITIONED|
  exchange 
  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
    group by ([]) decor ([]) {
              aggregate [$$4] <- [function-call: hive:avg(FINAL), Args:[%0->$$6]]
              -- AGGREGATE  |LOCAL|
                nested tuple source
                -- NESTED_TUPLE_SOURCE  |LOCAL|
           }
    -- PRE_CLUSTERED_GROUP_BY[]  |PARTITIONED|
      exchange 
      -- HASH_PARTITION_EXCHANGE []  |PARTITIONED|
        group by ([]) decor ([]) {
                  aggregate [$$6] <- [function-call: hive:avg(PARTIAL1), Args:[%0->$$1]]
                  -- AGGREGATE  |LOCAL|
                    nested tuple source
                    -- NESTED_TUPLE_SOURCE  |LOCAL|
               }
        -- PRE_CLUSTERED_GROUP_BY[]  |PARTITIONED|
          exchange 
          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
            select (function-call: algebricks:gt, Args:[%0->$$1, 0.0])
            -- STREAM_SELECT  |PARTITIONED|
              exchange 
              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                data-scan [$$1]<-[$$1, $$2, $$3] <- default.q22_customer_tmp
                -- DATASOURCE_SCAN  |PARTITIONED|
                  exchange 
                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                    empty-tuple-source
                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
write [%0->$$2]
-- SINK_WRITE  |PARTITIONED|
  exchange 
  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
    distinct ([%0->$$2])
    -- PRE_SORTED_DISTINCT_BY  |PARTITIONED|
      exchange 
      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
        order (ASC, %0->$$2) 
        -- STABLE_SORT [$$2(ASC)]  |PARTITIONED|
          exchange 
          -- HASH_PARTITION_EXCHANGE [$$2]  |PARTITIONED|
            data-scan [$$2]<-[$$1, $$2, $$3, $$4, $$5, $$6, $$7, $$8, $$9] <- default.orders
            -- DATASOURCE_SCAN  |PARTITIONED|
              exchange 
              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                empty-tuple-source
                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
write [%0->$$6, %0->$$9, %0->$$8]
-- SINK_WRITE  |PARTITIONED|
  project ([$$6, $$9, $$8])
  -- STREAM_PROJECT  |PARTITIONED|
    assign [$$9] <- [function-call: hive:org.apache.hadoop.hive.ql.udf.UDFToInteger, Args:[%0->$$7]]
    -- ASSIGN  |PARTITIONED|
      exchange 
      -- SORT_MERGE_EXCHANGE [$$6(ASC) ]  |PARTITIONED|
        group by ([$$6 := %0->$$13]) decor ([]) {
                  aggregate [$$7, $$8] <- [function-call: hive:count(FINAL), Args:[%0->$$11], function-call: hive:sum(FINAL), Args:[%0->$$12]]
                  -- AGGREGATE  |LOCAL|
                    nested tuple source
                    -- NESTED_TUPLE_SOURCE  |LOCAL|
               }
        -- PRE_CLUSTERED_GROUP_BY[$$13]  |PARTITIONED|
          exchange 
          -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$13(ASC)] HASH:[$$13]  |PARTITIONED|
            group by ([$$13 := %0->$$5]) decor ([]) {
                      aggregate [$$11, $$12] <- [function-call: hive:count(PARTIAL1), Args:[1], function-call: hive:sum(PARTIAL1), Args:[%0->$$3]]
                      -- AGGREGATE  |LOCAL|
                        nested tuple source
                        -- NESTED_TUPLE_SOURCE  |LOCAL|
                   }
            -- SORT_GROUP_BY[$$5]  |PARTITIONED|
              exchange 
              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                project ([$$5, $$3])
                -- STREAM_PROJECT  |PARTITIONED|
                  exchange 
                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                    join (function-call: algebricks:and, Args:[function-call: algebricks:gt, Args:[%0->$$3, %0->$$1], true])
                    -- NESTED_LOOP  |PARTITIONED|
                      exchange 
                      -- BROADCAST_EXCHANGE  |PARTITIONED|
                        project ([$$5, $$3])
                        -- STREAM_PROJECT  |PARTITIONED|
                          select (function-call: hive:org.apache.hadoop.hive.ql.udf.generic.GenericUDFOPNull, Args:[%0->$$2])
                          -- STREAM_SELECT  |PARTITIONED|
                            exchange 
                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                              left outer join (function-call: algebricks:eq, Args:[%0->$$4, %0->$$2])
                              -- HYBRID_HASH_JOIN [$$4][$$2]  |PARTITIONED|
                                exchange 
                                -- HASH_PARTITION_EXCHANGE [$$4]  |PARTITIONED|
                                  data-scan []<-[$$3, $$4, $$5] <- default.q22_customer_tmp
                                  -- DATASOURCE_SCAN  |PARTITIONED|
                                    exchange 
                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                      empty-tuple-source
                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
                                exchange 
                                -- HASH_PARTITION_EXCHANGE [$$2]  |PARTITIONED|
                                  data-scan [$$2]<-[$$2] <- default.q22_orders_tmp
                                  -- DATASOURCE_SCAN  |PARTITIONED|
                                    exchange 
                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                      empty-tuple-source
                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
                      exchange 
                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                        data-scan [$$1]<-[$$1] <- default.q22_customer_tmp1
                        -- DATASOURCE_SCAN  |PARTITIONED|
                          exchange 
                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                            empty-tuple-source
                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|