== Physical Plan ==
VeloxColumnarToRow (67)
+- TakeOrderedAndProjectExecTransformer (66)
   +- ^ ProjectExecTransformer (64)
      +- ^ BroadcastHashJoinExecTransformer Inner BuildRight (63)
         :- ^ ProjectExecTransformer (33)
         :  +- ^ BroadcastHashJoinExecTransformer Inner BuildRight (32)
         :     :- ^ ProjectExecTransformer (25)
         :     :  +- ^ BroadcastHashJoinExecTransformer Inner BuildRight (24)
         :     :     :- ^ FilterExecTransformer (2)
         :     :     :  +- ^ FileSourceScanExecTransformer parquet spark_catalog.default.store (1)
         :     :     +- ^ InputIteratorTransformer (23)
         :     :        +- ColumnarBroadcastExchange (21)
         :     :           +- ^ FilterExecTransformer (19)
         :     :              +- ^ ProjectExecTransformer (18)
         :     :                 +- ^ RegularHashAggregateExecTransformer (17)
         :     :                    +- ^ InputIteratorTransformer (16)
         :     :                       +- ColumnarExchange (14)
         :     :                          +- VeloxResizeBatches (13)
         :     :                             +- ^ ProjectExecTransformer (11)
         :     :                                +- ^ FlushableHashAggregateExecTransformer (10)
         :     :                                   +- ^ ProjectExecTransformer (9)
         :     :                                      +- ^ BroadcastHashJoinExecTransformer Inner BuildRight (8)
         :     :                                         :- ^ FilterExecTransformer (4)
         :     :                                         :  +- ^ FileSourceScanExecTransformer parquet spark_catalog.default.store_sales (3)
         :     :                                         +- ^ InputIteratorTransformer (7)
         :     :                                            +- ReusedExchange (5)
         :     +- ^ InputIteratorTransformer (31)
         :        +- ColumnarBroadcastExchange (29)
         :           +- ^ FilterExecTransformer (27)
         :              +- ^ FileSourceScanExecTransformer parquet spark_catalog.default.item (26)
         +- ^ InputIteratorTransformer (62)
            +- ColumnarBroadcastExchange (60)
               +- ^ FilterExecTransformer (58)
                  +- ^ RegularHashAggregateExecTransformer (57)
                     +- ^ InputIteratorTransformer (56)
                        +- ColumnarExchange (54)
                           +- VeloxResizeBatches (53)
                              +- ^ ProjectExecTransformer (51)
                                 +- ^ FlushableHashAggregateExecTransformer (50)
                                    +- ^ ProjectExecTransformer (49)
                                       +- ^ RegularHashAggregateExecTransformer (48)
                                          +- ^ InputIteratorTransformer (47)
                                             +- ColumnarExchange (45)
                                                +- VeloxResizeBatches (44)
                                                   +- ^ ProjectExecTransformer (42)
                                                      +- ^ FlushableHashAggregateExecTransformer (41)
                                                         +- ^ ProjectExecTransformer (40)
                                                            +- ^ BroadcastHashJoinExecTransformer Inner BuildRight (39)
                                                               :- ^ FilterExecTransformer (35)
                                                               :  +- ^ FileSourceScanExecTransformer parquet spark_catalog.default.store_sales (34)
                                                               +- ^ InputIteratorTransformer (38)
                                                                  +- ReusedExchange (36)


(1) FileSourceScanExecTransformer parquet spark_catalog.default.store
Output [2]: [s_store_sk#1, s_store_name#2]
Batched: true
Location: InMemoryFileIndex [{warehouse_dir}/store]
PushedFilters: [IsNotNull(s_store_sk)]
ReadSchema: struct<s_store_sk:int,s_store_name:string>

(2) FilterExecTransformer
Input [2]: [s_store_sk#1, s_store_name#2]
Arguments: isnotnull(s_store_sk#1)

(3) FileSourceScanExecTransformer parquet spark_catalog.default.store_sales
Output [4]: [ss_item_sk#3, ss_store_sk#4, ss_sales_price#5, ss_sold_date_sk#6]
Batched: true
Location: InMemoryFileIndex []
PartitionFilters: [isnotnull(ss_sold_date_sk#6), dynamicpruningexpression(ss_sold_date_sk#6 IN dynamicpruning#7)]
PushedFilters: [IsNotNull(ss_store_sk), IsNotNull(ss_item_sk)]
ReadSchema: struct<ss_item_sk:int,ss_store_sk:int,ss_sales_price:decimal(7,2)>

(4) FilterExecTransformer
Input [4]: [ss_item_sk#3, ss_store_sk#4, ss_sales_price#5, ss_sold_date_sk#6]
Arguments: (isnotnull(ss_store_sk#4) AND isnotnull(ss_item_sk#3))

(5) ReusedExchange [Reuses operator id: 72]
Output [1]: [d_date_sk#8]

(6) InputAdapter
Input [1]: [d_date_sk#8]

(7) InputIteratorTransformer
Input [1]: [d_date_sk#8]

(8) BroadcastHashJoinExecTransformer
Left keys [1]: [ss_sold_date_sk#6]
Right keys [1]: [d_date_sk#8]
Join type: Inner
Join condition: None

(9) ProjectExecTransformer
Output [3]: [ss_item_sk#3, ss_store_sk#4, UnscaledValue(ss_sales_price#5) AS _pre_1#9]
Input [5]: [ss_item_sk#3, ss_store_sk#4, ss_sales_price#5, ss_sold_date_sk#6, d_date_sk#8]

(10) FlushableHashAggregateExecTransformer
Input [3]: [ss_item_sk#3, ss_store_sk#4, _pre_1#9]
Keys [2]: [ss_store_sk#4, ss_item_sk#3]
Functions [1]: [partial_sum(_pre_1#9)]
Aggregate Attributes [1]: [sum#10]
Results [3]: [ss_store_sk#4, ss_item_sk#3, sum#11]

(11) ProjectExecTransformer
Output [4]: [hash(ss_store_sk#4, ss_item_sk#3, 42) AS hash_partition_key#12, ss_store_sk#4, ss_item_sk#3, sum#11]
Input [3]: [ss_store_sk#4, ss_item_sk#3, sum#11]

(12) WholeStageCodegenTransformer (3)
Input [4]: [hash_partition_key#12, ss_store_sk#4, ss_item_sk#3, sum#11]
Arguments: false

(13) VeloxResizeBatches
Input [4]: [hash_partition_key#12, ss_store_sk#4, ss_item_sk#3, sum#11]
Arguments: 1024, 2147483647, 10485760

(14) ColumnarExchange
Input [4]: [hash_partition_key#12, ss_store_sk#4, ss_item_sk#3, sum#11]
Arguments: hashpartitioning(ss_store_sk#4, ss_item_sk#3, 1), ENSURE_REQUIREMENTS, [ss_store_sk#4, ss_item_sk#3, sum#11], [plan_id=1], [shuffle_writer_type=hash]

(15) InputAdapter
Input [3]: [ss_store_sk#4, ss_item_sk#3, sum#11]

(16) InputIteratorTransformer
Input [3]: [ss_store_sk#4, ss_item_sk#3, sum#11]

(17) RegularHashAggregateExecTransformer
Input [3]: [ss_store_sk#4, ss_item_sk#3, sum#11]
Keys [2]: [ss_store_sk#4, ss_item_sk#3]
Functions [1]: [sum(UnscaledValue(ss_sales_price#5))]
Aggregate Attributes [1]: [sum(UnscaledValue(ss_sales_price#5))#13]
Results [3]: [ss_store_sk#4, ss_item_sk#3, sum(UnscaledValue(ss_sales_price#5))#13]

(18) ProjectExecTransformer
Output [3]: [ss_store_sk#4, ss_item_sk#3, MakeDecimal(sum(UnscaledValue(ss_sales_price#5))#13,17,2) AS revenue#14]
Input [3]: [ss_store_sk#4, ss_item_sk#3, sum(UnscaledValue(ss_sales_price#5))#13]

(19) FilterExecTransformer
Input [3]: [ss_store_sk#4, ss_item_sk#3, revenue#14]
Arguments: isnotnull(revenue#14)

(20) WholeStageCodegenTransformer (4)
Input [3]: [ss_store_sk#4, ss_item_sk#3, revenue#14]
Arguments: false

(21) ColumnarBroadcastExchange
Input [3]: [ss_store_sk#4, ss_item_sk#3, revenue#14]
Arguments: HashedRelationBroadcastMode(List(cast(input[0, int, true] as bigint)),false), [plan_id=2]

(22) InputAdapter
Input [3]: [ss_store_sk#4, ss_item_sk#3, revenue#14]

(23) InputIteratorTransformer
Input [3]: [ss_store_sk#4, ss_item_sk#3, revenue#14]

(24) BroadcastHashJoinExecTransformer
Left keys [1]: [s_store_sk#1]
Right keys [1]: [ss_store_sk#4]
Join type: Inner
Join condition: None

(25) ProjectExecTransformer
Output [4]: [s_store_name#2, ss_store_sk#4, ss_item_sk#3, revenue#14]
Input [5]: [s_store_sk#1, s_store_name#2, ss_store_sk#4, ss_item_sk#3, revenue#14]

(26) FileSourceScanExecTransformer parquet spark_catalog.default.item
Output [5]: [i_item_sk#15, i_item_desc#16, i_current_price#17, i_wholesale_cost#18, i_brand#19]
Batched: true
Location: InMemoryFileIndex [{warehouse_dir}/item]
PushedFilters: [IsNotNull(i_item_sk)]
ReadSchema: struct<i_item_sk:int,i_item_desc:string,i_current_price:decimal(7,2),i_wholesale_cost:decimal(7,2),i_brand:string>

(27) FilterExecTransformer
Input [5]: [i_item_sk#15, i_item_desc#16, i_current_price#17, i_wholesale_cost#18, i_brand#19]
Arguments: isnotnull(i_item_sk#15)

(28) WholeStageCodegenTransformer (5)
Input [5]: [i_item_sk#15, i_item_desc#16, i_current_price#17, i_wholesale_cost#18, i_brand#19]
Arguments: false

(29) ColumnarBroadcastExchange
Input [5]: [i_item_sk#15, i_item_desc#16, i_current_price#17, i_wholesale_cost#18, i_brand#19]
Arguments: HashedRelationBroadcastMode(List(cast(input[0, int, false] as bigint)),false), [plan_id=3]

(30) InputAdapter
Input [5]: [i_item_sk#15, i_item_desc#16, i_current_price#17, i_wholesale_cost#18, i_brand#19]

(31) InputIteratorTransformer
Input [5]: [i_item_sk#15, i_item_desc#16, i_current_price#17, i_wholesale_cost#18, i_brand#19]

(32) BroadcastHashJoinExecTransformer
Left keys [1]: [ss_item_sk#3]
Right keys [1]: [i_item_sk#15]
Join type: Inner
Join condition: None

(33) ProjectExecTransformer
Output [7]: [s_store_name#2, ss_store_sk#4, revenue#14, i_item_desc#16, i_current_price#17, i_wholesale_cost#18, i_brand#19]
Input [9]: [s_store_name#2, ss_store_sk#4, ss_item_sk#3, revenue#14, i_item_sk#15, i_item_desc#16, i_current_price#17, i_wholesale_cost#18, i_brand#19]

(34) FileSourceScanExecTransformer parquet spark_catalog.default.store_sales
Output [4]: [ss_item_sk#20, ss_store_sk#21, ss_sales_price#22, ss_sold_date_sk#23]
Batched: true
Location: InMemoryFileIndex []
PartitionFilters: [isnotnull(ss_sold_date_sk#23), dynamicpruningexpression(ss_sold_date_sk#23 IN dynamicpruning#7)]
PushedFilters: [IsNotNull(ss_store_sk)]
ReadSchema: struct<ss_item_sk:int,ss_store_sk:int,ss_sales_price:decimal(7,2)>

(35) FilterExecTransformer
Input [4]: [ss_item_sk#20, ss_store_sk#21, ss_sales_price#22, ss_sold_date_sk#23]
Arguments: isnotnull(ss_store_sk#21)

(36) ReusedExchange [Reuses operator id: 72]
Output [1]: [d_date_sk#24]

(37) InputAdapter
Input [1]: [d_date_sk#24]

(38) InputIteratorTransformer
Input [1]: [d_date_sk#24]

(39) BroadcastHashJoinExecTransformer
Left keys [1]: [ss_sold_date_sk#23]
Right keys [1]: [d_date_sk#24]
Join type: Inner
Join condition: None

(40) ProjectExecTransformer
Output [3]: [ss_item_sk#20, ss_store_sk#21, UnscaledValue(ss_sales_price#22) AS _pre_2#25]
Input [5]: [ss_item_sk#20, ss_store_sk#21, ss_sales_price#22, ss_sold_date_sk#23, d_date_sk#24]

(41) FlushableHashAggregateExecTransformer
Input [3]: [ss_item_sk#20, ss_store_sk#21, _pre_2#25]
Keys [2]: [ss_store_sk#21, ss_item_sk#20]
Functions [1]: [partial_sum(_pre_2#25)]
Aggregate Attributes [1]: [sum#26]
Results [3]: [ss_store_sk#21, ss_item_sk#20, sum#27]

(42) ProjectExecTransformer
Output [4]: [hash(ss_store_sk#21, ss_item_sk#20, 42) AS hash_partition_key#28, ss_store_sk#21, ss_item_sk#20, sum#27]
Input [3]: [ss_store_sk#21, ss_item_sk#20, sum#27]

(43) WholeStageCodegenTransformer (8)
Input [4]: [hash_partition_key#28, ss_store_sk#21, ss_item_sk#20, sum#27]
Arguments: false

(44) VeloxResizeBatches
Input [4]: [hash_partition_key#28, ss_store_sk#21, ss_item_sk#20, sum#27]
Arguments: 1024, 2147483647, 10485760

(45) ColumnarExchange
Input [4]: [hash_partition_key#28, ss_store_sk#21, ss_item_sk#20, sum#27]
Arguments: hashpartitioning(ss_store_sk#21, ss_item_sk#20, 1), ENSURE_REQUIREMENTS, [ss_store_sk#21, ss_item_sk#20, sum#27], [plan_id=4], [shuffle_writer_type=hash]

(46) InputAdapter
Input [3]: [ss_store_sk#21, ss_item_sk#20, sum#27]

(47) InputIteratorTransformer
Input [3]: [ss_store_sk#21, ss_item_sk#20, sum#27]

(48) RegularHashAggregateExecTransformer
Input [3]: [ss_store_sk#21, ss_item_sk#20, sum#27]
Keys [2]: [ss_store_sk#21, ss_item_sk#20]
Functions [1]: [sum(UnscaledValue(ss_sales_price#22))]
Aggregate Attributes [1]: [sum(UnscaledValue(ss_sales_price#22))#29]
Results [3]: [ss_store_sk#21, ss_item_sk#20, sum(UnscaledValue(ss_sales_price#22))#29]

(49) ProjectExecTransformer
Output [2]: [ss_store_sk#21, MakeDecimal(sum(UnscaledValue(ss_sales_price#22))#29,17,2) AS revenue#30]
Input [3]: [ss_store_sk#21, ss_item_sk#20, sum(UnscaledValue(ss_sales_price#22))#29]

(50) FlushableHashAggregateExecTransformer
Input [2]: [ss_store_sk#21, revenue#30]
Keys [1]: [ss_store_sk#21]
Functions [1]: [partial_avg(revenue#30)]
Aggregate Attributes [2]: [sum#31, count#32]
Results [3]: [ss_store_sk#21, sum#33, count#34]

(51) ProjectExecTransformer
Output [4]: [hash(ss_store_sk#21, 42) AS hash_partition_key#35, ss_store_sk#21, sum#33, count#34]
Input [3]: [ss_store_sk#21, sum#33, count#34]

(52) WholeStageCodegenTransformer (9)
Input [4]: [hash_partition_key#35, ss_store_sk#21, sum#33, count#34]
Arguments: false

(53) VeloxResizeBatches
Input [4]: [hash_partition_key#35, ss_store_sk#21, sum#33, count#34]
Arguments: 1024, 2147483647, 10485760

(54) ColumnarExchange
Input [4]: [hash_partition_key#35, ss_store_sk#21, sum#33, count#34]
Arguments: hashpartitioning(ss_store_sk#21, 1), ENSURE_REQUIREMENTS, [ss_store_sk#21, sum#33, count#34], [plan_id=5], [shuffle_writer_type=hash]

(55) InputAdapter
Input [3]: [ss_store_sk#21, sum#33, count#34]

(56) InputIteratorTransformer
Input [3]: [ss_store_sk#21, sum#33, count#34]

(57) RegularHashAggregateExecTransformer
Input [3]: [ss_store_sk#21, sum#33, count#34]
Keys [1]: [ss_store_sk#21]
Functions [1]: [avg(revenue#30)]
Aggregate Attributes [1]: [avg(revenue#30)#36]
Results [2]: [ss_store_sk#21, avg(revenue#30)#36 AS ave#37]

(58) FilterExecTransformer
Input [2]: [ss_store_sk#21, ave#37]
Arguments: isnotnull(ave#37)

(59) WholeStageCodegenTransformer (10)
Input [2]: [ss_store_sk#21, ave#37]
Arguments: false

(60) ColumnarBroadcastExchange
Input [2]: [ss_store_sk#21, ave#37]
Arguments: HashedRelationBroadcastMode(List(cast(input[0, int, true] as bigint)),false), [plan_id=6]

(61) InputAdapter
Input [2]: [ss_store_sk#21, ave#37]

(62) InputIteratorTransformer
Input [2]: [ss_store_sk#21, ave#37]

(63) BroadcastHashJoinExecTransformer
Left keys [1]: [ss_store_sk#4]
Right keys [1]: [ss_store_sk#21]
Join type: Inner
Join condition: (cast(revenue#14 as decimal(23,7)) <= (0.1 * ave#37))

(64) ProjectExecTransformer
Output [6]: [s_store_name#2, i_item_desc#16, revenue#14, i_current_price#17, i_wholesale_cost#18, i_brand#19]
Input [9]: [s_store_name#2, ss_store_sk#4, revenue#14, i_item_desc#16, i_current_price#17, i_wholesale_cost#18, i_brand#19, ss_store_sk#21, ave#37]

(65) WholeStageCodegenTransformer (11)
Input [6]: [s_store_name#2, i_item_desc#16, revenue#14, i_current_price#17, i_wholesale_cost#18, i_brand#19]
Arguments: false

(66) TakeOrderedAndProjectExecTransformer
Input [6]: [s_store_name#2, i_item_desc#16, revenue#14, i_current_price#17, i_wholesale_cost#18, i_brand#19]
Arguments: 100, [s_store_name#2 ASC NULLS FIRST, i_item_desc#16 ASC NULLS FIRST], [s_store_name#2, i_item_desc#16, revenue#14, i_current_price#17, i_wholesale_cost#18, i_brand#19], 0

(67) VeloxColumnarToRow
Input [6]: [s_store_name#2, i_item_desc#16, revenue#14, i_current_price#17, i_wholesale_cost#18, i_brand#19]

===== Subqueries =====

Subquery:1 Hosting operator id = 3 Hosting Expression = ss_sold_date_sk#6 IN dynamicpruning#7
ColumnarBroadcastExchange (72)
+- ^ ProjectExecTransformer (70)
   +- ^ FilterExecTransformer (69)
      +- ^ FileSourceScanExecTransformer parquet spark_catalog.default.date_dim (68)


(68) FileSourceScanExecTransformer parquet spark_catalog.default.date_dim
Output [2]: [d_date_sk#8, d_month_seq#38]
Batched: true
Location: InMemoryFileIndex [{warehouse_dir}/date_dim]
PushedFilters: [IsNotNull(d_month_seq), GreaterThanOrEqual(d_month_seq,1176), LessThanOrEqual(d_month_seq,1187), IsNotNull(d_date_sk)]
ReadSchema: struct<d_date_sk:int,d_month_seq:int>

(69) FilterExecTransformer
Input [2]: [d_date_sk#8, d_month_seq#38]
Arguments: (((isnotnull(d_month_seq#38) AND (d_month_seq#38 >= 1176)) AND (d_month_seq#38 <= 1187)) AND isnotnull(d_date_sk#8))

(70) ProjectExecTransformer
Output [1]: [d_date_sk#8]
Input [2]: [d_date_sk#8, d_month_seq#38]

(71) WholeStageCodegenTransformer (1)
Input [1]: [d_date_sk#8]
Arguments: false

(72) ColumnarBroadcastExchange
Input [1]: [d_date_sk#8]
Arguments: HashedRelationBroadcastMode(List(cast(input[0, int, true] as bigint)),false), [plan_id=7]

Subquery:2 Hosting operator id = 34 Hosting Expression = ss_sold_date_sk#23 IN dynamicpruning#7


