== Physical Plan ==
VeloxColumnarToRow (69)
+- TakeOrderedAndProjectExecTransformer (68)
   +- ^ ProjectExecTransformer (66)
      +- ^ BroadcastHashJoinExecTransformer Inner BuildRight (65)
         :- ^ ProjectExecTransformer (58)
         :  +- ^ BroadcastHashJoinExecTransformer Inner BuildRight (57)
         :     :- ^ ProjectExecTransformer (49)
         :     :  +- ^ BroadcastHashJoinExecTransformer Inner BuildRight (48)
         :     :     :- ^ 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_returns (1)
         :     :     :                             +- ^ InputIteratorTransformer (5)
         :     :     :                                +- ReusedExchange (3)
         :     :     +- ^ InputIteratorTransformer (47)
         :     :        +- ColumnarBroadcastExchange (45)
         :     :           +- ^ FilterExecTransformer (43)
         :     :              +- ^ ProjectExecTransformer (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_returns (18)
         :     :                                                              +- ^ InputIteratorTransformer (22)
         :     :                                                                 +- ReusedExchange (20)
         :     +- ^ InputIteratorTransformer (56)
         :        +- ColumnarBroadcastExchange (54)
         :           +- ^ ProjectExecTransformer (52)
         :              +- ^ FilterExecTransformer (51)
         :                 +- ^ FileSourceScanExecTransformer parquet spark_catalog.default.store (50)
         +- ^ InputIteratorTransformer (64)
            +- ColumnarBroadcastExchange (62)
               +- ^ FilterExecTransformer (60)
                  +- ^ FileSourceScanExecTransformer parquet spark_catalog.default.customer (59)


(1) FileSourceScanExecTransformer parquet spark_catalog.default.store_returns
Output [4]: [sr_customer_sk#1, sr_store_sk#2, sr_return_amt#3, sr_returned_date_sk#4]
Batched: true
Location: InMemoryFileIndex []
PartitionFilters: [isnotnull(sr_returned_date_sk#4), dynamicpruningexpression(sr_returned_date_sk#4 IN dynamicpruning#5)]
PushedFilters: [IsNotNull(sr_store_sk), IsNotNull(sr_customer_sk)]
ReadSchema: struct<sr_customer_sk:int,sr_store_sk:int,sr_return_amt:decimal(7,2)>

(2) FilterExecTransformer
Input [4]: [sr_customer_sk#1, sr_store_sk#2, sr_return_amt#3, sr_returned_date_sk#4]
Arguments: (isnotnull(sr_store_sk#2) AND isnotnull(sr_customer_sk#1))

(3) ReusedExchange [Reuses operator id: 74]
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]: [sr_returned_date_sk#4]
Right keys [1]: [d_date_sk#6]
Join type: Inner
Join condition: None

(7) ProjectExecTransformer
Output [3]: [sr_customer_sk#1, sr_store_sk#2, UnscaledValue(sr_return_amt#3) AS _pre_1#7]
Input [5]: [sr_customer_sk#1, sr_store_sk#2, sr_return_amt#3, sr_returned_date_sk#4, d_date_sk#6]

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

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

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

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

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

(13) InputAdapter
Input [3]: [sr_customer_sk#1, sr_store_sk#2, sum#9]

(14) InputIteratorTransformer
Input [3]: [sr_customer_sk#1, sr_store_sk#2, sum#9]

(15) RegularHashAggregateExecTransformer
Input [3]: [sr_customer_sk#1, sr_store_sk#2, sum#9]
Keys [2]: [sr_customer_sk#1, sr_store_sk#2]
Functions [1]: [sum(UnscaledValue(sr_return_amt#3))]
Aggregate Attributes [1]: [sum(UnscaledValue(sr_return_amt#3))#11]
Results [3]: [sr_customer_sk#1, sr_store_sk#2, sum(UnscaledValue(sr_return_amt#3))#11]

(16) ProjectExecTransformer
Output [3]: [sr_customer_sk#1 AS ctr_customer_sk#12, sr_store_sk#2 AS ctr_store_sk#13, MakeDecimal(sum(UnscaledValue(sr_return_amt#3))#11,17,2) AS ctr_total_return#14]
Input [3]: [sr_customer_sk#1, sr_store_sk#2, sum(UnscaledValue(sr_return_amt#3))#11]

(17) FilterExecTransformer
Input [3]: [ctr_customer_sk#12, ctr_store_sk#13, ctr_total_return#14]
Arguments: isnotnull(ctr_total_return#14)

(18) FileSourceScanExecTransformer parquet spark_catalog.default.store_returns
Output [4]: [sr_customer_sk#15, sr_store_sk#16, sr_return_amt#17, sr_returned_date_sk#18]
Batched: true
Location: InMemoryFileIndex []
PartitionFilters: [isnotnull(sr_returned_date_sk#18), dynamicpruningexpression(sr_returned_date_sk#18 IN dynamicpruning#5)]
PushedFilters: [IsNotNull(sr_store_sk)]
ReadSchema: struct<sr_customer_sk:int,sr_store_sk:int,sr_return_amt:decimal(7,2)>

(19) FilterExecTransformer
Input [4]: [sr_customer_sk#15, sr_store_sk#16, sr_return_amt#17, sr_returned_date_sk#18]
Arguments: isnotnull(sr_store_sk#16)

(20) ReusedExchange [Reuses operator id: 74]
Output [1]: [d_date_sk#19]

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

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

(23) BroadcastHashJoinExecTransformer
Left keys [1]: [sr_returned_date_sk#18]
Right keys [1]: [d_date_sk#19]
Join type: Inner
Join condition: None

(24) ProjectExecTransformer
Output [3]: [sr_customer_sk#15, sr_store_sk#16, UnscaledValue(sr_return_amt#17) AS _pre_2#20]
Input [5]: [sr_customer_sk#15, sr_store_sk#16, sr_return_amt#17, sr_returned_date_sk#18, d_date_sk#19]

(25) FlushableHashAggregateExecTransformer
Input [3]: [sr_customer_sk#15, sr_store_sk#16, _pre_2#20]
Keys [2]: [sr_customer_sk#15, sr_store_sk#16]
Functions [1]: [partial_sum(_pre_2#20)]
Aggregate Attributes [1]: [sum#21]
Results [3]: [sr_customer_sk#15, sr_store_sk#16, sum#22]

(26) ProjectExecTransformer
Output [4]: [hash(sr_customer_sk#15, sr_store_sk#16, 42) AS hash_partition_key#23, sr_customer_sk#15, sr_store_sk#16, sum#22]
Input [3]: [sr_customer_sk#15, sr_store_sk#16, sum#22]

(27) WholeStageCodegenTransformer (6)
Input [4]: [hash_partition_key#23, sr_customer_sk#15, sr_store_sk#16, sum#22]
Arguments: false

(28) VeloxResizeBatches
Input [4]: [hash_partition_key#23, sr_customer_sk#15, sr_store_sk#16, sum#22]
Arguments: 1024, 2147483647, 10485760

(29) ColumnarExchange
Input [4]: [hash_partition_key#23, sr_customer_sk#15, sr_store_sk#16, sum#22]
Arguments: hashpartitioning(sr_customer_sk#15, sr_store_sk#16, 1), ENSURE_REQUIREMENTS, [sr_customer_sk#15, sr_store_sk#16, sum#22], [plan_id=2], [shuffle_writer_type=hash]

(30) InputAdapter
Input [3]: [sr_customer_sk#15, sr_store_sk#16, sum#22]

(31) InputIteratorTransformer
Input [3]: [sr_customer_sk#15, sr_store_sk#16, sum#22]

(32) RegularHashAggregateExecTransformer
Input [3]: [sr_customer_sk#15, sr_store_sk#16, sum#22]
Keys [2]: [sr_customer_sk#15, sr_store_sk#16]
Functions [1]: [sum(UnscaledValue(sr_return_amt#17))]
Aggregate Attributes [1]: [sum(UnscaledValue(sr_return_amt#17))#11]
Results [3]: [sr_customer_sk#15, sr_store_sk#16, sum(UnscaledValue(sr_return_amt#17))#11]

(33) ProjectExecTransformer
Output [2]: [sr_store_sk#16 AS ctr_store_sk#24, MakeDecimal(sum(UnscaledValue(sr_return_amt#17))#11,17,2) AS ctr_total_return#25]
Input [3]: [sr_customer_sk#15, sr_store_sk#16, sum(UnscaledValue(sr_return_amt#17))#11]

(34) FlushableHashAggregateExecTransformer
Input [2]: [ctr_store_sk#24, ctr_total_return#25]
Keys [1]: [ctr_store_sk#24]
Functions [1]: [partial_avg(ctr_total_return#25)]
Aggregate Attributes [2]: [sum#26, count#27]
Results [3]: [ctr_store_sk#24, sum#28, count#29]

(35) ProjectExecTransformer
Output [4]: [hash(ctr_store_sk#24, 42) AS hash_partition_key#30, ctr_store_sk#24, sum#28, count#29]
Input [3]: [ctr_store_sk#24, sum#28, count#29]

(36) WholeStageCodegenTransformer (7)
Input [4]: [hash_partition_key#30, ctr_store_sk#24, sum#28, count#29]
Arguments: false

(37) VeloxResizeBatches
Input [4]: [hash_partition_key#30, ctr_store_sk#24, sum#28, count#29]
Arguments: 1024, 2147483647, 10485760

(38) ColumnarExchange
Input [4]: [hash_partition_key#30, ctr_store_sk#24, sum#28, count#29]
Arguments: hashpartitioning(ctr_store_sk#24, 1), ENSURE_REQUIREMENTS, [ctr_store_sk#24, sum#28, count#29], [plan_id=3], [shuffle_writer_type=hash]

(39) InputAdapter
Input [3]: [ctr_store_sk#24, sum#28, count#29]

(40) InputIteratorTransformer
Input [3]: [ctr_store_sk#24, sum#28, count#29]

(41) RegularHashAggregateExecTransformer
Input [3]: [ctr_store_sk#24, sum#28, count#29]
Keys [1]: [ctr_store_sk#24]
Functions [1]: [avg(ctr_total_return#25)]
Aggregate Attributes [1]: [avg(ctr_total_return#25)#31]
Results [2]: [ctr_store_sk#24, avg(ctr_total_return#25)#31]

(42) ProjectExecTransformer
Output [2]: [(avg(ctr_total_return#25)#31 * 1.2) AS (avg(ctr_total_return) * 1.2)#32, ctr_store_sk#24]
Input [2]: [ctr_store_sk#24, avg(ctr_total_return#25)#31]

(43) FilterExecTransformer
Input [2]: [(avg(ctr_total_return) * 1.2)#32, ctr_store_sk#24]
Arguments: isnotnull((avg(ctr_total_return) * 1.2)#32)

(44) WholeStageCodegenTransformer (8)
Input [2]: [(avg(ctr_total_return) * 1.2)#32, ctr_store_sk#24]
Arguments: false

(45) ColumnarBroadcastExchange
Input [2]: [(avg(ctr_total_return) * 1.2)#32, ctr_store_sk#24]
Arguments: HashedRelationBroadcastMode(List(cast(input[1, int, true] as bigint)),false), [plan_id=4]

(46) InputAdapter
Input [2]: [(avg(ctr_total_return) * 1.2)#32, ctr_store_sk#24]

(47) InputIteratorTransformer
Input [2]: [(avg(ctr_total_return) * 1.2)#32, ctr_store_sk#24]

(48) BroadcastHashJoinExecTransformer
Left keys [1]: [ctr_store_sk#13]
Right keys [1]: [ctr_store_sk#24]
Join type: Inner
Join condition: (cast(ctr_total_return#14 as decimal(24,7)) > (avg(ctr_total_return) * 1.2)#32)

(49) ProjectExecTransformer
Output [2]: [ctr_customer_sk#12, ctr_store_sk#13]
Input [5]: [ctr_customer_sk#12, ctr_store_sk#13, ctr_total_return#14, (avg(ctr_total_return) * 1.2)#32, ctr_store_sk#24]

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

(51) FilterExecTransformer
Input [2]: [s_store_sk#33, s_state#34]
Arguments: ((isnotnull(s_state#34) AND (s_state#34 = TN)) AND isnotnull(s_store_sk#33))

(52) ProjectExecTransformer
Output [1]: [s_store_sk#33]
Input [2]: [s_store_sk#33, s_state#34]

(53) WholeStageCodegenTransformer (9)
Input [1]: [s_store_sk#33]
Arguments: false

(54) ColumnarBroadcastExchange
Input [1]: [s_store_sk#33]
Arguments: HashedRelationBroadcastMode(List(cast(input[0, int, true] as bigint)),false), [plan_id=5]

(55) InputAdapter
Input [1]: [s_store_sk#33]

(56) InputIteratorTransformer
Input [1]: [s_store_sk#33]

(57) BroadcastHashJoinExecTransformer
Left keys [1]: [ctr_store_sk#13]
Right keys [1]: [s_store_sk#33]
Join type: Inner
Join condition: None

(58) ProjectExecTransformer
Output [1]: [ctr_customer_sk#12]
Input [3]: [ctr_customer_sk#12, ctr_store_sk#13, s_store_sk#33]

(59) FileSourceScanExecTransformer parquet spark_catalog.default.customer
Output [2]: [c_customer_sk#35, c_customer_id#36]
Batched: true
Location: InMemoryFileIndex [{warehouse_dir}/customer]
PushedFilters: [IsNotNull(c_customer_sk)]
ReadSchema: struct<c_customer_sk:int,c_customer_id:string>

(60) FilterExecTransformer
Input [2]: [c_customer_sk#35, c_customer_id#36]
Arguments: isnotnull(c_customer_sk#35)

(61) WholeStageCodegenTransformer (10)
Input [2]: [c_customer_sk#35, c_customer_id#36]
Arguments: false

(62) ColumnarBroadcastExchange
Input [2]: [c_customer_sk#35, c_customer_id#36]
Arguments: HashedRelationBroadcastMode(List(cast(input[0, int, false] as bigint)),false), [plan_id=6]

(63) InputAdapter
Input [2]: [c_customer_sk#35, c_customer_id#36]

(64) InputIteratorTransformer
Input [2]: [c_customer_sk#35, c_customer_id#36]

(65) BroadcastHashJoinExecTransformer
Left keys [1]: [ctr_customer_sk#12]
Right keys [1]: [c_customer_sk#35]
Join type: Inner
Join condition: None

(66) ProjectExecTransformer
Output [1]: [c_customer_id#36]
Input [3]: [ctr_customer_sk#12, c_customer_sk#35, c_customer_id#36]

(67) WholeStageCodegenTransformer (11)
Input [1]: [c_customer_id#36]
Arguments: false

(68) TakeOrderedAndProjectExecTransformer
Input [1]: [c_customer_id#36]
Arguments: 100, [c_customer_id#36 ASC NULLS FIRST], [c_customer_id#36], 0

(69) VeloxColumnarToRow
Input [1]: [c_customer_id#36]

===== Subqueries =====

Subquery:1 Hosting operator id = 1 Hosting Expression = sr_returned_date_sk#4 IN dynamicpruning#5
ColumnarBroadcastExchange (74)
+- ^ ProjectExecTransformer (72)
   +- ^ FilterExecTransformer (71)
      +- ^ FileSourceScanExecTransformer parquet spark_catalog.default.date_dim (70)


(70) FileSourceScanExecTransformer parquet spark_catalog.default.date_dim
Output [2]: [d_date_sk#6, d_year#37]
Batched: true
Location: InMemoryFileIndex [{warehouse_dir}/date_dim]
PushedFilters: [IsNotNull(d_year), EqualTo(d_year,2000), IsNotNull(d_date_sk)]
ReadSchema: struct<d_date_sk:int,d_year:int>

(71) FilterExecTransformer
Input [2]: [d_date_sk#6, d_year#37]
Arguments: ((isnotnull(d_year#37) AND (d_year#37 = 2000)) AND isnotnull(d_date_sk#6))

(72) ProjectExecTransformer
Output [1]: [d_date_sk#6]
Input [2]: [d_date_sk#6, d_year#37]

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

(74) 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 = sr_returned_date_sk#18 IN dynamicpruning#5


