task1 - Details for Query 10

Details for Query 10

Submitted Time: 2024/12/04 20:21:24
Duration: 1.1 min
Succeeded Jobs: 17 18 19 20 21 22

Show the Stage ID and Task ID that corresponds to the max metric

digraph G { 0 [labelType="html" label=" CollectLimit "]; subgraph cluster1 { isCluster="true"; label="WholeStageCodegen (4)\n \nduration: total (min, med, max (stageId: taskId))\n386 ms (1 ms, 2 ms, 15 ms (stage 27.0: task 370))"; 2 [labelType="html" label=" Project "]; 3 [labelType="html" label="Filter number of output rows: 3"]; 4 [labelType="html" label="HashAggregate time in aggregation build total (min, med, max (stageId: taskId)) 73 ms (0 ms, 0 ms, 12 ms (stage 27.0: task 370)) peak memory total (min, med, max (stageId: taskId)) 3.5 GiB (256.0 KiB, 256.0 KiB, 64.3 MiB (stage 29.0: task 374)) number of output rows: 61 avg hash probe bucket list iters (min, med, max (stageId: taskId)): (1, 1, 1 (stage 29.0: task 374))"]; } 5 [labelType="html" label="Exchange shuffle records written: 233 shuffle write time total (min, med, max (stageId: taskId)) 107 ms (3 ms, 5 ms, 8 ms (stage 24.0: task 353)) records read: 233 local bytes read total (min, med, max (stageId: taskId)) 10.8 KiB (0.0 B, 0.0 B, 579.0 B (stage 33.0: task 506)) fetch wait time total (min, med, max (stageId: taskId)) 19 ms (0 ms, 0 ms, 10 ms (stage 27.0: task 370)) remote bytes read total (min, med, max (stageId: taskId)) 10.8 KiB (0.0 B, 0.0 B, 694.0 B (stage 27.0: task 371)) local blocks read: 113 remote blocks read: 112 data size total (min, med, max (stageId: taskId)) 14.1 KiB (432.0 B, 744.0 B, 1224.0 B (stage 24.0: task 367)) shuffle bytes written total (min, med, max (stageId: taskId)) 21.6 KiB (639.0 B, 1122.0 B, 1875.0 B (stage 24.0: task 367))"]; subgraph cluster6 { isCluster="true"; label="WholeStageCodegen (3)\n \nduration: total (min, med, max (stageId: taskId))\n2.1 m (2.2 s, 7.0 s, 7.9 s (stage 24.0: task 350))"; 7 [labelType="html" label="HashAggregate time in aggregation build total (min, med, max (stageId: taskId)) 2.1 m (2.1 s, 7.0 s, 7.8 s (stage 24.0: task 350)) peak memory total (min, med, max (stageId: taskId)) 5.0 MiB (256.0 KiB, 256.0 KiB, 256.0 KiB (stage 24.0: task 349)) number of output rows: 233"]; 8 [labelType="html" label=" Project "]; 9 [labelType="html" label="BroadcastHashJoin number of output rows: 22,400,728"]; 10 [labelType="html" label=" Project "]; 11 [labelType="html" label="BroadcastHashJoin number of output rows: 22,400,728"]; 12 [labelType="html" label=" Project "]; 13 [labelType="html" label="Filter number of output rows: 22,400,728"]; } 14 [labelType="html" label="Scan csv number of files read: 7 metadata time: 0 ms size of files read: 2.2 GiB number of output rows: 22,400,728"]; 15 [labelType="html" label="BroadcastExchange data size: 1026.1 KiB time to collect: 121 ms time to build: 5 ms time to broadcast: 3 ms"]; subgraph cluster16 { isCluster="true"; label="WholeStageCodegen (1)\n \nduration: 38 ms"; 17 [labelType="html" label=" Project "]; 18 [labelType="html" label="Filter number of output rows: 265"]; } 19 [labelType="html" label="Scan csv number of files read: 1 metadata time: 0 ms size of files read: 10.5 KiB number of output rows: 265"]; 2->0; 3->2; 4->3; 5->4; 7->5; 8->7; 9->8; 10->9; 11->10; 12->11; 13->12; 14->13; 15->11; 17->15; 18->17; 19->18; 15->9; }

CollectLimit 11

Project [cast(month#669 as string) AS month#872, route#647, cast(total_tip_amount#823 as string) AS total_tip_amount#874, cast(total_passenger_count#825 as string) AS total_passenger_count#875, cast((total_tip_amount#823 / total_passenger_count#825) as string) AS average_tip_per_passenger#876]

Filter ((isnotnull(total_tip_amount#823) AND isnotnull(total_passenger_count#825)) AND ((total_tip_amount#823 / total_passenger_count#825) = 0.0))

HashAggregate(keys=[month#669, route#647], functions=[sum(cast(tip_amount#50 as double)), sum(cast(passenger_count#42 as double))])

WholeStageCodegen (4)

Exchange hashpartitioning(month#669, route#647, 200), true, [id=#445]

HashAggregate(keys=[month#669, route#647], functions=[partial_sum(cast(tip_amount#50 as double)), partial_sum(cast(passenger_count#42 as double))])

Project [passenger_count#42, tip_amount#50, concat_ws( to , Pickup_Borough#365, Borough#17) AS route#647, month(cast(cast(unix_timestamp(tpep_pickup_datetime#40, yyyy-MM-dd HH:mm:ss, Some(GMT)) as timestamp) as date)) AS month#669]

BroadcastHashJoin [cast(DOLocationID#45 as int)], [LocationID#16], Inner, BuildRight

Project [tpep_pickup_datetime#40, passenger_count#42, DOLocationID#45, tip_amount#50, Borough#17 AS Pickup_Borough#365]

BroadcastHashJoin [cast(DOLocationID#45 as int)], [LocationID#16], Inner, BuildRight

Project [tpep_pickup_datetime#40, passenger_count#42, DOLocationID#45, tip_amount#50]

Filter isnotnull(DOLocationID#45)

WholeStageCodegen (3)

FileScan csv [tpep_pickup_datetime#40,passenger_count#42,DOLocationID#45,tip_amount#50] Batched: false, DataFilters: [isnotnull(DOLocationID#45)], Format: CSV, Location: InMemoryFileIndex[s3a://data-repository-bkt/ECS765/nyc_taxi/yellow_tripdata/2023/yellow_tripdata_..., PartitionFilters: [], PushedFilters: [IsNotNull(DOLocationID)], ReadSchema: struct<tpep_pickup_datetime:string,passenger_count:string,DOLocationID:string,tip_amount:string>

BroadcastExchange HashedRelationBroadcastMode(List(cast(input[0, int, true] as bigint))), [id=#402]

Project [LocationID#16, Borough#17]

Filter isnotnull(LocationID#16)

WholeStageCodegen (1)

FileScan csv [LocationID#16,Borough#17] Batched: false, DataFilters: [isnotnull(LocationID#16)], Format: CSV, Location: InMemoryFileIndex[s3a://data-repository-bkt/ECS765/nyc_taxi/taxi_zone_lookup.csv], PartitionFilters: [], PushedFilters: [IsNotNull(LocationID)], ReadSchema: struct<LocationID:int,Borough:string>

Details

== Parsed Logical Plan ==
GlobalLimit 11
+- LocalLimit 11
   +- Project [cast(month#669 as string) AS month#872, cast(route#647 as string) AS route#873, cast(total_tip_amount#823 as string) AS total_tip_amount#874, cast(total_passenger_count#825 as string) AS total_passenger_count#875, cast(average_tip_per_passenger#830 as string) AS average_tip_per_passenger#876]
      +- Filter (average_tip_per_passenger#830 = cast(0 as double))
         +- Project [month#669, route#647, total_tip_amount#823, total_passenger_count#825, (total_tip_amount#823 / total_passenger_count#825) AS average_tip_per_passenger#830]
            +- Aggregate [month#669, route#647], [month#669, route#647, sum(cast(tip_amount#50 as double)) AS total_tip_amount#823, sum(cast(passenger_count#42 as double)) AS total_passenger_count#825]
               +- Project [tpep_pickup_datetime#40, tpep_dropoff_datetime#41, passenger_count#42, trip_distance#43, payment_type#46, fare_amount#47, extra#48, mta_tax#49, tip_amount#50, tolls_amount#51, total_amount#52, congestion_surcharge#53, airport_fee#54, taxi_type#55, Pickup_Borough#365, Pickup_Zone#385, Pickup_service_zone#406, Dropoff_Borough#512, Dropoff_Zone#536, Dropoff_service_zone#561, route#647, month(to_date('tpep_pickup_datetime, Some(yyyy-MM-dd HH:mm:ss))) AS month#669]
                  +- Project [tpep_pickup_datetime#40, tpep_dropoff_datetime#41, passenger_count#42, trip_distance#43, payment_type#46, fare_amount#47, extra#48, mta_tax#49, tip_amount#50, tolls_amount#51, total_amount#52, congestion_surcharge#53, airport_fee#54, taxi_type#55, Pickup_Borough#365, Pickup_Zone#385, Pickup_service_zone#406, Dropoff_Borough#512, Dropoff_Zone#536, Dropoff_service_zone#561, concat_ws( to , Pickup_Borough#365, Dropoff_Borough#512) AS route#647]
                     +- Project [tpep_pickup_datetime#40, tpep_dropoff_datetime#41, passenger_count#42, trip_distance#43, payment_type#46, fare_amount#47, extra#48, mta_tax#49, tip_amount#50, tolls_amount#51, total_amount#52, congestion_surcharge#53, airport_fee#54, taxi_type#55, Pickup_Borough#365, Pickup_Zone#385, Pickup_service_zone#406, Dropoff_Borough#512, Dropoff_Zone#536, Dropoff_service_zone#561]
                        +- Project [tpep_pickup_datetime#40, tpep_dropoff_datetime#41, passenger_count#42, trip_distance#43, payment_type#46, fare_amount#47, extra#48, mta_tax#49, tip_amount#50, tolls_amount#51, total_amount#52, congestion_surcharge#53, airport_fee#54, taxi_type#55, Pickup_Borough#365, Pickup_Zone#385, Pickup_service_zone#406, Dropoff_Borough#512, Dropoff_Zone#536, Dropoff_service_zone#561]
                           +- Project [tpep_pickup_datetime#40, tpep_dropoff_datetime#41, passenger_count#42, trip_distance#43, payment_type#46, fare_amount#47, extra#48, mta_tax#49, tip_amount#50, tolls_amount#51, total_amount#52, congestion_surcharge#53, airport_fee#54, taxi_type#55, Pickup_Borough#365, Pickup_Zone#385, Pickup_service_zone#406, Dropoff_Borough#512, Dropoff_Zone#536, Dropoff_service_zone#561]
                              +- Project [tpep_pickup_datetime#40, tpep_dropoff_datetime#41, passenger_count#42, trip_distance#43, DOLocationID#45, payment_type#46, fare_amount#47, extra#48, mta_tax#49, tip_amount#50, tolls_amount#51, total_amount#52, congestion_surcharge#53, airport_fee#54, taxi_type#55, Pickup_Borough#365, Pickup_Zone#385, Pickup_service_zone#406, LocationID#16, Borough#17, Zone#18, service_zone#19, Dropoff_Borough#512, Dropoff_Zone#536, service_zone#19 AS Dropoff_service_zone#561]
                                 +- Project [tpep_pickup_datetime#40, tpep_dropoff_datetime#41, passenger_count#42, trip_distance#43, DOLocationID#45, payment_type#46, fare_amount#47, extra#48, mta_tax#49, tip_amount#50, tolls_amount#51, total_amount#52, congestion_surcharge#53, airport_fee#54, taxi_type#55, Pickup_Borough#365, Pickup_Zone#385, Pickup_service_zone#406, LocationID#16, Borough#17, Zone#18, service_zone#19, Dropoff_Borough#512, Zone#18 AS Dropoff_Zone#536]
                                    +- Project [tpep_pickup_datetime#40, tpep_dropoff_datetime#41, passenger_count#42, trip_distance#43, DOLocationID#45, payment_type#46, fare_amount#47, extra#48, mta_tax#49, tip_amount#50, tolls_amount#51, total_amount#52, congestion_surcharge#53, airport_fee#54, taxi_type#55, Pickup_Borough#365, Pickup_Zone#385, Pickup_service_zone#406, LocationID#16, Borough#17, Zone#18, service_zone#19, Borough#17 AS Dropoff_Borough#512]
                                       +- Join Inner, (cast(DOLocationID#45 as int) = LocationID#16)
                                          :- Project [tpep_pickup_datetime#40, tpep_dropoff_datetime#41, passenger_count#42, trip_distance#43, DOLocationID#45, payment_type#46, fare_amount#47, extra#48, mta_tax#49, tip_amount#50, tolls_amount#51, total_amount#52, congestion_surcharge#53, airport_fee#54, taxi_type#55, Pickup_Borough#365, Pickup_Zone#385, Pickup_service_zone#406]
                                          :  +- Project [tpep_pickup_datetime#40, tpep_dropoff_datetime#41, passenger_count#42, trip_distance#43, DOLocationID#45, payment_type#46, fare_amount#47, extra#48, mta_tax#49, tip_amount#50, tolls_amount#51, total_amount#52, congestion_surcharge#53, airport_fee#54, taxi_type#55, Borough#17, Zone#18, service_zone#19, Pickup_Borough#365, Pickup_Zone#385, service_zone#19 AS Pickup_service_zone#406]
                                          :     +- Project [tpep_pickup_datetime#40, tpep_dropoff_datetime#41, passenger_count#42, trip_distance#43, DOLocationID#45, payment_type#46, fare_amount#47, extra#48, mta_tax#49, tip_amount#50, tolls_amount#51, total_amount#52, congestion_surcharge#53, airport_fee#54, taxi_type#55, Borough#17, Zone#18, service_zone#19, Pickup_Borough#365, Zone#18 AS Pickup_Zone#385]
                                          :        +- Project [tpep_pickup_datetime#40, tpep_dropoff_datetime#41, passenger_count#42, trip_distance#43, DOLocationID#45, payment_type#46, fare_amount#47, extra#48, mta_tax#49, tip_amount#50, tolls_amount#51, total_amount#52, congestion_surcharge#53, airport_fee#54, taxi_type#55, Borough#17, Zone#18, service_zone#19, Borough#17 AS Pickup_Borough#365]
                                          :           +- Project [tpep_pickup_datetime#40, tpep_dropoff_datetime#41, passenger_count#42, trip_distance#43, DOLocationID#45, payment_type#46, fare_amount#47, extra#48, mta_tax#49, tip_amount#50, tolls_amount#51, total_amount#52, congestion_surcharge#53, airport_fee#54, taxi_type#55, Borough#17, Zone#18, service_zone#19]
                                          :              +- Project [tpep_pickup_datetime#40, tpep_dropoff_datetime#41, passenger_count#42, trip_distance#43, PULocationID#44, DOLocationID#45, payment_type#46, fare_amount#47, extra#48, mta_tax#49, tip_amount#50, tolls_amount#51, total_amount#52, congestion_surcharge#53, airport_fee#54, taxi_type#55, Borough#17, Zone#18, service_zone#19]
                                          :                 +- Join Inner, (cast(DOLocationID#45 as int) = LocationID#16)
                                          :                    :- Relation[tpep_pickup_datetime#40,tpep_dropoff_datetime#41,passenger_count#42,trip_distance#43,PULocationID#44,DOLocationID#45,payment_type#46,fare_amount#47,extra#48,mta_tax#49,tip_amount#50,tolls_amount#51,total_amount#52,congestion_surcharge#53,airport_fee#54,taxi_type#55] csv
                                          :                    +- Relation[LocationID#16,Borough#17,Zone#18,service_zone#19] csv
                                          +- Relation[LocationID#16,Borough#17,Zone#18,service_zone#19] csv

== Analyzed Logical Plan ==
month: string, route: string, total_tip_amount: string, total_passenger_count: string, average_tip_per_passenger: string
GlobalLimit 11
+- LocalLimit 11
   +- Project [cast(month#669 as string) AS month#872, cast(route#647 as string) AS route#873, cast(total_tip_amount#823 as string) AS total_tip_amount#874, cast(total_passenger_count#825 as string) AS total_passenger_count#875, cast(average_tip_per_passenger#830 as string) AS average_tip_per_passenger#876]
      +- Filter (average_tip_per_passenger#830 = cast(0 as double))
         +- Project [month#669, route#647, total_tip_amount#823, total_passenger_count#825, (total_tip_amount#823 / total_passenger_count#825) AS average_tip_per_passenger#830]
            +- Aggregate [month#669, route#647], [month#669, route#647, sum(cast(tip_amount#50 as double)) AS total_tip_amount#823, sum(cast(passenger_count#42 as double)) AS total_passenger_count#825]
               +- Project [tpep_pickup_datetime#40, tpep_dropoff_datetime#41, passenger_count#42, trip_distance#43, payment_type#46, fare_amount#47, extra#48, mta_tax#49, tip_amount#50, tolls_amount#51, total_amount#52, congestion_surcharge#53, airport_fee#54, taxi_type#55, Pickup_Borough#365, Pickup_Zone#385, Pickup_service_zone#406, Dropoff_Borough#512, Dropoff_Zone#536, Dropoff_service_zone#561, route#647, month(to_date('tpep_pickup_datetime, Some(yyyy-MM-dd HH:mm:ss))) AS month#669]
                  +- Project [tpep_pickup_datetime#40, tpep_dropoff_datetime#41, passenger_count#42, trip_distance#43, payment_type#46, fare_amount#47, extra#48, mta_tax#49, tip_amount#50, tolls_amount#51, total_amount#52, congestion_surcharge#53, airport_fee#54, taxi_type#55, Pickup_Borough#365, Pickup_Zone#385, Pickup_service_zone#406, Dropoff_Borough#512, Dropoff_Zone#536, Dropoff_service_zone#561, concat_ws( to , Pickup_Borough#365, Dropoff_Borough#512) AS route#647]
                     +- Project [tpep_pickup_datetime#40, tpep_dropoff_datetime#41, passenger_count#42, trip_distance#43, payment_type#46, fare_amount#47, extra#48, mta_tax#49, tip_amount#50, tolls_amount#51, total_amount#52, congestion_surcharge#53, airport_fee#54, taxi_type#55, Pickup_Borough#365, Pickup_Zone#385, Pickup_service_zone#406, Dropoff_Borough#512, Dropoff_Zone#536, Dropoff_service_zone#561]
                        +- Project [tpep_pickup_datetime#40, tpep_dropoff_datetime#41, passenger_count#42, trip_distance#43, payment_type#46, fare_amount#47, extra#48, mta_tax#49, tip_amount#50, tolls_amount#51, total_amount#52, congestion_surcharge#53, airport_fee#54, taxi_type#55, Pickup_Borough#365, Pickup_Zone#385, Pickup_service_zone#406, Dropoff_Borough#512, Dropoff_Zone#536, Dropoff_service_zone#561]
                           +- Project [tpep_pickup_datetime#40, tpep_dropoff_datetime#41, passenger_count#42, trip_distance#43, payment_type#46, fare_amount#47, extra#48, mta_tax#49, tip_amount#50, tolls_amount#51, total_amount#52, congestion_surcharge#53, airport_fee#54, taxi_type#55, Pickup_Borough#365, Pickup_Zone#385, Pickup_service_zone#406, Dropoff_Borough#512, Dropoff_Zone#536, Dropoff_service_zone#561]
                              +- Project [tpep_pickup_datetime#40, tpep_dropoff_datetime#41, passenger_count#42, trip_distance#43, DOLocationID#45, payment_type#46, fare_amount#47, extra#48, mta_tax#49, tip_amount#50, tolls_amount#51, total_amount#52, congestion_surcharge#53, airport_fee#54, taxi_type#55, Pickup_Borough#365, Pickup_Zone#385, Pickup_service_zone#406, LocationID#16, Borough#17, Zone#18, service_zone#19, Dropoff_Borough#512, Dropoff_Zone#536, service_zone#19 AS Dropoff_service_zone#561]
                                 +- Project [tpep_pickup_datetime#40, tpep_dropoff_datetime#41, passenger_count#42, trip_distance#43, DOLocationID#45, payment_type#46, fare_amount#47, extra#48, mta_tax#49, tip_amount#50, tolls_amount#51, total_amount#52, congestion_surcharge#53, airport_fee#54, taxi_type#55, Pickup_Borough#365, Pickup_Zone#385, Pickup_service_zone#406, LocationID#16, Borough#17, Zone#18, service_zone#19, Dropoff_Borough#512, Zone#18 AS Dropoff_Zone#536]
                                    +- Project [tpep_pickup_datetime#40, tpep_dropoff_datetime#41, passenger_count#42, trip_distance#43, DOLocationID#45, payment_type#46, fare_amount#47, extra#48, mta_tax#49, tip_amount#50, tolls_amount#51, total_amount#52, congestion_surcharge#53, airport_fee#54, taxi_type#55, Pickup_Borough#365, Pickup_Zone#385, Pickup_service_zone#406, LocationID#16, Borough#17, Zone#18, service_zone#19, Borough#17 AS Dropoff_Borough#512]
                                       +- Join Inner, (cast(DOLocationID#45 as int) = LocationID#16)
                                          :- Project [tpep_pickup_datetime#40, tpep_dropoff_datetime#41, passenger_count#42, trip_distance#43, DOLocationID#45, payment_type#46, fare_amount#47, extra#48, mta_tax#49, tip_amount#50, tolls_amount#51, total_amount#52, congestion_surcharge#53, airport_fee#54, taxi_type#55, Pickup_Borough#365, Pickup_Zone#385, Pickup_service_zone#406]
                                          :  +- Project [tpep_pickup_datetime#40, tpep_dropoff_datetime#41, passenger_count#42, trip_distance#43, DOLocationID#45, payment_type#46, fare_amount#47, extra#48, mta_tax#49, tip_amount#50, tolls_amount#51, total_amount#52, congestion_surcharge#53, airport_fee#54, taxi_type#55, Borough#17, Zone#18, service_zone#19, Pickup_Borough#365, Pickup_Zone#385, service_zone#19 AS Pickup_service_zone#406]
                                          :     +- Project [tpep_pickup_datetime#40, tpep_dropoff_datetime#41, passenger_count#42, trip_distance#43, DOLocationID#45, payment_type#46, fare_amount#47, extra#48, mta_tax#49, tip_amount#50, tolls_amount#51, total_amount#52, congestion_surcharge#53, airport_fee#54, taxi_type#55, Borough#17, Zone#18, service_zone#19, Pickup_Borough#365, Zone#18 AS Pickup_Zone#385]
                                          :        +- Project [tpep_pickup_datetime#40, tpep_dropoff_datetime#41, passenger_count#42, trip_distance#43, DOLocationID#45, payment_type#46, fare_amount#47, extra#48, mta_tax#49, tip_amount#50, tolls_amount#51, total_amount#52, congestion_surcharge#53, airport_fee#54, taxi_type#55, Borough#17, Zone#18, service_zone#19, Borough#17 AS Pickup_Borough#365]
                                          :           +- Project [tpep_pickup_datetime#40, tpep_dropoff_datetime#41, passenger_count#42, trip_distance#43, DOLocationID#45, payment_type#46, fare_amount#47, extra#48, mta_tax#49, tip_amount#50, tolls_amount#51, total_amount#52, congestion_surcharge#53, airport_fee#54, taxi_type#55, Borough#17, Zone#18, service_zone#19]
                                          :              +- Project [tpep_pickup_datetime#40, tpep_dropoff_datetime#41, passenger_count#42, trip_distance#43, PULocationID#44, DOLocationID#45, payment_type#46, fare_amount#47, extra#48, mta_tax#49, tip_amount#50, tolls_amount#51, total_amount#52, congestion_surcharge#53, airport_fee#54, taxi_type#55, Borough#17, Zone#18, service_zone#19]
                                          :                 +- Join Inner, (cast(DOLocationID#45 as int) = LocationID#16)
                                          :                    :- Relation[tpep_pickup_datetime#40,tpep_dropoff_datetime#41,passenger_count#42,trip_distance#43,PULocationID#44,DOLocationID#45,payment_type#46,fare_amount#47,extra#48,mta_tax#49,tip_amount#50,tolls_amount#51,total_amount#52,congestion_surcharge#53,airport_fee#54,taxi_type#55] csv
                                          :                    +- Relation[LocationID#16,Borough#17,Zone#18,service_zone#19] csv
                                          +- Relation[LocationID#16,Borough#17,Zone#18,service_zone#19] csv

== Optimized Logical Plan ==
GlobalLimit 11
+- LocalLimit 11
   +- Project [cast(month#669 as string) AS month#872, route#647, cast(total_tip_amount#823 as string) AS total_tip_amount#874, cast(total_passenger_count#825 as string) AS total_passenger_count#875, cast((total_tip_amount#823 / total_passenger_count#825) as string) AS average_tip_per_passenger#876]
      +- Filter ((isnotnull(total_tip_amount#823) AND isnotnull(total_passenger_count#825)) AND ((total_tip_amount#823 / total_passenger_count#825) = 0.0))
         +- Aggregate [month#669, route#647], [month#669, route#647, sum(cast(tip_amount#50 as double)) AS total_tip_amount#823, sum(cast(passenger_count#42 as double)) AS total_passenger_count#825]
            +- Project [passenger_count#42, tip_amount#50, concat_ws( to , Pickup_Borough#365, Borough#17) AS route#647, month(cast(cast(unix_timestamp(tpep_pickup_datetime#40, yyyy-MM-dd HH:mm:ss, Some(GMT)) as timestamp) as date)) AS month#669]
               +- Join Inner, (cast(DOLocationID#45 as int) = LocationID#16)
                  :- Project [tpep_pickup_datetime#40, passenger_count#42, DOLocationID#45, tip_amount#50, Borough#17 AS Pickup_Borough#365]
                  :  +- Join Inner, (cast(DOLocationID#45 as int) = LocationID#16)
                  :     :- Project [tpep_pickup_datetime#40, passenger_count#42, DOLocationID#45, tip_amount#50]
                  :     :  +- Filter isnotnull(DOLocationID#45)
                  :     :     +- Relation[tpep_pickup_datetime#40,tpep_dropoff_datetime#41,passenger_count#42,trip_distance#43,PULocationID#44,DOLocationID#45,payment_type#46,fare_amount#47,extra#48,mta_tax#49,tip_amount#50,tolls_amount#51,total_amount#52,congestion_surcharge#53,airport_fee#54,taxi_type#55] csv
                  :     +- Project [LocationID#16, Borough#17]
                  :        +- Filter isnotnull(LocationID#16)
                  :           +- Relation[LocationID#16,Borough#17,Zone#18,service_zone#19] csv
                  +- Project [LocationID#16, Borough#17]
                     +- Filter isnotnull(LocationID#16)
                        +- Relation[LocationID#16,Borough#17,Zone#18,service_zone#19] csv

== Physical Plan ==
CollectLimit 11
+- *(4) Project [cast(month#669 as string) AS month#872, route#647, cast(total_tip_amount#823 as string) AS total_tip_amount#874, cast(total_passenger_count#825 as string) AS total_passenger_count#875, cast((total_tip_amount#823 / total_passenger_count#825) as string) AS average_tip_per_passenger#876]
   +- *(4) Filter ((isnotnull(total_tip_amount#823) AND isnotnull(total_passenger_count#825)) AND ((total_tip_amount#823 / total_passenger_count#825) = 0.0))
      +- *(4) HashAggregate(keys=[month#669, route#647], functions=[sum(cast(tip_amount#50 as double)), sum(cast(passenger_count#42 as double))], output=[month#669, route#647, total_tip_amount#823, total_passenger_count#825])
         +- Exchange hashpartitioning(month#669, route#647, 200), true, [id=#445]
            +- *(3) HashAggregate(keys=[month#669, route#647], functions=[partial_sum(cast(tip_amount#50 as double)), partial_sum(cast(passenger_count#42 as double))], output=[month#669, route#647, sum#853, sum#854])
               +- *(3) Project [passenger_count#42, tip_amount#50, concat_ws( to , Pickup_Borough#365, Borough#17) AS route#647, month(cast(cast(unix_timestamp(tpep_pickup_datetime#40, yyyy-MM-dd HH:mm:ss, Some(GMT)) as timestamp) as date)) AS month#669]
                  +- *(3) BroadcastHashJoin [cast(DOLocationID#45 as int)], [LocationID#16], Inner, BuildRight
                     :- *(3) Project [tpep_pickup_datetime#40, passenger_count#42, DOLocationID#45, tip_amount#50, Borough#17 AS Pickup_Borough#365]
                     :  +- *(3) BroadcastHashJoin [cast(DOLocationID#45 as int)], [LocationID#16], Inner, BuildRight
                     :     :- *(3) Project [tpep_pickup_datetime#40, passenger_count#42, DOLocationID#45, tip_amount#50]
                     :     :  +- *(3) Filter isnotnull(DOLocationID#45)
                     :     :     +- FileScan csv [tpep_pickup_datetime#40,passenger_count#42,DOLocationID#45,tip_amount#50] Batched: false, DataFilters: [isnotnull(DOLocationID#45)], Format: CSV, Location: InMemoryFileIndex[s3a://data-repository-bkt/ECS765/nyc_taxi/yellow_tripdata/2023/yellow_tripdata_..., PartitionFilters: [], PushedFilters: [IsNotNull(DOLocationID)], ReadSchema: struct<tpep_pickup_datetime:string,passenger_count:string,DOLocationID:string,tip_amount:string>
                     :     +- BroadcastExchange HashedRelationBroadcastMode(List(cast(input[0, int, true] as bigint))), [id=#402]
                     :        +- *(1) Project [LocationID#16, Borough#17]
                     :           +- *(1) Filter isnotnull(LocationID#16)
                     :              +- FileScan csv [LocationID#16,Borough#17] Batched: false, DataFilters: [isnotnull(LocationID#16)], Format: CSV, Location: InMemoryFileIndex[s3a://data-repository-bkt/ECS765/nyc_taxi/taxi_zone_lookup.csv], PartitionFilters: [], PushedFilters: [IsNotNull(LocationID)], ReadSchema: struct<LocationID:int,Borough:string>
                     +- ReusedExchange [LocationID#16, Borough#17], BroadcastExchange HashedRelationBroadcastMode(List(cast(input[0, int, true] as bigint))), [id=#402]