Shipment Cancellation rate

Introduction: To calculate the shipment cancellation rate for each client or product. This rate reflects the percentage of cancelled shipments in relation to total shipments. The calculation is performed by aggregating the data based on clients and counting both total and cancelled shipments. The cancellation rate is determined as the ratio of cancelled shipments to the total number of shipments.

 

Requirements:

 

Data Processing*: Aggregate data from the shipments table to count both total shipments and cancelled shipments per client. Cancellation flag = ‘Yes’

Cancellation Rate Calculation*: For each client, compute the cancellation rate as the percentage ratio of cancelled shipments to total shipments.

Join with Clients Table*: Merge the cancellation data with the clients table to show the cancellation rate alongside client names.

Display Results*: The output should display the client name, total shipments, cancelled shipments, and the calculated cancellation rate.

 

Unity Catalog details: ‘purgo_playground.shipments’, ‘purgo_playground.clients’.

 

Expected Output: Write Databricks Pyspark code without syntax error