Back to questions
Pharmacy Analytics (Part 1) [CVS Health SQL Interview Question]
CVS Health is trying to better understand its pharmacy sales, and how well different products are selling. Each drug can only be produced by one manufacturer.
Write a query to find the top 3 most profitable drugs sold, and how much profit they made. Assume that there are no ties in the profits. Display the result from the highest to the lowest total profit.
Definition:
- stands for Cost of Goods Sold which is the direct cost associated with producing the drug.
- Total Profit = Total Sales - Cost of Goods Sold
If you like this question, try out Pharmacy Analytics (Part 2)!
Table:
| Column Name | Type |
|---|---|
| product_id | integer |
| units_sold | integer |
| total_sales | decimal |
| cogs | decimal |
| manufacturer | varchar |
| drug | varchar |
Example Input:
| product_id | units_sold | total_sales | cogs | manufacturer | drug |
|---|---|---|---|---|---|
| 9 | 37410 | 293452.54 | 208876.01 | Eli Lilly | Zyprexa |
| 34 | 94698 | 600997.19 | 521182.16 | AstraZeneca | Surmontil |
| 61 | 77023 | 500101.61 | 419174.97 | Biogen | Varicose Relief |
| 136 | 144814 | 1084258 | 1006447.73 | Biogen | Burkhart |
Example Output:
| drug | total_profit |
|---|---|
| Zyprexa | 84576.53 |
| Varicose Relief | 80926.64 |
| Surmontil | 79815.03 |
Explanation:
Zyprexa made the most profit (of $84,576.53) followed by Varicose Relief (of $80,926.64) and Surmontil (of $79,815.3).
The dataset you are querying against may have different input & output - this is just an example!
Solution
First, we must establish the formula used to calculate the profits.
Total Profit = Total Sales - Cost of Goods Sold
The profit is calculated by subtracting the cost of goods sold (being the direct cost associated with producing the drug) () from the total sales generated ().
Displaying the result for 4 random drugs.
| drug | total_sales | cogs | total_profit |
|---|---|---|---|
| Zyprexa | 293452.54 | 208876.01 | 84576.53 |
| Surmontil | 600997.19 | 521182.16 | 79815.03 |
| Varicose Relief | 500101.61 | 419174.97 | 80926.64 |
| Burkhart | 1084258 | 1006447.73 | 77810.27 |
Profit of $84,576.53 has been made from the sale of Zyprexa.
Let's arrange the results in the decreasing order of the total profits generated by the sale of the drugs. clause with will be added to the query for this step.
| drug | total_sales | cogs | total_profit |
|---|---|---|---|
| Zyprexa | 293452.54 | 208876.01 | 84576.53 |
| Varicose Relief | 500101.61 | 419174.97 | 80926.64 |
| Surmontil | 600997.19 | 521182.16 | 79815.03 |
| Burkhart | 1084258 | 1006447.73 | 77810.27 |
The final step is to only keep the rows of drugs with the highest 3 profits. The clause keeps the specified number of rows and discards the rest of the table.
Solution:
PostgreSQL 14