You need to generate a report showing the current on-hand stock for each product. Stock changes over time due to:
-
Incoming shipments
(increase stock)
-
Customer orders
(decrease stock)
-
Customer returns
(increase stock)
Assume the following tables:
-
products(product_id, name)
-
inventory_baseline(product_id, baseline_qty)
— starting on-hand quantity (may be missing for some products)
-
incoming_shipments(shipment_id, product_id, qty, received_at)
-
order_items(order_id, product_id, qty, ordered_at)
-
returns(return_id, product_id, qty, returned_at)
Task: Write a SQL query that returns one row per product:
-
product_id
-
name
-
current_stock
Where:
\text{current_stock} = \text{baseline_qty} + \sum(\text{incoming qty}) + \sum(\text{return qty}) - \sum(\text{ordered qty})
Requirements:
-
Include products even if they have no shipments/orders/returns (treat missing quantities as 0).
-
Avoid double-counting due to join multiplication.
-
(Optional) If helpful, also show how you would filter to “as of” a given timestamp.