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.
Output details
- Return columns in this order:
product_id, product_name, center_name, breach_date, minimum_projected_stock, minimum_stock_threshold, seven_day_shipments.
- Treat NULL shipment quantities as contributing zero to shipment totals.
- Order by
breach_date ascending, then product_id ascending.
- Do not round projected stock, thresholds, or shipment totals.
