Medicines Below Their Reorder Level

Medium
214.

Medicines Below Their Reorder Level

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

ColumnType
medicine_idint
namevarchar
reorder_levelint

Primary key: medicine_id.

Table: StockBatch

ColumnType
batch_idint
medicine_idint
quantityint
expiry_datedate

Primary key: batch_id.

Units on the shelf per batch; medicine_id always exists in Medicine.

Example

The rows must come back in the order shown.

Medicine

medicine_idnamereorder_level
1Paracetamol 500mg500
2Insulin Glargine40
3Azithromycin 500mg100
4ORS Sachet200
5Ceftriaxone 1g60

StockBatch

batch_idmedicine_idquantityexpiry_date
1113002026-01-31
1212502025-12-31
132252025-08-15
142302025-06-30
1531002025-11-30
1645002025-05-31
175202025-07-01

Output

medicine_idnameusable_stockreorder_level
2Insulin Glargine2540
4ORS Sachet0200
5Ceftriaxone 1g2060
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
medicine_idint
namevarchar
reorder_levelint

Primary key: medicine_id.

Table: StockBatch

ColumnType
batch_idint
medicine_idint
quantityint
expiry_datedate

Primary key: batch_id.

Units on the shelf per batch; medicine_id always exists in Medicine.

Examples

Example 1

Medicine

medicine_idnamereorder_level
1Paracetamol 500mg500
2Insulin Glargine40
3Azithromycin 500mg100
4ORS Sachet200
5Ceftriaxone 1g60

StockBatch

batch_idmedicine_idquantityexpiry_date
1113002026-01-31
1212502025-12-31
132252025-08-15
142302025-06-30
1531002025-11-30
1645002025-05-31
175202025-07-01

Output

medicine_idnameusable_stockreorder_level
2Insulin Glargine2540
4ORS Sachet0200
5Ceftriaxone 1g2060

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_id

Another 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_id

Another 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 →