Pharmacy Dataset for Analysis

• inventory.product_id →references products.product_idmany to one: each Inventory row points to one Products row
• sales.product_id →references products.product_idmany to one: each Sales row points to one Products row

1. Which products have the highest sales volume (total quantity sold)? Show top 10 products.

   Join sales and products tables, group by product, sum quantity_sold, order by sum DESC.

   tables: sales, products

2. Identify products that are low in inventory (quantity_on_hand < 50) but high in demand (average daily sales > 5).

   Calculate average daily sales per product from sales, then join with inventory and filter.

   tables: sales, inventory, products

3. Analyze the sales trend of prescription versus over-the-counter drugs over time.

   Join sales and products, group by category and date, sum transaction_total.

   tables: sales, products

Table names match the SQLite file and the SQL script.

SELECT p.product_name, SUM(s.quantity_sold) AS total_quantity
FROM sales s
JOIN products p ON s.product_id = p.product_id
GROUP BY p.product_name
ORDER BY total_quantity DESC
LIMIT 20;

SELECT
  strftime('%Y-%m', s.sale_date) AS sale_month,
  p.category,
  SUM(s.transaction_total) AS monthly_revenue
FROM sales s
JOIN products p ON s.product_id = p.product_id
GROUP BY sale_month, p.category
ORDER BY sale_month, p.category
LIMIT 20;

SELECT
  p.category,
  SUM(s.transaction_total) AS total_revenue
FROM sales s
JOIN products p ON s.product_id = p.product_id
GROUP BY p.category
ORDER BY total_revenue DESC
LIMIT 20;

Excel

Load each table into a separate sheet. Use XLOOKUP or Power Query to join tables. Create a PivotTable on the sales data, grouping by product category and date.

Power BI

Create a star schema with 'products' as the dimension table and 'sales' and 'inventory' as fact tables. Establish relationships on 'product_id'. DAX measures could include Total Sales Amount and Current Stock Level.

SQL

Load the SQLite or SQL dump into your preferred database. Primary keys are defined in the schema. Join tables using product_id to link sales and inventory to product details.

Python

Use pandas to load CSV or SQL data. Merge tables on 'product_id'. Analyze sales trends and inventory levels using DataFrame operations.

• CSV (zip)One CSV file per table, zipped
• Excel workbookOne worksheet per table
• SQLite databaseA ready-to-query database file with every table
• Parquet (zip)One Parquet file per table, zipped
• SQL scriptCREATE TABLE with primary and foreign keys, then INSERTs
• CSVA single CSV file
• JSONA single JSON file