logo

8 IQVIA SQL Interview Questions (Updated 2024)

Updated on

February 14, 2024

At IQVIA, SQL is used day-to-day for analyzing real-world health data, and for managing MDM processes for ensuring data quality and consistency. That's why IQVIA frequently asks SQL coding questions in interviews for Data Analyst, Data Science, and BI jobs.

As such, to help you practice for the IQVIA SQL interview, this blog covers 8 IQVIA SQL interview questions – how many can you solve?

8 IQVIA SQL Interview Questions

SQL Question 1: Identify VIP Customers at IQVIA

IQVIA is a company that operates at the intersection of healthcare and analytics. For this company, a 'VIP Customer' could be defined as those clients who frequently purchase most or all of their healthcare-related services. Your task is to write a SQL query to identify these power users. Assume the database has two tables: and .

The table logs all purchases made by clients, including the client_id, service_id, and the date of purchase. The table lists the services provided by the company, including their service_id, service_name and cost.

Example Input:
purchase_idclient_idservice_idpurchase_date
10012078010/01/2022
10022567510/03/2022
10032078010/21/2022
10043067511/01/2022
10052067511/03/2022
Example Input:
service_idservice_namecost
675Analytics Audit5000
780Healthcare Consulting8000

Your output should contain the client_id, the total number of purchases made by the client, and total cost of those purchases.

Example Output:
client_idtotal_purchasestotal_cost
20321000
2515000
3015000

Answer:


This query first joins the purchases and services tables on the service_id field. Then it groups the results by client_id, and for each client, it calculates the total number of purchases (using COUNT(*)) and the total cost of those purchases (using SUM(s.cost)). The results are ordered by total_cost and total_purchases in descending order, so the clients who have made the most purchases and spent the most are at the top.

To solve a related super-user data analysis question on DataLemur's free interactive coding environment, try this recently asked Microsoft SQL interview question: Microsoft SQL Interview Question: Teams Super User

SQL Question 2: Analyzing Drug Sales with Window Functions

Consider you're given two tables at IQVIA, a global provider of information, innovative technology solutions, and contract research services focused on using data and science to help healthcare clients find better solutions for their patients. The first table contains drugs and their details and the second table contains sales data for the drugs.

Example Input:
drug_iddrug_namedrug_type
1DrugAType1
2DrugBType2
3DrugCType3
4DrugDType1
5DrugEType2
Example Input:
sale_iddrug_idsale_dateunits_sold
101106/08/2022 00:00:00100
102106/10/2022 00:00:00150
103206/18/2022 00:00:00200
104207/26/2022 00:00:00250
105307/05/2022 00:00:00150

The question is: Can you write a SQL query to find out monthly sales and cumulative sales over time for each drug?

The result needs to contain following columns: drug_name, sale_month, monthly_sales (i.e., total units sold in the month), cumulative_sales (i.e., total units sold until that month) grouped by drug_name and sale_month and ordered by drug_name and sale_month.

The function can be used to extract the month from sale_date in PostgreSQL. can be used to calculate the cumulative sales.

Answer:


The first part of the query calculates monthly sales for each drug. The second part of the query calculates cumulative sales for each drug. The final SELECT statement joins these two parts together to give the final result.

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

Uber Data Science SQL Interview Question

SQL Question 3: When would you use the constraint?

A is a field in a table that references the of another table. It creates a link between the two tables and ensures that the data in the field is valid.

Say for example you had sales analytics data from IQVIA's CRM (customer-relationship management) tool.


In this example, the table has a foreign key field called that references the "account_id" field in the table (the primary key). This helps to link the data about each opportunity to the corresponding account information in the accounts table.

This makes sure the insertion of rows in the table that do not have corresponding entries in the table. It also helps to enforce the relationship between the two tables and ensures that data is not deleted from the table if there are still references to it in the table.

IQVIA SQL Interview Questions

SQL Question 4: Calculate Average Duration of Clinical Trials

In your role at IQVIA, a company that provides technology solutions and contract research services to the life sciences industry, you might be asked to find the average duration of clinical trials.

The data you have available includes information on each clinical trial, such as the trial id, the disease area it is targeting, the drug being tested, and the start and end dates.

Example Input:
trial_iddisease_areastart_dateend_datedrug_being_tested
1255Oncology01/01/2021 00:00:0007/10/2021 00:00:00Drug A
2842Cardiology04/15/2021 00:00:0010/25/2021 00:00:00Drug B
5947Neurology06/20/2021 00:00:0001/29/2022 00:00:00Drug C
1729Oncology10/13/2021 00:00:0004/22/2022 00:00:00Drug D
8482Immunology12/01/2021 00:00:0006/15/2022 00:00:00Drug E
Example Output:
disease_areaavg_trial_duration_days
Oncology259.5
Cardiology193
Neurology223
Immunology196

Answer:


This PostgreSQL query calculates the average duration of clinical trials for each disease area. The 'EXTRACT(DAY FROM (end_date - start_date))' function is used to calculate the duration of each clinical trial in days. These durations are then averaged for each disease area using the AVG() function. The data is grouped by the disease_area column so that the average is calculated separately for trials related to each disease area. The results could be used to understand the time efficiency of trials in different disease areas, which could affect planning and resource allocation for future trials.

To practice a very similar question try this interactive Facebook Average Post Hiatus (Part 1) Question which is similar for calculating duration between events or this Amazon Server Utilization Time Question which is similar for calculating duration of usage.

SQL Question 5: What sets a cross join apart from a natural join?

A cross join is a type of JOIN operation in SQL that creates a new table by combining each row from the first table with every row from the second table. It is also known as a cartesian join.

For example, say you worked on the Marketing Analytics team at IQVIA, and needed to understand what advertising copy (text) goes best with what advertising creative (the photo that gets posted with the advertisement copy).

Here is an example of a cross-join between a table of ad_copy and a table of ad_creative:


A natural join, on the other hand, is a type of JOIN that combines rows from two or more tables based on their common columns. It is called a "natural" join because it is based on the natural relationship that exists between the common columns in the tables being joined.

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

An (which is a type of natural join) combines the two tables on the common


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.

One main difference between cross joins and natural joins is that cross joins do not require any common columns between the tables being joined, while natural joins do. Another difference is that cross joins can create very large tables if the input tables have a large number of rows, while natural joins will only return a table with the number of rows equal to the number of matching rows in the input tables.

SQL Question 6: Average Sales per Customer

As part of the sales team at IQVIA, you are tasked with identifying the average purchase amount for customers who bought "Pharmaceutical Products" and "Hospital & Healthcare Products". You are provided with two tables, and .

The table has details of all sales to a customer including the , , and for each purchase.

The table includes , and .

Example Input

customer_idproduct_idpurchase_amount
12003500
13002500
22004000
21001500
33002000
41001000
52004000

Example Input

product_idproduct_typeproduct_name
100"Hospital and Healthcare Products""Healthcare Equipment"
200"Pharmaceutical Products""Analgesics"
300"Pharmaceutical Products""Antipyretics"

You need to write a SQL query to find the average purchase amount for each type of product ("Pharmaceutical Products", "Hospital & Healthcare Products") bought by each customer.

Example Output

customer_idproduct_typeavg_purchase_amount
1"Pharmaceutical Products"3000
2"Pharmaceutical Products"4000
2"Hospital and Healthcare Products"1500
3"Pharmaceutical Products"2000
4"Hospital and Healthcare Products"1000
5"Pharmaceutical Products"4000

Answer


This SQL query first joins the and tables on their shared column, . It only considers rows where the is either Pharmaceutical Products or Hospital & Healthcare Products. Finally, it groups by and , calculating the average for each group.

Because joins come up so often during SQL interviews, try this interactive Snapchat JOIN SQL interview question: Snapchat SQL Interview question using JOINS

SQL Question 7: What's the SQL command do, and when would you use it?

The SQL command merges the results of multiple statements and keeps only those rows that are present in all sets.

For example, say you were doing an HR Analytics project for IQVIA, and had access to IQVIA's employees and contractors data. Assume that some employees were previously contractors, and vice versa, and thus would show up in both tables. You could use operator to find all contractors who also show up in the employees table:


SQL Question 8: Maintain Overall Sale Ratio and Average Sale Price for Regional Pharmaceutical Sales

IQVIA helps healthcare and pharmaceutical companies understand market trends. One such use case could involve maintaining the overall sale ratio of a specific medication in each region and the average sale price per unit of that medication by month. In addition to this, the company might also want to know the square root of the total income received from each region during each month.

Consider the following table where is the ID of the region where the sale is made, is the ID of the product sold, is the number of units sold, is the price sold per unit, and is the date when the sale was made:

Example Input:
sale_idregion_idproduct_idquantityprice_per_unitsale_date
10519823719.52021-08-01
20416985238.02021-08-02
221269851032.02021-08-02
35419823420.02021-08-02
40829823518.52021-08-03

Answer:

Given that, your task is to write a PostgreSQL query to calculate the overall sale ratio for each product in each region, average sale price per unit of that product and the square root of the total income (product of quantity and price per unit) from each region for each month.


The query above works by first isolating the year and month from the sale_date with . The sale ratio is then computed by dividing the quantity sold for each product by the total quantity of products sold in each region each month (calculated using ). This is followed by calculating average sale price per unit using . This helps in establishing the mean value per unit sold. Lastly, the function is used for calculating the square root of the total income generated from a region, which is the product of quantity and price_per_unit. is used to keep the results looking clean and tidy. All results are then grouped by sale_month, region_id, and product_id in the clause.

To practice a very similar question try this interactive CVS Health Pharmacy Analytics (Part 1) Question which is similar for calculating profitability of sold items or this Amazon Average Review Ratings Question which is similar for calculating averages and grouping by month.

How To Prepare for the IQVIA SQL Interview

The best way to prepare for a IQVIA SQL interview is to practice, practice, practice. In addition to solving the above IQVIA SQL interview questions, you should also solve the 200+ SQL questions from real Data Science & Analytics interviews which come from companies like FAANG tech companies and tech startups. DataLemur Question Bank

Each interview question has multiple hints, detailed solutions and crucially, there's an online SQL coding environment so you can right online code up your SQL query and have it executed.

To prep for the IQVIA SQL interview you can also be wise to solve interview questions from other tech companies like:

In case your SQL query skills are weak, forget about diving straight into solving questions – improve your SQL foundations with this interactive SQL tutorial.

Free SQL tutorial

This tutorial covers SQL concepts such as WHERE vs. HAVING and joins – both of these show up frequently in SQL job interviews at IQVIA.

IQVIA Data Science Interview Tips

What Do IQVIA Data Science Interviews Cover?

Beyond writing SQL queries, the other topics to prepare for the IQVIA Data Science Interview are:

  • Probability & Statistics Questions
  • Python or R Coding Questions
  • Product Data Science Interview Questions
  • Machine Learning Questions
  • Resume-Based Behavioral Questions

IQVIA Data Scientist

How To Prepare for IQVIA Data Science Interviews?

The best way to prepare for IQVIA Data Science interviews is by reading Ace the Data Science Interview. The book's got:

  • 201 Interview Questions from companies like Microsoft, Google & Amazon
  • A Refresher on Stats, ML, & Data Case Studies
  • Great Reviews (900+ reviews, 4.5-star rating)

Acing Data Science Interview