Cardinal Health distribution operations need to identify products whose projected end-of-day inventory fell below the minimum stock threshold during the most recent seven calendar days. The inventory snapshot is recorded before that day's outgoing shipments.
Write a PostgreSQL query using the latest inventory_date as the reporting date.
Requirements
- Restrict inventory and outgoing shipments to the latest date and preceding six calendar days.
- Calculate projected stock as
on_hand_quantity - outgoing shipment quantity for each product, distribution center, and day.
- Use a window function to identify the first day each product fell below its threshold.
- Return only products with a threshold breach, including the breach date, minimum projected stock, threshold, and total shipments during the seven-day period.
- Include products with no shipments by treating shipment quantity as zero, and order results by breach date and product ID.
Representative data
warehouse_inventory contains daily snapshots for products P-1001, P-1002, and P-1003. outgoing_shipments contains shipments inside and outside the reporting window, including a shipment with a NULL quantity.
