== Physical Plan ==
VeloxColumnarToRow (66)
+- ^ SortExecTransformer (64)
   +- ^ InputIteratorTransformer (63)
      +- ColumnarExchange (61)
         +- VeloxResizeBatches (60)
            +- ^ ProjectExecTransformer (58)
               +- ^ RegularHashAggregateExecTransformer (57)
                  +- ^ InputIteratorTransformer (56)
                     +- ColumnarExchange (54)
                        +- VeloxResizeBatches (53)
                           +- ^ ProjectExecTransformer (51)
                              +- ^ FlushableHashAggregateExecTransformer (50)
                                 +- ^ ProjectExecTransformer (49)
                                    +- ^ BroadcastHashJoinExecTransformer Inner BuildRight (48)
                                       :- ^ ProjectExecTransformer (41)
                                       :  +- ^ BroadcastHashJoinExecTransformer Inner BuildRight (40)
                                       :     :- ^ ProjectExecTransformer (28)
                                       :     :  +- ^ BroadcastHashJoinExecTransformer Inner BuildRight (27)
                                       :     :     :- ^ ProjectExecTransformer (19)
                                       :     :     :  +- ^ BroadcastHashJoinExecTransformer Inner BuildRight (18)
                                       :     :     :     :- ^ ProjectExecTransformer (10)
                                       :     :     :     :  +- ^ BroadcastHashJoinExecTransformer Inner BuildRight (9)
                                       :     :     :     :     :- ^ FilterExecTransformer (2)
                                       :     :     :     :     :  +- ^ FileSourceScanExecTransformer parquet spark_catalog.default.customer (1)
                                       :     :     :     :     +- ^ InputIteratorTransformer (8)
                                       :     :     :     :        +- ColumnarBroadcastExchange (6)
                                       :     :     :     :           +- ^ FilterExecTransformer (4)
                                       :     :     :     :              +- ^ FileSourceScanExecTransformer parquet spark_catalog.default.customer_demographics (3)
                                       :     :     :     +- ^ InputIteratorTransformer (17)
                                       :     :     :        +- ColumnarBroadcastExchange (15)
                                       :     :     :           +- ^ ProjectExecTransformer (13)
                                       :     :     :              +- ^ FilterExecTransformer (12)
                                       :     :     :                 +- ^ FileSourceScanExecTransformer parquet spark_catalog.default.household_demographics (11)
                                       :     :     +- ^ InputIteratorTransformer (26)
                                       :     :        +- ColumnarBroadcastExchange (24)
                                       :     :           +- ^ ProjectExecTransformer (22)
                                       :     :              +- ^ FilterExecTransformer (21)
                                       :     :                 +- ^ FileSourceScanExecTransformer parquet spark_catalog.default.customer_address (20)
                                       :     +- ^ InputIteratorTransformer (39)
                                       :        +- ColumnarBroadcastExchange (37)
                                       :           +- ^ ProjectExecTransformer (35)
                                       :              +- ^ BroadcastHashJoinExecTransformer Inner BuildRight (34)
                                       :                 :- ^ FilterExecTransformer (30)
                                       :                 :  +- ^ FileSourceScanExecTransformer parquet spark_catalog.default.catalog_returns (29)
                                       :                 +- ^ InputIteratorTransformer (33)
                                       :                    +- ReusedExchange (31)
                                       +- ^ InputIteratorTransformer (47)
                                          +- ColumnarBroadcastExchange (45)
                                             +- ^ FilterExecTransformer (43)
                                                +- ^ FileSourceScanExecTransformer parquet spark_catalog.default.call_center (42)


(1) FileSourceScanExecTransformer parquet spark_catalog.default.customer
Output [4]: [c_customer_sk#1, c_current_cdemo_sk#2, c_current_hdemo_sk#3, c_current_addr_sk#4]
Batched: true
Location: InMemoryFileIndex [{warehouse_dir}/customer]
PushedFilters: [IsNotNull(c_customer_sk), IsNotNull(c_current_addr_sk), IsNotNull(c_current_cdemo_sk), IsNotNull(c_current_hdemo_sk)]
ReadSchema: struct<c_customer_sk:int,c_current_cdemo_sk:int,c_current_hdemo_sk:int,c_current_addr_sk:int>

(2) FilterExecTransformer
Input [4]: [c_customer_sk#1, c_current_cdemo_sk#2, c_current_hdemo_sk#3, c_current_addr_sk#4]
Arguments: (((isnotnull(c_customer_sk#1) AND isnotnull(c_current_addr_sk#4)) AND isnotnull(c_current_cdemo_sk#2)) AND isnotnull(c_current_hdemo_sk#3))

(3) FileSourceScanExecTransformer parquet spark_catalog.default.customer_demographics
Output [3]: [cd_demo_sk#5, cd_marital_status#6, cd_education_status#7]
Batched: true
Location: InMemoryFileIndex [{warehouse_dir}/customer_demographics]
PushedFilters: [Or(And(EqualTo(cd_marital_status,M),EqualTo(cd_education_status,Unknown             )),And(EqualTo(cd_marital_status,W),EqualTo(cd_education_status,Advanced Degree     ))), IsNotNull(cd_demo_sk)]
ReadSchema: struct<cd_demo_sk:int,cd_marital_status:string,cd_education_status:string>

(4) FilterExecTransformer
Input [3]: [cd_demo_sk#5, cd_marital_status#6, cd_education_status#7]
Arguments: ((((cd_marital_status#6 = M) AND (cd_education_status#7 = Unknown             )) OR ((cd_marital_status#6 = W) AND (cd_education_status#7 = Advanced Degree     ))) AND isnotnull(cd_demo_sk#5))

(5) WholeStageCodegenTransformer (1)
Input [3]: [cd_demo_sk#5, cd_marital_status#6, cd_education_status#7]
Arguments: false

(6) ColumnarBroadcastExchange
Input [3]: [cd_demo_sk#5, cd_marital_status#6, cd_education_status#7]
Arguments: HashedRelationBroadcastMode(List(cast(input[0, int, false] as bigint)),false), [plan_id=1]

(7) InputAdapter
Input [3]: [cd_demo_sk#5, cd_marital_status#6, cd_education_status#7]

(8) InputIteratorTransformer
Input [3]: [cd_demo_sk#5, cd_marital_status#6, cd_education_status#7]

(9) BroadcastHashJoinExecTransformer
Left keys [1]: [c_current_cdemo_sk#2]
Right keys [1]: [cd_demo_sk#5]
Join type: Inner
Join condition: None

(10) ProjectExecTransformer
Output [5]: [c_customer_sk#1, c_current_hdemo_sk#3, c_current_addr_sk#4, cd_marital_status#6, cd_education_status#7]
Input [7]: [c_customer_sk#1, c_current_cdemo_sk#2, c_current_hdemo_sk#3, c_current_addr_sk#4, cd_demo_sk#5, cd_marital_status#6, cd_education_status#7]

(11) FileSourceScanExecTransformer parquet spark_catalog.default.household_demographics
Output [2]: [hd_demo_sk#8, hd_buy_potential#9]
Batched: true
Location: InMemoryFileIndex [{warehouse_dir}/household_demographics]
PushedFilters: [IsNotNull(hd_buy_potential), StringStartsWith(hd_buy_potential,Unknown), IsNotNull(hd_demo_sk)]
ReadSchema: struct<hd_demo_sk:int,hd_buy_potential:string>

(12) FilterExecTransformer
Input [2]: [hd_demo_sk#8, hd_buy_potential#9]
Arguments: ((isnotnull(hd_buy_potential#9) AND StartsWith(hd_buy_potential#9, Unknown)) AND isnotnull(hd_demo_sk#8))

(13) ProjectExecTransformer
Output [1]: [hd_demo_sk#8]
Input [2]: [hd_demo_sk#8, hd_buy_potential#9]

(14) WholeStageCodegenTransformer (2)
Input [1]: [hd_demo_sk#8]
Arguments: false

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

(16) InputAdapter
Input [1]: [hd_demo_sk#8]

(17) InputIteratorTransformer
Input [1]: [hd_demo_sk#8]

(18) BroadcastHashJoinExecTransformer
Left keys [1]: [c_current_hdemo_sk#3]
Right keys [1]: [hd_demo_sk#8]
Join type: Inner
Join condition: None

(19) ProjectExecTransformer
Output [4]: [c_customer_sk#1, c_current_addr_sk#4, cd_marital_status#6, cd_education_status#7]
Input [6]: [c_customer_sk#1, c_current_hdemo_sk#3, c_current_addr_sk#4, cd_marital_status#6, cd_education_status#7, hd_demo_sk#8]

(20) FileSourceScanExecTransformer parquet spark_catalog.default.customer_address
Output [2]: [ca_address_sk#10, ca_gmt_offset#11]
Batched: true
Location: InMemoryFileIndex [{warehouse_dir}/customer_address]
PushedFilters: [IsNotNull(ca_gmt_offset), EqualTo(ca_gmt_offset,-7.00), IsNotNull(ca_address_sk)]
ReadSchema: struct<ca_address_sk:int,ca_gmt_offset:decimal(5,2)>

(21) FilterExecTransformer
Input [2]: [ca_address_sk#10, ca_gmt_offset#11]
Arguments: ((isnotnull(ca_gmt_offset#11) AND (ca_gmt_offset#11 = -7.00)) AND isnotnull(ca_address_sk#10))

(22) ProjectExecTransformer
Output [1]: [ca_address_sk#10]
Input [2]: [ca_address_sk#10, ca_gmt_offset#11]

(23) WholeStageCodegenTransformer (3)
Input [1]: [ca_address_sk#10]
Arguments: false

(24) ColumnarBroadcastExchange
Input [1]: [ca_address_sk#10]
Arguments: HashedRelationBroadcastMode(List(cast(input[0, int, true] as bigint)),false), [plan_id=3]

(25) InputAdapter
Input [1]: [ca_address_sk#10]

(26) InputIteratorTransformer
Input [1]: [ca_address_sk#10]

(27) BroadcastHashJoinExecTransformer
Left keys [1]: [c_current_addr_sk#4]
Right keys [1]: [ca_address_sk#10]
Join type: Inner
Join condition: None

(28) ProjectExecTransformer
Output [3]: [c_customer_sk#1, cd_marital_status#6, cd_education_status#7]
Input [5]: [c_customer_sk#1, c_current_addr_sk#4, cd_marital_status#6, cd_education_status#7, ca_address_sk#10]

(29) FileSourceScanExecTransformer parquet spark_catalog.default.catalog_returns
Output [4]: [cr_returning_customer_sk#12, cr_call_center_sk#13, cr_net_loss#14, cr_returned_date_sk#15]
Batched: true
Location: InMemoryFileIndex []
PartitionFilters: [isnotnull(cr_returned_date_sk#15), dynamicpruningexpression(cr_returned_date_sk#15 IN dynamicpruning#16)]
PushedFilters: [IsNotNull(cr_call_center_sk), IsNotNull(cr_returning_customer_sk)]
ReadSchema: struct<cr_returning_customer_sk:int,cr_call_center_sk:int,cr_net_loss:decimal(7,2)>

(30) FilterExecTransformer
Input [4]: [cr_returning_customer_sk#12, cr_call_center_sk#13, cr_net_loss#14, cr_returned_date_sk#15]
Arguments: (isnotnull(cr_call_center_sk#13) AND isnotnull(cr_returning_customer_sk#12))

(31) ReusedExchange [Reuses operator id: 71]
Output [1]: [d_date_sk#17]

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

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

(34) BroadcastHashJoinExecTransformer
Left keys [1]: [cr_returned_date_sk#15]
Right keys [1]: [d_date_sk#17]
Join type: Inner
Join condition: None

(35) ProjectExecTransformer
Output [3]: [cr_returning_customer_sk#12, cr_call_center_sk#13, cr_net_loss#14]
Input [5]: [cr_returning_customer_sk#12, cr_call_center_sk#13, cr_net_loss#14, cr_returned_date_sk#15, d_date_sk#17]

(36) WholeStageCodegenTransformer (6)
Input [3]: [cr_returning_customer_sk#12, cr_call_center_sk#13, cr_net_loss#14]
Arguments: false

(37) ColumnarBroadcastExchange
Input [3]: [cr_returning_customer_sk#12, cr_call_center_sk#13, cr_net_loss#14]
Arguments: HashedRelationBroadcastMode(List(cast(input[0, int, true] as bigint)),false), [plan_id=4]

(38) InputAdapter
Input [3]: [cr_returning_customer_sk#12, cr_call_center_sk#13, cr_net_loss#14]

(39) InputIteratorTransformer
Input [3]: [cr_returning_customer_sk#12, cr_call_center_sk#13, cr_net_loss#14]

(40) BroadcastHashJoinExecTransformer
Left keys [1]: [c_customer_sk#1]
Right keys [1]: [cr_returning_customer_sk#12]
Join type: Inner
Join condition: None

(41) ProjectExecTransformer
Output [4]: [cd_marital_status#6, cd_education_status#7, cr_call_center_sk#13, cr_net_loss#14]
Input [6]: [c_customer_sk#1, cd_marital_status#6, cd_education_status#7, cr_returning_customer_sk#12, cr_call_center_sk#13, cr_net_loss#14]

(42) FileSourceScanExecTransformer parquet spark_catalog.default.call_center
Output [4]: [cc_call_center_sk#18, cc_call_center_id#19, cc_name#20, cc_manager#21]
Batched: true
Location: InMemoryFileIndex [{warehouse_dir}/call_center]
PushedFilters: [IsNotNull(cc_call_center_sk)]
ReadSchema: struct<cc_call_center_sk:int,cc_call_center_id:string,cc_name:string,cc_manager:string>

(43) FilterExecTransformer
Input [4]: [cc_call_center_sk#18, cc_call_center_id#19, cc_name#20, cc_manager#21]
Arguments: isnotnull(cc_call_center_sk#18)

(44) WholeStageCodegenTransformer (7)
Input [4]: [cc_call_center_sk#18, cc_call_center_id#19, cc_name#20, cc_manager#21]
Arguments: false

(45) ColumnarBroadcastExchange
Input [4]: [cc_call_center_sk#18, cc_call_center_id#19, cc_name#20, cc_manager#21]
Arguments: HashedRelationBroadcastMode(List(cast(input[0, int, false] as bigint)),false), [plan_id=5]

(46) InputAdapter
Input [4]: [cc_call_center_sk#18, cc_call_center_id#19, cc_name#20, cc_manager#21]

(47) InputIteratorTransformer
Input [4]: [cc_call_center_sk#18, cc_call_center_id#19, cc_name#20, cc_manager#21]

(48) BroadcastHashJoinExecTransformer
Left keys [1]: [cr_call_center_sk#13]
Right keys [1]: [cc_call_center_sk#18]
Join type: Inner
Join condition: None

(49) ProjectExecTransformer
Output [6]: [cc_call_center_id#19, cc_name#20, cc_manager#21, cd_marital_status#6, cd_education_status#7, UnscaledValue(cr_net_loss#14) AS _pre_1#22]
Input [8]: [cd_marital_status#6, cd_education_status#7, cr_call_center_sk#13, cr_net_loss#14, cc_call_center_sk#18, cc_call_center_id#19, cc_name#20, cc_manager#21]

(50) FlushableHashAggregateExecTransformer
Input [6]: [cc_call_center_id#19, cc_name#20, cc_manager#21, cd_marital_status#6, cd_education_status#7, _pre_1#22]
Keys [5]: [cc_call_center_id#19, cc_name#20, cc_manager#21, cd_marital_status#6, cd_education_status#7]
Functions [1]: [partial_sum(_pre_1#22)]
Aggregate Attributes [1]: [sum#23]
Results [6]: [cc_call_center_id#19, cc_name#20, cc_manager#21, cd_marital_status#6, cd_education_status#7, sum#24]

(51) ProjectExecTransformer
Output [7]: [hash(cc_call_center_id#19, cc_name#20, cc_manager#21, cd_marital_status#6, cd_education_status#7, 42) AS hash_partition_key#25, cc_call_center_id#19, cc_name#20, cc_manager#21, cd_marital_status#6, cd_education_status#7, sum#24]
Input [6]: [cc_call_center_id#19, cc_name#20, cc_manager#21, cd_marital_status#6, cd_education_status#7, sum#24]

(52) WholeStageCodegenTransformer (8)
Input [7]: [hash_partition_key#25, cc_call_center_id#19, cc_name#20, cc_manager#21, cd_marital_status#6, cd_education_status#7, sum#24]
Arguments: false

(53) VeloxResizeBatches
Input [7]: [hash_partition_key#25, cc_call_center_id#19, cc_name#20, cc_manager#21, cd_marital_status#6, cd_education_status#7, sum#24]
Arguments: 1024, 2147483647, 10485760

(54) ColumnarExchange
Input [7]: [hash_partition_key#25, cc_call_center_id#19, cc_name#20, cc_manager#21, cd_marital_status#6, cd_education_status#7, sum#24]
Arguments: hashpartitioning(cc_call_center_id#19, cc_name#20, cc_manager#21, cd_marital_status#6, cd_education_status#7, 1), ENSURE_REQUIREMENTS, [cc_call_center_id#19, cc_name#20, cc_manager#21, cd_marital_status#6, cd_education_status#7, sum#24], [plan_id=6], [shuffle_writer_type=hash]

(55) InputAdapter
Input [6]: [cc_call_center_id#19, cc_name#20, cc_manager#21, cd_marital_status#6, cd_education_status#7, sum#24]

(56) InputIteratorTransformer
Input [6]: [cc_call_center_id#19, cc_name#20, cc_manager#21, cd_marital_status#6, cd_education_status#7, sum#24]

(57) RegularHashAggregateExecTransformer
Input [6]: [cc_call_center_id#19, cc_name#20, cc_manager#21, cd_marital_status#6, cd_education_status#7, sum#24]
Keys [5]: [cc_call_center_id#19, cc_name#20, cc_manager#21, cd_marital_status#6, cd_education_status#7]
Functions [1]: [sum(UnscaledValue(cr_net_loss#14))]
Aggregate Attributes [1]: [sum(UnscaledValue(cr_net_loss#14))#26]
Results [6]: [cc_call_center_id#19, cc_name#20, cc_manager#21, cd_marital_status#6, cd_education_status#7, sum(UnscaledValue(cr_net_loss#14))#26]

(58) ProjectExecTransformer
Output [4]: [cc_call_center_id#19 AS Call_Center#27, cc_name#20 AS Call_Center_Name#28, cc_manager#21 AS Manager#29, MakeDecimal(sum(UnscaledValue(cr_net_loss#14))#26,17,2) AS Returns_Loss#30]
Input [6]: [cc_call_center_id#19, cc_name#20, cc_manager#21, cd_marital_status#6, cd_education_status#7, sum(UnscaledValue(cr_net_loss#14))#26]

(59) WholeStageCodegenTransformer (9)
Input [4]: [Call_Center#27, Call_Center_Name#28, Manager#29, Returns_Loss#30]
Arguments: false

(60) VeloxResizeBatches
Input [4]: [Call_Center#27, Call_Center_Name#28, Manager#29, Returns_Loss#30]
Arguments: 1024, 2147483647, 10485760

(61) ColumnarExchange
Input [4]: [Call_Center#27, Call_Center_Name#28, Manager#29, Returns_Loss#30]
Arguments: rangepartitioning(Returns_Loss#30 DESC NULLS LAST, 1), ENSURE_REQUIREMENTS, [plan_id=7], [shuffle_writer_type=hash]

(62) InputAdapter
Input [4]: [Call_Center#27, Call_Center_Name#28, Manager#29, Returns_Loss#30]

(63) InputIteratorTransformer
Input [4]: [Call_Center#27, Call_Center_Name#28, Manager#29, Returns_Loss#30]

(64) SortExecTransformer
Input [4]: [Call_Center#27, Call_Center_Name#28, Manager#29, Returns_Loss#30]
Arguments: [Returns_Loss#30 DESC NULLS LAST], true, 0

(65) WholeStageCodegenTransformer (10)
Input [4]: [Call_Center#27, Call_Center_Name#28, Manager#29, Returns_Loss#30]
Arguments: false

(66) VeloxColumnarToRow
Input [4]: [Call_Center#27, Call_Center_Name#28, Manager#29, Returns_Loss#30]

===== Subqueries =====

Subquery:1 Hosting operator id = 29 Hosting Expression = cr_returned_date_sk#15 IN dynamicpruning#16
ColumnarBroadcastExchange (71)
+- ^ ProjectExecTransformer (69)
   +- ^ FilterExecTransformer (68)
      +- ^ FileSourceScanExecTransformer parquet spark_catalog.default.date_dim (67)


(67) FileSourceScanExecTransformer parquet spark_catalog.default.date_dim
Output [3]: [d_date_sk#17, d_year#31, d_moy#32]
Batched: true
Location: InMemoryFileIndex [{warehouse_dir}/date_dim]
PushedFilters: [IsNotNull(d_year), IsNotNull(d_moy), EqualTo(d_year,1998), EqualTo(d_moy,11), IsNotNull(d_date_sk)]
ReadSchema: struct<d_date_sk:int,d_year:int,d_moy:int>

(68) FilterExecTransformer
Input [3]: [d_date_sk#17, d_year#31, d_moy#32]
Arguments: ((((isnotnull(d_year#31) AND isnotnull(d_moy#32)) AND (d_year#31 = 1998)) AND (d_moy#32 = 11)) AND isnotnull(d_date_sk#17))

(69) ProjectExecTransformer
Output [1]: [d_date_sk#17]
Input [3]: [d_date_sk#17, d_year#31, d_moy#32]

(70) WholeStageCodegenTransformer (4)
Input [1]: [d_date_sk#17]
Arguments: false

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


