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.
table name: pharmacy_sales
Solution:
select top 3 drug, (total_sales-cogs) as total_profits from pharmacy_sales
order by total_profits 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 (9, 37410, CAST(293452.54 AS Decimal(18, 2)), CAST(208876.01 AS Decimal(18, 2)), N'Eli Lilly', N'Zyprexa')
GO
INSERT [dbo].[pharmacy_sales] ([product_id], [units_sold], [total_sales], [cogs], [manufacturer], [drug]) VALUES (34, 94698, CAST(600997.19 AS Decimal(18, 2)), CAST(521182.16 AS Decimal(18, 2)), N'AstraZeneca', N'Surmontil')
GO
INSERT [dbo].[pharmacy_sales] ([product_id], [units_sold], [total_sales], [cogs], [manufacturer], [drug]) VALUES (61, 77023, CAST(500101.61 AS Decimal(18, 2)), CAST(419174.97 AS Decimal(18, 2)), N'Biogen', N'Varicose
Relief')
GO
INSERT [dbo].[pharmacy_sales] ([product_id], [units_sold], [total_sales], [cogs], [manufacturer], [drug]) VALUES (136, 144814, CAST(1084258.00 AS Decimal(18, 2)), CAST(1006447.73 AS Decimal(18, 2)), N'Biogen', N'Burkhart')
GO