== Physical Plan ==
VeloxColumnarToRow (33)
+- ^ RegularHashAggregateExecTransformer (31)
   +- ^ ProjectExecTransformer (30)
      +- ^ ShuffledHashJoinExecTransformer FullOuter BuildRight (29)
         :- ^ RegularHashAggregateExecTransformer (14)
         :  +- ^ InputIteratorTransformer (13)
         :     +- ColumnarExchange (11)
         :        +- VeloxResizeBatches (10)
         :           +- ^ ProjectExecTransformer (8)
         :              +- ^ FlushableHashAggregateExecTransformer (7)
         :                 +- ^ ProjectExecTransformer (6)
         :                    +- ^ BroadcastHashJoinExecTransformer Inner BuildRight (5)
         :                       :- ^ FileSourceScanExecTransformer parquet spark_catalog.default.store_sales (1)
         :                       +- ^ InputIteratorTransformer (4)
         :                          +- ReusedExchange (2)
         +- ^ RegularHashAggregateExecTransformer (28)
            +- ^ InputIteratorTransformer (27)
               +- ColumnarExchange (25)
                  +- VeloxResizeBatches (24)
                     +- ^ ProjectExecTransformer (22)
                        +- ^ FlushableHashAggregateExecTransformer (21)
                           +- ^ ProjectExecTransformer (20)
                              +- ^ BroadcastHashJoinExecTransformer Inner BuildRight (19)
                                 :- ^ FileSourceScanExecTransformer parquet spark_catalog.default.catalog_sales (15)
                                 +- ^ InputIteratorTransformer (18)
                                    +- ReusedExchange (16)


(1) FileSourceScanExecTransformer parquet spark_catalog.default.store_sales
Output [3]: [ss_item_sk#1, ss_customer_sk#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)]
ReadSchema: struct<ss_item_sk:int,ss_customer_sk:int>

(2) ReusedExchange [Reuses operator id: 38]
Output [1]: [d_date_sk#5]

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

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

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

(6) ProjectExecTransformer
Output [2]: [ss_item_sk#1, ss_customer_sk#2]
Input [4]: [ss_item_sk#1, ss_customer_sk#2, ss_sold_date_sk#3, d_date_sk#5]

(7) FlushableHashAggregateExecTransformer
Input [2]: [ss_item_sk#1, ss_customer_sk#2]
Keys [2]: [ss_customer_sk#2, ss_item_sk#1]
Functions: []
Aggregate Attributes: []
Results [2]: [ss_customer_sk#2, ss_item_sk#1]

(8) ProjectExecTransformer
Output [3]: [hash(ss_customer_sk#2, ss_item_sk#1, 42) AS hash_partition_key#6, ss_customer_sk#2, ss_item_sk#1]
Input [2]: [ss_customer_sk#2, ss_item_sk#1]

(9) WholeStageCodegenTransformer (3)
Input [3]: [hash_partition_key#6, ss_customer_sk#2, ss_item_sk#1]
Arguments: false

(10) VeloxResizeBatches
Input [3]: [hash_partition_key#6, ss_customer_sk#2, ss_item_sk#1]
Arguments: 1024, 2147483647, 10485760

(11) ColumnarExchange
Input [3]: [hash_partition_key#6, ss_customer_sk#2, ss_item_sk#1]
Arguments: hashpartitioning(ss_customer_sk#2, ss_item_sk#1, 1), ENSURE_REQUIREMENTS, [ss_customer_sk#2, ss_item_sk#1], [plan_id=1], [shuffle_writer_type=hash]

(12) InputAdapter
Input [2]: [ss_customer_sk#2, ss_item_sk#1]

(13) InputIteratorTransformer
Input [2]: [ss_customer_sk#2, ss_item_sk#1]

(14) RegularHashAggregateExecTransformer
Input [2]: [ss_customer_sk#2, ss_item_sk#1]
Keys [2]: [ss_customer_sk#2, ss_item_sk#1]
Functions: []
Aggregate Attributes: []
Results [2]: [ss_customer_sk#2 AS customer_sk#7, ss_item_sk#1 AS item_sk#8]

(15) FileSourceScanExecTransformer parquet spark_catalog.default.catalog_sales
Output [3]: [cs_bill_customer_sk#9, cs_item_sk#10, cs_sold_date_sk#11]
Batched: true
Location: InMemoryFileIndex []
PartitionFilters: [isnotnull(cs_sold_date_sk#11), dynamicpruningexpression(cs_sold_date_sk#11 IN dynamicpruning#4)]
ReadSchema: struct<cs_bill_customer_sk:int,cs_item_sk:int>

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

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

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

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

(20) ProjectExecTransformer
Output [2]: [cs_bill_customer_sk#9, cs_item_sk#10]
Input [4]: [cs_bill_customer_sk#9, cs_item_sk#10, cs_sold_date_sk#11, d_date_sk#12]

(21) FlushableHashAggregateExecTransformer
Input [2]: [cs_bill_customer_sk#9, cs_item_sk#10]
Keys [2]: [cs_bill_customer_sk#9, cs_item_sk#10]
Functions: []
Aggregate Attributes: []
Results [2]: [cs_bill_customer_sk#9, cs_item_sk#10]

(22) ProjectExecTransformer
Output [3]: [hash(cs_bill_customer_sk#9, cs_item_sk#10, 42) AS hash_partition_key#13, cs_bill_customer_sk#9, cs_item_sk#10]
Input [2]: [cs_bill_customer_sk#9, cs_item_sk#10]

(23) WholeStageCodegenTransformer (6)
Input [3]: [hash_partition_key#13, cs_bill_customer_sk#9, cs_item_sk#10]
Arguments: false

(24) VeloxResizeBatches
Input [3]: [hash_partition_key#13, cs_bill_customer_sk#9, cs_item_sk#10]
Arguments: 1024, 2147483647, 10485760

(25) ColumnarExchange
Input [3]: [hash_partition_key#13, cs_bill_customer_sk#9, cs_item_sk#10]
Arguments: hashpartitioning(cs_bill_customer_sk#9, cs_item_sk#10, 1), ENSURE_REQUIREMENTS, [cs_bill_customer_sk#9, cs_item_sk#10], [plan_id=2], [shuffle_writer_type=hash]

(26) InputAdapter
Input [2]: [cs_bill_customer_sk#9, cs_item_sk#10]

(27) InputIteratorTransformer
Input [2]: [cs_bill_customer_sk#9, cs_item_sk#10]

(28) RegularHashAggregateExecTransformer
Input [2]: [cs_bill_customer_sk#9, cs_item_sk#10]
Keys [2]: [cs_bill_customer_sk#9, cs_item_sk#10]
Functions: []
Aggregate Attributes: []
Results [2]: [cs_bill_customer_sk#9 AS customer_sk#14, cs_item_sk#10 AS item_sk#15]

(29) ShuffledHashJoinExecTransformer
Left keys [2]: [customer_sk#7, item_sk#8]
Right keys [2]: [customer_sk#14, item_sk#15]
Join type: FullOuter
Join condition: None

(30) ProjectExecTransformer
Output [3]: [CASE WHEN (isnotnull(customer_sk#7) AND isnull(customer_sk#14)) THEN 1 ELSE 0 END AS _pre_1#16, CASE WHEN (isnull(customer_sk#7) AND isnotnull(customer_sk#14)) THEN 1 ELSE 0 END AS _pre_2#17, CASE WHEN (isnotnull(customer_sk#7) AND isnotnull(customer_sk#14)) THEN 1 ELSE 0 END AS _pre_3#18]
Input [4]: [customer_sk#7, item_sk#8, customer_sk#14, item_sk#15]

(31) RegularHashAggregateExecTransformer
Input [3]: [_pre_1#16, _pre_2#17, _pre_3#18]
Keys: []
Functions [3]: [sum(_pre_1#16), sum(_pre_2#17), sum(_pre_3#18)]
Aggregate Attributes [3]: [sum(CASE WHEN (isnotnull(customer_sk#7) AND isnull(customer_sk#14)) THEN 1 ELSE 0 END)#19, sum(CASE WHEN (isnull(customer_sk#7) AND isnotnull(customer_sk#14)) THEN 1 ELSE 0 END)#20, sum(CASE WHEN (isnotnull(customer_sk#7) AND isnotnull(customer_sk#14)) THEN 1 ELSE 0 END)#21]
Results [3]: [sum(CASE WHEN (isnotnull(customer_sk#7) AND isnull(customer_sk#14)) THEN 1 ELSE 0 END)#19 AS store_only#22, sum(CASE WHEN (isnull(customer_sk#7) AND isnotnull(customer_sk#14)) THEN 1 ELSE 0 END)#20 AS catalog_only#23, sum(CASE WHEN (isnotnull(customer_sk#7) AND isnotnull(customer_sk#14)) THEN 1 ELSE 0 END)#21 AS store_and_catalog#24]

(32) WholeStageCodegenTransformer (7)
Input [3]: [store_only#22, catalog_only#23, store_and_catalog#24]
Arguments: false

(33) VeloxColumnarToRow
Input [3]: [store_only#22, catalog_only#23, store_and_catalog#24]

===== Subqueries =====

Subquery:1 Hosting operator id = 1 Hosting Expression = ss_sold_date_sk#3 IN dynamicpruning#4
ColumnarBroadcastExchange (38)
+- ^ ProjectExecTransformer (36)
   +- ^ FilterExecTransformer (35)
      +- ^ FileSourceScanExecTransformer parquet spark_catalog.default.date_dim (34)


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

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

(36) ProjectExecTransformer
Output [1]: [d_date_sk#5]
Input [2]: [d_date_sk#5, d_month_seq#25]

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

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

Subquery:2 Hosting operator id = 15 Hosting Expression = cs_sold_date_sk#11 IN dynamicpruning#4


