AI Hints
Homeโ€บSQL Tutorialโ€บProject: The Stock Alert
SQL ยท Lesson 88 of 126

Months of Stock Left

The reorder alert.

โฑ 2 min readโ–ถ Video at 2:04:47๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

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.

Months of Stock Left
From the video: Months of Stock Left
  • 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.

query.sqlsql
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;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚        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.

query.sqlsql
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;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚          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.

โœ๏ธ Exercise: Where is the money tied up?

Stock copies and value per genre, top 3 by value

Show hint

SUM(stock), SUM(stock * price), GROUP BY genre

Show solution
solution.sqlsql
SELECT genre,
       SUM(stock)                  AS copies,
       ROUND(SUM(stock * price), 2) AS value
FROM books
GROUP BY genre
ORDER BY value DESC
LIMIT 3;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚   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
Next up: Cleaning Messy Data โ€” the job nobody talks about