== Physical Plan ==
VeloxColumnarToRow (67)
+- TakeOrderedAndProjectExecTransformer (66)
   +- ^ ProjectExecTransformer (64)
      +- ^ BroadcastHashJoinExecTransformer Inner BuildLeft (63)
         :- ^ InputIteratorTransformer (60)
         :  +- ColumnarBroadcastExchange (58)
         :     +- ^ ProjectExecTransformer (56)
         :        +- ^ BroadcastHashJoinExecTransformer Inner BuildLeft (55)
         :           :- ^ InputIteratorTransformer (52)
         :           :  +- ColumnarBroadcastExchange (50)
         :           :     +- ^ ProjectExecTransformer (48)
         :           :        +- ^ BroadcastHashJoinExecTransformer Inner BuildRight (47)
         :           :           :- ^ FilterExecTransformer (17)
         :           :           :  +- ^ ProjectExecTransformer (16)
         :           :           :     +- ^ RegularHashAggregateExecTransformer (15)
         :           :           :        +- ^ InputIteratorTransformer (14)
         :           :           :           +- ColumnarExchange (12)
         :           :           :              +- VeloxResizeBatches (11)
         :           :           :                 +- ^ ProjectExecTransformer (9)
         :           :           :                    +- ^ FlushableHashAggregateExecTransformer (8)
         :           :           :                       +- ^ ProjectExecTransformer (7)
         :           :           :                          +- ^ BroadcastHashJoinExecTransformer Inner BuildRight (6)
         :           :           :                             :- ^ FilterExecTransformer (2)
         :           :           :                             :  +- ^ FileSourceScanExecTransformer parquet spark_catalog.default.store_sales (1)
         :           :           :                             +- ^ InputIteratorTransformer (5)
         :           :           :                                +- ReusedExchange (3)
         :           :           +- ^ InputIteratorTransformer (46)
         :           :              +- ColumnarBroadcastExchange (44)
         :           :                 +- ^ FilterExecTransformer (42)
         :           :                    +- ^ RegularHashAggregateExecTransformer (41)
         :           :                       +- ^ InputIteratorTransformer (40)
         :           :                          +- ColumnarExchange (38)
         :           :                             +- VeloxResizeBatches (37)
         :           :                                +- ^ ProjectExecTransformer (35)
         :           :                                   +- ^ FlushableHashAggregateExecTransformer (34)
         :           :                                      +- ^ ProjectExecTransformer (33)
         :           :                                         +- ^ RegularHashAggregateExecTransformer (32)
         :           :                                            +- ^ InputIteratorTransformer (31)
         :           :                                               +- ColumnarExchange (29)
         :           :                                                  +- VeloxResizeBatches (28)
         :           :                                                     +- ^ ProjectExecTransformer (26)
         :           :                                                        +- ^ FlushableHashAggregateExecTransformer (25)
         :           :                                                           +- ^ ProjectExecTransformer (24)
         :           :                                                              +- ^ BroadcastHashJoinExecTransformer Inner BuildRight (23)
         :           :                                                                 :- ^ FilterExecTransformer (19)
         :           :                                                                 :  +- ^ FileSourceScanExecTransformer parquet spark_catalog.default.store_sales (18)
         :           :                                                                 +- ^ InputIteratorTransformer (22)
         :           :                                                                    +- ReusedExchange (20)
         :           +- ^ FilterExecTransformer (54)
         :              +- ^ FileSourceScanExecTransformer parquet spark_catalog.default.store (53)
         +- ^ FilterExecTransformer (62)
            +- ^ FileSourceScanExecTransformer parquet spark_catalog.default.item (61)


(1) FileSourceScanExecTransformer parquet spark_catalog.default.store_sales
Output [4]: [ss_item_sk#1, ss_store_sk#2, ss_sales_price#3, ss_sold_date_sk#4]
Batched: true
Location: InMemoryFileIndex []
PartitionFilters: [isnotnull(ss_sold_date_sk#4), (ss_sold_date_sk#4 >= 2451911), (ss_sold_date_sk#4 <= 2452275), dynamicpruningexpression(ss_sold_date_sk#4 IN dynamicpruning#5)]
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)>

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

(3) ReusedExchange [Reuses operator id: 72]
Output [1]: [d_date_sk#6]

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

(5) InputIteratorTransformer
Input [1]: [d_date_sk#6]

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

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

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

(9) ProjectExecTransformer
Output [4]: [hash(ss_store_sk#2, ss_item_sk#1, 42) AS hash_partition_key#10, ss_store_sk#2, ss_item_sk#1, sum#9]
Input [3]: [ss_store_sk#2, ss_item_sk#1, sum#9]

(10) WholeStageCodegenTransformer (3)
Input [4]: [hash_partition_key#10, ss_store_sk#2, ss_item_sk#1, sum#9]
Arguments: false

(11) VeloxResizeBatches
Input [4]: [hash_partition_key#10, ss_store_sk#2, ss_item_sk#1, sum#9]
Arguments: 1024, 2147483647, 10485760

(12) ColumnarExchange
Input [4]: [hash_partition_key#10, ss_store_sk#2, ss_item_sk#1, sum#9]
Arguments: hashpartitioning(ss_store_sk#2, ss_item_sk#1, 1), ENSURE_REQUIREMENTS, [ss_store_sk#2, ss_item_sk#1, sum#9], [plan_id=1], [shuffle_writer_type=hash]

(13) InputAdapter
Input [3]: [ss_store_sk#2, ss_item_sk#1, sum#9]

(14) InputIteratorTransformer
Input [3]: [ss_store_sk#2, ss_item_sk#1, sum#9]

(15) RegularHashAggregateExecTransformer
Input [3]: [ss_store_sk#2, ss_item_sk#1, sum#9]
Keys [2]: [ss_store_sk#2, ss_item_sk#1]
Functions [1]: [sum(UnscaledValue(ss_sales_price#3))]
Aggregate Attributes [1]: [sum(UnscaledValue(ss_sales_price#3))#11]
Results [3]: [ss_store_sk#2, ss_item_sk#1, sum(UnscaledValue(ss_sales_price#3))#11]

(16) ProjectExecTransformer
Output [3]: [ss_store_sk#2, ss_item_sk#1, MakeDecimal(sum(UnscaledValue(ss_sales_price#3))#11,17,2) AS revenue#12]
Input [3]: [ss_store_sk#2, ss_item_sk#1, sum(UnscaledValue(ss_sales_price#3))#11]

(17) FilterExecTransformer
Input [3]: [ss_store_sk#2, ss_item_sk#1, revenue#12]
Arguments: isnotnull(revenue#12)

(18) FileSourceScanExecTransformer parquet spark_catalog.default.store_sales
Output [4]: [ss_item_sk#13, ss_store_sk#14, ss_sales_price#15, ss_sold_date_sk#16]
Batched: true
Location: InMemoryFileIndex []
PartitionFilters: [isnotnull(ss_sold_date_sk#16), (ss_sold_date_sk#16 >= 2451911), (ss_sold_date_sk#16 <= 2452275), dynamicpruningexpression(ss_sold_date_sk#16 IN dynamicpruning#5)]
PushedFilters: [IsNotNull(ss_store_sk)]
ReadSchema: struct<ss_item_sk:int,ss_store_sk:int,ss_sales_price:decimal(7,2)>

(19) FilterExecTransformer
Input [4]: [ss_item_sk#13, ss_store_sk#14, ss_sales_price#15, ss_sold_date_sk#16]
Arguments: isnotnull(ss_store_sk#14)

(20) ReusedExchange [Reuses operator id: 72]
Output [1]: [d_date_sk#17]

(21) InputAdapter
Input [1]: [d_date_sk#17]

(22) InputIteratorTransformer
Input [1]: [d_date_sk#17]

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

(24) ProjectExecTransformer
Output [3]: [ss_item_sk#13, ss_store_sk#14, UnscaledValue(ss_sales_price#15) AS _pre_2#18]
Input [5]: [ss_item_sk#13, ss_store_sk#14, ss_sales_price#15, ss_sold_date_sk#16, d_date_sk#17]

(25) FlushableHashAggregateExecTransformer
Input [3]: [ss_item_sk#13, ss_store_sk#14, _pre_2#18]
Keys [2]: [ss_store_sk#14, ss_item_sk#13]
Functions [1]: [partial_sum(_pre_2#18)]
Aggregate Attributes [1]: [sum#19]
Results [3]: [ss_store_sk#14, ss_item_sk#13, sum#20]

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

(27) WholeStageCodegenTransformer (6)
Input [4]: [hash_partition_key#21, ss_store_sk#14, ss_item_sk#13, sum#20]
Arguments: false

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

(29) ColumnarExchange
Input [4]: [hash_partition_key#21, ss_store_sk#14, ss_item_sk#13, sum#20]
Arguments: hashpartitioning(ss_store_sk#14, ss_item_sk#13, 1), ENSURE_REQUIREMENTS, [ss_store_sk#14, ss_item_sk#13, sum#20], [plan_id=2], [shuffle_writer_type=hash]

(30) InputAdapter
Input [3]: [ss_store_sk#14, ss_item_sk#13, sum#20]

(31) InputIteratorTransformer
Input [3]: [ss_store_sk#14, ss_item_sk#13, sum#20]

(32) RegularHashAggregateExecTransformer
Input [3]: [ss_store_sk#14, ss_item_sk#13, sum#20]
Keys [2]: [ss_store_sk#14, ss_item_sk#13]
Functions [1]: [sum(UnscaledValue(ss_sales_price#15))]
Aggregate Attributes [1]: [sum(UnscaledValue(ss_sales_price#15))#22]
Results [3]: [ss_store_sk#14, ss_item_sk#13, sum(UnscaledValue(ss_sales_price#15))#22]

(33) ProjectExecTransformer
Output [2]: [ss_store_sk#14, MakeDecimal(sum(UnscaledValue(ss_sales_price#15))#22,17,2) AS revenue#23]
Input [3]: [ss_store_sk#14, ss_item_sk#13, sum(UnscaledValue(ss_sales_price#15))#22]

(34) FlushableHashAggregateExecTransformer
Input [2]: [ss_store_sk#14, revenue#23]
Keys [1]: [ss_store_sk#14]
Functions [1]: [partial_avg(revenue#23)]
Aggregate Attributes [2]: [sum#24, count#25]
Results [3]: [ss_store_sk#14, sum#26, count#27]

(35) ProjectExecTransformer
Output [4]: [hash(ss_store_sk#14, 42) AS hash_partition_key#28, ss_store_sk#14, sum#26, count#27]
Input [3]: [ss_store_sk#14, sum#26, count#27]

(36) WholeStageCodegenTransformer (7)
Input [4]: [hash_partition_key#28, ss_store_sk#14, sum#26, count#27]
Arguments: false

(37) VeloxResizeBatches
Input [4]: [hash_partition_key#28, ss_store_sk#14, sum#26, count#27]
Arguments: 1024, 2147483647, 10485760

(38) ColumnarExchange
Input [4]: [hash_partition_key#28, ss_store_sk#14, sum#26, count#27]
Arguments: hashpartitioning(ss_store_sk#14, 1), ENSURE_REQUIREMENTS, [ss_store_sk#14, sum#26, count#27], [plan_id=3], [shuffle_writer_type=hash]

(39) InputAdapter
Input [3]: [ss_store_sk#14, sum#26, count#27]

(40) InputIteratorTransformer
Input [3]: [ss_store_sk#14, sum#26, count#27]

(41) RegularHashAggregateExecTransformer
Input [3]: [ss_store_sk#14, sum#26, count#27]
Keys [1]: [ss_store_sk#14]
Functions [1]: [avg(revenue#23)]
Aggregate Attributes [1]: [avg(revenue#23)#29]
Results [2]: [ss_store_sk#14, avg(revenue#23)#29 AS ave#30]

(42) FilterExecTransformer
Input [2]: [ss_store_sk#14, ave#30]
Arguments: isnotnull(ave#30)

(43) WholeStageCodegenTransformer (8)
Input [2]: [ss_store_sk#14, ave#30]
Arguments: false

(44) ColumnarBroadcastExchange
Input [2]: [ss_store_sk#14, ave#30]
Arguments: HashedRelationBroadcastMode(List(cast(input[0, int, true] as bigint)),false), [plan_id=4]

(45) InputAdapter
Input [2]: [ss_store_sk#14, ave#30]

(46) InputIteratorTransformer
Input [2]: [ss_store_sk#14, ave#30]

(47) BroadcastHashJoinExecTransformer
Left keys [1]: [ss_store_sk#2]
Right keys [1]: [ss_store_sk#14]
Join type: Inner
Join condition: (cast(revenue#12 as decimal(23,7)) <= (0.1 * ave#30))

(48) ProjectExecTransformer
Output [3]: [ss_store_sk#2, ss_item_sk#1, revenue#12]
Input [5]: [ss_store_sk#2, ss_item_sk#1, revenue#12, ss_store_sk#14, ave#30]

(49) WholeStageCodegenTransformer (9)
Input [3]: [ss_store_sk#2, ss_item_sk#1, revenue#12]
Arguments: false

(50) ColumnarBroadcastExchange
Input [3]: [ss_store_sk#2, ss_item_sk#1, revenue#12]
Arguments: HashedRelationBroadcastMode(List(cast(input[0, int, true] as bigint)),false), [plan_id=5]

(51) InputAdapter
Input [3]: [ss_store_sk#2, ss_item_sk#1, revenue#12]

(52) InputIteratorTransformer
Input [3]: [ss_store_sk#2, ss_item_sk#1, revenue#12]

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

(54) FilterExecTransformer
Input [2]: [s_store_sk#31, s_store_name#32]
Arguments: isnotnull(s_store_sk#31)

(55) BroadcastHashJoinExecTransformer
Left keys [1]: [ss_store_sk#2]
Right keys [1]: [s_store_sk#31]
Join type: Inner
Join condition: None

(56) ProjectExecTransformer
Output [3]: [ss_item_sk#1, revenue#12, s_store_name#32]
Input [5]: [ss_store_sk#2, ss_item_sk#1, revenue#12, s_store_sk#31, s_store_name#32]

(57) WholeStageCodegenTransformer (10)
Input [3]: [ss_item_sk#1, revenue#12, s_store_name#32]
Arguments: false

(58) ColumnarBroadcastExchange
Input [3]: [ss_item_sk#1, revenue#12, s_store_name#32]
Arguments: HashedRelationBroadcastMode(List(cast(input[0, int, true] as bigint)),false), [plan_id=6]

(59) InputAdapter
Input [3]: [ss_item_sk#1, revenue#12, s_store_name#32]

(60) InputIteratorTransformer
Input [3]: [ss_item_sk#1, revenue#12, s_store_name#32]

(61) FileSourceScanExecTransformer parquet spark_catalog.default.item
Output [5]: [i_item_sk#33, i_item_desc#34, i_current_price#35, i_wholesale_cost#36, i_brand#37]
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>

(62) FilterExecTransformer
Input [5]: [i_item_sk#33, i_item_desc#34, i_current_price#35, i_wholesale_cost#36, i_brand#37]
Arguments: isnotnull(i_item_sk#33)

(63) BroadcastHashJoinExecTransformer
Left keys [1]: [ss_item_sk#1]
Right keys [1]: [i_item_sk#33]
Join type: Inner
Join condition: None

(64) ProjectExecTransformer
Output [6]: [s_store_name#32, i_item_desc#34, revenue#12, i_current_price#35, i_wholesale_cost#36, i_brand#37]
Input [8]: [ss_item_sk#1, revenue#12, s_store_name#32, i_item_sk#33, i_item_desc#34, i_current_price#35, i_wholesale_cost#36, i_brand#37]

(65) WholeStageCodegenTransformer (11)
Input [6]: [s_store_name#32, i_item_desc#34, revenue#12, i_current_price#35, i_wholesale_cost#36, i_brand#37]
Arguments: false

(66) TakeOrderedAndProjectExecTransformer
Input [6]: [s_store_name#32, i_item_desc#34, revenue#12, i_current_price#35, i_wholesale_cost#36, i_brand#37]
Arguments: 100, [s_store_name#32 ASC NULLS FIRST, i_item_desc#34 ASC NULLS FIRST], [s_store_name#32, i_item_desc#34, revenue#12, i_current_price#35, i_wholesale_cost#36, i_brand#37], 0

(67) VeloxColumnarToRow
Input [6]: [s_store_name#32, i_item_desc#34, revenue#12, i_current_price#35, i_wholesale_cost#36, i_brand#37]

===== Subqueries =====

Subquery:1 Hosting operator id = 1 Hosting Expression = ss_sold_date_sk#4 IN dynamicpruning#5
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#6, d_month_seq#38]
Batched: true
Location: InMemoryFileIndex [{warehouse_dir}/date_dim]
PushedFilters: [IsNotNull(d_month_seq), GreaterThanOrEqual(d_month_seq,1212), LessThanOrEqual(d_month_seq,1223), GreaterThanOrEqual(d_date_sk,2451911), LessThanOrEqual(d_date_sk,2452275), IsNotNull(d_date_sk)]
ReadSchema: struct<d_date_sk:int,d_month_seq:int>

(69) FilterExecTransformer
Input [2]: [d_date_sk#6, d_month_seq#38]
Arguments: (((((isnotnull(d_month_seq#38) AND (d_month_seq#38 >= 1212)) AND (d_month_seq#38 <= 1223)) AND (d_date_sk#6 >= 2451911)) AND (d_date_sk#6 <= 2452275)) AND isnotnull(d_date_sk#6))

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

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

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

Subquery:2 Hosting operator id = 18 Hosting Expression = ss_sold_date_sk#16 IN dynamicpruning#5


