10 Eli Lilly SQL Interview Questions (Updated 2024)

Updated on

October 30, 2024

Eli Lilly employees rely on SQL queries to analyze and interpret complex pharmaceutical datasets, helping them track the effectiveness of new drugs and monitor patient outcomes. They also use SQL to manage the relational databases that store vital clinical trial data, ensuring that researchers have access to accurate and up-to-date information. this is the reason why Eli Lilly asks SQL questions in interviews for Data Analytics, Data Science, and Data Engineering jobs.

So, to help you prepare, we've collected 10 Eli Lilly SQL interview questions – can you solve them?

Eli Lilly SQL Interview Questions

10 Eli Lilly SQL Interview Questions

SQL Question 1: Analyze Pharmaceutical Sales

Eli Lilly is a global pharmaceutical company. Let's say they want to evaluate the sales performance of their products over time across different regions. You are given the following tables: and . The table logs every sale, including the product id, the sale date and the region of sale. The table has information about each product, including the product id and name.

Example Input:

sale_idsale_dateproduct_idregionunits_sold
12022-01-05 00:00:00101North America20
22022-01-21 00:00:00102Europe15
32022-02-10 00:00:00101Asia30
42022-02-15 00:00:00103North America12
52022-03-01 00:00:00102Europe25

Example Input:

product_idproduct_name
101Drug A
102Drug B
103Drug C

Create a PostgreSQL query to find the total units sold for each product for every month, alongside the average monthly units sold for each product across all regions. Order the result by product_name and the month of sale_date.

Example Output:

productmonthtotal_unitsavg_units
Drug A12017.5
Drug A23017.5
Drug B11520
Drug B32520
Drug C21212

Answer:


This PostgreSQL query works by joining the and tables on the .

It then groups the results by name and the month of .

The function is used to calculate the total units sold, and the function, combined with the clause (a window function), is used to calculate the average monthly units sold for each product across all regions. The result is ordered by and the month of .

To practice a similar window function interview problem which uses RANK() on DataLemur's free online SQL code editor, try this Amazon SQL question asked in a BI Engineer interview:

Amazon Business Intelligence SQL Question

SQL Question 2: Second Highest Salary

Imagine there was a table of Eli Lilly employee salary data. Write a SQL query to find the 2nd highest salary among all employees.

Eli Lilly Example Input:

employee_idsalary
12500
2800
31000
41200

Example Output:

second_highest_salary
1200

Code your solution to this interview question and run your code right in the browser:

2nd Highest Salary SQL Interview Question

Answer:


You can find a step-by-step solution here: 2nd Highest Salary.

SQL Question 3: Could you explain the differences between an inner and full outer join?

A full outer join returns all rows from both tables, including any unmatched rows, whereas an inner join only returns rows that match the join condition between the two tables.

For an example of each one, say you had sales data exported from Eli Lilly's Salesforce CRM stored in a data warehouse which had two tables: and .

: retrieves rows from both tables where there is a match in the shared key or keys.


This query will return rows from the sales and tables that have matching customer id values. Only rows with matching values will be included in the results.

: retrieves all rows from both tables, regardless of whether there is a match in the shared key or keys. If there is no match, NULL values will be returned for the columns of the non-matching table.

Here is an example of a SQL full outer join using the sales and tables:


Eli Lilly SQL Interview Questions

SQL Question 4: Managing Drug Trials at Eli Lilly

Eli Lilly is a pharmaceutical company that often conducts clinical trials for their new drugs. The following tables describe a simplified view of the trial data. A can participate in multiple and a can be part of multiple trials also. However, a entry is unique and represents a particular drug in a specific trial. After trials, patients provide based on their experience.

Design tables using the following information and write a SQL query to find the average rating for each drug across all trials.

Example Input:

patient_idnameagegender
1John Doe55M
2Jane Smith30F
3Mary Johnson45F

Example Input:

drug_idname
1Drug A
2Drug B
3Drug C

Example Input:

trial_idstart_dateend_date
101/01/202001/31/2020
202/01/202002/29/2020
303/01/202003/31/2020

Example Input:

trial_iddrug_iddosage
1150
1260
2260
3170
3280
3390

Example Input:

patient_idtrial_drug_idrating
114
123
225
234
344
352

Answer:


This query first joins the table with the table based on the . It then joins the resulted table with the table based on the . Finally, it calculates the average rating for each drug by grouping the result based on .

SQL Question 5: What does it mean for a DBMS to enforce atomicity, consistency, isolation, and durability?

A DBMS (database management system), in order to ensure transactions are relaible and don't ruin the integrity of the data, tries to mantain the following ACID properties: Atomicity, Consistency, Isolation, and Durability.

To make this concept more concrete, here is what each of the ACID properties would mean in the context of banking transactions:

  • Atomicity: a transaction is either completed fully, or not complete at all. For example, if a customer is transferring money from one account to another, the transaction should either transfer the full amount or none at all.
  • Consistency: a transaction will only be completed if it follows all database constraints and checks. For example, if a customer is withdrawing money from an account, the transaction should only be completed if the account has sufficient funds available, otherwise the transaction is rejected
  • Isolation: ensures that concurrent transactions are isolated from each other, so that the changes made by one transaction cannot be seen by another transaction. This isolation prevents race conditions, like two customers trying to withdraw money from the same account at the same time.
  • Durability: ensures that once a transaction has been committed and completed, the changes are permanent. A reset / shutdown of the database shouldn't erase someone's savings accounts!

SQL Question 6: Filter Patients based on Certain Conditions

Eli Lilly often is required to filter on its patient database for different studies. The database includes columns like , , , , , and .

Given below is the Patients' database.

Example Input:

patient_idagegenderstage_of_illnessmedicationregion
100167Male4Medication AEast
100258Female3Medication BWest
100370Male2Medication CNorth
100462Female3Medication DEast
100555Male1Medication ASouth

Write a PostgreSQL query to filter the Patients' database which should fetch the records of patients who are older than 60 years and are in the 3rd or 4th stage of the illness and have been prescribed with either Medication A or B. Also, the selected patients should not be from the East region.

Example Output:

patient_idagegenderstage_of_illnessmedicationregion
100167Male4Medication ASouth
100258Female3Medication BWest

Answer:

In PostgreSQL, you can use a combination of , and clauses to filter the records. Here's the SQL query that meets the defined conditions:


This command will first select all columns in the table (). It will then filter the database () such that it only includes patients of age over 60 (), in stages 3 or 4 of their illness (), prescribed either with or (), and they are not from the region ().

Please note that the output table is fictional as none of the patients in the given table meets all these conditions.

SQL Question 7: In SQL, Are NULL values the same as a zero or blank space?

In SQL, zero's are numerical values which can be used in calculations and comparisons just like any other number. A blank space, also known as an empty string, is a character value and can be used in character manipulation functions and comparisons.

NULLs aren't the same as zero's or blank spaces. NULLs represent unkonwn, missing, or not applicable values. They are not included in calculations and comparisons involving NULL values always result in NULL.

SQL Question 8: Calculate the AVG Medication Purchase Price.

Eli Lilly is a global pharmaceutical company. For the business, it might be beneficial to monitor the average purchase price of their medications. For this scenario, assume Eli Lilly has a table where each row represents a transaction for a specific medication.

Please write a PostgreSQL query to find the average purchase price of each medication on a monthly basis.

Here's the sample data:

Example Input:

sale_idmedication_idpurchase_dateprice
317112302/20/2022 00:00:0050
780212302/21/2022 00:00:0055
529312302/24/2022 00:00:0052
396226503/01/2022 00:00:00100
451726503/05/2022 00:00:00105

Example Output:

monthmedicationavg_price
212352.33
3265102.50

Answer:


This query uses the function to calculate the average price for each medication month by month. The function is used to get the month from the . The clause groups the data by month and medication to calculate the average price for each medication in each month. The clause is used to sort the result by month and medication.

To practice a very similar question try this interactive Amazon Highest-Grossing Items Question which is similar for requiring a monthly grouping and calculation on transaction data or this CVS Health Pharmacy Analytics (Part 1) Question which is similar for calculating profitability of products on a per-item basis.

SQL Question 9: Calculate Average Monthly Sales of Each Product

Eli Lilly is a company in the pharmaceutical industry. As part of their business strategy, they want to better understand their sales pattern. They want to know the average sales volume for each of their products on a monthly basis.

Example Input:

sales_idproduct_idsales_datequantity
11012022-04-05120
21022022-04-10200
31012022-04-20150
41032022-04-28300
51012022-05-05100
61022022-05-15150
71032022-05-25200
81012022-06-0550
91022022-06-15180
101032022-06-25240

Example Output:

monthproduct_idavg_quantity
4101135.0
4102200.0
4103300.0
5101100.0
5102150.0
5103200.0
610150.0
6102180.0
6103240.0

Answer:


This SQL statement extracts the month part from the column and for each month and , it calculates the average quantity sold (using aggregate function). The results are ordered based on the month and . This helps Eli Lilly to understand how product sales fluctuate over the months.

SQL Question 10: 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 Eli Lilly customers and a 2nd table of all purchases made with Eli Lilly. 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.

Preparing For The Eli Lilly SQL Interview

Assuming that you've already got basic SQL skills, the next best tip we have to prepare for the Eli Lilly SQL interview is to solve as many practice SQL interview questions as you can! In addition to solving the above Eli Lilly SQL interview questions, you should also solve the 200+ SQL questions from real Data Science & Analytics interviews which come from companies like Microsoft, Google, and Meta.

DataLemur SQL Interview Questions

Each exercise has multiple hints, fully explained answers along with a discussion board to see how others solved it and most importantly, there's an online SQL coding environment so you can right online code up your SQL query and have it graded.

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

In case your SQL coding skills are weak, don't worry about going right into solving questions – strengthen your SQL foundations with this SQL tutorial for Data Scientists & Analysts.

Free SQL tutorial

This tutorial covers SQL topics like RANK vs. DENSE RANK and UNION – both of which show up routinely during SQL job interviews at Eli Lilly.

Eli Lilly Data Science Interview Tips

What Do Eli Lilly Data Science Interviews Cover?

In addition to SQL interview questions, the other types of questions to practice for the Eli Lilly Data Science Interview are:

Eli Lilly Data Scientist

How To Prepare for Eli Lilly Data Science Interviews?

To prepare for Eli Lilly Data Science interviews read the book Ace the Data Science Interview because it's got:

  • 201 interview questions taken from FAANG, tech startups, and Wall Street
  • a crash course on Python, SQL & ML
  • over 1000+ 5-star reviews on Amazon

Acing Data Science Interview

Don't ignore the behavioral interview – prepare for it using this Behavioral Interview Guide for Data Scientists.

© 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