Total shipment cancellation percentage by monthwise on client basis

Introduction: To calculate month-wise total and cancelled shipments, along with the cancellation percentage of shipments with client name.

 

Requirements:

 

* Filter: Extracting Year and Month from shipment date column.

* Data Processing: Group the shipments data by client_id, year, and month to calculate: Aggregate data from the shipments table to count both total shipments and cancelled shipments per client. Cancellation flag = ‘Yes’

* Join the aggregated data with the clients table on client_id to include the client name in the output.

 

Expected Output: Select and display the relevant columns: client_name, year, month, monthwise total_shipments, monthwise cancelled_shipments, and monthwise cancellation_percentage.

 

Unity Catalog details(table): ‘purgo_playground.shipments’, ‘purgo_playground.clients’ Tables.

 

Expected Output: Write Databricks Pyspark code without syntax error