SQL Practice -HealthCare

Database: Adventureworks

Pro-Tip – pay extra attention to:

• GROUP BY and HAVING (Question 1)
• INNER JOIN (Question 2)
• LEFT JOIN with IS NULL (Question 3)
  
  
• Question 1: Finding High-Value Customers (The Aggregate Test)
• Healthcare Connection: In a hospital, managers often need to see which patient groups or insurance models cost the most money.
• The SQL Goal: Write a query using the FactInternetSales table. Find the total amount of money spent (SalesAmount) by each customer (CustomerKey). Only show customers who have spent more than $5,000 total, and sort them from highest to lowest.
• The Key Trick: Remember that you cannot use WHERE to filter on a sum. You must use GROUP BY and HAVING. [1, 2]

Click to see the correct SQL Code

sql

SELECT 
    CustomerKey, 
    ROUND(SUM(SalesAmount), 2) AS TotalSpent
FROM FactInternetSales
GROUP BY CustomerKey
HAVING SUM(SalesAmount) > 5000
ORDER BY TotalSpent DESC;

Use code with caution.

---

Question 2: Combining Data Tables (The Inner Join Test)

• Healthcare Connection: As an analyst, you will constantly combine a data table (like patient visits) with a lookup table (like doctor names or medical codes).
• The SQL Goal: Connect the FactInternetSales table with the DimCustomer table. Pull the customer’s first name, last name, and the total number of orders they placed.
• The Key Trick: Use an INNER JOIN on CustomerKey and use COUNT(SalesOrderNumber) to get the order totals. [1]

Click to see the correct SQL Code

sql

SELECT 
    CONCAT(C.FirstName, ' ', ISNULL(C.MiddleName, ''), C.LastName) AS FullName,
    COUNT(FIS.SalesOrderNumber) AS TotalOrders
FROM  FactInternetSales AS FIS
INNER JOIN  DimCustomer AS C 
	ON FIS.CustomerKey = C.CustomerKey
GROUP BY  C.FirstName, C.MiddleName, C.LastName
ORDER BY  TotalOrders DESC;

Use code with caution.

---

Question 3: Finding Unsold Products (The Left Join Test)

• Healthcare Connection: Hospitals often look for “gaps in care”—such as identifying patients who have an HCC diagnosis code but have not had a doctor visit this year.
• The SQL Goal: Write a query to find all products in DimProduct that have never been sold in FactInternetSales.
• The Key Trick: Use a LEFT JOIN from the product table to the sales table. Look for rows where the sales table’s key IS NULL. [1]

Click to see the correct SQL Code

Why Healthcare Analysts Prefer NOT EXISTS

There are three major reasons why the second method wins in a hospital or insurance setting:

• It is Much Safer (Handles NULLs Better): Healthcare databases are full of missing data (NULL values). If you try to use NOT IN or certain complex JOINs, NULL values can break your query or give you the wrong answer. NOT EXISTS handles missing healthcare data perfectly every time.
• It Runs Much Faster (Performance): A hospital database has millions of patient rows. A LEFT JOIN forces the computer to look at every single row in both tables and match them up before filtering. NOT EXISTS is smarter. It stops looking the exact second it finds a match, saving a lot of computer memory.
• It Reads Like Natural English: NOT EXISTS reads like a sentence: “Show me the patient info WHERE a doctor visit DOES NOT EXIST.” It matches how a medical researcher thinks.

SELECT P.ProductKey,
		P.EnglishProductName
FROM	DimProduct AS P
LEFT JOIN FactInternetSales AS FIS
	ON P.ProductKey = FIS.ProductKey
WHERE FIS.ProductKey IS NULL
ORDER BY P.ProductKey ASC;

SELECT P.ProductKey,
		P.EnglishProductName
FROM	DimProduct AS P
WHERE NOT EXISTS ( 
		SELECT *
		FROM FactInternetSales AS FIS
		WHERE FIS.ProductKey = P.ProductKey
		)
ORDER BY P.ProductKey ASC;

---