Pharmacy Analytics (Part 4) CVS Health SQL Interview Question

CVS Health is trying to better understand its pharmacy sales, and how well different drugs are selling.

Write a query to find the top 2 drugs sold, in terms of units sold, for each manufacturer. List your results in alphabetical order by manufacturer.

Table:

Example Input:

Example Output:

Explanation

Biogen sold 144,814 units of Burkhart drug (ranked 1) followed by the second highest with 132,362 units of UP and UP drug (ranked 2).

Eli Lilly sold 118,696 units of Tizanidine Hydrochloride drug (ranked 1) followed by the second highest with 90,484 units of TA Complete Kit drug (ranked 2).

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

Let's divide this question into these 3 steps:

• Separate the drug data into groups by manufacturer and rank them.
• Restrict results to the top 2 drugs sold by each manufacturer.
• Sort the results in alphabetical order by the manufacturer.

Step 1: Segregation and Ranking Assignment

The data will first be grouped according to manufacturers, and the ranks will be determined by the units sold.

We can implement this by using the window function to partition all drug-related information by manufacturers. The clause sorts the rows in each manufacturer-partition by the column in descending order.

In each partition, the drug with the highest number of units sold will be positioned as no. 1, followed by the drug with the second-highest units sold at no. 2, and so on.

Showing the top 3 records for Biogen and 2 records for Eli Lilly:

Let's examine the implementation of the window function here:

• The rows are divided by manufacturer using the clause.
• window function is applied to each manufacturer-partition separately, and the ranking is reset in a new partition.
• clause with sorts the rows in each partition according to the units sold.

For Biogen, DIPHENHYDRAMINE HYDROCHLORIDE and Nefazodone Hydrochloride drugs are ranked 1 and 2 as both drugs reported the highest units sold among the 3 drugs by Biogen.

Step 2: Get the Top Results

From there on, we can simply wrap the query into a common table expression (CTE) and filter for the top 2 drugs sold (denoted as ) by each manufacturer.

A CTE is a temporary data set to be used as part of a query and it exists during the entire query session. Click here to read about it in detail.

Query output:

Step 3: Sort the output

As the final step, we will sort the result in alphabetical order by the manufacturer using the clause. Additionally, we will eliminate the unnecessary columns from the final solution.

We have completed all three steps to address the problem and concluded the final query.

Solution #1: Using CTE

Solution #2: Using Subquery