Schemac

WITH TopUsers AS (
    SELECT
        USER_ID,
        OH_REGION_NAME,
        AGEGROUP,
        TOTALCOST_WITHLTC,
        TOTALCOST_LTCANDCM,
        TOTALCOST_WITHOPDP,
        NTILE(100) OVER (ORDER BY TOTALCOST_WITHLTC DESC) AS PercentileRank
    FROM
        All_Users_File
),
RegionAgeGroupCosts AS (
    SELECT
        OH_REGION_NAME,
        AGEGROUP,
        AVG(TOTALCOST_WITHLTC) AS AvgTotalCost,
        SUM(TOTALCOST_LTCANDCM) AS TotalLTCAndCM,
        SUM(TOTALCOST_WITHOPDP) AS TotalOPDP,
        COUNT(USER_ID) AS UserCount
    FROM
        TopUsers
    WHERE
        PercentileRank = 1 -- Top 1% of users
    GROUP BY
        OH_REGION_NAME,
        AGEGROUP
)
SELECT
    R.OH_REGION_NAME AS Region,
    R.AGEGROUP AS AgeGroup,
    R.AvgTotalCost,
    R.TotalLTCAndCM,
    R.TotalOPDP,
    R.UserCount
FROM
    RegionAgeGroupCosts R
ORDER BY
    R.AvgTotalCost DESC;