Back to questions
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)!
Column Name | Type |
---|---|
product_id | integer |
units_sold | integer |
total_sales | decimal |
cogs | decimal |
manufacturer | varchar |
drug | varchar |
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 |
manufacturer | drug_count | total_loss |
---|---|---|
Biogen | 1 | 297324.73 |
AbbVie | 1 | 221429.36 |
Eli Lilly | 1 | 221422.17 |
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!
Here's a multi-step approach to solving the question:
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.
To filter for unprofitable drugs only, we will keep rows where the is equal to or less than 0 in the clause.
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.
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