9 Cardinal Health SQL Interview Questions (Updated 2024)
Cardinal Health employees write SQL queries to analyze pharmaceutical sales patterns, helping them understand how different products are performing in the market. They also use SQL to manage health inventory data, allowing them to keep track of stock levels and ensure that medications are available when needed, that is why Cardinal Health includes SQL query questions in interviews for Data Analyst, Data Science, and BI positions.
Thus, to help you study, we've curated 9 Cardinal Health SQL interview questions – can you solve them?
9 Cardinal Health SQL Interview Questions
SQL Question 1: Identify the VIP Customers for Cardinal Health
As a data analyst at Cardinal Health, you have been tasked with identifying the customers who are considered 'VIP' based on their purchasing behavior in the past year. A 'VIP' customer is defined as those who have made the highest number of purchases.
You have access to the table which has the following columns:
| Column Name | Description |
|---|---|
| purchase_id | A unique identifier for each purchase |
| user_id | The ID of the customer making the purchase |
| product_id | The ID of the product purchased |
| purchase_date | The date the purchase was made |
| amount | The amount spent on the purchase |
Example Input:
| purchase_id | user_id | product_id | purchase_date | amount |
|---|---|---|---|---|
| 101 | 6583 | 234 | 2022-06-09 | 100.00 |
| 102 | 8346 | 849 | 2022-05-11 | 150.00 |
| 103 | 6583 | 234 | 2022-07-10 | 300.00 |
| 104 | 2053 | 985 | 2022-03-04 | 400.00 |
| 105 | 8346 | 234 | 2022-06-09 | 200.00 |
Answer:
To identify VIP customers, we can count the number of purchases by each customer and arrange in descending order. Here is an example using PostgreSQL:
In this query, we are grouping purchases by users and considering only those purchases that have been made in the current year. We use the clause to only select those users who have the same or higher count as the user with the most number of purchases in the past year.
This will give us the list of users who are considered VIPs based on their purchase behaviour.
To solve a related super-user data analysis question on DataLemur's free interactive SQL code editor, try this Microsoft Teams Power User SQL Interview Question:
Explore Cardinal Health's analytics solutions to see how powerful data can drive efficiencies and empower your decision-making! Understanding their approach to analytics can provide you with insights into how data is transforming healthcare practices.
SQL Question 2: Second Highest Salary
Given a table of Cardinal Health employee salaries, write a SQL query to find the 2nd highest salary amongst all the employees.
Cardinal Health Example Input:
| employee_id | salary |
|---|---|
| 1 | 2500 |
| 2 | 800 |
| 3 | 1000 |
| 4 | 1200 |
Example Output:
| second_highest_salary |
|---|
| 1200 |
Code your solution to this interview question directly within the browser on DataLemur:
Answer:
You can find a step-by-step solution here: 2nd Highest Salary.
SQL Question 3: DBMS transactions are expected to follow the ACID properties. What are they, and what does each property mean?
To ensure the reliability and integrity of data, a database management system (DBMS) strives to maintain the ACID properties: Atomicity, Consistency, Isolation, and Durability. To illustrate these concepts, consider the following examples of how ACID properties apply to banking transactions:
- Atomicity: A transaction is either completed in full or not at all. For example, if a customer is transferring money between accounts, the transaction should either transfer the entire amount or none at all.
- Consistency: A transaction is only allowed to complete if it follows all rules and constraints within the database. For example, if a customer is withdrawing money from an account, the transaction should only be allowed to proceed if there are sufficient funds available. Otherwise, the transaction is rejected.
- Isolation: Concurrent transactions are kept separate from each other, so that the changes made by one transaction cannot be seen by another transaction until the first one is complete. This helps prevent conflicts, such as two customers attempting to withdraw money from the same account at the same time.
- Durability: Once a transaction has been committed and completed, the changes made by the transaction are permanently stored in the database and will not be lost even if the database or system crashes. For example, if a customer makes a deposit, the transaction should be durable so that the deposit is not lost in the event of a system failure.
SQL Question 4: Analyzing Product Sales Performance
Assume you are a data analyst at Cardinal Health, a healthcare services and products company. You are tasked with analyzing pharmacy product sales data to understand trends. Consider a table that records sales data for different products in different regions across the US.
Your task is to write a SQL query to find the total sales per product per month and calculate the monthly sales increase percentage using SQL window functions.
Here's the sales table:
Example Input:
| sale_id | product_id | region | sale_date | quantity |
|---|---|---|---|---|
| 1 | 1001 | "Ohio" | 2022-06-01 | 20 |
| 2 | 1001 | "Ohio" | 2022-06-15 | 30 |
| 3 | 1002 | "NY" | 2022-06-10 | 15 |
| 4 | 1001 | "Ohio" | 2022-07-05 | 25 |
| 5 | 1002 | "NY" | 2022-07-15 | 20 |
| 6 | 1002 | "NY" | 2022-07-25 | 30 |
Expected Output:
| month | product | total_sales | sales_increase_pct |
|---|---|---|---|
| 6 | 1001 | 50 | null |
| 7 | 1001 | 25 | -50.00 |
| 6 | 1002 | 15 | null |
| 7 | 1002 | 50 | 233.33 |
Answer:
In this query, we first aggregate the sales quantity for each product on a monthly basis. We then use the window function to get the previous month's total sales and calculate the sales increase percentage. If there's no sales record in the previous month, the will be null.
Pro Tip: Window functions are a frequent SQL interview topic, so practice all the window function problems on DataLemur
SQL Question 5: What sets a cross join apart from a natural join?
Imagine you are organizing a party and have two database tables: one table of people you want to invite and another list of food items you want to serve.
A cross join would be like inviting every person on your list to the party and serving them every food item on the menu, regardless of whether they like the food or not. So, if you had 10 people on your invite list and 5 food items on the menu, you would generate all 50 different combinations of people and food (10 x 5 = 50).
On the other hand, a natural join would be like inviting only the people who like the food items on the menu (based on doing a inner/left/right/outer JOIN on a common key like ).
SQL Question 6: Inventory Management and Pharmaceutical Sales
Cardinal Health, a health care services company, needs to manage its inventory across different warehouses and track the sales of its pharmaceuticals. Consider the following tables:
:
| warehouse_id | location |
|---|---|
| 1 | Ohio |
| 2 | Florida |
| 3 | California |
:
| product_id | name | price |
|---|---|---|
| 101 | Medicine A | 55 |
| 102 | Medicine B | 33 |
| 103 | Medicine C | 22 |
:
| product_id | warehouse_id | stock |
|---|---|---|
| 101 | 1 | 150 |
| 102 | 2 | 200 |
| 103 | 3 | 120 |
| 101 | 2 | 90 |
| 102 | 3 | 240 |
| 103 | 1 | 300 |
:
| sale_id | product_id | warehouse_id | quantity | date_sold |
|---|---|---|---|---|
| 1 | 101 | 1 | 30 | 2021-06-15 |
| 2 | 102 | 2 | 50 | 2021-06-15 |
| 3 | 103 | 1 | 70 | 2021-06-16 |
| 4 | 101 | 2 | 20 | 2021-06-17 |
| 5 | 102 | 3 | 60 | 2021-06-18 |
Write a PostgreSQL query that gives the sales information for each warehouse per month, including the total number of sales, total revenue, and the most sold product.
Answer:
This query first truncates the date_sold to the month level, then groups by warehouse_id and the month. It counts the total sales, calculates total revenue by multiplying quantity and price. Finally, it identifies the most sold product by grouping the sales by product_id, ordering the sum of quantity in descending order and taking the first row. It gets the name of the product using the product_id.
SQL Question 7: What are the similarities and differences between a clustered and non-clustered index?
Here's an example of a clustered index on the column of a table of Cardinal Health payments table:
This creates a clustered index on the column, which will determine the physical order of the data rows in the table.
Here is an example of a non-clustered index on the column of the same table:
This will create a non-clustered index on the column, which will not affect the physical order of the data rows in the table.
In terms of query performance, a clustered index is usually faster for searches that return a large number of records, while a non-clustered index is faster for searches that return a small number of records. However, updates to a clustered index are slower, as they require the data rows to be physically rearranged, while updates to a non-clustered index are faster, as they only require the index data structure to be updated.
SQL Question 8: Filter Customers By Product Purchase and Geographic Area
Cardinal Health, a healthcare services and products company, would like to perform a detailed analysis of its customer database. They are interested in filtering their customer and sales data to identify customers who have purchased specific products () and live in certain geographic areas ().
Make a list of the customers’ and that live in either 'California' or 'Arizona' and have made any purchases of either 20001 or 30001.
For your consideration, here are example tables for and :
Example Input:
| customer_id | name | state |
|---|---|---|
| 2345 | John Doe | California |
| 3942 | Jane Smith | Arizona |
| 2921 | Jim Brown | New York |
| 7183 | Sarah Connor | California |
| 1293 | Thomas Anderson | Texas |
Example Input:
| sale_id | customer_id | product_id | sale_date |
|---|---|---|---|
| 10001 | 2345 | 20001 | 05/08/2022 |
| 10002 | 2921 | 30001 | 05/08/2022 |
| 10003 | 3942 | 20001 | 05/10/2022 |
| 10004 | 7183 | 30001 | 06/05/2022 |
| 10005 | 1293 | 50001 | 06/22/2022 |
Answer:
In the provided PostgreSQL query, we first join the and tables on the field. We then use the WHERE clause to filter out records where the customer's state is either 'California' or 'Arizona', and the purchased product's ID is either 20001 or 30001. The result is a list of and for the customers who meet these conditions.
SQL Question 9: Average Monthly Sales per Product
Cardinal Health is a healthcare services and products company. You're given the table, containing records of all product sales. Your task is to write an SQL query to find the average sales per month for each product.
Consider a product as sold once a record exists in the table.
Example Input:
| sale_id | product_id | sale_date | quantity |
|---|---|---|---|
| 7813 | 101 | 2022-01-07 | 50 |
| 8924 | 102 | 2022-01-15 | 30 |
| 6431 | 101 | 2022-02-15 | 60 |
| 7235 | 102 | 2022-02-28 | 45 |
| 8457 | 101 | 2022-03-15 | 40 |
| 9642 | 102 | 2022-03-22 | 35 |
Example Output:
| month | product | avg_sales |
|---|---|---|
| 1 | 101 | 50 |
| 1 | 102 | 30 |
| 2 | 101 | 60 |
| 2 | 102 | 45 |
| 3 | 101 | 40 |
| 3 | 102 | 35 |
Answer:
The query would look as follows:
This query first extracts the month portion of the field using the function. Then it groups by both this extracted month and to compute the average sales per month per product using the function on the field. Finally, it orders by and for easier interpretation.
How To Prepare for the Cardinal Health SQL Interview
The key to acing a Cardinal Health SQL interview is to practice, practice, and then practice some more! Besides solving the earlier Cardinal Health SQL interview questions, you should also solve the 200+ SQL Interview Questions on DataLemur which come from companies like Microsoft, Google, Amazon, and tech startups.
Each SQL question has multiple hints, fully explained answers along with a discussion board to see how others solved it and crucially, there's an interactive coding environment so you can instantly run your SQL query and have it executed.
To prep for the Cardinal Health SQL interview it is also helpful to practice SQL problems from other healthcare and pharmaceutical companies like:
- Cigna SQL questions
- AmerisourceBergen SQL questions
- McKesson SQL questions
- UnitedHealh SQL questions
However, if your SQL foundations are weak, don't worry about jumping right into solving questions – refresh your SQL knowledge with this free SQL tutorial.
This tutorial covers SQL topics like AND/OR/NOT and rank window functions – both of these show up frequently during Cardinal Health interviews.
Cardinal Health Data Science Interview Tips
What Do Cardinal Health Data Science Interviews Cover?
Besides SQL interview questions, the other types of problems tested in the Cardinal Health Data Science Interview are:
- Probability & Stats Questions
- Coding Questions in Python or R
- Business Sense and Product-Sense Questions
- ML Modelling Questions
- Behavioral Interview Questions
How To Prepare for Cardinal Health Data Science Interviews?
To prepare for the Cardinal Health Data Science interview make sure you have a strong understanding of the company's values and mission – this will be key to acing the behavioral interview. For technical interviews get ready by reading Ace the Data Science Interview. The book's got:
- 201 Interview Questions from Facebook, Google, & Amazon
- A Refresher covering SQL, Product-Sense & ML
- Great Reviews (1000+ reviews, 4.5-star rating)