B.Pharm or M.Pharm to data analyst: expiry, stock and batch data

The project the answers refer to

The model answers assume one portfolio project: a near-expiry and FEFO (first-expiry, first-out) analysis for a fictional chain of three retail pharmacies. The core query, on synthetic data:

CREATE TABLE stock (
  store TEXT, sku TEXT, batch TEXT, expiry TEXT, qty INTEGER, unit_cost REAL
);
CREATE TABLE dispensed (
  store TEXT, sku TEXT, batch TEXT, sold_on TEXT, qty INTEGER
);
INSERT INTO stock VALUES
 ('Store-1','Paracetamol 500','P-2231','2026-11-30',400,1.2),
 ('Store-1','Paracetamol 500','P-2310','2027-06-30',600,1.2),
 ('Store-1','Amoxicillin 250','A-1102','2026-10-31',120,4.5),
 ('Store-2','Amoxicillin 250','A-1102','2026-10-31',90,4.5),
 ('Store-2','Cetirizine 10','C-0907','2027-03-31',300,0.8),
 ('Store-3','Metformin 500','M-4410','2026-12-31',250,1.6),
 ('Store-3','Metformin 500','M-4502','2027-08-31',500,1.6);
INSERT INTO dispensed VALUES
 ('Store-1','Paracetamol 500','P-2310','2026-09-20',80),
 ('Store-1','Paracetamol 500','P-2231','2026-09-21',20),
 ('Store-3','Metformin 500','M-4502','2026-09-22',60),
 ('Store-2','Cetirizine 10','C-0907','2026-09-22',40);

-- 1. Stock value expiring within 90 days of 27 Sep 2026
SELECT store,
       ROUND(SUM(qty * unit_cost), 2) AS value_expiring_90d
FROM stock
WHERE julianday(expiry) - julianday('2026-09-27') <= 90
GROUP BY store
ORDER BY value_expiring_90d DESC;

-- 2. FEFO breaches: a later-expiry batch sold while an earlier one sat in stock
SELECT d.store, d.sku, d.batch AS sold_batch, s_old.batch AS older_batch_in_stock
FROM dispensed d
JOIN stock s_sold ON s_sold.store = d.store AND s_sold.batch = d.batch
JOIN stock s_old ON s_old.store = d.store AND s_old.sku = d.sku
 AND s_old.expiry < s_sold.expiry
ORDER BY d.store;

The first query returns Store-1 at ₹1,020 and Store-2 at ₹405 (Store-3's nearest batch expires on 31 December, just outside the window). The second returns two FEFO breaches: Paracetamol batch P-2310 sold at Store-1 while P-2231 was older, and Metformin M-4502 sold at Store-3 while M-4410 was older.

Technical questions

"Walk me through your near-expiry query."

I filter batches whose expiry is within 90 days of the reporting date, multiply quantity by unit cost and group by store. I used a fixed reporting date rather than today's date so the result is reproducible. Store-1 has about ₹1,020 of stock at risk, mostly an antibiotic batch that expires next month. In a real pharmacy I'd check whether that batch can be transferred to a store that dispenses more amoxicillin before it becomes a return.

"Why does the FEFO query join the stock table twice?"

Once to find the expiry of the batch that was sold, and once to find any other batch of the same medicine in the same store that expires earlier. If both exist, the counter picked the newer stock while older stock was on the shelf. In practice that's usually a shelving issue — the new delivery went to the front — and it's exactly what leads to expiry write-offs later.

"What would make your analysis wrong?"

Three things. The stock table is a snapshot, so a batch that was sold out before the snapshot wouldn't appear, and a breach against it wouldn't be caught. Unit cost might be purchase price in one store and MRP in another if the source system isn't consistent. And batch codes typed by hand at the counter are sometimes wrong, so I'd check for dispensed batches that don't exist in stock before trusting the breach count.

That third answer matters most. Pharmacists already know that batch records have to be right — you have filled them in under inspection. Saying you would validate the batch codes first shows the same instinct applied to data.

Business questions

"The chain wants to cut expiry losses. What would you look at first?"

I'd rank SKUs by value written off over the last year, then check which ones are slow movers in some stores but fast in others. Those are transfer candidates. Then I'd look at FEFO breaches by store, because a store with many breaches probably has a shelving or receiving practice problem that a process change fixes more cheaply than any analysis.

"How would you measure whether a fix worked?"

Compare near-expiry value and FEFO breach counts per store for a few months before and after, alongside a store where nothing changed. Expiry has seasonality — antihistamines, for example — so a single month's comparison could mislead.

The career question

"Why are you leaving pharmacy?"

I've spent three years in retail pharmacy, and the part I became best at was stock: batch tracking, expiry checks, returns to distributors. I noticed most write-offs were predictable from the data we already had, but nobody was looking at it across stores. I learned SQL and Power BI to do that analysis, and built a project that finds near-expiry value and FEFO breaches. I want a role where that kind of analysis is the job, ideally in pharma distribution, healthcare operations or retail.

"Won't you miss the clinical side?"

Some of it. But my pharmacology knowledge still helps: I can tell when a data pattern doesn't make sense, like an antibiotic selling like a chronic medicine. That kind of sense-check is useful to any analytics team working with healthcare data.

Gaps these questions will expose

Pharmacy graduates usually need deliberate practice in:

• SQL joins and date functions — the FEFO query is a self-join, which is where most people get stuck.
• Excel modelling at scale — pivot tables, lookups and cleaning distributor exports with inconsistent SKU names.
• Dashboards — Power BI for store managers who will not read a query.
• Business metrics — inventory turns, days of cover, gross margin by category.

The Data Analyst programme covers these in a sequence that fits someone who is new to code but comfortable with regulated records.

Pharmacy operations analyst as the natural first title

Start with inventory analyst, supply chain analyst or healthcare data analyst roles, and look at pharma distributors, pharmacy chains, hospital groups and health-tech companies. A general data analyst role is also possible, but your domain is an advantage — use it. If clinical-trial data interests you more, clinical research coordinator to data scientist covers a related route.