Investigate Inventory Below Threshold

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

1. Restrict inventory and outgoing shipments to the latest date and preceding six calendar days.
2. Calculate projected stock as on_hand_quantity - outgoing shipment quantity for each product, distribution center, and day.
3. Use a window function to identify the first day each product fell below its threshold.
4. Return only products with a threshold breach, including the breach date, minimum projected stock, threshold, and total shipments during the seven-day period.
5. 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.