Unsold Batches Analysis and Financial Impact Assessment
Requirement
Introduction: To analyze the unsold batch IDs in a warehouse with respect to their expiry dates, analyze batch-wise inventory, and assess the financial impact on batches nearing expiry in 90 days and 180 days.
Requirements:
Read the table 'purgo_playground.supply_chain_delivery', 'purgo_playground.sales_info'.
calculate days to expiry for each batch with current date.
Determine the Remaining Stock by calculating batch sales inventory by:
Assessing stock levels* from the supply_chain table.
Performing a join operation* with the sales_info table.
Subtracting sale_quantity from the available stock* to compute the updated stock levels.
calculate the remaining financial impact by multiplying remaining stock with unit cost.
Filter the batches with days to expire less than or equal to 90days and greater than 90 to 180 days respectively.
final output: Show the results with batches, warehouse, expiry date, days to expiry with Financial impact of 90 days to expiry and 180 days to expiry.
Write the pyspark code without syntax error.
Unity Catalog Details: 'purgo_playground.supply_chain_delivery', 'purgo_playground.sales_info'