Data explorer

Which products are below their reorder point in inventory?

1. [ok] sample_data ProductInventory 5 rows
2. [ok] submit_query WITH inv AS (SELECT ProductID, SUM(Quantity) AS TotalQty FRO… 1 row · 6 ms

   WITH inv AS (SELECT ProductID, SUM(Quantity) AS TotalQty FROM ProductInventory GROUP BY ProductID)
   SELECT COUNT(*) AS n_products,
     SUM(CASE WHEN COALESCE(inv.TotalQty,0) < p.ReorderPoint THEN 1 ELSE 0 END) AS n_below_reorder,
     SUM(CASE WHEN inv.ProductID IS NULL THEN 1 ELSE 0 END) AS n_without_inventory_rows,
     SUM(CASE WHEN inv.ProductID IS NULL AND 0 < p.ReorderPoint THEN 1 ELSE 0 END) AS n_no_stock_below_rp
   FROM Product p LEFT JOIN inv ON inv.ProductID = p.ProductID;

3. [ok] submit_query WITH inv AS (SELECT ProductID, SUM(Quantity) AS TotalQty FRO… 79 rows · 3 ms

   WITH inv AS (SELECT ProductID, SUM(Quantity) AS TotalQty FROM ProductInventory GROUP BY ProductID)
   SELECT p.ProductID, p.Name, p.ProductNumber,
          COALESCE(inv.TotalQty,0) AS TotalQtyOnHand,
          p.ReorderPoint,
          p.ReorderPoint - COALESCE(inv.TotalQty,0) AS Shortfall
   FROM Product p LEFT JOIN inv ON inv.ProductID = p.ProductID
   WHERE COALESCE(inv.TotalQty,0) < p.ReorderPoint
   ORDER BY Shortfall DESC, p.ProductID;