Objective

Write a SQL query to identify the top 3 most profitable drugs from pharmacy sales data.

Table Schema:

• pharmacy_sales

Task Requirements:

• Calculate total profit for each drug (total_sales - cogs)
• Sum profits across all records for each drug
• Select only the top 3 most profitable drugs
• Sort results by profit in descending order
  
  

Output columns: drug_name, total_profit

SELECT 
    drug_name,
    SUM(total_sales - cogs) AS total_profit
FROM pharmacy_sales
GROUP BY drug_name
ORDER BY total_profit DESC
LIMIT 3;

Explanation

Step 1: Understanding Profit Calculation

Profit is calculated as the difference between revenue (total_sales) and costs (cogs - cost of goods sold).

Step 2: Calculating and Aggregating Profit

SUM(total_sales - cogs) AS total_profit

Subtracts cogs from total_sales per row, then SUM aggregates all profit amounts per drug.

Step 3: Grouping by Drug Name

GROUP BY drug_name

Ensures total profit is calculated separately for each drug product.

Step 4: Sorting and Limiting Results

ORDER BY total_profit DESC
LIMIT 3

Sorting descending puts the most profitable drugs first; LIMIT 3 restricts output to the top 3.