Business Question Example (Using Subquery)

Business Question (AdventureWorks):
Find all products in the Bikes category that have a list price higher than the average list price of all bikes.

SQL Query:

SELECT 
    DP.EnglishProductName,
    DP.ListPrice
FROM dbo.DimProduct AS DP
WHERE DP.ProductSubcategoryKey IN (
        SELECT ProductSubcategoryKey 
        FROM dbo.DimProductSubcategory 
        WHERE ProductCategoryKey IN (
            SELECT ProductCategoryKey 
            FROM dbo.DimProductCategory 
            WHERE EnglishProductCategoryName = 'Bikes'
        )
    )
AND DP.ListPrice > (
        SELECT AVG(ListPrice)
        FROM dbo.DimProduct AS DP2
        WHERE DP2.ProductSubcategoryKey IN (
            SELECT ProductSubcategoryKey 
            FROM dbo.DimProductSubcategory 
            WHERE ProductCategoryKey IN (
                SELECT ProductCategoryKey 
                FROM dbo.DimProductCategory 
                WHERE EnglishProductCategoryName = 'Bikes'
            )
        )
    )
ORDER BY DP.ListPrice DESC;

Healthcare Version

Business Question (Healthcare):
Find all members who have a Risk Adjustment Factor (RAF) score higher than the average RAF score in their age group.

SQL Query:

SELECT 
    MemberID,
    FullName,
    AgeGroup,
    RAF_Score
FROM Risk_Adjustment_Members AS M
WHERE RAF_Score > (
        SELECT AVG(RAF_Score)
        FROM Risk_Adjustment_Members AS M2
        WHERE M2.AgeGroup = M.AgeGroup
    )
ORDER BY AgeGroup, RAF_Score DESC;

Key Learning: Subqueries are very useful for comparing individual values against group averages — a common pattern in both retail and healthcare analytics.