Capstone Workflows

Inventory Reorder

Aggregate Stock Movement

Inventory reports often aggregate movement rows, compare stock to policy, and produce a focused action list.

Program

Play the script to choose an extra safety buffer and see which products need reorder attention.

inventory_reorder_capstone.sql 
CREATE TABLE products (id INTEGER, name TEXT, reorder_level INTEGER);
INSERT INTO products VALUES (1, 'pens', 20), (2, 'markers', 15), (3, 'notebooks', 5);
CREATE TABLE stock_movements (product_id INTEGER, quantity INTEGER);
INSERT INTO stock_movements VALUES (1, 25), (1, -10), (2, 8), (3, 10), (3, -2);
WITH params(extra_buffer) AS (VALUES ()), stock AS (SELECT product_id, SUM(quantity) AS on_hand FROM stock_movements GROUP BY product_id), reorder AS (SELECT products.name, stock.on_hand, products.reorder_level + (SELECT extra_buffer FROM params) AS trigger_level FROM products JOIN stock ON stock.product_id = products.id) SELECT name, on_hand, trigger_level FROM reorder WHERE on_hand <= trigger_level ORDER BY name;

CREATE TABLE products (id INTEGER, name TEXT, reorder_level INTEGER);
INSERT INTO products VALUES (1, 'pens', 20), (2, 'markers', 15), (3, 'notebooks', 5);
CREATE TABLE stock_movements (product_id INTEGER, quantity INTEGER);
INSERT INTO stock_movements VALUES (1, 25), (1, -10), (2, 8), (3, 10), (3, -2);
WITH params(extra_buffer) AS (VALUES ()), stock AS (SELECT product_id, SUM(quantity) AS on_hand FROM stock_movements GROUP BY product_id), reorder AS (SELECT products.name, stock.on_hand, products.reorder_level + (SELECT extra_buffer FROM params) AS trigger_level FROM products JOIN stock ON stock.product_id = products.id) SELECT name, on_hand, trigger_level FROM reorder WHERE on_hand <= trigger_level ORDER BY name;
stock CTE `stock` collapses movement rows into one on-hand value per product.
policy comparison `reorder` compares on-hand stock to the reorder level plus buffer.
action list The final query returns only products at or below the trigger level.