Katkat
Back

Write SQL to compute current product stock

Reinforce LabsSoftware Engineer · Online Assessment
MediumSoftware EngineerOnline AssessmentSoftware Engineering Fundamentals

Last updated

This problem tests the ability to calculate current stock levels by aggregating historical transaction events against an initial inventory baseline using SQL joins and window functions.

Problem Statement

Given two tables, inventory_baseline and inventory_transactions, calculate the current stock level for every product.

Tables

inventory_baseline (Initial state as of the start of the year):

  • product_id: INT
  • stock_count: INT

inventory_transactions (All movements since the start of the year):

  • transaction_id: INT
  • product_id: INT
  • change_amount: INT (Positive for incoming stock, negative for sales)
  • transaction_date: DATETIME

Task

Write a SQL query that returns a list of all product_ids and their current_stock. The current_stock is defined as the sum of the stock_count from the baseline and the total sum of change_amount from all transactions. If a product has no transactions, the baseline value should still be returned.

Examples

Example Input: inventory_baseline

product_idstock_count
1100
250

inventory_transactions

product_idchange_amount
1-20
15
210

Example Output:

product_idcurrent_stock
185
260

Constraints

  • product_id in inventory_transactions might not exist in inventory_baseline.
  • A product may have zero transactions.
  • Handle NULL values appropriately if a product exists in the baseline but not in the transaction table.

Hints

Hint 1

Consider using a LEFT JOIN to ensure all products from the baseline are included even if they have no corresponding transactions.

Hint 2

Use the COALESCE function to treat NULL transaction sums as zero.

Hint 3

Group the transactions by `product_id` before joining them to the baseline table to optimize performance.

Follow-up questions

  • How would you modify your query to return only products where the current stock is below a certain reorder threshold?
  • What changes would be required to calculate the stock levels as of a specific historical date?
  • If the `inventory_transactions` table grows to billions of rows, how would you optimize this calculation?