8 Bristol-Myers SQL Interview Questions (Updated 2024)
Bristol-Myers employees write SQL queries to extract and analyze clinical trial data, helping them gain insights that are crucial for drug development research. They also use SQL to manage patient healthcare records, allowing them to track important information that supports their research efforts, for this reason, Bristol-Myers evaluates job seekers with SQL questions during interviews for Data Analytics, Data Science, and Data Engineering roles.
To help you practice for the Bristol-Myers SQL interview, we've collected 8 Bristol-Myers Squibb SQL interview questions in this blog.
8 Bristol-Myers Squibb SQL Interview Questions
SQL Question 1: Average Sales of Top 10 Medicines by Month
Write a SQL query to find the average sales of the top 10 medicines per month from the Bristol-Myers company. The data is given in two tables, one containing the sales data and the other containing the medicine details. You will have to use SQL window functions for this problem.
:
| id | medicine_id | sales_date | units_sold |
|---|---|---|---|
| 1 | 101 | 02/15/2022 | 200 |
| 2 | 102 | 02/20/2022 | 150 |
| 3 | 101 | 02/25/2022 | 250 |
| 4 | 103 | 02/28/2022 | 300 |
| 5 | 104 | 03/01/2022 | 100 |
| 6 | 105 | 03/05/2022 | 350 |
| 7 | 106 | 03/08/2022 | 300 |
| 8 | 102 | 03/15/2022 | 400 |
| 9 | 103 | 03/20/2022 | 200 |
| 10 | 101 | 03/25/2022 | 500 |
:
| medicine_id | medicine_name |
|---|---|
| 101 | Opdivo |
| 102 | Eliquis |
| 103 | Orencia |
| 104 | Yervoy |
| 105 | Reyataz |
| 106 | Sprycel |
Answer:
In this query, first we are calculating the average sales per month for each medicine using a window function. Then we are ranking these medicines based on their average sales using another window function, ROW_NUMBER(), partitioning by month and ordering by average sales in descending order. The final query fetches the top 10 medicines per month based on these rankings. We then join with the medicine table to fetch the medicine name.
Pro Tip: Window functions are a popular SQL interview topic, so practice every window function problem on DataLemur
Discover how Bristol-Myers is unleashing the power of digital technologies to enhance their research and development processes! This knowledge is essential for grasping how digital transformation is shaping the pharmaceutical industry and improving drug discovery.
SQL Question 2: Top 3 Department Salaries
Imagine there was a table of Bristol-Myers employee salary data. Write a SQL query to find the top three highest paid employees in each department.
Bristol-Myers Example Input:
| employee_id | name | salary | department_id |
|---|---|---|---|
| 1 | Emma Thompson | 3800 | 1 |
| 2 | Daniel Rodriguez | 2230 | 1 |
| 3 | Olivia Smith | 2000 | 1 |
| 4 | Noah Johnson | 6800 | 2 |
| 5 | Sophia Martinez | 1750 | 1 |
| 8 | William Davis | 6800 | 2 |
| 10 | James Anderson | 4000 | 1 |
Example Input:
| department_id | department_name |
|---|---|
| 1 | Data Analytics |
| 2 | Data Science |
Example Output:
| department_name | name | salary |
|---|---|---|
| Data Analytics | James Anderson | 4000 |
| Data Analytics | Emma Thompson | 3800 |
| Data Analytics | Daniel Rodriguez | 2230 |
| Data Science | Noah Johnson | 6800 |
| Data Science | William Davis | 6800 |
Try this interview question and run your code right in DataLemur's online SQL environment:
Answer:
We use the DENSE_RANK() window function to generate unique ranks for each employee's salary within their department, with higher salaries receiving lower ranks. Then, we wrap this up in a CTE and filter the employees with a ranking of 3 or lower.
If the solution above is hard to understand, you can find a step-by-step solution here: Top 3 Department Salaries.
SQL Question 3: What are the different kinds of joins in SQL?
A join in SQL allows you to retrieve data from multiple tables and combine it into a single set of results.
In SQL, there are four different types of JOINs. To demonstrate each kind, imagine you had two database tables, an advertising campaigns table which had information on Google Ads keywords and how much was bid for each keyword, and a sales table, which has data on how many products were sold and which Google Ads keyword drove that sale.
: An INNER JOIN retrieves rows from both tables where there is a match in the shared key or keys. For example, an INNER JOIN between the table and the table could be performed using the keyword column as the shared key. This would retrieve only the rows where the keyword in the table matches the keyword in the table.
: A LEFT JOIN retrieves all rows from the left table (in this case, the table) and any matching rows from the right table (the Sales table). If there is no match in the right table, values will be returned for the right table's columns.
: A RIGHT JOIN retrieves all rows from the right table (in this case, the Sales table) and any matching rows from the left table (the table). If there is no match in the left table, values will be returned for the left table's columns.
: A FULL OUTER JOIN retrieves all rows from both tables, regardless of whether there is a match in the shared key or keys. If there is no match, values will be returned for the columns of the non-matching table.
SQL Question 4: Click-through-rate for Bristol-Myers Drug Campaigns
The marketing team at Bristol-Myers has been running several digital ad campaigns for their different drug products. For each campaign, they've tracked every time an ad was viewed as well as the number of times these views resulted in a click to the company's website.
Your task is to calculate the click-through-rate (CTR) of each campaign. CTR is calculated by the number of clicks divided by the number of views, typically represented as a percentage.
Provide the CTR for each campaign, sorted by the CTR in descending order.
Example Input:
| campaign_id | drug |
|---|---|
| 1 | Opdivo |
| 2 | Bristol-Myers |
| 3 | Reyataz |
Example Input:
| impression_id | campaign_id | view_date |
|---|---|---|
| 4567 | 1 | 06/08/2022 |
| 5678 | 3 | 08/12/2022 |
| 6789 | 2 | 07/26/2022 |
| 7891 | 3 | 07/21/2022 |
| 8912 | 1 | 06/20/2022 |
Example Input:
| click_id | impression_id | click_date |
|---|---|---|
| 1123 | 4567 | 06/08/2022 |
| 2234 | 7891 | 07/22/2023 |
| 3345 | 8912 | 06/20/2022 |
| 4456 | 4567 | 06/09/2022 |
Answer:
This query first joins the , , and tables together using the appropriate keys. It then counts the number of unique impressions that result in a click, and divides this by the total number of unique ad impressions. Finally, it sorts the results by the CTR in descending order. Since the number of impressions or clicks per campaign could be very large, grouping by the campaign drug is more efficient than grouping by each impression.
To solve a similar problem about calculating rates, try this SQL interview question from TikTok within DataLemur's online SQL coding environment:
SQL Question 5: When would you use the function?
The function allows you to select the first non-NULL value from a list of values. It is often used to replace a NULL value with a default value in order to run aggregate functions like or on a column without being affected by values.
For example, if you conducted a customer satisfaction survey for a company and asked customers to rate their level of agreement with a statement on a scale of 1 to 10, some customers may not have answered all the questions, resulting in NULL values in the survey data:
| customer_id | question_id | agree_amount |
|---|---|---|
| 101 | 1 | 9 |
| 101 | 2 | 10 |
| 202 | 1 | 6 |
| 202 | 2 | NULL |
| 303 | 1 | 4 |
| 303 | 2 | NULL |
To replace the NULLs in the column with the default value of 5 which corresponds to neutral on the survey, you could run the following SQL query:
You'd get the following output:
| customer_id | question_id | agree_amount |
|---|---|---|
| 101 | 1 | 9 |
| 101 | 2 | 10 |
| 202 | 1 | 6 |
| 202 | 2 | 5 |
| 303 | 1 | 4 |
| 303 | 2 | 5 |
SQL Question 6: Customer and Order Join
You are working as a data analyst at Bristol-Meyers and your task is to write a SQL query to analyze the customer database and join it with the orders database to find out the total amount spent by each customer.
Here are the databases you have to work with:
Example Input:
| customer_id | first_name | last_name |
|---|---|---|
| 100 | John | Doe |
| 101 | Jane | Smith |
| 102 | Tom | Brown |
| 103 | Alice | Johnson |
| 104 | Bob | Miller |
Example Input:
| order_id | customer_id | product | quantity | price |
|---|---|---|---|---|
| 50000 | 100 | Item1 | 2 | 20.00 |
| 50001 | 101 | Item2 | 1 | 15.00 |
| 50002 | 102 | Item3 | 2 | 10.00 |
| 50003 | 103 | Item1 | 1 | 20.00 |
| 50004 | 104 | Item2 | 1 | 15.00 |
Answer:
This query first joins the and tables using the column that is common to both tables. Then, for each unique combination of and (i.e., for each customer), it computes the total amount spent by that customer (which is the quantity multiplied by the price for each order that customer has) and returns these results.
Because join questions come up routinely during SQL interviews, practice this interactive Snapchat JOIN SQL interview question:
SQL Question 7: What does the keyword do?
The clause in SQL allows you to select records that are unique, eliminating duplicates.
For example, if you had a table of Bristol-Myers employees, and wanted to see what are all the unique job titles that currently worked at the company, you could write the following query:
SQL Question 8: Calculate the Adjusted Sales and The Growth Rate
Bristol-Myers has a table named , which holds their monthly sales data for their products. The columns are , , , , , and . The shows the fractional discount given on the listed price.
Your task is to calculate the total 'Adjusted Sales' for each product on a monthly basis. 'Adjusted Sales' is calculated as the product's quantity sold multiplied by the unit price after applying the discount ().
You are also required to calculate 'Growth Rate' () on a monthly basis. Remember, if 'Adjusted Sales' for last month is zero or null, 'Growth Rate' should be treated as null.
Example Input:
| sale_id | product_id | sale_date | quantity | unit_price | discount_rate |
|---|---|---|---|---|---|
| 3463 | 20034 | 03/05/2022 | 100 | $80.5 | 0.05 |
| 4562 | 20034 | 03/12/2022 | 50 | $80.5 | 0.10 |
| 6927 | 30054 | 03/27/2022 | 200 | $45.0 | 0.20 |
| 9345 | 20034 | 04/05/2022 | 70 | $80.5 | 0.15 |
| 2847 | 30054 | 04/14/2022 | 150 | $45.0 | 0.10 |
Answer:
This query first calculates the 'Adjusted Sales' for each month per product. Then it creates another table where the 'Adjusted Sales' of last month are linked to the current month for each product. In the final step, the 'Growth Rate' is calculated using the formula given and the result is returned with each product's 'Adjusted Sales' & 'Growth Rate' on a monthly basis.
To practice a very similar question try this interactive Amazon Highest-Grossing Items Question which is similar for calculating top grossing items or this Wayfair Y-on-Y Growth Rate Question which is similar for calculating growth rates.
Bristol-Myers SQL Interview Tips
The key to acing a Bristol-Myers SQL interview is to practice, practice, and then practice some more! Beyond just solving the above Bristol-Myers SQL interview questions, you should also solve the 200+ SQL coding questions which come from companies like Netflix, Google, and Amazon.
Each DataLemur SQL question has multiple hints, detailed solutions and best of all, there's an interactive coding environment so you can instantly run your SQL query and have it graded.
To prep for the Bristol-Myers SQL interview it is also a great idea to solve SQL questions from other healthcare and pharmaceutical companies like:
But if your SQL skills are weak, forget about diving straight into solving questions – improve your SQL foundations with this SQL tutorial for Data Scientists & Analysts.
This tutorial covers things like filtering data with WHERE and math functions – both of these come up routinely in Bristol-Myers interviews.
Bristol-Myers Squibb Data Science Interview Tips
What Do Bristol-Myers Data Science Interviews Cover?
Besides SQL interview questions, the other question categories tested in the Bristol-Myers Data Science Interview include:
- Statistics & AB Testing Questions
- Python or R Coding Questions
- Business Sense and Product-Sense Questions
- ML Interview Questions
- Behavioral Interview Questions focussed on Bristol-Myers company values
How To Prepare for Bristol-Myers Data Science Interviews?
To prepare for Bristol-Myers Data Science interviews read the book Ace the Data Science Interview because it's got:
- 201 interview questions sourced from FAANG tech companies
- a crash course covering SQL, Product-Sense & ML
- over 1000+ reviews on Amazon & 4.5-star rating
Don't forget about the behavioral interview – prepare for it with this guide on behavioral interview questions.