logo

Back to questions

Pharmacy Analytics (Part 2) [CVS Health SQL Interview Question]

Easy

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 NameType
product_idinteger
units_soldinteger
total_salesdecimal
cogsdecimal
manufacturervarchar
drugvarchar

Example Input:

product_idunits_soldtotal_salescogsmanufacturerdrug
156895143130097.003427421.73BiogenAcyclovir
252223312753546.002974975.36AbbVieLamivudine and Zidovudine
50904842521023.732742445.90Eli LillyDermasorb TA Complete Kit
98110746813188.82140422.87BiogenMedi-Chord

Example Output:

manufacturerdrug_counttotal_loss
Biogen1297324.73
AbbVie1221429.36
Eli Lilly1221422.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!

PostgreSQL 14