Back to questions
Pharmacy Analytics (Part 2) [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 out which manufacturer is associated with the drugs that were not profitable and how much money CVS lost on these drugs.
Output the manufacturer, number of drugs and total losses. Total losses should be in absolute value. Display the results with the highest losses on top.
If you like this question, try out Pharmacy Analytics (Part 3)!
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 |
|---|---|---|---|---|---|
| 156 | 89514 | 3130097.00 | 3427421.73 | Biogen | Acyclovir |
| 25 | 222331 | 2753546.00 | 2974975.36 | AbbVie | Lamivudine and Zidovudine |
| 50 | 90484 | 2521023.73 | 2742445.90 | Eli Lilly | Dermasorb TA Complete Kit |
| 98 | 110746 | 813188.82 | 140422.87 | Biogen | Medi-Chord |
Example Output:
| manufacturer | drug_count | total_loss |
|---|---|---|
| Biogen | 1 | 297324.73 |
| AbbVie | 1 | 221429.36 |
| Eli Lilly | 1 | 221422.17 |
Explanation:
The drugs in the first 3 rows of the Example Input table reported losses. Biogen's losses were the highest followed by AbbVie's and Eli Lilly's.
Medi-Chord drug by Biogen reported a profit, so it was excluded from the result.
The dataset you are querying against may have different input & output - this is just an example!
Solution
Here's a multi-step approach to solving the question:
- Find the total profit or loss for each manufacturer.
- Keep rows where drugs are making losses.
- Obtain the number of unprofitable drugs and total losses for each manufacturer.
- Convert the total losses to absolute value and order output accordingly.
Step 1: Find the total profit or loss for each manufacturer
Before we start, we have to define how the total profit or loss is calculated.
Total Profit/(Total Loss) = Total Sales - Total Cost of Goods Sold
A positive value indicates profit, whereas a negative value indicates a loss.
Here's how the query looks after incorporating the formula.
Showing the outcomes for 4 randomly selected drugs.
| manufacturer | drug | net_value |
|---|---|---|
| Biogen | Acyclovir | -297324.73 |
| AbbVie | Lamivudine and Zidovudine | -221429.36 |
| Eli Lilly | Dermasorb TA Complete Kit | -221422.17 |
| Biogen | Medi-Chord | 672765.95 |
Negative values in the column indicate losses. Medi-Chord is making a profit so this drug should be removed.
Step 2: Keep rows where drugs are making losses
To filter for unprofitable drugs only, we will keep rows where the is equal to or less than 0 in the clause.
Step 3: Obtain count of unprofitable drugs and total losses for each manufacturer
Next, we obtain the number of drugs associated with each manufacturer using the function and the total losses suffered by CVS Health using the function.
Step 4: Convert total loss to absolute value and order output
To return the in absolute value (it means to remove the negative symbol ), we can use the function on .
Finally, order the results based on the highest losses followed by the least losses.
Solution #1: Using ABS() -- Explained above
Solution #2: Without ABS()
By simply switching the and positions in , we can easily calculate the total losses in absolute value with no negative sign and do not need to use the function.
Additionally, in the clause, filtering for rows where means we are only keeping rows where the cost is more than the sales, indicating losses as well.
PostgreSQL 14