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.
table
name: pharmacy_sales
Solution:
with cte as
(
select manufacturer,drug, (total_sales-cogs) as loss from pharmacy_sales
where (total_sales-cogs)<0
)
select manufacturer,count(drug) as drug_count,sum(loss)*-1 as total_loss
from cte group by manufacturer
order by total_loss desc
Output:
SQL Script:
CREATE TABLE [dbo].[pharmacy_sales](
[product_id]
[int] NULL,
[units_sold]
[int] NULL,
[total_sales]
[decimal](18, 2) NULL,
[cogs]
[decimal](18, 2) NULL,
[manufacturer]
[varchar](50) NULL,
[drug]
[varchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[pharmacy_sales] ([product_id], [units_sold], [total_sales], [cogs], [manufacturer], [drug]) VALUES (156, 89514, CAST(3130097.00 AS Decimal(18, 2)), CAST(3427421.73 AS Decimal(18, 2)), N'Biogen', N'Acyclovir')
GO
INSERT [dbo].[pharmacy_sales] ([product_id], [units_sold], [total_sales], [cogs], [manufacturer], [drug]) VALUES (25, 222331, CAST(2753546.00 AS Decimal(18, 2)), CAST(2974975.36 AS Decimal(18, 2)), N'AbbVie', N'Lamivudine and
Zidovudine')
GO
INSERT [dbo].[pharmacy_sales] ([product_id], [units_sold], [total_sales], [cogs], [manufacturer], [drug]) VALUES (50, 90484, CAST(2521023.73 AS Decimal(18, 2)), CAST(2742445.90 AS Decimal(18, 2)), N'Eli Lilly', N'Dermasorb TA
Complete Kit')
GO
INSERT [dbo].[pharmacy_sales] ([product_id], [units_sold], [total_sales], [cogs], [manufacturer], [drug]) VALUES (98, 110746, CAST(813188.82 AS Decimal(18, 2)), CAST(140422.87 AS Decimal(18, 2)), N'Biogen', N'Medi-Chord')
GO