Subqueries in Healthcare Analytics

By /

SQL Study Notes – AdventureWorksDW2017

INNER JOIN Example (Product Hierarchy)

Show total sales for Bikes by subcategory, sold more than 5 times in Northwest:

SELECT 
    PSC.EnglishProductSubcategoryName,
    COUNT(*) AS NumberOfSales,
    SUM(FIS.SalesAmount) AS TotalSales
FROM FactInternetSales FIS
INNER JOIN DimProduct P ON FIS.ProductKey = P.ProductKey
INNER JOIN DimProductSubcategory PSC ON P.ProductSubcategoryKey = PSC.ProductSubcategoryKey
INNER JOIN DimProductCategory PC ON PSC.ProductCategoryKey = PC.ProductCategoryKey
INNER JOIN DimSalesTerritory ST ON FIS.SalesTerritoryKey = ST.SalesTerritoryKey
WHERE PC.EnglishProductCategoryName = 'Bikes'
  AND ST.SalesTerritoryRegion = 'Northwest'
GROUP BY PSC.EnglishProductSubcategoryName
HAVING COUNT(*) > 5
ORDER BY TotalSales DESC;

LEFT JOIN vs NOT EXISTS – “Bikes Never Sold Online”

Goal: Find bike products that have never been sold on the internet.

1. Using LEFT JOIN + IS NULL (Most Common)

SELECT 
    P.ProductKey,
    P.EnglishProductName
FROM DimProduct P
INNER JOIN DimProductSubcategory PSC ON P.ProductSubcategoryKey = PSC.ProductSubcategoryKey
INNER JOIN DimProductCategory PC ON PSC.ProductCategoryKey = PC.ProductCategoryKey
LEFT JOIN FactInternetSales FIS ON P.ProductKey = FIS.ProductKey
WHERE PC.EnglishProductCategoryName = 'Bikes'
  AND FIS.ProductKey IS NULL
ORDER BY P.EnglishProductName;

2. Using NOT EXISTS (Subquery)

SELECT 
    P.ProductKey,
    P.EnglishProductName
FROM DimProduct P
INNER JOIN DimProductSubcategory PSC ON P.ProductSubcategoryKey = PSC.ProductSubcategoryKey
INNER JOIN DimProductCategory PC ON PSC.ProductCategoryKey = PC.ProductCategoryKey
WHERE PC.EnglishProductCategoryName = 'Bikes'
  AND NOT EXISTS (
    SELECT 1 
    FROM FactInternetSales FIS 
    WHERE FIS.ProductKey = P.ProductKey
)
ORDER BY P.EnglishProductName;

Key Takeaways for Exam

  • Use short aliases like P, PSC, PC, FIS, ST
  • Product hierarchy join path: DimProduct → DimProductSubcategory → DimProductCategory
  • LEFT JOIN + IS NULL is the most common way to find records that “do not exist”
  • NOT EXISTS is the subquery version of the same logic
  • INNER JOIN returns only matching records

Practice these patterns — they frequently appear in homework and final exams.

Scroll to Top