logo

Back to questions

Pharmacy Analytics (Part 3) [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 the total sales of drugs for each manufacturer. Round your answer to the closest million, and report your results in descending order of total sales.

Because this data is being directly fed into a dashboard which is being seen by business stakeholders, format your result like this: "$36 million".

If you like this question, try out Pharmacy Analytics (Part 4)!

Table:

Column NameType
product_idinteger
units_soldinteger
total_salesdecimal
cogsdecimal
manufacturervarchar
drugvarchar

Example Input:

product_idunits_soldtotal_salescogsmanufacturerdrug
941323622041758.411373721.70BiogenUP and UP
937410293452.54208876.01Eli LillyZyprexa
50904842521023.732742445.9Eli LillyDermasorb
6177023500101.61419174.97BiogenVaricose Relief
1361448141084258.001006447.73BiogenBurkhart

Example Output:

manufacturersale
Biogen$4 million
Eli Lilly$3 million

Explanation

The total sales for Biogen is $4 million ($2,041,758.41 + $500,101.61 + $1,084,258.00 = $3,626,118.02) and for Eli Lilly is $3 million ($293,452.54 + $2,521,023.73 = $2,814,476.27).

The dataset you are querying against may have different input & output - this is just an example!

PostgreSQL 14