Medicines Below Their Reorder Level — SQL Joins Problem
Each medicine has a reorder level; when the usable stock falls below it, the store raises a purchase order. Take 2025-06-30 as today.
- Difficulty: Medium
- Topics: Joins, Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #214
Problem statement
Each medicine has a reorder level; when the usable stock falls below it, the store raises a purchase order. Take 2025-06-30 as today. Usable stock is the total quantity of the medicine's batches whose expiry_date is after today — a batch expiring today is already pulled from the shelf. A medicine with no usable batch has a usable stock of 0.
Return medicine_id, name, usable_stock and reorder_level of every medicine whose usable stock is strictly below its reorder level, ordered by medicine_id.
Tables
Table: Medicine
| Column | Type |
|---|---|
| medicine_id | int |
| name | varchar |
| reorder_level | int |
Primary key: medicine_id.
Table: StockBatch
| Column | Type |
|---|---|
| batch_id | int |
| medicine_id | int |
| quantity | int |
| expiry_date | date |
Primary key: batch_id.
Units on the shelf per batch; medicine_id always exists in Medicine.
Examples
Example 1
Medicine
| medicine_id | name | reorder_level |
|---|---|---|
| 1 | Paracetamol 500mg | 500 |
| 2 | Insulin Glargine | 40 |
| 3 | Azithromycin 500mg | 100 |
| 4 | ORS Sachet | 200 |
| 5 | Ceftriaxone 1g | 60 |
StockBatch
| batch_id | medicine_id | quantity | expiry_date |
|---|---|---|---|
| 11 | 1 | 300 | 2026-01-31 |
| 12 | 1 | 250 | 2025-12-31 |
| 13 | 2 | 25 | 2025-08-15 |
| 14 | 2 | 30 | 2025-06-30 |
| 15 | 3 | 100 | 2025-11-30 |
| 16 | 4 | 500 | 2025-05-31 |
| 17 | 5 | 20 | 2025-07-01 |
Output
| medicine_id | name | usable_stock | reorder_level |
|---|---|---|---|
| 2 | Insulin Glargine | 25 | 40 |
| 4 | ORS Sachet | 0 | 200 |
| 5 | Ceftriaxone 1g | 20 | 60 |
How to solve Medicines Below Their Reorder Level
The answer is per medicine, and the medicines most in need of reordering are the ones with no usable batch at all, so the query must start from Medicine and LEFT JOIN the batches. The expiry rule (expiry_date > '2025-06-30') goes into the ON clause: batches that fail it simply don't join, while the medicine row stays.
After grouping, SUM(b.quantity) is the usable stock — or NULL when nothing joined, because SUM of no values is NULL. COALESCE(…, 0) turns that into 0, and the HAVING clause compares it strictly with the reorder level; without the COALESCE, NULL < 40 is unknown and the empty medicines would vanish from the list.
A CASE inside SUM achieves the same by adding 0 for expired batches (a medicine with no batch at all then sums one NULL-ish 0 row from the LEFT JOIN — ELSE 0 handles it). A correlated subquery per medicine is the third form. All are one pass over the batches.
Reference solution (MySQL)
SELECT m.medicine_id, m.name,
COALESCE(SUM(b.quantity), 0) AS usable_stock,
m.reorder_level
FROM Medicine m
LEFT JOIN StockBatch b
ON b.medicine_id = m.medicine_id AND b.expiry_date > '2025-06-30'
GROUP BY m.medicine_id, m.name, m.reorder_level
HAVING COALESCE(SUM(b.quantity), 0) < m.reorder_level
ORDER BY m.medicine_idAnother way
SELECT * FROM (
SELECT m.medicine_id, m.name,
(SELECT COALESCE(SUM(quantity), 0) FROM StockBatch b WHERE b.medicine_id = m.medicine_id AND b.expiry_date > '2025-06-30') AS usable_stock,
m.reorder_level
FROM Medicine m
) t WHERE usable_stock < reorder_level ORDER BY medicine_idAnother way
SELECT m.medicine_id, m.name, SUM(CASE WHEN b.expiry_date > '2025-06-30' THEN b.quantity ELSE 0 END) AS usable_stock, m.reorder_level
FROM Medicine m LEFT JOIN StockBatch b ON b.medicine_id = m.medicine_id
GROUP BY m.medicine_id, m.name, m.reorder_level
HAVING SUM(CASE WHEN b.expiry_date > '2025-06-30' THEN b.quantity ELSE 0 END) < m.reorder_level
ORDER BY m.medicine_id← Diabetic Patients Whose Latest HbA1c Improved · Peak Arrival Hour in Emergency Each Day →