Pharmacy Inventory Optimization

This dataset provides detailed, location-specific pharmacy inventory records, including product stock levels, demand forecasts, reorder patterns, and risk flags for stockouts and excess inventory. It enables supply chain optimization, predictive analytics, and operational efficiency for pharmacies by integrating product, demand, and location data. Ideal for inventory management, demand planning, and reducing waste in pharmaceutical retail.

  • opened 35 times
  • last updated 20 Aug 2025
  • by GoMask
The brief that made it

Pharmacy inventory management and optimization

Sample rows

preview · 8 of 200 rows · all 23 columns
pharmacy_idstringaverage_daily_demandfloatstockout_risk_flagbooleanaddress_statestringpharmacy_namestringaddress_streetstringaddress_citystringaddress_postal_codestringaddress_countrystringproduct_idstringproduct_namestringproduct_categorystringproduct_manufacturerstringstock_levelintegerstock_threshold_minintegerstock_threshold_maxintegerpredicted_demand_next_30_daysfloatlast_reorder_datedatenext_reorder_date_predicteddatereorder_quantity_suggestedintegerexcess_inventory_flagbooleanlast_stockout_datedatenotesstring
PH0014.5trueCACityCare Pharmacy128 Market AvenueSan Francisco94111USAPR1001Ibuprofen 200mg TabletspainkillerMediGenix8520801352024-05-022024-07-0455true2024-05-01Recent bulk delivery led to excess inventory, monitoring demand spike.
PH0027.8falseBEEcoPharm Berlin12 AlexanderplatzBerlin10178GermanyPR1002Metformin XR 500mgantidiabeticBioHealth600505002342024-05-152024-08-10blanktrueblankInventory above recommended maximum due to seasonal bulk order.
PH0032.1falseILCentral Pharmacy478 Main StreetChicago60616USAPR1003Amoxicillin 500mg CapsulesantibioticWellMed601570632024-05-222024-06-3040falseblankStable demand, regular monthly restocks recommended.
PH0040.09trueGLRemote Village Pharmacy9 Nuuk RoadNuuk3900GreenlandPR1004Vitamin D3 1000IU Capsulessupplementblank35152.72024-04-302024-06-1512false2024-04-28Stockout risk due to infrequent shipments and low supply.
PH0051.8falseQLDFamily Pharmacy Brisbane34 Queen StreetBrisbane4000AustraliaPR1005Amlodipine 5mg TabletsantihypertensivePharmaDirect421060542024-05-102024-07-0528falseblankDemand stable; maintain regular order schedule.
PH0063.3falseZHLakefront Pharmacy Zurich7 SeestrasseZurich8001SwitzerlandPR1006Omeprazole 20mg CapsulesantacidSwissPharma30025100992024-05-082024-08-12blanktrueblankExcess inventory due to manufacturer promotion event.
PH0072.7trueMANUrban Wellness Manchester55 Oxford RoadManchesterM13 9PLUKPR1007Paracetamol 500mg TabletspainkillerBritMeds01080812024-03-152024-05-2545false2024-05-15Urgent reorder needed; stock depleted after local flu outbreak.
PH0081.2falseAZDesert Health Phoenix201 Camelback RoadPhoenix85016USAPR1008Loratadine 10mg TabletsantihistamineAllergyMed151050362024-05-182024-07-0135falseblankAllergy season approaching; expect moderate increase in demand.

What the 200 rows show

from the 200-row sample
  • 25%stockout_risk_flag = true
  • 1.5median average_daily_demand
  • 9address countries
  • 10product categories
  • 47address cities
  • 42.5median stock_level
average_daily_demand200 rows, in bands of 1
03570664949208422048average_daily_demand →

Median 1.5, from 0.02 to 7.9.

address_state200 rows · top 10 of 34 values
  1. GL16
  2. ON16
  3. CA13
  4. NSW12
  5. LDN12
  6. BE10
  7. ZH10
  8. IDF10
  9. DL8
  10. HE8
23 columns by typefrom the column list below
  • string 12
  • integer 4
  • float 2
  • date 3
  • boolean 2

Columns

23 columns in four groups
blueprint · 23 columns
columntypedescriptionexample
Text 12 columns
pharmacy_idstringUnique identifier for the pharmacy locationPH001
pharmacy_namestringName of the pharmacyCityCare Pharmacy
address_streetstringStreet address of the pharmacy128 Market Avenue
address_citystringCity where the pharmacy is locatedSan Francisco
address_statestringState or province of the pharmacyCA
address_postal_codestringPostal or ZIP code of the pharmacy94111
address_countrystringCountry where the pharmacy is located9 countriesUSA
product_idstringUnique identifier for the productPR1001
product_namestringName of the product or medicationIbuprofen 200mg Tablets
product_categorystringCategory of the product (e.g., antibiotic, painkiller, supplement)10 categoriespainkiller
product_manufacturerstringManufacturer of the productoptionalMediGenix
notesstringAdditional notes or comments regarding the product's inventory statusoptionalRecent bulk delivery led …
Numbers 6 columns
stock_levelintegerCurrent quantity of the product in stock at the pharmacy0 or more85
stock_threshold_minintegerMinimum stock level before reorder is triggered0 or more20
stock_threshold_maxintegerMaximum recommended stock level to avoid excess inventory0 or more80
average_daily_demandfloatAverage daily demand for the product at this pharmacy (units per day)0 or more4.5
predicted_demand_next_30_daysfloatPredicted total demand for the product over the next 30 days0 or more · optional135
reorder_quantity_suggestedintegerSuggested quantity to reorder based on demand and stock levels0 or more · optional55
Dates and times 3 columns
last_reorder_datedateDate when the product was last reorderedoptional2024-05-02
next_reorder_date_predicteddatePredicted date when the next reorder should occuroptional2024-07-04
last_stockout_datedateDate when the product last experienced a stockoutoptional2024-05-01
True or false 2 columns
stockout_risk_flagbooleanIndicates if the product is at risk of stockout (true/false)true
excess_inventory_flagbooleanIndicates if the product is at risk of excess inventory (true/false)true

15 SQL practice questions for the Pharmacy Inventory Optimization

Written for this data and checked against it: 12 SQL and 3 Power BI exercises from beginner to advanced. Try the first three free; the practice kit has all 15, a starter .sql file and an answer key with every expected result.

  1. 01BeginnerFreeSELECT · WHERE · ORDER BY

    Stockout-risk items in Canada

    Return pharmacy_id, product_name and stock_level for every row where address_country is 'Canada' and stockout_risk_flag is 1. Order the results by stock_level ascending, then by pharmacy_id ascending.

    Tables: pharmacy_inventory_optimization · your result: 3 columns, 7 rows
  2. 02BeginnerFreeDISTINCT · ORDER BY

    Distinct product categories

    Return each distinct product_category exactly once, as a single column named product_category, sorted alphabetically.

    Tables: pharmacy_inventory_optimization · your result: 1 columns, 10 rows
  3. 03BeginnerFreeGROUP BY · COUNT

    Rows per country

    Count the rows for each address_country. Return address_country and row_count, ordered by row_count descending, then by address_country ascending.

    Tables: pharmacy_inventory_optimization · your result: 2 columns, 9 rows
  1. 04Stock and demand by categoryBeginner
  2. 05Excess inventory count by countryPower BI
  3. 06Categories with many stockout risksIntermediate
  4. 07Stock level bucketsIntermediate
  5. 08Countries above average demandIntermediate
  6. 09Reorders by monthIntermediate
  7. 10Coverage days by categoryPower BI
  8. 11Top-stock pharmacies within each countryAdvanced
  9. 12Gap between consecutive painkiller reordersAdvanced
  10. 13Running total of stock in May 2024Advanced
  11. 14Category share of predicted demandAdvanced
  12. 15Demand anomalies within categoryPower BI

Get the practice kit: the full dataset, all 15 exercises, a starter .sql and the answer key. One-time $3.99.

Get the practice kit · $3.99

Use it for

  • stockout risk …25%49 of 200 rows

    A healthcare dashboard

    The stockout_risk_flag rate, average_daily_demand and a breakdown of address_state. Excel, Power BI or Tableau.

  • Why do 49 of 200 rows have stockout_risk_flag = true?

    A class exercise

    Hand out the rows and one question. Everyone works from the same 200 rows.

  • A software demo

    Believable pharmacies with pharmacy_name, address_street and address_city to fill a screen in front of a buyer.

Not quite right?

Make it yours.

Same 23 columns, your size and your rules. See 20 rows before you pay.

Preview 20 rows free

1,000 rows: $1.99 · 10,000: $12.99One-time. No subscription. All prices

This dataset200 rows23 columns
Yours1,000 rows23 columnsaddress_street: UK only

blueprint · pharmacy-inventory-optimization

Behind this dataset

Same schema. As many rows as you need.

These 200 rows came out of a blueprint — 23 columns with generation rules behind each one. Open it in Data Factory to retune a column, add your own, wire in foreign keys, and run it at the size you actually need.

Rules it was built with
  • Product SKUs mapped to demand
  • Daily sales and stock levels recorded
  • Reorder recommendations based on trends
  • Expiry dates factored in replenishments
Rows
Open the blueprint in Data Factory

1 credit per row. Preview 20 rows free. Buy rows for the full run.

Exports
CSV, JSON, JSONL, Parquet, SQL, Excel, TSV, XML
Licence
yours to use, including commercially
API slug
pharmacy-inventory-optimization

What should your data show?

Preview 20 rows free
No signup. No card.