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 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)!
|94||132362||2041758.41||1373721.70||Biogen||UP and UP|
|Eli Lilly||$3 million|
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!
Goal: Find the total sales in million for each manufacturer.
Output showing the 2 randomly selected records:
The output above shows that Eli Lilly and Biogen each sold drugs with a total sales value of $81,641,381.27 and $69,824,472.58, respectively.
Although each manufacturer’s sales have been calculated, the figures are not in the million-dollar format.
Next, we round up the to the closest million.
Showing the output for Eli Lilly and Biogen:
Eli Lilly’s sales of $81,641,381.27 is rounded to the closest million to $82 and Biogen’s $69,824,472.58 is rounded to $70.
The sales data will be fed into a dashboard, thus it has to be formatted like this: "$xx million".
P.S. It is not necessary to convert into data type as the function accepts both and data types. Bear in mind that the column is now a data type.
|Eli Lilly||$82 million|
Finally, sort the results in the descending order of sales.
Hence, we will utilize the clause on to place the highest total sales at the top followed by the least total sales.
Solution 2: Using CTE