9 Amgen SQL Interview Questions (Updated 2024)

Updated on

October 30, 2024

At Amgen, SQL is used to analyze complex biomedical data from clinical trials, allowing researchers to evaluate treatment efficacy and safety across diverse patient populations. It also helps manage the relational databases of patient records, ensuring that all data is organized and compliant with regulations, which is why Amgen asks SQL coding questions during interviews for Data Analyst, Data Science, and BI jobs.

Thus, to help you prep, here's 9 Amgen SQL interview questions – can you solve them?

Amgen SQL Interview Questions

9 Amgen SQL Interview Questions

SQL Question 1: Analyzing Drug Efficiency

Given a table containing clinical trial data for various drugs, write a SQL query to calculate, for each drug, the average change in patient health score over all trials where the drug was used. Your output should rank the drugs in descending order of this average change, and include both drug name and average change.

The table has the following schema:

Example Input:

trial_idpatient_idstart_dateend_datedrug_namestart_health_scoreend_health_score
4201100106/01/202207/01/2022DrugA5070
6510106506/10/202207/10/2022DrugB6585
6372157806/18/202207/18/2022DrugA6070
7694112207/26/202208/26/2022DrugB8090
9172158107/05/202208/05/2022DrugC7075

We define the health score change as .

Answer:


This PostgreSQL query will group rows by the drug name, then for each group calculate the average change in health score from the start to the end of trials (as defined by the provided formula). It then orders the resulting rows in descending order of this average change.

For more window function practice, try this Uber SQL Interview Question within DataLemur's interactive SQL code editor:

Uber Data Science SQL Interview Question

SQL Question 2: Department vs. Company Salary

You're given a table of Amgen employee and department salary information. Write a SQL query to find departments where the departments average salary is higher than the company's average salary.

Code your solution to this problem directly within the browser on DataLemur:

Department vs. Company Salary

The answer is LONG – 30+ lines of SQL. You can find a detailed solution here: Department vs. Company Salary.

SQL Question 3: What are the different types of database indexes?

A database index is a way to optimize the performance of a database by reducing the amount of data that needs to be searched to retrieve a record.

There are several types of indexes:

  • unique & non-inuqie indexes
  • primary & composite indexes
  • clustered & non-clustered indexes

Amgen SQL Interview Questions

SQL Question 4: Drug Inventory Management

Amgen is a major biopharmaceutical company that specializes in a wide range of both patented and generic drugs. They have a complex distribution network that includes multiple warehouses from where drugs are supplied to various pharmacies across the nation. Suppose that the company wants to keep track of its drug stock for the purpose of inventory management. The company wants an SQL script that can determine the quantity of each drug available in each warehouse at the end of each month.

The overall database design includes two tables: and .

Example Input:

warehouse_idwarehouse_location
'W101''New York'
'W102''Los Angeles'
'W103''Chicago'

Example Input:

drug_idwarehouse_idstocktransaction_date
'D001''W101'500'2022-09-30'
'D002''W102'400'2022-09-30'
'D001''W103'200'2022-09-30'
'D002''W101'150'2022-10-31'
'D001''W102'450'2022-10-31'
'D002''W103'350'2022-10-31'
'D001''W102'650'2022-11-30'
'D002''W103'250'2022-11-30'
'D001''W101'750'2022-11-30'
'D002''W102'550'2022-11-30'

The task is to write an SQL query that would give us the quantity of each drug available in each warehouse at the end of each month.

Answer:


This PostgreSQL query uses the function to get the first day of each month from the column. It then groups the records by month, , and , and calculates the total stock for each group. The clause ensures that the result is sorted by month, warehouse, and drug. Please note that this solution assumes that every transaction records the final stock of a drug in a warehouse at the end of the month. There will need to be a different formulation if this assumption does not hold.

SQL Question 5: What's a stored procedure?

Stored procedures in SQL are like recipes in a cookbook. Just like a recipe tells you the ingredients and instructions for making a particular dish, a stored procedure tells the DBMS the logic/statements needed to perform a specific task. Just like you can use a recipe to make the same dish over and over again, you can use a stored procedure to repeat the same task multiple times with different input parameters (which is why stored procedures are so damn useful!).

Say you were a Data Analyst working on a HR analytics project. A common sub-task you might have to do is calculate the average salary for a given department at Amgen, which would be perfect for a stored procedure:


To call this stored procedure and find the average salary for the Data Science department you'd execute a query like this:


SQL Question 6: Calculating the Click-Through-Rate for Amgen Ads

Amgen is a multinational biopharmaceutical company. Let's assume the company has carried out several digital marketing campaigns for different drugs and wants to evaluate their performance.

Here’s the corrected version with appropriate use of backticks:

Use the two tables and . provides information about which ads have been clicked by users, while tells which ads have been viewed by the users. Our task is to calculate the click-through rate (CTR) for each ad. CTR is calculated as (total clicks on an ad/ total views of an ad)*100.

Example Input:

click_iduser_idclicked_datead_id
100134507/10/2022 00:00:00123
200265707/10/2022 00:30:00456
300387807/11/2022 00:00:00123
400489707/11/2022 00:30:00789

Example Input:

view_iduser_idviewed_datead_id
500134507/10/2022 00:00:00123
600265707/10/2022 00:30:00456
800365407/11/2022 00:00:00123
900489707/11/2022 00:30:00789
800523807/11/2022 01:30:00789

Answer:


This query first calculates the total number of views per ad and total clicks per ad in two separate derived tables and , respectively. Then it performs a on these tables using the . In the clause, we calculate the CTR for each ad. If an ad has no clicks, the function is used to ensure the CTR is zero, instead of .

To practice a related problem on DataLemur's free online SQL code editor, try this Meta SQL interview question:

Facebook Click-through-rate SQL Question

SQL Question 7: How do you locate records in one table that are absent from another?

To discover records in one table that are not present in another, you can utilize a and filter out any values in the right-side table.

For example, say you had a table of Amgen customers and a 2nd table of all purchases made with Amgen. To find all customers who did not make a purchase, you'd use the following


This query returns all rows from the customers table, along with any matching rows from the purchases table. If there is no matching row in the purchases table, values will be returned for all of the right table's columns. The clause then filters out any rows where the purchases.id column is , leaving only customers who have not made a purchase.

SQL Question 8: Average Sales of Amgen's Drugs

Considering that Amgen is a multinational biopharmaceutical company, let's formulate a question related to the average sales of their drugs.

You are given two tables, and . The table has columns (the product's unique identifier), (the name of the drug), and (the category which drug belongs to). The table has columns (the unique identifiers for each sale), (the identifier of the drug sold), (the date of sale), and (the revenue from the sale).

Can you write a SQL query to find the average revenue per month for each drug category in 2021?

Let's use following data as sample:

Table Example Input:

product_idproduct_namecategory
101EPOBiotech
102NeupogenOncology
103EnbrelImmunology
104ProliaBone

Table Example Input:

sale_idproduct_idsale_daterevenue
55110101/05/2021500.00
55210201/15/2021300.00
55310202/20/2021400.00
55410301/22/2021700.00

Answer:

The following SQL query can be used to answer the given question:


This query joins the sales and products tables together using the product_id. After the join is made, the query focuses on sales within the year 2021 (from January 1st to December 31st). The results are grouped by month and drug category, making it possible to calculate the average revenue for each category per month. The results are then ordered by month and category for easier reading and data interpretation.

SQL Question 9: Retrieve Customer Records Matching a Specific Pattern

Amgen is a biopharmaceutical company that develops and distributes pharmaceuticals to customers globally. You have been given a database table named . This table includes Full Name of the customers, along with other details. Your task is to write an SQL query to retrieve the records of all customers whose names start with the letter 'A'.

Below are the sample input and output.

Example Input:

customer_idfull_nameaddresscontact_number
1Jack TaylorBeverly Hills, CA906-382-2837
2Anna SmithSunnyvale, CA217-850-6451
3Robert JohnsonCupertino, CA785-618-3654
4Alice JohnsonSan Francisco, CA973-372-0857
5Aiden MoralesSacramento, CA859-283-9384

Example Output:

customer_idfull_nameaddresscontact_number
2Anna SmithSunnyvale, CA217-850-6451
4Alice JohnsonSan Francisco, CA973-372-0857
5Aiden MoralesSacramento, CA859-283-9384

Answer:


Here’s the corrected version with appropriate use of backticks:

This SQL query functions by selecting all records from the table where the starts with the letter 'A'. The keyword is used in the clause to search for a specified pattern in a column. Here, the pattern 'A%' signifies any string that starts with 'A'.

How To Prepare for the Amgen SQL Interview

The best way to prepare for a Amgen SQL interview is to practice, practice, practice. Beyond just solving the above Amgen SQL interview questions, you should also solve the 200+ DataLemur SQL Interview Questions which come from companies like Microsoft, Google, and Meta.

DataLemur SQL Interview Questions

Each interview question has hints to guide you, full answers and best of all, there is an interactive coding environment so you can right online code up your SQL query answer and have it checked.

To prep for the Amgen SQL interview you can also be wise to practice SQL problems from other healthcare and pharmaceutical companies like:

In case your SQL skills are weak, forget about jumping right into solving questions – refresh your SQL knowledge with this SQL interview tutorial.

DataLemur SQL Tutorial for Data Science

This tutorial covers things like transforming strings with CONCAT()/LOWER()/TRIM() and grouping by multiple columns – both of these come up frequently during Amgen SQL interviews.

Amgen Data Science Interview Tips

What Do Amgen Data Science Interviews Cover?

In addition to SQL query questions, the other topics to prepare for the Amgen Data Science Interview include:

Amgen Data Scientist

How To Prepare for Amgen Data Science Interviews?

I think the optimal way to prepare for Amgen Data Science interviews is to read the book Ace the Data Science Interview.

It covers 201 data interview questions taken from Facebook, Google, & Amazon. The book's also got a refresher covering Python, SQL & ML. And finally it's helped thousands of people land their dream job in data, which is why it's earned 1000+ reviews on Amazon and a 4.5-star rating.

Nick Singh author of the book Ace the Data Science Interview

While the book is more technical, it's also crucial to prepare for the Amgen behavioral interview. A good place to start is by reading the company's values and company principles.

© 2024 DataLemur, Inc

Career Resources

Free 9-Day Data Interview Crash CourseFree SQL Tutorial for Data AnalyticsUltimate SQL Interview GuideAce the Data Job Hunt Video CourseAce the Data Science InterviewBest Books for Data Analysts