基于会员总数提取Claims中Top3%高理赔量Medicaid ID的SQL实现

Alright, let's work through this problem step by step to build a SQL query that uses CTEs to pull the top 3% of members (≈59 total) with the highest claim volumes, and dynamically adjusts the number of rows based on your total membership count (1961). Here's how to structure it:

Breakdown of Each CTE #

Let's walk through what each CTE does to make the logic clear:

1. DATES (Optional but Useful) #

This CTE defines the date range for your claims data. Adjust the start/end dates to match the period you're analyzing:

DATES AS (
    SELECT 
        CAST('2023-01-01' AS DATE) AS start_date,
        CAST('2023-12-31' AS DATE) AS end_date
)

2. membership #

This pulls your full list of unique Medicaid IDs (total 1961). Add filters here if you need to focus on active members during your target period:

membership AS (
    SELECT DISTINCT 
        medicaid_id
    FROM your_membership_table
    -- Optional: Join with DATES to filter active members
    JOIN DATES d 
        ON your_membership_table.enroll_start <= d.end_date
        AND your_membership_table.enroll_end >= d.start_date
)

3. number_to_pull #

This calculates exactly how many rows we need to pull (3% of 1961 ≈59). Using CEILING ensures we round up to the nearest whole number, so we don't miss any members:

number_to_pull AS (
    SELECT 
        CEILING(COUNT(*) * 0.03) AS top_n
    FROM membership
)

4. Claims #

This aggregates claim volumes per Medicaid ID by joining your membership list with your claims table:

Claims AS (
    SELECT 
        m.medicaid_id,
        COUNT(c.claim_id) AS total_claims
    FROM membership m
    JOIN your_claims_table c 
        ON m.medicaid_id = c.medicaid_id
    JOIN DATES d 
        ON c.claim_date BETWEEN d.start_date AND d.end_date
    GROUP BY m.medicaid_id
)

5. sum_of_claims #

This is where we dynamically filter the top N members using the top_n value from the previous CTE. We use ROW_NUMBER() to rank members by claim volume, then pick only the top ones:

sum_of_claims AS (
    SELECT 
        medicaid_id,
        total_claims
    FROM (
        SELECT 
            medicaid_id,
            total_claims,
            ROW_NUMBER() OVER (ORDER BY total_claims DESC) AS claim_rank
        FROM Claims
    ) ranked_claims
    WHERE claim_rank <= (SELECT top_n FROM number_to_pull)
)

Full Working Query #

Putting it all together, here's the complete SQL:

WITH DATES AS (
    -- Set your desired claim period here
    SELECT 
        CAST('2023-01-01' AS DATE) AS start_date,
        CAST('2023-12-31' AS DATE) AS end_date
),
membership AS (
    -- Get all unique Medicaid IDs (total 1961)
    SELECT DISTINCT 
        medicaid_id
    FROM your_membership_table
    -- Optional: Filter for members active during the date range
    JOIN DATES d 
        ON your_membership_table.enroll_start <= d.end_date
        AND your_membership_table.enroll_end >= d.start_date
),
number_to_pull AS (
    -- Calculate top 3% of total members (≈59)
    SELECT 
        CEILING(COUNT(*) * 0.03) AS top_n
    FROM membership
),
Claims AS (
    -- Calculate total claims per member
    SELECT 
        m.medicaid_id,
        COUNT(c.claim_id) AS total_claims
    FROM membership m
    JOIN your_claims_table c 
        ON m.medicaid_id = c.medicaid_id
    JOIN DATES d 
        ON c.claim_date BETWEEN d.start_date AND d.end_date
    GROUP BY m.medicaid_id
),
sum_of_claims AS (
    -- Dynamically select top N members by claim volume
    SELECT 
        medicaid_id,
        total_claims
    FROM (
        SELECT 
            medicaid_id,
            total_claims,
            ROW_NUMBER() OVER (ORDER BY total_claims DESC) AS claim_rank
        FROM Claims
    ) ranked_claims
    WHERE claim_rank <= (SELECT top_n FROM number_to_pull)
)
-- Final output: Medicaid ID and their total claim count
SELECT 
    medicaid_id,
    total_claims
FROM sum_of_claims
ORDER BY total_claims DESC;

Quick Tips #

• Replace your_membership_table and your_claims_table with your actual table names.
• If you want to include all members tied for the Nth position (instead of just cutting off), swap ROW_NUMBER() with RANK() or DENSE_RANK().
• If you prefer rounding to the nearest integer instead of up, replace CEILING with ROUND(COUNT(*) * 0.03, 0).

内容的提问来源于stack exchange，提问作者cardonas