Product Replenishment Priority Analysis

WITH sales_data AS (SELECT oi.product_id, SUM(oi.quantity) AS total_sold, SUM(oi.price * oi.quantity) AS revenue FROM order_items oi GROUP BY oi.product_id) SELECT p.product_name, p.category, w.location AS warehouse_location, p.stock_quantity AS current_stock, COALESCE(ROUND(sd.total_sold / 8.0, 2), 0) AS avg_daily_sales, CASE WHEN COALESCE(sd.total_sold, 0) > 0 THEN ROUND(p.stock_quantity * 1.0 / (sd.total_sold / 8.0), 1) ELSE 999 END AS days_remaining, COALESCE(ROUND(sd.revenue, 2), 0) AS potential_revenue_loss, ROW_NUMBER() OVER (ORDER BY p.stock_quantity ASC, COALESCE(sd.total_sold, 0) DESC, p.product_id ASC) AS restock_rank FROM products p INNER JOIN warehouses w ON p.warehouse_id = w.warehouse_id LEFT JOIN sales_data sd ON p.product_id = sd.product_id ORDER BY restock_rank LIMIT 5;

Why this works

The CTE produces one sales row per product before joining inventory. The outer join retains products with no sales, while COALESCE supplies display-safe zeroes. ROW_NUMBER uses stock, sales, and product ID for a deterministic priority order. The 8.0 divisor represents the fixture's stated eight-day observation window.

Success check

Top 5 products starting with Python Programming Book (rank 1, 150 stock, 1200 days remaining)