Months of Stock Left
The reorder alert.
Here's the smart way to think about stock. Not, how many copies are left. But, how many months will they last? Twenty copies of a slow book might last a year. Twenty copies of a best seller might last two weeks.

- Not 'how many copies?'
- But 'how many months?'
- Speed matters
The reorder alert
Step one, a CTE works out average monthly sales, the last three months divided by three. We write three point zero, so SQL does decimal division. Then, for each book, stock divided by monthly sales gives months left. Where keeps books with less than two months left. Run it. Four books need reordering now. The Quiet Kitchen has none left. The Last Lighthouse has less than a month.
WITH sales AS (
SELECT oi.book_id,
SUM(oi.quantity) / 3.0 AS per_month
FROM order_items AS oi
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 oi.book_id
)
SELECT b.title,
b.stock,
ROUND(s.per_month, 1) AS per_month,
ROUND(b.stock / s.per_month, 1) AS months_left
FROM books AS b
JOIN sales AS s ON s.book_id = b.book_id
WHERE b.stock / s.per_month < 2
ORDER BY months_left;โโโโโโโโโโโโโโโโโโโโโโโฌโโโโโโโโฌโโโโโโโโโโโโฌโโโโโโโโโโโโโโ โ title โ stock โ per_month โ months_left โ โโโโโโโโโโโโโโโโโโโโโโโผโโโโโโโโผโโโโโโโโโโโโผโโโโโโโโโโโโโโค โ The Quiet Kitchen โ 0 โ 3.7 โ 0.0 โ โ The Last Lighthouse โ 5 โ 7.3 โ 0.7 โ โ Data for Everyone โ 3 โ 3.0 โ 1.0 โ โ The Painted Door โ 8 โ 5.7 โ 1.4 โ โโโโโโโโโโโโโโโโโโโโโโโดโโโโโโโโดโโโโโโโโโโโโดโโโโโโโโโโโโโโ
The slow sellers
Now the opposite. Which books are hardly selling? We use a left join, so books with no recent sales still appear. And coalesce turns their empty sales into zero. Run it. Spice Roads sold only two, but it only has two left, so that's fine. The Map Maker's Daughter sold only four copies, with twenty seven on the shelf. Wild Coast has forty two copies, but sold just seven. Helen could put them on sale.
WITH sales AS (
SELECT oi.book_id, SUM(oi.quantity) AS sold
FROM order_items AS oi
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 oi.book_id
)
SELECT b.title, b.stock, COALESCE(s.sold, 0) AS sold
FROM books AS b
LEFT JOIN sales AS s ON s.book_id = b.book_id
ORDER BY sold, b.stock DESC
LIMIT 5;โโโโโโโโโโโโโโโโโโโโโโโโโโโโฌโโโโโโโโฌโโโโโโโ โ title โ stock โ sold โ โโโโโโโโโโโโโโโโโโโโโโโโโโโโผโโโโโโโโผโโโโโโโค โ Spice Roads โ 2 โ 2 โ โ The Map Maker's Daughter โ 27 โ 4 โ โ Garden of Small Things โ 32 โ 6 โ โ Wild Coast โ 42 โ 7 โ โ The Pricing Game โ 19 โ 8 โ โโโโโโโโโโโโโโโโโโโโโโโโโโโโดโโโโโโโโดโโโโโโโ
Where is the money tied up?
Your turn. For each genre, show the number of copies in stock, and their total value. Show the top three by value. Pause and try. Here's a solution. Thrillers have the most money on the shelves, about two thousand one hundred and fifty dollars.
Stock copies and value per genre, top 3 by value
Show hint
SUM(stock), SUM(stock * price), GROUP BY genre
Show solution
SELECT genre,
SUM(stock) AS copies,
ROUND(SUM(stock * price), 2) AS value
FROM books
GROUP BY genre
ORDER BY value DESC
LIMIT 3;โโโโโโโโโโโโโโฌโโโโโโโโโฌโโโโโโโโโโ โ genre โ copies โ value โ โโโโโโโโโโโโโโผโโโโโโโโโผโโโโโโโโโโค โ Thriller โ 160 โ 2152.9 โ โ Technology โ 63 โ 1980.37 โ โ History โ 96 โ 1940.04 โ โโโโโโโโโโโโโโดโโโโโโโโโดโโโโโโโโโโ
Summary
You've built three real reports. Monthly sales, customers, and stock. That's a real analyst's toolkit. Next, the most common problem in real data. Mess.
Key takeaways- Months of stock left
- Slow sellers
- Money on the shelves