== Physical Plan ==
VeloxColumnarToRow (72)
+- TakeOrderedAndProjectExecTransformer (71)
   +- ^ ProjectExecTransformer (69)
      +- ^ WindowExecTransformer (68)
         +- ^ SortExecTransformer (67)
            +- ^ InputIteratorTransformer (66)
               +- ColumnarExchange (64)
                  +- VeloxResizeBatches (63)
                     +- ^ ProjectExecTransformer (61)
                        +- ^ ProjectExecTransformer (60)
                           +- ^ RegularHashAggregateExecTransformer (59)
                              +- ^ InputIteratorTransformer (58)
                                 +- ColumnarExchange (56)
                                    +- VeloxResizeBatches (55)
                                       +- ^ ProjectExecTransformer (53)
                                          +- ^ FlushableHashAggregateExecTransformer (52)
                                             +- ^ ProjectExecTransformer (51)
                                                +- ^ ExpandExecTransformer (50)
                                                   +- ^ ProjectExecTransformer (49)
                                                      +- ^ BroadcastHashJoinExecTransformer Inner BuildRight (48)
                                                         :- ^ ProjectExecTransformer (7)
                                                         :  +- ^ BroadcastHashJoinExecTransformer Inner BuildRight (6)
                                                         :     :- ^ FilterExecTransformer (2)
                                                         :     :  +- ^ FileSourceScanExecTransformer parquet spark_catalog.default.store_sales (1)
                                                         :     +- ^ InputIteratorTransformer (5)
                                                         :        +- ReusedExchange (3)
                                                         +- ^ InputIteratorTransformer (47)
                                                            +- ColumnarBroadcastExchange (45)
                                                               +- ^ BroadcastHashJoinExecTransformer LeftSemi BuildRight (43)
                                                                  :- ^ FilterExecTransformer (9)
                                                                  :  +- ^ FileSourceScanExecTransformer parquet spark_catalog.default.store (8)
                                                                  +- ^ InputIteratorTransformer (42)
                                                                     +- ColumnarBroadcastExchange (40)
                                                                        +- ^ ProjectExecTransformer (38)
                                                                           +- ^ FilterExecTransformer (37)
                                                                              +- ^ WindowExecTransformer (36)
                                                                                 +- ^ SortExecTransformer (35)
                                                                                    +- ^ WindowGroupLimitExecTransformer (34)
                                                                                       +- ^ ProjectExecTransformer (33)
                                                                                          +- ^ RegularHashAggregateExecTransformer (32)
                                                                                             +- ^ InputIteratorTransformer (31)
                                                                                                +- ColumnarExchange (29)
                                                                                                   +- VeloxResizeBatches (28)
                                                                                                      +- ^ ProjectExecTransformer (26)
                                                                                                         +- ^ FlushableHashAggregateExecTransformer (25)
                                                                                                            +- ^ ProjectExecTransformer (24)
                                                                                                               +- ^ BroadcastHashJoinExecTransformer Inner BuildRight (23)
                                                                                                                  :- ^ ProjectExecTransformer (16)
                                                                                                                  :  +- ^ BroadcastHashJoinExecTransformer Inner BuildRight (15)
                                                                                                                  :     :- ^ FilterExecTransformer (11)
                                                                                                                  :     :  +- ^ FileSourceScanExecTransformer parquet spark_catalog.default.store_sales (10)
                                                                                                                  :     +- ^ InputIteratorTransformer (14)
                                                                                                                  :        +- ReusedExchange (12)
                                                                                                                  +- ^ InputIteratorTransformer (22)
                                                                                                                     +- ColumnarBroadcastExchange (20)
                                                                                                                        +- ^ FilterExecTransformer (18)
                                                                                                                           +- ^ FileSourceScanExecTransformer parquet spark_catalog.default.store (17)


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

(2) FilterExecTransformer
Input [3]: [ss_store_sk#1, ss_net_profit#2, ss_sold_date_sk#3]
Arguments: isnotnull(ss_store_sk#1)

(3) ReusedExchange [Reuses operator id: 77]
Output [1]: [d_date_sk#5]

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

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

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

(7) ProjectExecTransformer
Output [2]: [ss_store_sk#1, ss_net_profit#2]
Input [4]: [ss_store_sk#1, ss_net_profit#2, ss_sold_date_sk#3, d_date_sk#5]

(8) FileSourceScanExecTransformer parquet spark_catalog.default.store
Output [3]: [s_store_sk#6, s_county#7, s_state#8]
Batched: true
Location: InMemoryFileIndex [{warehouse_dir}/store]
PushedFilters: [IsNotNull(s_store_sk)]
ReadSchema: struct<s_store_sk:int,s_county:string,s_state:string>

(9) FilterExecTransformer
Input [3]: [s_store_sk#6, s_county#7, s_state#8]
Arguments: isnotnull(s_store_sk#6)

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

(11) FilterExecTransformer
Input [3]: [ss_store_sk#9, ss_net_profit#10, ss_sold_date_sk#11]
Arguments: isnotnull(ss_store_sk#9)

(12) ReusedExchange [Reuses operator id: 77]
Output [1]: [d_date_sk#12]

(13) InputAdapter
Input [1]: [d_date_sk#12]

(14) InputIteratorTransformer
Input [1]: [d_date_sk#12]

(15) BroadcastHashJoinExecTransformer
Left keys [1]: [ss_sold_date_sk#11]
Right keys [1]: [d_date_sk#12]
Join type: Inner
Join condition: None

(16) ProjectExecTransformer
Output [2]: [ss_store_sk#9, ss_net_profit#10]
Input [4]: [ss_store_sk#9, ss_net_profit#10, ss_sold_date_sk#11, d_date_sk#12]

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

(18) FilterExecTransformer
Input [2]: [s_store_sk#13, s_state#14]
Arguments: isnotnull(s_store_sk#13)

(19) WholeStageCodegenTransformer (5)
Input [2]: [s_store_sk#13, s_state#14]
Arguments: false

(20) ColumnarBroadcastExchange
Input [2]: [s_store_sk#13, s_state#14]
Arguments: HashedRelationBroadcastMode(List(cast(input[0, int, false] as bigint)),false), [plan_id=1]

(21) InputAdapter
Input [2]: [s_store_sk#13, s_state#14]

(22) InputIteratorTransformer
Input [2]: [s_store_sk#13, s_state#14]

(23) BroadcastHashJoinExecTransformer
Left keys [1]: [ss_store_sk#9]
Right keys [1]: [s_store_sk#13]
Join type: Inner
Join condition: None

(24) ProjectExecTransformer
Output [2]: [s_state#14, UnscaledValue(ss_net_profit#10) AS _pre_1#15]
Input [4]: [ss_store_sk#9, ss_net_profit#10, s_store_sk#13, s_state#14]

(25) FlushableHashAggregateExecTransformer
Input [2]: [s_state#14, _pre_1#15]
Keys [1]: [s_state#14]
Functions [1]: [partial_sum(_pre_1#15)]
Aggregate Attributes [1]: [sum#16]
Results [2]: [s_state#14, sum#17]

(26) ProjectExecTransformer
Output [3]: [hash(s_state#14, 42) AS hash_partition_key#18, s_state#14, sum#17]
Input [2]: [s_state#14, sum#17]

(27) WholeStageCodegenTransformer (6)
Input [3]: [hash_partition_key#18, s_state#14, sum#17]
Arguments: false

(28) VeloxResizeBatches
Input [3]: [hash_partition_key#18, s_state#14, sum#17]
Arguments: 1024, 2147483647, 10485760

(29) ColumnarExchange
Input [3]: [hash_partition_key#18, s_state#14, sum#17]
Arguments: hashpartitioning(s_state#14, 1), ENSURE_REQUIREMENTS, [s_state#14, sum#17], [plan_id=2], [shuffle_writer_type=hash]

(30) InputAdapter
Input [2]: [s_state#14, sum#17]

(31) InputIteratorTransformer
Input [2]: [s_state#14, sum#17]

(32) RegularHashAggregateExecTransformer
Input [2]: [s_state#14, sum#17]
Keys [1]: [s_state#14]
Functions [1]: [sum(UnscaledValue(ss_net_profit#10))]
Aggregate Attributes [1]: [sum(UnscaledValue(ss_net_profit#10))#19]
Results [2]: [s_state#14, sum(UnscaledValue(ss_net_profit#10))#19]

(33) ProjectExecTransformer
Output [3]: [s_state#14, MakeDecimal(sum(UnscaledValue(ss_net_profit#10))#19,17,2) AS _w0#20, s_state#14]
Input [2]: [s_state#14, sum(UnscaledValue(ss_net_profit#10))#19]

(34) WindowGroupLimitExecTransformer
Input [3]: [s_state#14, _w0#20, s_state#14]
Arguments: [s_state#14], [_w0#20 DESC NULLS LAST], rank(_w0#20), 5, GlutenFinal

(35) SortExecTransformer
Input [3]: [s_state#14, _w0#20, s_state#14]
Arguments: [s_state#14 ASC NULLS FIRST, _w0#20 DESC NULLS LAST], false, 0

(36) WindowExecTransformer
Input [3]: [s_state#14, _w0#20, s_state#14]
Arguments: [rank(_w0#20) windowspecdefinition(s_state#14, _w0#20 DESC NULLS LAST, specifiedwindowframe(RowFrame, unboundedpreceding$(), currentrow$())) AS ranking#21], [s_state#14], [_w0#20 DESC NULLS LAST]

(37) FilterExecTransformer
Input [4]: [s_state#14, _w0#20, s_state#14, ranking#21]
Arguments: (ranking#21 <= 5)

(38) ProjectExecTransformer
Output [1]: [s_state#14]
Input [4]: [s_state#14, _w0#20, s_state#14, ranking#21]

(39) WholeStageCodegenTransformer (7)
Input [1]: [s_state#14]
Arguments: false

(40) ColumnarBroadcastExchange
Input [1]: [s_state#14]
Arguments: HashedRelationBroadcastMode(List(input[0, string, true]),false), [plan_id=3]

(41) InputAdapter
Input [1]: [s_state#14]

(42) InputIteratorTransformer
Input [1]: [s_state#14]

(43) BroadcastHashJoinExecTransformer
Left keys [1]: [s_state#8]
Right keys [1]: [s_state#14]
Join type: LeftSemi
Join condition: None

(44) WholeStageCodegenTransformer (8)
Input [3]: [s_store_sk#6, s_county#7, s_state#8]
Arguments: false

(45) ColumnarBroadcastExchange
Input [3]: [s_store_sk#6, s_county#7, s_state#8]
Arguments: HashedRelationBroadcastMode(List(cast(input[0, int, false] as bigint)),false), [plan_id=4]

(46) InputAdapter
Input [3]: [s_store_sk#6, s_county#7, s_state#8]

(47) InputIteratorTransformer
Input [3]: [s_store_sk#6, s_county#7, s_state#8]

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

(49) ProjectExecTransformer
Output [3]: [ss_net_profit#2, s_state#8, s_county#7]
Input [5]: [ss_store_sk#1, ss_net_profit#2, s_store_sk#6, s_county#7, s_state#8]

(50) ExpandExecTransformer
Input [3]: [ss_net_profit#2, s_state#8, s_county#7]
Arguments: [[ss_net_profit#2, s_state#8, s_county#7, 0], [ss_net_profit#2, s_state#8, null, 1], [ss_net_profit#2, null, null, 3]], [ss_net_profit#2, s_state#22, s_county#23, spark_grouping_id#24]

(51) ProjectExecTransformer
Output [4]: [s_state#22, s_county#23, spark_grouping_id#24, UnscaledValue(ss_net_profit#2) AS _pre_2#25]
Input [4]: [ss_net_profit#2, s_state#22, s_county#23, spark_grouping_id#24]

(52) FlushableHashAggregateExecTransformer
Input [4]: [s_state#22, s_county#23, spark_grouping_id#24, _pre_2#25]
Keys [3]: [s_state#22, s_county#23, spark_grouping_id#24]
Functions [1]: [partial_sum(_pre_2#25)]
Aggregate Attributes [1]: [sum#26]
Results [4]: [s_state#22, s_county#23, spark_grouping_id#24, sum#27]

(53) ProjectExecTransformer
Output [5]: [hash(s_state#22, s_county#23, spark_grouping_id#24, 42) AS hash_partition_key#28, s_state#22, s_county#23, spark_grouping_id#24, sum#27]
Input [4]: [s_state#22, s_county#23, spark_grouping_id#24, sum#27]

(54) WholeStageCodegenTransformer (9)
Input [5]: [hash_partition_key#28, s_state#22, s_county#23, spark_grouping_id#24, sum#27]
Arguments: false

(55) VeloxResizeBatches
Input [5]: [hash_partition_key#28, s_state#22, s_county#23, spark_grouping_id#24, sum#27]
Arguments: 1024, 2147483647, 10485760

(56) ColumnarExchange
Input [5]: [hash_partition_key#28, s_state#22, s_county#23, spark_grouping_id#24, sum#27]
Arguments: hashpartitioning(s_state#22, s_county#23, spark_grouping_id#24, 1), ENSURE_REQUIREMENTS, [s_state#22, s_county#23, spark_grouping_id#24, sum#27], [plan_id=5], [shuffle_writer_type=hash]

(57) InputAdapter
Input [4]: [s_state#22, s_county#23, spark_grouping_id#24, sum#27]

(58) InputIteratorTransformer
Input [4]: [s_state#22, s_county#23, spark_grouping_id#24, sum#27]

(59) RegularHashAggregateExecTransformer
Input [4]: [s_state#22, s_county#23, spark_grouping_id#24, sum#27]
Keys [3]: [s_state#22, s_county#23, spark_grouping_id#24]
Functions [1]: [sum(UnscaledValue(ss_net_profit#2))]
Aggregate Attributes [1]: [sum(UnscaledValue(ss_net_profit#2))#29]
Results [4]: [s_state#22, s_county#23, spark_grouping_id#24, sum(UnscaledValue(ss_net_profit#2))#29]

(60) ProjectExecTransformer
Output [7]: [MakeDecimal(sum(UnscaledValue(ss_net_profit#2))#29,17,2) AS total_sum#30, s_state#22, s_county#23, (cast((shiftright(spark_grouping_id#24, 1) & 1) as tinyint) + cast((shiftright(spark_grouping_id#24, 0) & 1) as tinyint)) AS lochierarchy#31, MakeDecimal(sum(UnscaledValue(ss_net_profit#2))#29,17,2) AS _w0#32, (cast((shiftright(spark_grouping_id#24, 1) & 1) as tinyint) + cast((shiftright(spark_grouping_id#24, 0) & 1) as tinyint)) AS _w1#33, CASE WHEN (cast((shiftright(spark_grouping_id#24, 0) & 1) as tinyint) = 0) THEN s_state#22 END AS _w2#34]
Input [4]: [s_state#22, s_county#23, spark_grouping_id#24, sum(UnscaledValue(ss_net_profit#2))#29]

(61) ProjectExecTransformer
Output [8]: [hash(_w1#33, _w2#34, 42) AS hash_partition_key#35, total_sum#30, s_state#22, s_county#23, lochierarchy#31, _w0#32, _w1#33, _w2#34]
Input [7]: [total_sum#30, s_state#22, s_county#23, lochierarchy#31, _w0#32, _w1#33, _w2#34]

(62) WholeStageCodegenTransformer (10)
Input [8]: [hash_partition_key#35, total_sum#30, s_state#22, s_county#23, lochierarchy#31, _w0#32, _w1#33, _w2#34]
Arguments: false

(63) VeloxResizeBatches
Input [8]: [hash_partition_key#35, total_sum#30, s_state#22, s_county#23, lochierarchy#31, _w0#32, _w1#33, _w2#34]
Arguments: 1024, 2147483647, 10485760

(64) ColumnarExchange
Input [8]: [hash_partition_key#35, total_sum#30, s_state#22, s_county#23, lochierarchy#31, _w0#32, _w1#33, _w2#34]
Arguments: hashpartitioning(_w1#33, _w2#34, 1), ENSURE_REQUIREMENTS, [total_sum#30, s_state#22, s_county#23, lochierarchy#31, _w0#32, _w1#33, _w2#34], [plan_id=6], [shuffle_writer_type=hash]

(65) InputAdapter
Input [7]: [total_sum#30, s_state#22, s_county#23, lochierarchy#31, _w0#32, _w1#33, _w2#34]

(66) InputIteratorTransformer
Input [7]: [total_sum#30, s_state#22, s_county#23, lochierarchy#31, _w0#32, _w1#33, _w2#34]

(67) SortExecTransformer
Input [7]: [total_sum#30, s_state#22, s_county#23, lochierarchy#31, _w0#32, _w1#33, _w2#34]
Arguments: [_w1#33 ASC NULLS FIRST, _w2#34 ASC NULLS FIRST, _w0#32 DESC NULLS LAST], false, 0

(68) WindowExecTransformer
Input [7]: [total_sum#30, s_state#22, s_county#23, lochierarchy#31, _w0#32, _w1#33, _w2#34]
Arguments: [rank(_w0#32) windowspecdefinition(_w1#33, _w2#34, _w0#32 DESC NULLS LAST, specifiedwindowframe(RowFrame, unboundedpreceding$(), currentrow$())) AS rank_within_parent#36], [_w1#33, _w2#34], [_w0#32 DESC NULLS LAST]

(69) ProjectExecTransformer
Output [6]: [total_sum#30, s_state#22, s_county#23, lochierarchy#31, rank_within_parent#36, CASE WHEN (lochierarchy#31 = 0) THEN s_state#22 END AS _pre_3#37]
Input [8]: [total_sum#30, s_state#22, s_county#23, lochierarchy#31, _w0#32, _w1#33, _w2#34, rank_within_parent#36]

(70) WholeStageCodegenTransformer (11)
Input [6]: [total_sum#30, s_state#22, s_county#23, lochierarchy#31, rank_within_parent#36, _pre_3#37]
Arguments: false

(71) TakeOrderedAndProjectExecTransformer
Input [6]: [total_sum#30, s_state#22, s_county#23, lochierarchy#31, rank_within_parent#36, _pre_3#37]
Arguments: 100, [lochierarchy#31 DESC NULLS LAST, _pre_3#37 ASC NULLS FIRST, rank_within_parent#36 ASC NULLS FIRST], [total_sum#30, s_state#22, s_county#23, lochierarchy#31, rank_within_parent#36], 0

(72) VeloxColumnarToRow
Input [5]: [total_sum#30, s_state#22, s_county#23, lochierarchy#31, rank_within_parent#36]

===== Subqueries =====

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


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

(74) FilterExecTransformer
Input [2]: [d_date_sk#5, d_month_seq#38]
Arguments: (((isnotnull(d_month_seq#38) AND (d_month_seq#38 >= 1200)) AND (d_month_seq#38 <= 1211)) AND isnotnull(d_date_sk#5))

(75) ProjectExecTransformer
Output [1]: [d_date_sk#5]
Input [2]: [d_date_sk#5, d_month_seq#38]

(76) WholeStageCodegenTransformer (1)
Input [1]: [d_date_sk#5]
Arguments: false

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

Subquery:2 Hosting operator id = 10 Hosting Expression = ss_sold_date_sk#11 IN dynamicpruning#4


