Project 3: The Stock Alert
Which books to reorder.
Nothing annoys a customer more than a book that's out of stock. But ordering too many copies ties up Helen's money on the shelves. She needs to know which books will run out soon, and which ones are just sitting there. Let's build a stock alert.

- Out of stock loses sales
- Too much stock wastes money
- Build a stock alert
The lowest stock
Start simple. Which books have the fewest copies left? Run it. The Quiet Kitchen has sold out completely. Spice Roads has only two left. But low stock alone doesn't tell the whole story.
SELECT title, stock
FROM books
ORDER BY stock
LIMIT 5;โโโโโโโโโโโโโโโโโโโโโโโฌโโโโโโโโ โ title โ stock โ โโโโโโโโโโโโโโโโโโโโโโโผโโโโโโโโค โ The Quiet Kitchen โ 0 โ โ Spice Roads โ 2 โ โ Data for Everyone โ 3 โ โ The Last Lighthouse โ 5 โ โ The Painted Door โ 8 โ โโโโโโโโโโโโโโโโโโโโโโโดโโโโโโโโ
Money on the shelves
How much money is sitting on Helen's shelves? Stock times price, added up. Run it. About fourteen thousand two hundred dollars' worth of books.
SELECT ROUND(SUM(stock * price), 2) AS stock_value
FROM books;โโโโโโโโโโโโโโโ โ stock_value โ โโโโโโโโโโโโโโโค โ 14211.45 โ โโโโโโโโโโโโโโโ
What is selling now?
Low stock only matters if a book is selling. So let's look at sales in the last three months, October to December. Run it. Dragons of Ember Bay sold forty two copies, but only twenty eight are left. That's a warning sign. The Last Lighthouse sold twenty two, with only five left.
SELECT b.title,
b.stock,
SUM(oi.quantity) AS sold_last_3_months
FROM books AS b
JOIN order_items AS oi ON oi.book_id = b.book_id
JOIN orders AS o ON o.order_id = oi.order_id
WHERE o.order_date >= '2025-10-01'
AND o.status IN ('delivered', 'shipped')
GROUP BY b.book_id
ORDER BY sold_last_3_months DESC
LIMIT 5;โโโโโโโโโโโโโโโโโโโโโโโโฌโโโโโโโโฌโโโโโโโโโโโโโโโโโโโโโ โ title โ stock โ sold_last_3_months โ โโโโโโโโโโโโโโโโโโโโโโโโผโโโโโโโโผโโโโโโโโโโโโโโโโโโโโโค โ Dragons of Ember Bay โ 28 โ 42 โ โ Code of the River โ 26 โ 26 โ โ Stars Over Lagos โ 47 โ 23 โ โ Crown of Ash โ 31 โ 22 โ โ The Last Lighthouse โ 5 โ 22 โ โโโโโโโโโโโโโโโโโโโโโโโโดโโโโโโโโดโโโโโโโโโโโโโโโโโโโโโ
Thinking like a buyer
Before we build the alert, think like Helen's stock buyer. A book with low stock and fast sales is urgent. A book with high stock and slow sales is wasted money. A book with low stock and slow sales is fine. And a book with high stock and fast sales is perfect. Our query needs both numbers, stock and speed.
Summary
Stock tells you what's left, and recent sales tell you how fast it's going. Next, combining them into one alert.
Key takeaways- Low stock
- Stock value
- Recent sales