A pharmacy has tasked us with business problems solvable by querying their Database. We drew insights and provided recommendations for the business problems.
Within the SQL scripts are code used to query the database and insights/recommendations made from the results. We also explain why the business would need to solve such a problem.
The CSV files contain results from the queries and an ER diagram in PNG format.
PHARMACY DATABASE RESULTS AND VISUALIZATIONS
We created this DB with Mockaroo. It’s a pharmacy’s daily business activities. Through it, the business can gain insights that will guide its strategic decision-making. It has the following tables: Prescriptions Doctors Patients Medications
Business Problems.
Calculate the average transaction amount for each payment method used
Insights from this will inform on:
Resource allocation toward enhancing payment compatibility to maximize customer satisfaction and efficiency.
Fraud activities as unexpected revenues will be suspicious.
Promotional strategies e.g., incentives for using insurance
Profitability per payment method
Calculate the total number of prescriptions issued each month for the past year, grouped by month
Insights from this will inform on:
Patient education for conditions prevalent in certain months e.g., flu in July
Marketing resources allocation in the peak months e.g., November to maximize revenues
Inventory adjustment to match expected monthly sales e.g., sufficient inventory to see through November.
Plotting monthly sales growth and setting targets
Retrieve the patients who have prescriptions for medications that are out of stock.
Insights from this will inform on:
Communication with the relevant customers and providing alternatives to enhance efficiency and customer satisfaction
Prioritizing Inventory replenishment
Calculate the total revenues generated from each medication, considering the quantity sold and the price per unit.
Insights from this will inform on:
Prioritizing resources and investments to drugs that reflect high demand
Cost analysis to assess profitability for each drug
Price negotiations with suppliers to manage the cost of doing business.
Price adjustments to boost sales for struggling SKUs or capitalize on profits due to good sales.
Find the most commonly prescribed medication and the corresponding doctor who prescribed it.
Insights from this will inform on:
Aligning the prescriptions with respective ailments and carrying patient health education.
Training of doctors to enrich their knowledge as they are the best medium of communication with patients.
Demand assessment and inventory replenishment