== Physical Plan ==
VeloxColumnarToRow (74)
+- TakeOrderedAndProjectExecTransformer (73)
   +- ^ ProjectExecTransformer (71)
      +- ^ ShuffledHashJoinExecTransformer Inner BuildRight (70)
         :- ^ InputIteratorTransformer (61)
         :  +- ColumnarExchange (59)
         :     +- VeloxResizeBatches (58)
         :        +- ^ ProjectExecTransformer (56)
         :           +- ^ BroadcastHashJoinExecTransformer Inner BuildRight (55)
         :              :- ^ 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)
         :              +- ^ InputIteratorTransformer (54)
         :                 +- ColumnarBroadcastExchange (52)
         :                    +- ^ FilterExecTransformer (50)
         :                       +- ^ FileSourceScanExecTransformer parquet spark_catalog.default.store (49)
         +- ^ InputIteratorTransformer (69)
            +- ColumnarExchange (67)
               +- VeloxResizeBatches (66)
                  +- ^ ProjectExecTransformer (64)
                     +- ^ FilterExecTransformer (63)
                        +- ^ FileSourceScanExecTransformer parquet spark_catalog.default.item (62)


(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), 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: 79]
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), 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: 79]
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) 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>

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

(51) WholeStageCodegenTransformer (9)
Input [2]: [s_store_sk#31, s_store_name#32]
Arguments: false

(52) ColumnarBroadcastExchange
Input [2]: [s_store_sk#31, s_store_name#32]
Arguments: HashedRelationBroadcastMode(List(cast(input[0, int, false] as bigint)),false), [plan_id=5]

(53) InputAdapter
Input [2]: [s_store_sk#31, s_store_name#32]

(54) InputIteratorTransformer
Input [2]: [s_store_sk#31, s_store_name#32]

(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 [4]: [hash(ss_item_sk#1, 42) AS hash_partition_key#33, 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 [4]: [hash_partition_key#33, ss_item_sk#1, revenue#12, s_store_name#32]
Arguments: false

(58) VeloxResizeBatches
Input [4]: [hash_partition_key#33, ss_item_sk#1, revenue#12, s_store_name#32]
Arguments: 1024, 2147483647, 10485760

(59) ColumnarExchange
Input [4]: [hash_partition_key#33, ss_item_sk#1, revenue#12, s_store_name#32]
Arguments: hashpartitioning(ss_item_sk#1, 1), ENSURE_REQUIREMENTS, [ss_item_sk#1, revenue#12, s_store_name#32], [plan_id=6], [shuffle_writer_type=hash]

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

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

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

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

(64) ProjectExecTransformer
Output [6]: [hash(i_item_sk#34, 42) AS hash_partition_key#39, i_item_sk#34, i_item_desc#35, i_current_price#36, i_wholesale_cost#37, i_brand#38]
Input [5]: [i_item_sk#34, i_item_desc#35, i_current_price#36, i_wholesale_cost#37, i_brand#38]

(65) WholeStageCodegenTransformer (11)
Input [6]: [hash_partition_key#39, i_item_sk#34, i_item_desc#35, i_current_price#36, i_wholesale_cost#37, i_brand#38]
Arguments: false

(66) VeloxResizeBatches
Input [6]: [hash_partition_key#39, i_item_sk#34, i_item_desc#35, i_current_price#36, i_wholesale_cost#37, i_brand#38]
Arguments: 1024, 2147483647, 10485760

(67) ColumnarExchange
Input [6]: [hash_partition_key#39, i_item_sk#34, i_item_desc#35, i_current_price#36, i_wholesale_cost#37, i_brand#38]
Arguments: hashpartitioning(i_item_sk#34, 1), ENSURE_REQUIREMENTS, [i_item_sk#34, i_item_desc#35, i_current_price#36, i_wholesale_cost#37, i_brand#38], [plan_id=7], [shuffle_writer_type=hash]

(68) InputAdapter
Input [5]: [i_item_sk#34, i_item_desc#35, i_current_price#36, i_wholesale_cost#37, i_brand#38]

(69) InputIteratorTransformer
Input [5]: [i_item_sk#34, i_item_desc#35, i_current_price#36, i_wholesale_cost#37, i_brand#38]

(70) ShuffledHashJoinExecTransformer
Left keys [1]: [ss_item_sk#1]
Right keys [1]: [i_item_sk#34]
Join type: Inner
Join condition: None

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

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

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

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

===== Subqueries =====

Subquery:1 Hosting operator id = 1 Hosting Expression = ss_sold_date_sk#4 IN dynamicpruning#5
ColumnarBroadcastExchange (79)
+- ^ ProjectExecTransformer (77)
   +- ^ FilterExecTransformer (76)
      +- ^ FileSourceScanExecTransformer parquet spark_catalog.default.date_dim (75)


(75) FileSourceScanExecTransformer parquet spark_catalog.default.date_dim
Output [2]: [d_date_sk#6, d_month_seq#40]
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>

(76) FilterExecTransformer
Input [2]: [d_date_sk#6, d_month_seq#40]
Arguments: (((isnotnull(d_month_seq#40) AND (d_month_seq#40 >= 1176)) AND (d_month_seq#40 <= 1187)) AND isnotnull(d_date_sk#6))

(77) ProjectExecTransformer
Output [1]: [d_date_sk#6]
Input [2]: [d_date_sk#6, d_month_seq#40]

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

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

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


