Komodo Remits: Stoploss

Takeaways:

There is no clear stoploss pattern and certainly nothing that aligns with expectations based on hospital MRF data. One reason for the noisiness may be that we are not aggregating charges and allowed amounts correctly. There is not sufficient data to prove whether aggregation is correct or not.

We also conduct volume analysis to see how many high-dollar claims exist for the top hospitals in each state (by revenue) for BUCA contracts. For United, Cigna, and Aetna, we would expect 510 * 3 = 1530 contracts (payer–provider pairs) if every top hospital in each state had contracts with these payers. We find 206 contracts with at least 1 high-cost encounter (13.5%).

Goal

Assess whether stoploss inference is feasible with Remits data. Stoploss inference requires estimation of the stoploss threshold and percentage for each contract. Feasibility will be determined based on volume and accuracy:

Accuracy

• Where we have contracts, does the estimated stoploss threshold and percentage align with expected values?
• Where we don’t have contracts, does the distribution of thresholds and percentages align with expectations?

Volume

• Are there sufficiently many contracts (payer–providers) in remits data where we can estimate stoploss threshold and percentage?

Analysis should be narrow in scope to answer questions above as thoroughly as possible within a day or two.

---

Data

• Remits data: tq_intermediate.external_komodo.remits
• Payer crosswalk: tq_production.spines.spines_payer_identifiers
• Provider crosswalk: tq_production.spines.spines_provider_hospitals_additional_npis
• Stoploss extracted from hospital MRF data: tq_dev.internal_dev_csong_sandbox.clear_rates_hospital_data_stoploss

---

Inclusion Criteria

For this analysis, only consider:

• encounters with bill_type_code = '11'
• payer/hospital exist in crosswalks (spines)

---

Methodology

Analysis

Sample/Accuracy Analysis

1. Randomly sample 30 contracts where:
   • we have enough high-cost encounters, and
   • stoploss values are extracted from hospital MRF data
2. For these 30 contracts:
   • create plots showing the distribution of allowed amounts as a percentage of total cost
   • add a horizontal line to indicate the expected values based on MRF data

Take 1: Flat Allowed Amount / Total Cost Scatter Plots and Summary Statistics

Code

# %%
df = pd.read_sql(f"""
WITH
gtr_100k AS (
    SELECT encounter_key
    FROM tq_intermediate.external_komodo.remits
    WHERE bill_type_code = '11'
    GROUP BY 1
    HAVING SUM(billed_amount) > 100000
    AND SUM(allowed_amount) > 0
),
df AS (
    SELECT 
        p.payer_id,
        s.provider_id,
        r.encounter_key,
        r.billed_amount,
        r.allowed_amount,
        sp.additional_payer_notes,
        sp.highest_number_payer
    FROM tq_intermediate.external_komodo.remits r
    JOIN tq_production.spines.spines_payer_identifiers  p
        ON TRY_CAST(r.payer_id AS INT) = CAST(p.payer_identifier_value AS INT)
    JOIN tq_production.spines.spines_provider_hospitals_additional_npis s
        ON s.npi = r.payee_npi
    JOIN tq_dev.internal_dev_csong_sandbox.clear_rates_hospital_data_stoploss sp
        ON sp.provider_id = s.provider_id
        AND sp.payer_id = p.payer_id
    WHERE p.payer_identifier_type_id = '3'
    AND r.bill_type_code = '11'
    AND r.encounter_key IN (SELECT encounter_key FROM gtr_100k)
    AND sp.additional_payer_notes IS NOT NULL
    AND sp.highest_number_payer IS NOT NULL
),
encounters AS (
    SELECT 
        payer_id, 
        provider_id, 
        encounter_key,
        MAX(additional_payer_notes) as additional_payer_notes,
        MAX(highest_number_payer) as highest_number_payer,
        sum(billed_amount) as total_billed,
        sum(allowed_amount) as total_allowed,
        sum(allowed_amount) / nullif(sum(billed_amount), 0) as percent_allowed
    FROM df
    GROUP BY 1, 2, 3
),
samples AS (
    SELECT *
    FROM (
        SELECT payer_id, provider_id
        FROM encounters
        GROUP BY 1, 2
        HAVING count(distinct encounter_key) BETWEEN 50 AND 500
    )
    ORDER BY RANDOM()
    LIMIT 30
)
SELECT 
    payer_id || '_' || provider_id AS contract_id,
    payer_id,
    provider_id,
    encounter_key,
    additional_payer_notes,
    highest_number_payer,
    total_billed,
    total_allowed,
    percent_allowed
FROM encounters
-- WHERE (payer_id, provider_id) IN (SELECT payer_id, provider_id FROM samples)
WHERE payer_id || '_' || provider_id IN (
    '42_3295',
    '76_5297',
    '643_2251',
    '388_2251',
    '391_4704',
    '52_1729',
    '42_1521',
    '454_4553',
    '42_3204',
    '510_3200',
    '42_3147',
    '7_5297',
    '42_591',
    '174_1225',
    '52_1634',
    '42_3200',
    '169_5297',
    '42_2621',
    '643_3200',
    '510_3147',
    '391_4781'
)
""", con=trino_conn)


# %%
print(df[['contract_id', 'additional_payer_notes']].drop_duplicates().to_markdown(index=False))

#
expected_stoploss = [
    ('42_3295', 380488, 0.244),
    ('76_5297', 332423, 0.6061),
    ('643_2251', 260000, None),
    ('388_2251', 73420, 0.7246),
    ('391_4704', 94500, 0.30),
    ('52_1729', 99727.58, 0.2271),
    ('42_1521', 347288, 0.585),
    ('454_4553', 67500, 0.55),
    ('42_3204', 100000, None),
    ('510_3200', 250000, None),
    ('42_3147', 100000, None),
    ('7_5297', 176384, 0.777),
    ('42_591', 181076.10, 0.7251),
    ('174_1225', None, None),
    ('52_1634', 99727.58, 0.2271),
    ('42_3200', 100000, None),
    ('169_5297', 290427, 0.635),
    ('42_2621', 92000, 0.60),
    ('643_3200', 250000, None),
    ('510_3147', 202517, 0.594),
    ('391_4781', 104000, 0.45),
]
df_expected = pd.DataFrame(expected_stoploss, columns=['contract_id', 'expected_stoploss_amount', 'expected_stoploss_percent'])
df_merged = df.merge(df_expected, on='contract_id', how='left')


# %%
for contract_id in df_merged['contract_id'].unique():
    df_contract = df_merged[df_merged['contract_id'] == contract_id]
    expected_percent = df_contract['expected_stoploss_percent'].iloc[0]
    expected_stoploss_amount = df_contract['expected_stoploss_amount'].iloc[0]
    if expected_percent > 0:
        ax = sns.scatterplot(data=df_contract, x='total_billed', y='percent_allowed')
        ax.set_ylim(0, 1)
        ax.set_xlim(0, min(max(df_contract['total_billed']), 2e6))
        plt.axhline(expected_percent, color='red', linestyle='--', label='Expected Stoploss Percent')
        plt.axvline(expected_stoploss_amount, color='blue', linestyle='--', label='Expected Stoploss Amount')
        plt.title(f'Payer_Provider: {contract_id}')
        plt.legend()
        # save as png
        plt.savefig(f'plots/stoploss_scatter_{contract_id}.png')
        plt.show()

Results

contract_id	payer_id	provider_id	expected_stoploss_amount	expected_stoploss_percent	encounter_count	p05	p25	p50	p75	p95
169_5297	169	5297	290427	0.635	592	0.14135	0.253054	0.381893	0.608586	1
388_2251	388	2251	73420	0.7246	304	0.0014516	0.0128525	0.03224	0.399664	0.618201
391_4704	391	4704	94500	0.3	1292	0.0374963	0.193629	0.27227	0.383736	0.584909
391_4781	391	4781	104000	0.45	365	0.0175486	0.173704	0.226448	0.318637	0.473881
42_1521	42	1521	347288	0.585	994	0.0078552	0.113604	0.189798	0.324892	0.618703
42_3147	42	3147	100000	nan	336	0.00039325	0.00092225	0.110713	0.480914	0.933049
42_3200	42	3200	100000	nan	3897	0.00033565	0.00639175	0.255987	0.512539	0.954417
42_3295	42	3295	380488	0.244	217	0.0004258	0.001072	0.021523	0.117421	0.307892
42_591	42	591	181076	0.7251	1674	0.0003517	0.016899	0.317202	0.538068	0.983149
454_4553	454	4553	67500	0.55	837	0.0071898	0.013442	0.02035	0.440173	1
510_3200	510	3200	250000	nan	1106	0	0.00516225	0.086977	0.163594	0.306241
52_1634	52	1634	99727.6	0.2271	2054	0.0866703	0.221318	0.285187	0.414152	0.602497
52_1729	52	1729	99727.6	0.2271	420	0.0148072	0.212728	0.247924	0.351015	0.564136
643_2251	643	2251	260000	nan	543	0.0657102	0.201895	0.309173	0.479418	0.70984
643_3200	643	3200	250000	nan	5516	0	0.0617272	0.180669	0.299559	0.605435
76_5297	76	5297	332423	0.6061	316	0.0133334	0.27028	0.466917	0.730688	0.938858
7_5297	7	5297	176384	0.777	507	0.0006359	0.172855	0.331072	0.488043	0.799535

Take 2: Second Dollar Allowed Amount / Total Cost Scatter Plots and Summary Statistics

Difference from section above: only consider allowed amounts after stoploss threshold is exceeded.

df['allowed_less_stoploss'] = df['total_allowed'] - df['expected_stoploss_amount']
df['charges_less_stoploss'] = df['total_billed'] - df['expected_stoploss_amount']
df = df.loc[df['allowed_less_stoploss'] >= 0]
df['percent_allowed'] = df['allowed_less_stoploss'] / df['charges_less_stoploss']

Code

df = pd.read_sql(f"""
WITH
gtr_100k AS (
    SELECT encounter_key
    FROM tq_intermediate.external_komodo.remits
    WHERE bill_type_code = '11'
    GROUP BY 1
    HAVING SUM(billed_amount) > 100000
    AND SUM(allowed_amount) > 0
),
df AS (
    SELECT 
        p.payer_id,
        s.provider_id,
        r.encounter_key,
        r.billed_amount,
        r.allowed_amount,
        sp.additional_payer_notes,
        sp.highest_number_payer
    FROM tq_intermediate.external_komodo.remits r
    JOIN tq_production.spines.spines_payer_identifiers  p
        ON TRY_CAST(r.payer_id AS INT) = CAST(p.payer_identifier_value AS INT)
    JOIN tq_production.spines.spines_provider_hospitals_additional_npis s
        ON s.npi = r.payee_npi
    JOIN tq_dev.internal_dev_csong_sandbox.clear_rates_hospital_data_stoploss sp
        ON sp.provider_id = s.provider_id
        AND sp.payer_id = p.payer_id
    WHERE p.payer_identifier_type_id = '3'
    AND r.bill_type_code = '11'
    AND r.encounter_key IN (SELECT encounter_key FROM gtr_100k)
    AND sp.additional_payer_notes IS NOT NULL
    AND sp.highest_number_payer IS NOT NULL
),
encounters AS (
    SELECT 
        payer_id, 
        provider_id, 
        encounter_key,
        MAX(additional_payer_notes) as additional_payer_notes,
        MAX(highest_number_payer) as highest_number_payer,
        sum(billed_amount) as total_billed,
        sum(allowed_amount) as total_allowed,
        sum(allowed_amount) / nullif(sum(billed_amount), 0) as percent_allowed
    FROM df
    GROUP BY 1, 2, 3
),
samples AS (
    SELECT *
    FROM (
        SELECT payer_id, provider_id
        FROM encounters
        GROUP BY 1, 2
        HAVING count(distinct encounter_key) BETWEEN 50 AND 500
    )
    ORDER BY RANDOM()
    LIMIT 30
)
SELECT 
    payer_id || '_' || provider_id AS contract_id,
    payer_id,
    provider_id,
    encounter_key,
    additional_payer_notes,
    highest_number_payer,
    total_billed,
    total_allowed
FROM encounters
-- WHERE (payer_id, provider_id) IN (SELECT payer_id, provider_id FROM samples)
WHERE payer_id || '_' || provider_id IN (
    '42_3295',
    '76_5297',
    '643_2251',
    '388_2251',
    '391_4704',
    '52_1729',
    '42_1521',
    '454_4553',
    '42_3204',
    '510_3200',
    '42_3147',
    '7_5297',
    '42_591',
    '174_1225',
    '52_1634',
    '42_3200',
    '169_5297',
    '42_2621',
    '643_3200',
    '510_3147',
    '391_4781'
)
""", con=trino_conn)


# %%
print(df[['contract_id', 'additional_payer_notes']].drop_duplicates().to_markdown(index=False))

#
expected_stoploss = [
    ('42_3295', 380488, 0.244),
    ('76_5297', 332423, 0.6061),
    ('643_2251', 260000, None),
    ('388_2251', 73420, 0.7246),
    ('391_4704', 94500, 0.30),
    ('52_1729', 99727.58, 0.2271),
    ('42_1521', 347288, 0.585),
    ('454_4553', 67500, 0.55),
    ('42_3204', 100000, None),
    ('510_3200', 250000, None),
    ('42_3147', 100000, None),
    ('7_5297', 176384, 0.777),
    ('42_591', 181076.10, 0.7251),
    ('174_1225', None, None),
    ('52_1634', 99727.58, 0.2271),
    ('42_3200', 100000, None),
    ('169_5297', 290427, 0.635),
    ('42_2621', 92000, 0.60),
    ('643_3200', 250000, None),
    ('510_3147', 202517, 0.594),
    ('391_4781', 104000, 0.45),
]
df_expected = pd.DataFrame(expected_stoploss, columns=['contract_id', 'expected_stoploss_amount', 'expected_stoploss_percent'])
df_merged = df.merge(df_expected, on='contract_id', how='left')

# %%
df_merged['allowed_less_stoploss'] = df_merged['total_allowed'] - df_merged['expected_stoploss_amount']
df_merged['charges_less_stoploss'] = df_merged['total_billed'] - df_merged['expected_stoploss_amount']
df_merged = df_merged.loc[df_merged['allowed_less_stoploss'] >= 0]
df_merged['percent_allowed'] = df_merged['allowed_less_stoploss'] / df_merged['charges_less_stoploss']

# %%
for contract_id in df_merged['contract_id'].unique():
    df_contract = df_merged[df_merged['contract_id'] == contract_id]
    expected_percent = df_contract['expected_stoploss_percent'].iloc[0]
    expected_stoploss_amount = df_contract['expected_stoploss_amount'].iloc[0]
    if len(df_contract.loc[df_contract['total_billed']<2e6]) < 10:
        continue
    if expected_percent > 0:
        ax = sns.scatterplot(data=df_contract, x='total_billed', y='percent_allowed')
        ax.set_ylim(0, 1)
        ax.set_xlim(0, min(max(df_contract['total_billed']), 2e6))
        plt.axhline(expected_percent, color='red', linestyle='--', label='Expected Stoploss Percent')
        plt.axvline(expected_stoploss_amount, color='blue', linestyle='--', label='Expected Stoploss Amount')
        plt.title(f'Payer_Provider: {contract_id}')
        plt.legend()
        # save as png
        plt.savefig(f'plots_second_dollar/stoploss_scatter_{contract_id}.png')
        plt.show()


# %%
percentiles = [0.05, 0.25, 0.5, 0.75, 0.95]

df_summary = (
    df_merged
    .groupby(
        [
            'contract_id',
            'payer_id',
            'provider_id',
            'expected_stoploss_amount',
            'expected_stoploss_percent',
        ],
        dropna=False
    )
    .agg(
        encounter_count=('encounter_key', 'nunique'),
        p05=('percent_allowed', lambda x: x.quantile(0.05)),
        p25=('percent_allowed', lambda x: x.quantile(0.25)),
        p50=('percent_allowed', lambda x: x.quantile(0.50)),
        p75=('percent_allowed', lambda x: x.quantile(0.75)),
        p95=('percent_allowed', lambda x: x.quantile(0.95)),
    )
    .reset_index()
)
print(df_summary.to_markdown(index=False))

Results

contract_id	payer_id	provider_id	expected_stoploss_amount	expected_stoploss_percent	encounter_count	p05	p25	p50	p75	p95
169_5297	169	5297	290427	0.635	272	0.0376996	0.198965	0.402271	0.759511	1
388_2251	388	2251	73420	0.7246	131	0.0524143	0.211948	0.3306	0.518541	0.637591
391_4704	391	4704	94500	0.3	654	0.0131984	0.0685662	0.135729	0.246995	0.482096
391_4781	391	4781	104000	0.45	118	0.0177519	0.0493295	0.106947	0.154044	0.322301
42_1521	42	1521	347288	0.585	14	0.0337878	0.0514004	0.129379	0.268161	0.363054
42_2621	42	2621	92000	0.6	1	0.0498745	0.0498745	0.0498745	0.0498745	0.0498745
42_3147	42	3147	100000	nan	40	0.0368339	0.173552	0.421006	0.66636	1
42_3200	42	3200	100000	nan	1055	0.0212657	0.13842	0.322802	0.683058	1
42_3204	42	3204	100000	nan	5	0.0342118	0.169052	0.218186	0.390123	0.63194
42_591	42	591	181076	0.7251	1266	0.0169576	0.239322	0.414806	0.612172	1
454_4553	454	4553	67500	0.55	138	0.114381	0.35056	1	1	1
510_3200	510	3200	250000	nan	12	0.00898586	0.0282423	0.056171	0.107712	0.14864
52_1634	52	1634	99727.6	0.2271	1961	0.105973	0.221051	0.286644	0.414493	0.602048
52_1729	52	1729	99727.6	0.2271	393	0.0938355	0.219413	0.249024	0.349268	0.556422
643_2251	643	2251	260000	nan	21	0.0346848	0.180523	0.273179	0.36027	1
643_3200	643	3200	250000	nan	10	0.0118385	0.0242027	0.0303235	0.0656462	0.100539
76_5297	76	5297	332423	0.6061	144	0.028269	0.194409	0.557989	0.679707	0.945095
7_5297	7	5297	176384	0.777	201	0.0273271	0.115174	0.268855	0.530093	0.888584

Take 3: Exclude Drugs and Implants

In ADDITION to second dollar calculation, exclude encounters where revenue_code NOT IN ('0636', '0343', '0344', '0274', '0275', '0276', '0278')

Code

# %%
df = pd.read_sql(f"""
WITH
gtr_100k AS (
    SELECT encounter_key
    FROM tq_intermediate.external_komodo.remits
    WHERE bill_type_code = '11'
    AND revenue_code NOT IN (
        '0636', 
        '0343', 
        '0344', 
        '0274', 
        '0275', 
        '0276', 
        '0278'
    )
    GROUP BY 1
    HAVING SUM(billed_amount) > 100000
    AND SUM(allowed_amount) > 0
),
df AS (
    SELECT 
        p.payer_id,
        s.provider_id,
        r.encounter_key,
        r.billed_amount,
        r.allowed_amount,
        sp.additional_payer_notes,
        sp.highest_number_payer
    FROM tq_intermediate.external_komodo.remits r
    JOIN tq_production.spines.spines_payer_identifiers  p
        ON TRY_CAST(r.payer_id AS INT) = CAST(p.payer_identifier_value AS INT)
    JOIN tq_production.spines.spines_provider_hospitals_additional_npis s
        ON s.npi = r.payee_npi
    JOIN tq_dev.internal_dev_csong_sandbox.clear_rates_hospital_data_stoploss sp
        ON sp.provider_id = s.provider_id
        AND sp.payer_id = p.payer_id
    WHERE p.payer_identifier_type_id = '3'
    AND r.bill_type_code = '11'
    AND revenue_code NOT IN (
        '0636', 
        '0343', 
        '0344', 
        '0274', 
        '0275', 
        '0276', 
        '0278'
    )
    AND r.encounter_key IN (SELECT encounter_key FROM gtr_100k)
    AND sp.additional_payer_notes IS NOT NULL
    AND sp.highest_number_payer IS NOT NULL
),
encounters AS (
    SELECT 
        payer_id, 
        provider_id, 
        encounter_key,
        MAX(additional_payer_notes) as additional_payer_notes,
        MAX(highest_number_payer) as highest_number_payer,
        sum(billed_amount) as total_billed,
        sum(allowed_amount) as total_allowed,
        sum(allowed_amount) / nullif(sum(billed_amount), 0) as percent_allowed
    FROM df
    GROUP BY 1, 2, 3
),
samples AS (
    SELECT *
    FROM (
        SELECT payer_id, provider_id
        FROM encounters
        GROUP BY 1, 2
        HAVING count(distinct encounter_key) BETWEEN 50 AND 500
    )
    ORDER BY RANDOM()
    LIMIT 30
)
SELECT 
    payer_id || '_' || provider_id AS contract_id,
    payer_id,
    provider_id,
    encounter_key,
    additional_payer_notes,
    highest_number_payer,
    total_billed,
    total_allowed
FROM encounters
-- WHERE (payer_id, provider_id) IN (SELECT payer_id, provider_id FROM samples)
WHERE payer_id || '_' || provider_id IN (
    '42_3295',
    '76_5297',
    '643_2251',
    '388_2251',
    '391_4704',
    '52_1729',
    '42_1521',
    '454_4553',
    '42_3204',
    '510_3200',
    '42_3147',
    '7_5297',
    '42_591',
    '174_1225',
    '52_1634',
    '42_3200',
    '169_5297',
    '42_2621',
    '643_3200',
    '510_3147',
    '391_4781'
)
""", con=trino_conn)


# %%
print(df[['contract_id', 'additional_payer_notes']].drop_duplicates().to_markdown(index=False))

#
expected_stoploss = [
    ('42_3295', 380488, 0.244),
    ('76_5297', 332423, 0.6061),
    ('643_2251', 260000, None),
    ('388_2251', 73420, 0.7246),
    ('391_4704', 94500, 0.30),
    ('52_1729', 99727.58, 0.2271),
    ('42_1521', 347288, 0.585),
    ('454_4553', 67500, 0.55),
    ('42_3204', 100000, None),
    ('510_3200', 250000, None),
    ('42_3147', 100000, None),
    ('7_5297', 176384, 0.777),
    ('42_591', 181076.10, 0.7251),
    ('174_1225', None, None),
    ('52_1634', 99727.58, 0.2271),
    ('42_3200', 100000, None),
    ('169_5297', 290427, 0.635),
    ('42_2621', 92000, 0.60),
    ('643_3200', 250000, None),
    ('510_3147', 202517, 0.594),
    ('391_4781', 104000, 0.45),
]
df_expected = pd.DataFrame(expected_stoploss, columns=['contract_id', 'expected_stoploss_amount', 'expected_stoploss_percent'])
df_merged = df.merge(df_expected, on='contract_id', how='left')

# %%
df_merged['allowed_less_stoploss'] = df_merged['total_allowed'] - df_merged['expected_stoploss_amount']
df_merged['charges_less_stoploss'] = df_merged['total_billed'] - df_merged['expected_stoploss_amount']
df_merged = df_merged.loc[df_merged['allowed_less_stoploss'] >= 0]
df_merged['percent_allowed'] = df_merged['allowed_less_stoploss'] / df_merged['charges_less_stoploss']

# %%
for contract_id in df_merged['contract_id'].unique():
    df_contract = df_merged[df_merged['contract_id'] == contract_id]
    expected_percent = df_contract['expected_stoploss_percent'].iloc[0]
    expected_stoploss_amount = df_contract['expected_stoploss_amount'].iloc[0]
    print(contract_id, len(df_contract))
    if len(df_contract.loc[df_contract['total_billed']<2e6]) < 10:
        continue
    if expected_percent > 0:
        ax = sns.scatterplot(data=df_contract, x='total_billed', y='percent_allowed')
        ax.set_ylim(0, 1)
        ax.set_xlim(0, min(max(df_contract['total_billed']), 2e6))
        plt.axhline(expected_percent, color='red', linestyle='--', label='Expected Stoploss Percent')
        plt.axvline(expected_stoploss_amount, color='blue', linestyle='--', label='Expected Stoploss Amount')
        plt.title(f'Payer_Provider: {contract_id}')
        plt.legend()
        # save as png
        plt.savefig(f'plots_second_dollar_no_drug_implants/stoploss_scatter_{contract_id}.png')
        plt.show()


# %%
percentiles = [0.05, 0.25, 0.5, 0.75, 0.95]

df_summary = (
    df_merged
    .groupby(
        [
            'contract_id',
            'payer_id',
            'provider_id',
            'expected_stoploss_amount',
            'expected_stoploss_percent',
        ],
        dropna=False
    )
    .agg(
        encounter_count=('encounter_key', 'nunique'),
        p05=('percent_allowed', lambda x: x.quantile(0.05)),
        p25=('percent_allowed', lambda x: x.quantile(0.25)),
        p50=('percent_allowed', lambda x: x.quantile(0.50)),
        p75=('percent_allowed', lambda x: x.quantile(0.75)),
        p95=('percent_allowed', lambda x: x.quantile(0.95)),
    )
    .reset_index()
)
print(df_summary.to_markdown(index=False))

Results

---

Volume Analysis

1. Create a table where each row is a contract with the following statistics:
   • count of encounters with total charges > $200k
2. Using Clear Rates whispers, identify the top 10 hospitals in each state based on revenue.
3. Using the table from (1), filter to:
   • contracts that exist in the hospital list from (2), and
   • payer is united, aetna, cigna, blues

Code

df = pd.read_sql(f"""
WITH
provider_names AS (
    SELECT 
        DISTINCT
        provider_id,
        provider_name
    FROM tq_production.spines.spines_provider_hospitals
),
payer_names AS (
    SELECT 
        DISTINCT
        payer_id,
        payer_name
    FROM tq_production.spines.spines_payer
),
ranked AS (
    SELECT 
        state,
        provider_id,
        ROW_NUMBER() OVER (PARTITION BY state ORDER BY provider_net_patient_revenue_state_rank ASC) AS rn
    FROM tq_dev.internal_dev_csong_cld_v2_3_1.prod_rollup_provider
),
top_providers AS (
    SELECT *
    FROM ranked
    WHERE rn <= 10
    AND state NOT IN ('VI', 'PR')
),
inpatient_claims AS (
    SELECT DISTINCT
        encounter_key
    FROM tq_intermediate.external_komodo.remits
    WHERE (
        bill_type_code = '11'
    ) OR (
        drg_code IS NOT NULL
        AND payee_npi IN (
        SELECT npi
        FROM tq_production.spines.spines_provider_hospitals_additional_npis
        )
    )
),
df AS (
    SELECT 
        tp.state,
        p.payer_id,
        s.provider_id,
        r.encounter_key,
        r.billed_amount,
        r.allowed_amount
    FROM tq_intermediate.external_komodo.remits r
    JOIN tq_production.spines.spines_payer_identifiers  p
        ON TRY_CAST(r.payer_id AS INT) = CAST(p.payer_identifier_value AS INT)
    JOIN tq_production.spines.spines_provider_hospitals_additional_npis s on
        s.npi = r.payee_npi
    JOIN top_providers tp
        ON s.provider_id = tp.provider_id
    WHERE p.payer_identifier_type_id = '3'
    AND r.encounter_key IN (
        SELECT encounter_key FROM inpatient_claims
    )
    AND p.payer_id IN (
        '643', '76', '7', 
        '42','43','44','46','47','48','49','50','51','52','53','54','56','61','62',
        '101','160','165','166','168','169','170','174','229','383','388','389',
        '390','391','392','397','398','403','454','455','456','458','461','462',
        '464','628','729','837','914', '952'
    )
),
encounters AS (
    SELECT 
        state,
        payer_id, 
        provider_id, 
        encounter_key,
        sum(billed_amount) as total_billed,
        sum(allowed_amount) as total_allowed,
        sum(allowed_amount) / nullif(sum(billed_amount), 0) as percent_allowed
    FROM df
    WHERE encounter_key IS NOT NULL
    AND allowed_amount > 0
    GROUP BY 1, 2, 3, 4
    HAVING sum(billed_amount) > 200000
)
SELECT 
    state,
    enc.payer_id,
    enc.provider_id,
    payn.payer_name,
    pn.provider_name,
    count(distinct encounter_key) as n_encounters
FROM encounters enc
LEFT JOIN provider_names pn
    ON enc.provider_id = pn.provider_id
LEFT JOIN payer_names payn
    ON enc.payer_id = payn.payer_id
GROUP BY 1,2,3,4,5
ORDER BY 1, n_encounters DESC
""", con=trino_conn)

# %%
df

# %%
print(df.to_markdown(index=False))
df.loc[df['payer_id'].isin(['7', '76', '643']), :]

# %%
df.to_excel('stoploss_coverage_by_top_providers.xlsx', index=False)

Results

state	payer_id	provider_id	payer_name	provider_name	n_encounters
AK	628	6703	Premera Blue Cross	Bartlett Regional Hospital	8
AK	7	6703	Aetna	Bartlett Regional Hospital	1
AL	160	3510	Blue Cross Blue Shield of Alabama	Childrens of Alabama	69
AL	76	3520	Cigna	UAB Hospital	28
AL	643	3520	UnitedHealthcare	UAB Hospital	27
AL	7	3520	Aetna	UAB Hospital	8
AL	42	3520	Anthem	UAB Hospital	2
AL	76	3510	Cigna	Childrens of Alabama	1
AR	454	4553	Blue Cross Blue Shield of Arkansas	St Bernards Medical Center	25
AR	643	4553	UnitedHealthcare	St Bernards Medical Center	3
AR	76	4553	Cigna	St Bernards Medical Center	1
AZ	643	5919	UnitedHealthcare	Banner - University Medical Center Phoenix	728
AZ	643	5960	UnitedHealthcare	Banner - University Medical Center Tucson	570
AZ	43	5919	Blue Cross Blue Shield of Arizona	Banner - University Medical Center Phoenix	518
AZ	643	5872	UnitedHealthcare	Banner Desert Medical Center	385
AZ	43	5872	Blue Cross Blue Shield of Arizona	Banner Desert Medical Center	373
AZ	643	5871	UnitedHealthcare	Banner Thunderbird Medical Center	352
AZ	43	5960	Blue Cross Blue Shield of Arizona	Banner - University Medical Center Tucson	326
AZ	43	5871	Blue Cross Blue Shield of Arizona	Banner Thunderbird Medical Center	200
AZ	76	5919	Cigna	Banner - University Medical Center Phoenix	114
AZ	76	5872	Cigna	Banner Desert Medical Center	94
AZ	76	5871	Cigna	Banner Thunderbird Medical Center	59
AZ	76	5960	Cigna	Banner - University Medical Center Tucson	45
AZ	7	5871	Aetna	Banner Thunderbird Medical Center	3
AZ	7	5872	Aetna	Banner Desert Medical Center	3
AZ	76	5876	Cigna	Tucson Medical Center	2
AZ	42	5876	Anthem	Tucson Medical Center	1
AZ	7	5919	Aetna	Banner - University Medical Center Phoenix	1
AZ	7	5960	Aetna	Banner - University Medical Center Tucson	1
AZ	643	5876	UnitedHealthcare	Tucson Medical Center	1
AZ	7	5876	Aetna	Tucson Medical Center	1
CA	42	6546	Anthem	Stanford University Hospital	3001
CA	403	6546	Blue Shield of California	Stanford University Hospital	2277
CA	643	6546	UnitedHealthcare	Stanford University Hospital	648
CA	76	6546	Cigna	Stanford University Hospital	593
CA	643	6646	UnitedHealthcare	Lucile Packard Childrens Hospital Stanford	343
CA	7	6546	Aetna	Stanford University Hospital	240
CA	42	6646	Anthem	Lucile Packard Childrens Hospital Stanford	231
CA	403	6646	Blue Shield of California	Lucile Packard Childrens Hospital Stanford	73
CA	7	6646	Aetna	Lucile Packard Childrens Hospital Stanford	37
CA	76	6646	Cigna	Lucile Packard Childrens Hospital Stanford	28
CA	52	6546	Blue Cross Blue Shield of North Carolina	Stanford University Hospital	4
CA	44	6546	Blue Cross Blue Shield of Maryland & DC (CareFirst)	Stanford University Hospital	3
CA	392	6546	Blue Cross Blue Shield of Massachusetts	Stanford University Hospital	3
CA	383	6546	Blue Cross of Idaho	Stanford University Hospital	2
CA	229	6546	Blue Cross Blue Shield of New Jersey (Horizon)	Stanford University Hospital	2
CO	643	5722	UnitedHealthcare	HCA HealthONE Sky Ridge	1
CT	42	747	Anthem	Hartford Hospital	165
CT	643	747	UnitedHealthcare	Hartford Hospital	60
CT	42	752	Anthem	Stamford Hospital	56
CT	42	753	Anthem	St Vincents Medical Center	51
CT	76	747	Cigna	Hartford Hospital	43
CT	76	753	Cigna	St Vincents Medical Center	16
CT	76	752	Cigna	Stamford Hospital	11
CT	7	747	Aetna	Hartford Hospital	9
CT	643	753	UnitedHealthcare	St Vincents Medical Center	9
CT	643	752	UnitedHealthcare	Stamford Hospital	8
CT	397	747	Blue Cross Blue Shield of Rhode Island	Hartford Hospital	2
CT	7	753	Aetna	St Vincents Medical Center	2
CT	7	752	Aetna	Stamford Hospital	1
DC	44	1434	Blue Cross Blue Shield of Maryland & DC (CareFirst)	MedStar Washington Hospital Center	877
DC	44	1435	Blue Cross Blue Shield of Maryland & DC (CareFirst)	MedStar Georgetown University Hospital	806
DC	643	1434	UnitedHealthcare	MedStar Washington Hospital Center	316
DC	643	1435	UnitedHealthcare	MedStar Georgetown University Hospital	271
DC	76	1434	Cigna	MedStar Washington Hospital Center	172
DC	76	1435	Cigna	MedStar Georgetown University Hospital	166
DC	42	1435	Anthem	MedStar Georgetown University Hospital	19
DC	643	1438	UnitedHealthcare	MedStar National Rehabilitation Hospital	5
DC	7	1434	Aetna	MedStar Washington Hospital Center	3
DC	76	1438	Cigna	MedStar National Rehabilitation Hospital	2
DC	42	1434	Anthem	MedStar Washington Hospital Center	1
DC	44	1438	Blue Cross Blue Shield of Maryland & DC (CareFirst)	MedStar National Rehabilitation Hospital	1
DC	7	1435	Aetna	MedStar Georgetown University Hospital	1
DE	7	1357	Aetna	Nemours Childrens Hospital - Delaware	170
DE	229	1357	Blue Cross Blue Shield of New Jersey (Horizon)	Nemours Childrens Hospital - Delaware	73
DE	174	1355	Highmark Blue Cross Blue Shield	Beebe Healthcare - Margaret H Rollins Lewes Campus	68
DE	56	1357	Blue Cross Blue Shield of Pennsylvania (Independence)	Nemours Childrens Hospital - Delaware	50
DE	643	1357	UnitedHealthcare	Nemours Childrens Hospital - Delaware	31
DE	174	1357	Highmark Blue Cross Blue Shield	Nemours Childrens Hospital - Delaware	25
DE	643	1355	UnitedHealthcare	Beebe Healthcare - Margaret H Rollins Lewes Campus	10
DE	76	1357	Cigna	Nemours Childrens Hospital - Delaware	10
DE	76	1355	Cigna	Beebe Healthcare - Margaret H Rollins Lewes Campus	2
DE	7	1356	Aetna	TidalHealth Nanticoke	1
FL	388	2251	Blue Cross Blue Shield of Florida (Florida Blue)	Moffitt Cancer Center Magnolia Campus	120
FL	643	2251	UnitedHealthcare	Moffitt Cancer Center Magnolia Campus	88
FL	76	2251	Cigna	Moffitt Cancer Center Magnolia Campus	43
FL	7	2251	Aetna	Moffitt Cancer Center Magnolia Campus	26
FL	42	2251	Anthem	Moffitt Cancer Center Magnolia Campus	1
FL	643	2203	UnitedHealthcare	Orlando Health Orlando Regional Medical Center	1
GA	76	1884	Cigna	Piedmont Atlanta Hospital	248
GA	7	1884	Aetna	Piedmont Atlanta Hospital	196
GA	42	1884	Anthem	Piedmont Atlanta Hospital	166
GA	643	1884	UnitedHealthcare	Piedmont Atlanta Hospital	155
GA	76	1864	Cigna	Arthur M Blank Hospital	63
GA	168	1884	Blue Cross Blue Shield of South Carolina	Piedmont Atlanta Hospital	8
GA	388	1884	Blue Cross Blue Shield of Florida (Florida Blue)	Piedmont Atlanta Hospital	4
GA	643	1952	UnitedHealthcare	Emory University Hospital	2
ID	383	5632	Blue Cross of Idaho	Bingham Memorial Hospital	1
KY	42	3266	Anthem	Baptist Health Louisville	821
KY	42	3271	Anthem	Baptist Health Lexington	300
KY	42	3313	Anthem	Pikeville Medical Center	82
KY	643	3271	UnitedHealthcare	Baptist Health Lexington	59
KY	643	3266	UnitedHealthcare	Baptist Health Louisville	26
KY	643	3313	UnitedHealthcare	Pikeville Medical Center	24
KY	7	3266	Aetna	Baptist Health Louisville	20
KY	7	3271	Aetna	Baptist Health Lexington	13
KY	52	3266	Blue Cross Blue Shield of North Carolina	Baptist Health Louisville	8
KY	7	3313	Aetna	Pikeville Medical Center	4
KY	76	3313	Cigna	Pikeville Medical Center	4
KY	76	3271	Cigna	Baptist Health Lexington	3
KY	52	3271	Blue Cross Blue Shield of North Carolina	Baptist Health Lexington	1
KY	461	3266	Wellmark Blue Cross Blue Shield	Baptist Health Louisville	1
KY	461	3271	Wellmark Blue Cross Blue Shield	Baptist Health Lexington	1
KY	76	3266	Cigna	Baptist Health Louisville	1
LA	391	4704	Blue Cross Blue Shield of Louisiana	Ochsner Lafayette General Medical Center	409
LA	391	4667	Blue Cross Blue Shield of Louisiana	St Tammany Health System	326
LA	643	4704	UnitedHealthcare	Ochsner Lafayette General Medical Center	215
LA	643	4667	UnitedHealthcare	St Tammany Health System	101
LA	76	4667	Cigna	St Tammany Health System	5
LA	76	4704	Cigna	Ochsner Lafayette General Medical Center	4
MA	392	651	Blue Cross Blue Shield of Massachusetts	Boston Childrens Hospital	2070
MA	643	651	UnitedHealthcare	Boston Childrens Hospital	237
MA	392	665	Blue Cross Blue Shield of Massachusetts	Dana-Farber Cancer Institute	144
MA	76	651	Cigna	Boston Childrens Hospital	114
MA	7	651	Aetna	Boston Childrens Hospital	101
MA	42	665	Anthem	Dana-Farber Cancer Institute	6
MA	643	665	UnitedHealthcare	Dana-Farber Cancer Institute	4
MA	76	665	Cigna	Dana-Farber Cancer Institute	2
MA	7	665	Aetna	Dana-Farber Cancer Institute	1
MD	643	1372	UnitedHealthcare	MedStar Franklin Square Medical Center	20
MD	44	1372	Blue Cross Blue Shield of Maryland & DC (CareFirst)	MedStar Franklin Square Medical Center	6
MD	44	1424	Blue Cross Blue Shield of Maryland & DC (CareFirst)	TidalHealth Peninsula Regional	4
MD	7	1424	Aetna	TidalHealth Peninsula Regional	1
MD	76	1372	Cigna	MedStar Franklin Square Medical Center	1
MD	7	1372	Aetna	MedStar Franklin Square Medical Center	1
MI	49	3037	Blue Cross Blue Shield of Michigan	Corewell Health William Beaumont University Hospital	261
MI	49	2907	Blue Cross Blue Shield of Michigan	Corewell Health Butterworth Hospital	214
MI	49	2931	Blue Cross Blue Shield of Michigan	University Hospital	192
MI	49	3055	Blue Cross Blue Shield of Michigan	Corewell Health Beaumont Troy Hospital	107
MI	643	2907	UnitedHealthcare	Corewell Health Butterworth Hospital	53
MI	643	3037	UnitedHealthcare	Corewell Health William Beaumont University Hospital	48
MI	7	2931	Aetna	University Hospital	40
MI	643	2931	UnitedHealthcare	University Hospital	28
MI	76	2907	Cigna	Corewell Health Butterworth Hospital	21
MI	7	2907	Aetna	Corewell Health Butterworth Hospital	20
MI	76	3037	Cigna	Corewell Health William Beaumont University Hospital	15
MI	76	2931	Cigna	University Hospital	12
MI	643	3055	UnitedHealthcare	Corewell Health Beaumont Troy Hospital	11
MI	76	3055	Cigna	Corewell Health Beaumont Troy Hospital	9
MI	7	3037	Aetna	Corewell Health William Beaumont University Hospital	8
MI	49	2963	Blue Cross Blue Shield of Michigan	Henry Ford St John Hospital	4
MI	7	3055	Aetna	Corewell Health Beaumont Troy Hospital	2
MI	42	2907	Anthem	Corewell Health Butterworth Hospital	1
MN	643	3795	UnitedHealthcare	Hennepin County Medical Center	73
MN	50	3795	Blue Cross Blue Shield of Minnesota	Hennepin County Medical Center	43
MN	76	3795	Cigna	Hennepin County Medical Center	9
MN	7	3795	Aetna	Hennepin County Medical Center	3
MN	461	3795	Wellmark Blue Cross Blue Shield	Hennepin County Medical Center	2
MS	456	3599	Blue Cross Blue Shield of Mississippi	University Hospital	427
MS	643	3599	UnitedHealthcare	University Hospital	293
MS	76	3599	Cigna	University Hospital	10
MT	165	5561	Blue Cross Blue Shield of Montana	Great Falls Clinic Hospital	32
MT	7	5561	Aetna	Great Falls Clinic Hospital	2
NE	643	4305	UnitedHealthcare	Childrens Nebraska	149
NE	51	4243	Blue Cross Blue Shield of Nebraska	Bryan East Campus	147
NE	51	4310	Blue Cross Blue Shield of Nebraska	Methodist Hospital of Nebraska Methodist Health System	106
NE	643	4243	UnitedHealthcare	Bryan East Campus	101
NE	51	4305	Blue Cross Blue Shield of Nebraska	Childrens Nebraska	32
NE	461	4310	Wellmark Blue Cross Blue Shield	Methodist Hospital of Nebraska Methodist Health System	23
NE	7	4310	Aetna	Methodist Hospital of Nebraska Methodist Health System	16
NE	7	4243	Aetna	Bryan East Campus	15
NE	76	4310	Cigna	Methodist Hospital of Nebraska Methodist Health System	8
NE	643	4310	UnitedHealthcare	Methodist Hospital of Nebraska Methodist Health System	8
NE	76	4305	Cigna	Childrens Nebraska	8
NE	76	4243	Cigna	Bryan East Campus	7
NE	51	4319	Blue Cross Blue Shield of Nebraska	Regional West Medical Center	5
NE	76	4319	Cigna	Regional West Medical Center	1
NH	42	591	Anthem	Dartmouth-Hitchcock Medical Center	310
NH	42	584	Anthem	Concord Hospital	205
NH	462	591	Blue Cross Blue Shield of Vermont	Dartmouth-Hitchcock Medical Center	168
NH	643	591	UnitedHealthcare	Dartmouth-Hitchcock Medical Center	114
NH	76	591	Cigna	Dartmouth-Hitchcock Medical Center	78
NH	42	598	Anthem	Elliot Hospital	72
NH	643	584	UnitedHealthcare	Concord Hospital	27
NH	76	584	Cigna	Concord Hospital	25
NH	42	600	Anthem	Southern New Hampshire Medical Center	17
NH	42	592	Anthem	Cheshire Medical Center	8
NH	76	600	Cigna	Southern New Hampshire Medical Center	7
NH	76	598	Cigna	Elliot Hospital	6
NH	7	591	Aetna	Dartmouth-Hitchcock Medical Center	5
NH	643	600	UnitedHealthcare	Southern New Hampshire Medical Center	5
NH	643	598	UnitedHealthcare	Elliot Hospital	3
NH	7	584	Aetna	Concord Hospital	2
NH	462	592	Blue Cross Blue Shield of Vermont	Cheshire Medical Center	2
NH	643	592	UnitedHealthcare	Cheshire Medical Center	1
NH	76	592	Cigna	Cheshire Medical Center	1
NJ	229	1020	Blue Cross Blue Shield of New Jersey (Horizon)	Cooper University Hospital	2003
NJ	229	1097	Blue Cross Blue Shield of New Jersey (Horizon)	Virtua Voorhees Hospital	1002
NJ	229	1061	Blue Cross Blue Shield of New Jersey (Horizon)	Cooperman Barnabas Medical Center	553
NJ	643	1020	UnitedHealthcare	Cooper University Hospital	499
NJ	229	1056	Blue Cross Blue Shield of New Jersey (Horizon)	Morristown Medical Center	342
NJ	643	1061	UnitedHealthcare	Cooperman Barnabas Medical Center	322
NJ	643	1097	UnitedHealthcare	Virtua Voorhees Hospital	241
NJ	643	1065	UnitedHealthcare	Robert Wood Johnson University Hospital New Brunswick	175
NJ	229	1065	Blue Cross Blue Shield of New Jersey (Horizon)	Robert Wood Johnson University Hospital New Brunswick	139
NJ	76	1056	Cigna	Morristown Medical Center	103
NJ	7	1056	Aetna	Morristown Medical Center	82
NJ	643	1056	UnitedHealthcare	Morristown Medical Center	76
NJ	76	1097	Cigna	Virtua Voorhees Hospital	68
NJ	76	1061	Cigna	Cooperman Barnabas Medical Center	47
NJ	56	1020	Blue Cross Blue Shield of Pennsylvania (Independence)	Cooper University Hospital	35
NJ	56	1097	Blue Cross Blue Shield of Pennsylvania (Independence)	Virtua Voorhees Hospital	31
NJ	229	1060	Blue Cross Blue Shield of New Jersey (Horizon)	University Hospital	30
NJ	76	1020	Cigna	Cooper University Hospital	28
NJ	76	1065	Cigna	Robert Wood Johnson University Hospital New Brunswick	20
NJ	7	1097	Aetna	Virtua Voorhees Hospital	17
NJ	643	1060	UnitedHealthcare	University Hospital	8
NJ	42	1056	Anthem	Morristown Medical Center	7
NJ	7	1060	Aetna	University Hospital	4
NJ	7	1061	Aetna	Cooperman Barnabas Medical Center	3
NJ	7	1020	Aetna	Cooper University Hospital	3
NJ	76	1060	Cigna	University Hospital	3
NJ	7	1065	Aetna	Robert Wood Johnson University Hospital New Brunswick	2
NJ	42	1060	Anthem	University Hospital	1
NM	166	5850	Blue Cross Blue Shield of New Mexico	CHRISTUS St Vincent Regional Medical Center	245
NM	166	5814	Blue Cross Blue Shield of New Mexico	CHRISTUS Health Southern New Mexico	122
NM	166	5825	Blue Cross Blue Shield of New Mexico	Presbyterian Hospital	93
NM	643	5825	UnitedHealthcare	Presbyterian Hospital	70
NM	76	5825	Cigna	Presbyterian Hospital	10
NM	643	5850	UnitedHealthcare	CHRISTUS St Vincent Regional Medical Center	8
NM	643	5814	UnitedHealthcare	CHRISTUS Health Southern New Mexico	5
NM	7	5814	Aetna	CHRISTUS Health Southern New Mexico	4
NM	76	5850	Cigna	CHRISTUS St Vincent Regional Medical Center	4
NM	76	5814	Cigna	CHRISTUS Health Southern New Mexico	3
NM	42	5825	Anthem	Presbyterian Hospital	1
NM	7	5850	Aetna	CHRISTUS St Vincent Regional Medical Center	1
NV	76	6057	Cigna	Carson Tahoe Regional Medical Center	3
NV	643	6057	UnitedHealthcare	Carson Tahoe Regional Medical Center	2
OH	643	2412	UnitedHealthcare	Cleveland Clinic Main Campus	696
OH	7	2412	Aetna	Cleveland Clinic Main Campus	162
OH	76	2412	Cigna	Cleveland Clinic Main Campus	136
OH	643	2378	UnitedHealthcare	University of Cincinnati Medical Center	76
OH	76	2376	Cigna	Cincinnati Childrens Burnet Campus	68
OH	7	2376	Aetna	Cincinnati Childrens Burnet Campus	61
OH	643	2376	UnitedHealthcare	Cincinnati Childrens Burnet Campus	53
OH	76	2378	Cigna	University of Cincinnati Medical Center	53
OH	7	2378	Aetna	University of Cincinnati Medical Center	36
OH	42	2412	Anthem	Cleveland Clinic Main Campus	8
OH	461	2412	Wellmark Blue Cross Blue Shield	Cleveland Clinic Main Campus	3
OK	53	4827	Blue Cross Blue Shield of Oklahoma	Oklahoma Heart Hospital North	132
OK	643	4827	UnitedHealthcare	Oklahoma Heart Hospital North	15
OK	53	4926	Blue Cross Blue Shield of Oklahoma	Stillwater Medical Center	6
OK	76	4827	Cigna	Oklahoma Heart Hospital North	4
PA	174	1106	Highmark Blue Cross Blue Shield	Lehigh Valley Hospital - Cedar Crest	2171
PA	54	1106	Blue Cross Blue Shield of Pennsylvania (Capital Blue Cross)	Lehigh Valley Hospital - Cedar Crest	1099
PA	643	1106	UnitedHealthcare	Lehigh Valley Hospital - Cedar Crest	393
PA	76	1106	Cigna	Lehigh Valley Hospital - Cedar Crest	131
PA	643	1279	UnitedHealthcare	Temple University Hospital	122
PA	56	1279	Blue Cross Blue Shield of Pennsylvania (Independence)	Temple University Hospital	19
PA	229	1279	Blue Cross Blue Shield of New Jersey (Horizon)	Temple University Hospital	15
PA	76	1279	Cigna	Temple University Hospital	11
PA	7	1279	Aetna	Temple University Hospital	3
PA	7	1106	Aetna	Lehigh Valley Hospital - Cedar Crest	1
RI	397	741	Blue Cross Blue Shield of Rhode Island	South County Hospital	2
SC	168	1854	Blue Cross Blue Shield of South Carolina	Lexington Medical Center	967
SC	643	1854	UnitedHealthcare	Lexington Medical Center	59
SC	76	1854	Cigna	Lexington Medical Center	20
SC	643	1824	UnitedHealthcare	Prisma Health Greenville Memorial Hospital	4
SC	7	1808	Aetna	Prisma Health Richland Hospital	3
SC	643	1808	UnitedHealthcare	Prisma Health Richland Hospital	3
SC	7	1824	Aetna	Prisma Health Greenville Memorial Hospital	2
SC	42	1808	Anthem	Prisma Health Richland Hospital	1
SC	76	1808	Cigna	Prisma Health Richland Hospital	1
SC	76	1824	Cigna	Prisma Health Greenville Memorial Hospital	1
SC	51	1824	Blue Cross Blue Shield of Nebraska	Prisma Health Greenville Memorial Hospital	1
SD	461	4222	Wellmark Blue Cross Blue Shield	Avera McKennan Hospital & University Health Center	12
SD	643	4222	UnitedHealthcare	Avera McKennan Hospital & University Health Center	4
SD	461	4177	Wellmark Blue Cross Blue Shield	Avera St Lukes Hospital	1
SD	461	4234	Wellmark Blue Cross Blue Shield	Avera Sacred Heart Hospital	1
TN	76	3432	Cigna	Methodist University Hospital	295
TN	643	3432	UnitedHealthcare	Methodist University Hospital	266
TN	398	3432	Blue Cross Blue Shield of Tennessee	Methodist University Hospital	262
TN	398	3471	Blue Cross Blue Shield of Tennessee	West Tennessee Healthcare Jackson-Madison County General Hospital	32
TN	76	3372	Cigna	Erlanger Baroness Hospital	21
TN	398	3372	Blue Cross Blue Shield of Tennessee	Erlanger Baroness Hospital	17
TN	643	3471	UnitedHealthcare	West Tennessee Healthcare Jackson-Madison County General Hospital	17
TN	76	3471	Cigna	West Tennessee Healthcare Jackson-Madison County General Hospital	10
TN	76	3351	Cigna	CHI Memorial Hospital Chattanooga	5
TN	7	3372	Aetna	Erlanger Baroness Hospital	3
TN	643	3372	UnitedHealthcare	Erlanger Baroness Hospital	2
TN	42	3372	Anthem	Erlanger Baroness Hospital	1
TX	169	5297	Blue Cross Blue Shield of Texas	William P Clements Jr University Hospital	1595
TX	643	5297	UnitedHealthcare	William P Clements Jr University Hospital	222
TX	76	5297	Cigna	William P Clements Jr University Hospital	124
TX	7	5297	Aetna	William P Clements Jr University Hospital	76
TX	42	5297	Anthem	William P Clements Jr University Hospital	2
TX	389	5297	Blue Cross Blue Shield of Illinois	William P Clements Jr University Hospital	1
VA	42	1523	Anthem	Sentara Norfolk General Hospital	267
VA	42	1517	Anthem	Riverside Regional Medical Center	95
VA	643	1523	UnitedHealthcare	Sentara Norfolk General Hospital	72
VA	76	1523	Cigna	Sentara Norfolk General Hospital	30
VA	643	1517	UnitedHealthcare	Riverside Regional Medical Center	22
VA	76	1517	Cigna	Riverside Regional Medical Center	8
VA	7	1523	Aetna	Sentara Norfolk General Hospital	6
VA	52	1523	Blue Cross Blue Shield of North Carolina	Sentara Norfolk General Hospital	2
VA	388	1523	Blue Cross Blue Shield of Florida (Florida Blue)	Sentara Norfolk General Hospital	1
VA	461	1523	Wellmark Blue Cross Blue Shield	Sentara Norfolk General Hospital	1
VT	462	610	Blue Cross Blue Shield of Vermont	The University of Vermont Medical Center	2
VT	462	625	Blue Cross Blue Shield of Vermont	Mt Ascutney Hospital and Health Center	1
WA	61	6163	Regence Blue Cross Blue Shield	Seattle Childrens Hospital	762
WA	628	6163	Premera Blue Cross	Seattle Childrens Hospital	586
WA	643	6165	UnitedHealthcare	Harborview Medical Center	402
WA	643	6163	UnitedHealthcare	Seattle Childrens Hospital	372
WA	61	6171	Regence Blue Cross Blue Shield	UW Medical Center - Montlake	309
WA	628	6165	Premera Blue Cross	Harborview Medical Center	180
WA	61	6165	Regence Blue Cross Blue Shield	Harborview Medical Center	175
WA	628	6171	Premera Blue Cross	UW Medical Center - Montlake	146
WA	643	6171	UnitedHealthcare	UW Medical Center - Montlake	132
WA	76	6171	Cigna	UW Medical Center - Montlake	35
WA	76	6163	Cigna	Seattle Childrens Hospital	34
WA	7	6163	Aetna	Seattle Childrens Hospital	18
WA	76	6165	Cigna	Harborview Medical Center	14
WA	7	6171	Aetna	UW Medical Center - Montlake	1
WA	7	6165	Aetna	Harborview Medical Center	1
WI	643	3200	UnitedHealthcare	Froedtert Hospital	399
WI	42	3200	Anthem	Froedtert Hospital	326
WI	42	3121	Anthem	Bellin Hospital Green Bay	7
WI	643	3121	UnitedHealthcare	Bellin Hospital Green Bay	7
WV	174	1630	Highmark Blue Cross Blue Shield	WVU Medicine Wheeling Hospital	1
WY	643	5681	UnitedHealthcare	Banner Wyoming Medical Center	25
WY	464	5688	Blue Cross Blue Shield of Wyoming	Campbell County Memorial Hospital	4
WY	76	5681	Cigna	Banner Wyoming Medical Center	2

Full List of Top 10 Hospitals by State

Code

all_candidates = pd.read_sql(f"""
WITH
provider_names AS (
    SELECT 
        DISTINCT
        provider_id,
        provider_name
    FROM tq_production.spines.spines_provider_hospitals
),
payer_names AS (
    SELECT 
        DISTINCT
        payer_id,
        payer_name
    FROM tq_production.spines.spines_payer
),
ranked AS (
    SELECT 
        state,
        provider_id,
        ROW_NUMBER() OVER (PARTITION BY state ORDER BY provider_net_patient_revenue_state_rank ASC) AS rn
    FROM tq_dev.internal_dev_csong_cld_v2_3_1.prod_rollup_provider
),
top_providers AS (
    SELECT *
    FROM ranked
    WHERE rn <= 10
    AND state NOT IN ('VI', 'PR')
)
SELECT 
    tp.state,
    tp.provider_id,
    pn.provider_name
FROM top_providers tp
LEFT JOIN provider_names pn
    ON tp.provider_id = pn.provider_id
WHERE pn.provider_name IS NOT NULL
ORDER BY 1, tp.provider_id
""", con=trino_conn)
print(all_candidates.to_markdown(index=False))
all_candidates.to_excel('all_top_providers_candidates.xlsx', index=False)

Results

state	provider_id	provider_name
AK	6695	Alaska Regional Hospital
AK	6696	Providence Alaska Medical Center
AK	6697	Samuel Simmonds Memorial Hospital
AK	6701	Fairbanks Memorial Hospital
AK	6703	Bartlett Regional Hospital
AK	6705	PeaceHealth Ketchikan Medical Center
AK	6711	Mat-Su Regional Medical Center
AL	3482	Mobile Infirmary
AL	3509	Grandview Medical Center
AL	3510	Childrens of Alabama
AL	3519	UAB St Vincents Birmingham
AL	3520	UAB Hospital
AL	3531	Southeast Health Medical Center
AL	3554	Huntsville Hospital
AL	3566	Baptist Medical Center South
AL	3568	East Alabama Medical Center - Opelika
AL	3584	DCH Regional Medical Center
AR	4498	Northwest Medical Center - Springdale
AR	4539	Washington Regional Medical Center
AR	4542	Mercy Hospital Fort Smith
AR	4543	Baptist Health - Fort Smith
AR	4553	St Bernards Medical Center
AR	4556	Baptist Health Medical Center - Little Rock
AR	4557	Arkansas Childrens Hospital
AR	4560	CHI St Vincent Infirmary
AR	4561	University of Arkansas for Medical Sciences Medical Center
AR	4581	Mercy Hospital Northwest Arkansas
AZ	5871	Banner Thunderbird Medical Center
AZ	5872	Banner Desert Medical Center
AZ	5875	Mayo Clinic Hospital - Arizona
AZ	5876	Tucson Medical Center
AZ	5882	Chandler Regional Medical Center
AZ	5910	Phoenix Childrens Hospital
AZ	5919	Banner - University Medical Center Phoenix
AZ	5924	City of Hope Phoenix
AZ	5927	St Josephs Hospital and Medical Center
AZ	5960	Banner - University Medical Center Tucson
CA	6287	UCSF Helen Diller Medical Center at Parnassus Heights
CA	6394	Cedars-Sinai Medical Center
CA	6404	Helford Clinical Research Hospital
CA	6497	Los Angeles General Medical Center
CA	6504	Ronald Reagan UCLA Medical Center
CA	6546	Stanford University Hospital
CA	6577	University of California Davis Medical Center
CA	6593	UC San Diego Medical Center
CA	6615	Santa Clara Valley Medical Center
CA	6646	Lucile Packard Childrens Hospital Stanford
CO	5717	UCHealth Poudre Valley Hospital
CO	5722	HCA HealthONE Sky Ridge
CO	5744	UCHealth Memorial Hospital Central
CO	5749	Childrens Hospital Colorado - Anschutz Medical Campus
CO	5750	Denver Health Main Campus
CO	5754	HCA HealthONE Presbyterian St Lukes
CO	5756	Intermountain Health Saint Joseph Hospital
CO	5759	UCHealth University of Colorado Hospital
CO	5766	HCA HealthONE Swedish
CO	7368	Penrose Hospital
CT	68	The Hospital of Central Connecticut - Bradley Memorial Campus
CT	746	Bridgeport Hospital
CT	747	Hartford Hospital
CT	748	Saint Francis Hospital and Medical Center
CT	752	Stamford Hospital
CT	753	St Vincents Medical Center
CT	758	Danbury Hospital
CT	760	Greenwich Hospital
CT	761	UConn John Dempsey Hospital
CT	769	Yale New Haven Hospital
DC	1432	Childrens National Hospital
DC	1433	Howard University Hospital
DC	1434	MedStar Washington Hospital Center
DC	1435	MedStar Georgetown University Hospital
DC	1436	The George Washington University Hospital
DC	1438	MedStar National Rehabilitation Hospital
DC	1441	Sibley Memorial Hospital
DC	1442	United Medical Center
DE	10065	PAM Rehabilitation Hospital of Dover
DE	1350	Bayhealth Hospital - Kent Campus
DE	1355	Beebe Healthcare - Margaret H Rollins Lewes Campus
DE	1356	TidalHealth Nanticoke
DE	1357	Nemours Childrens Hospital - Delaware
DE	1358	Christiana Hospital
DE	1359	Saint Francis Hospital
DE	1362	Encompass Health Rehabilitation Hospital of Middletown
DE	71	Bayhealth Hospital - Sussex Campus
DE	9484	PAM Health Rehabilitation Hospital of Georgetown
FL	2055	St Josephs Hospital
FL	2131	UF Health Shands Hospital
FL	2135	Memorial Regional Hospital
FL	2140	Baptist Medical Center Jacksonville
FL	2180	Jackson Memorial Hospital
FL	2181	Baptist Hospital
FL	2201	AdventHealth Orlando
FL	2203	Orlando Health Orlando Regional Medical Center
FL	2250	Tampa General Hospital
FL	2251	Moffitt Cancer Center Magnolia Campus
GA	1862	Northeast Georgia Medical Center Gainesville
GA	1864	Arthur M Blank Hospital
GA	1880	Emory University Hospital Midtown
GA	1882	Grady Memorial Hospital
GA	1883	Northside Hospital Atlanta
GA	1884	Piedmont Atlanta Hospital
GA	1905	Northside Hospital Gwinnett
GA	1933	City of Hope Atlanta cancer hospital
GA	1952	Emory University Hospital
GA	1975	Wellstar Kennestone Regional Medical Center
HI	6719	Pali Momi Medical Center
HI	6721	Hilo Benioff Medical Center Main
HI	6727	Wilcox Medical Center
HI	6731	Maui Memorial Medical Center
HI	6734	Kaiser Permanente Moanalua Medical Center
HI	6735	Kapiolani Medical Center for Women & Children
HI	6737	Kuakini Medical Center
HI	6739	Queens Medical Center
HI	6741	Straub Medical Center
HI	6744	Adventist Health Castle
IA	3854	MercyOne North Iowa Medical Center
IA	3855	MercyOne Des Moines Medical Center
IA	3856	MercyOne Genesis Davenport Medical Center
IA	3869	Southeast Iowa Regional Medical Center - West Burlington Campus
IA	3872	Mercy Medical Center - Cedar Rapids
IA	3873	UnityPoint Health St Lukes Hospital
IA	3894	Iowa Methodist Medical Center
IA	3919	University of Iowa Hospitals & Clinics
IA	3968	Allen Hospital
IA	3969	MercyOne Waterloo Medical Center
ID	5624	Portneuf Medical Center
ID	5625	Mountain View Hospital
ID	5632	Bingham Memorial Hospital
ID	5639	Saint Alphonsus Regional Medical Center
ID	5640	St Lukes Boise Medical Center
ID	5646	Kootenai Health
ID	5653	Eastern Idaho Regional Medical Center
ID	5662	Saint Alphonsus Medical Center - Nampa
ID	5672	St Lukes Magic Valley Medical Center
ID	5675	St Lukes Nampa Medical Center
IL	2692	Northwestern Medicine Central DuPage Hospital
IL	2694	Evanston Hospital
IL	2728	Advocate Lutheran General Hospital
IL	2733	Northwestern Memorial Hospital
IL	2745	Loyola University Medical Center
IL	2750	Rush University Medical Center
IL	2765	Uchicago Medicine Mitchell Hospital - Hyde Park
IL	2851	Advocate Christ Medical Center
IL	2863	OSF Saint Francis Medical Center
IL	2888	Carle Foundation Hospital
IN	2530	Community Hospital North
IN	2532	Ascension St Vincent Evansville
IN	2533	Franciscan Health Indianapolis
IN	2589	Deaconess Midtown Hospital
IN	2614	Community Hospital East
IN	2618	Ascension St Vincent Hospital - Indianapolis
IN	2641	Community Hospital
IN	2660	Memorial Hospital of South Bend
IN	2666	Union Hospital
IN	6845	IU Health Methodist Hospital
KS	4349	Ascension Via Christi St Francis
KS	4351	Menorah Medical Center
KS	4420	The University of Kansas Hospital
KS	4428	Lawrence Memorial Hospital
KS	4451	Olathe Medical Center
KS	4455	Overland Park Regional Medical Center
KS	4457	AdventHealth Shawnee Mission
KS	4477	The University of Kansas Health System St Francis Campus
KS	4478	Stormont Vail Hospital
KS	4487	Wesley Medical Center
KY	10320	St Elizabeth Covington Hospital
KY	3215	Owensboro Health Regional Hospital
KY	3229	Kings Daughters Medical Center
KY	3266	Baptist Health Louisville
KY	3271	Baptist Health Lexington
KY	3277	UK Albert B Chandler Hospital
KY	3282	Norton Hospital
KY	3284	UofL Health - Jewish Hospital
KY	3286	UofL Hospital
KY	3313	Pikeville Medical Center
LA	4603	University Medical Center New Orleans
LA	4604	Our Lady of the Lake Regional Medical Center
LA	4609	Baton Rouge General Medical Center - Bluebonnet Campus
LA	4641	Womans Hospital
LA	4667	St Tammany Health System
LA	4704	Ochsner Lafayette General Medical Center
LA	4705	Our Lady of Lourdes Regional Medical Center
LA	4755	Manning Family Childrens
LA	4756	Ochsner Medical Center - Main Campus
LA	4782	Willis-Knighton Medical Center
MA	632	Boston Medical Center
MA	635	Beth Israel Deaconess Medical Center
MA	636	Charlton Memorial Hospital
MA	646	Brigham and Womens Hospital
MA	651	Boston Childrens Hospital
MA	658	Massachusetts General Hospital
MA	665	Dana-Farber Cancer Institute
MA	673	Lahey Hospital & Medical Center - Burlington
MA	687	UMass Memorial Medical Center - University Campus
MA	714	Baystate Medical Center
MD	1369	Luminis Health Anne Arundel Medical Center
MD	1370	Johns Hopkins Bayview Medical Center
MD	1372	MedStar Franklin Square Medical Center
MD	1377	The Johns Hopkins Hospital
MD	1381	Mercy Medical Center
MD	1382	Ascension Saint Agnes Hospital
MD	1384	Sinai Hospital
MD	1387	University of Maryland Medical Center
MD	1424	TidalHealth Peninsula Regional
MD	1425	Holy Cross Hospital
ME	541	MaineHealth Mid Coast Hospital
ME	545	Northern Light Eastern Maine Medical Center
ME	546	St Joseph Hospital of ME
ME	560	Central Maine Medical Center
ME	561	MaineHealth Maine Medical Center Biddeford
ME	562	St Marys Health System
ME	568	MaineHealth Maine Medical Center Portland
ME	575	MaineGeneral Medical Center - Alfond Center for Health
ME	578	York Hospital
ME	8047	Northern Light Mercy Hospital - Fore River Campus
MI	2907	Corewell Health Butterworth Hospital
MI	2930	Trinity Health Ann Arbor Hospital
MI	2931	University Hospital
MI	2959	Henry Ford Hospital
MI	2963	Henry Ford St John Hospital
MI	2999	Bronson Methodist Hospital
MI	3003	UM Health - Sparrow Lansing
MI	3037	Corewell Health William Beaumont University Hospital
MI	3055	Corewell Health Beaumont Troy Hospital
MI	9942	Henry Ford Providence Southfield
MN	3710	M Health Fairview University of Minnesota Medical Center - West Bank East
MN	3722	Mercy Hospital
MN	3757	Childrens Minnesota Hospital - Minneapolis
MN	3758	Mayo Clinic Hospital - Saint Marys Campus
MN	3789	Abbott Northwestern Hospital
MN	3791	Methodist Hospital
MN	3795	Hennepin County Medical Center
MN	3821	CentraCare - St Cloud Hospital
MN	3824	Regions Hospital
MN	3826	United Hospital
MO	4020	University Hospital
MO	4042	Childrens Mercy Adele Hall Campus
MO	4051	Saint Lukes Hospital of Kansas City
MO	4082	Barnes-Jewish Hospital
MO	4086	SSM Health St Marys Hospital - St Louis
MO	4088	SSM Health Saint Louis University Hospital
MO	4093	Mercy Hospital St Louis
MO	4094	St Louis Childrens Hospital
MO	4102	Cox Medical Center South
MO	4103	Mercy Hospital Springfield
MS	3597	Baptist Memorial Hospital - Golden Triangle
MS	3599	University Hospital
MS	3602	Mississippi Baptist Medical Center
MS	3612	Pascagoula Hospital
MS	3645	Memorial Hospital Gulfport
MS	3646	Forrest General Hospital
MS	3656	St Dominic Hospital
MS	3680	Baptist Memorial Hospital - North Mississippi
MS	3691	North Mississippi Medical Center - Tupelo
MS	3699	Baptist Memorial Hospital - DeSoto
MT	5557	Benefis Hospital - East Campus
MT	5560	Logan Health Medical Center
MT	5561	Great Falls Clinic Hospital
MT	5565	Billings Clinic Hospital
MT	5566	St Vincent Regional Hospital
MT	5567	Bozeman Health Deaconess Regional Medical Center
MT	5569	St James Healthcare
MT	5592	St Peters Health Regional Medical Center
MT	5601	Community Medical Center
MT	5602	Providence St Patrick Hospital
NC	1635	Cone Health Moses Cone Hospital
NC	1644	Novant Health New Hanover Regional Medical Center
NC	1663	UNC Medical Center
NC	1664	Atrium Health Carolinas Medical Center
NC	1666	Novant Health Presbyterian Medical Center
NC	1670	Duke University Hospital
NC	1693	ECU Health Medical Center
NC	1731	UNC Rex Hospital
NC	1762	Novant Health Forsyth Medical Center
NC	1765	Atrium Health Wake Forest Baptist Medical Center
ND	4125	Altru Hospital
ND	4126	Essentia Health - Fargo
ND	4127	Trinity Hospital
ND	4134	Sanford Medical Center Bismarck
ND	4135	CHI St Alexius Health Bismarck Medical Center
ND	4144	CHI St Alexius Health Dickinson Medical Center
ND	4154	Jamestown Regional Medical Center
ND	4173	CHI St Alexius Health Williston Medical Center
ND	7712	Sanford Broadway Medical Center
ND	8702	PAM Rehabilitation Hospital of Fargo
NE	4240	Faith Regional Health Services
NE	4242	Nebraska Medical Center
NE	4243	Bryan East Campus
NE	4246	CHI Health Lakeside
NE	4283	Mary Lanning Memorial Hospital
NE	4302	Great Plains Health
NE	4305	Childrens Nebraska
NE	4310	Methodist Hospital of Nebraska Methodist Health System
NE	4311	CHI Health Creighton University Medical Center - Bergan Mercy
NE	4319	Regional West Medical Center
NH	579	Portsmouth Regional Hospital
NH	584	Concord Hospital
NH	586	Wentworth-Douglass Hospital
NH	588	Exeter Hospital
NH	591	Dartmouth-Hitchcock Medical Center
NH	592	Cheshire Medical Center
NH	596	Catholic Medical Center
NH	598	Elliot Hospital
NH	600	Southern New Hampshire Medical Center
NH	601	St Joseph Hospital of NH
NJ	1020	Cooper University Hospital
NJ	1032	Englewood Hospital and Medical Center
NJ	1035	Hackensack University Medical Center
NJ	1056	Morristown Medical Center
NJ	1058	Jersey Shore University Medical Center
NJ	1060	University Hospital
NJ	1061	Cooperman Barnabas Medical Center
NJ	1065	Robert Wood Johnson University Hospital New Brunswick
NJ	1080	The Valley Hospital
NJ	1097	Virtua Voorhees Hospital
NM	5814	CHRISTUS Health Southern New Mexico
NM	5817	MountainView Regional Medical Center
NM	5824	University of New Mexico Hospital
NM	5825	Presbyterian Hospital
NM	5826	Lovelace Medical Center
NM	5836	San Juan Regional Medical Center
NM	5842	Memorial Medical Center
NM	5846	Nor-Lea Hospital District
NM	5850	CHRISTUS St Vincent Regional Medical Center
NM	5863	Lovelace Womens Hospital
NV	6043	Summerlin Hospital Medical Center
NV	6044	MountainView Hospital
NV	6045	Dignity Health St Rose Dominican - Siena Campus
NV	6049	Spring Valley Hospital Medical Center
NV	6057	Carson Tahoe Regional Medical Center
NV	6074	Sunrise Hospital & Medical Center
NV	6075	University Medical Center
NV	6077	Valley Hospital Medical Center
NV	6086	Renown Regional Medical Center
NV	6092	Henderson Hospital
NY	794	NewYork-Presbyterian Weill Cornell Medical Center
NY	876	Long Island Jewish Medical Center
NY	897	North Shore University Hospital
NY	919	NYC Langone Tisch Hospital
NY	924	Memorial Sloan Kettering Cancer Center
NY	926	Montefiore Hospital - Moses Campus
NY	927	The Mount Sinai Hospital
NY	964	Strong Memorial Hospital
NY	978	Stony Brook University Hospital
NY	989	Westchester Medical Center
OH	2295	The James Cancer Hospital and Solove Research Institute
OH	2376	Cincinnati Childrens Burnet Campus
OH	2378	University of Cincinnati Medical Center
OH	2412	Cleveland Clinic Main Campus
OH	2419	University Hospitals Cleveland Medical Center
OH	2422	Nationwide Childrens Hospital
OH	2428	Ohio State University Wexner Medical Center
OH	2429	OhioHealth Riverside Methodist Hospital
OH	2436	Miami Valley Hospital
OH	2499	ProMedica Toledo Hospital
OK	4827	Oklahoma Heart Hospital North
OK	4888	Comanche County Memorial Hospital
OK	4904	INTEGRIS Baptist Medical Center
OK	4907	Mercy Hospital Oklahoma City
OK	4909	SSM Health St Anthony Hospital - Oklahoma City
OK	4926	Stillwater Medical Center
OK	4934	Hillcrest Medical Center
OK	4936	Saint Francis Hospital
OK	4937	Ascension St John Medical Center
OK	8859	OU Health - University of Oklahoma Medical Center
OR	6204	Legacy Emanuel Medical Center
OR	6212	PeaceHealth Sacred Heart Medical Center at RiverBend
OR	6215	St Charles Bend
OR	6218	Good Samaritan Regional Medical Center
OR	6238	Asante Rogue Regional Medical Center
OR	6247	Legacy Good Samaritan Medical Center
OR	6250	Providence Portland Medical Center
OR	6251	Providence St Vincent Medical Center
OR	6253	OHSU Hospital - Portland
OR	6261	Salem Hospital
PA	1106	Lehigh Valley Hospital - Cedar Crest
PA	1115	UPMC Presbyterian
PA	1120	Thomas Jefferson University Hospital
PA	1124	St Lukes University Hospital - Bethlehem
PA	1191	Geisinger Medical Center
PA	1206	Penn State Health Milton S Hershey Medical Center
PA	1263	Childrens Hospital of Philadelphia
PA	1267	Hospital of the University of Pennsylvania
PA	1279	Temple University Hospital
PA	1341	WellSpan York Hospital
RI	733	Newport Hospital
RI	734	Our Lady of Fatima Hospital
RI	736	Miriam Hospital
RI	737	Women & Infants Hospital
RI	738	Rhode Island Hospital
RI	739	Roger Williams Medical Center
RI	741	South County Hospital
RI	742	Landmark Medical Center
RI	743	Kent Hospital
RI	744	Westerly Hospital
SC	1774	McLeod Regional Medical Center
SC	1790	AnMed Health Medical Center
SC	1808	Prisma Health Richland Hospital
SC	1815	Trident Medical Center
SC	1824	Prisma Health Greenville Memorial Hospital
SC	1825	St Francis Downtown
SC	1838	Grand Strand Medical Center
SC	1847	Spartanburg Medical Center
SC	1854	Lexington Medical Center
SC	9730	Medical University of South Carolina Medical Center
SD	4175	Monument Health Rapid City Hospital
SD	4176	Avera Queen of Peace Hospital
SD	4177	Avera St Lukes Hospital
SD	4179	Sanford USD Medical Center
SD	4180	Avera Heart Hospital
SD	4182	Sioux Falls Specialty Hospital
SD	4222	Avera McKennan Hospital & University Health Center
SD	4225	Monument Health Spearfish Hospital
SD	4231	Prairie Lakes Healthcare System
SD	4234	Avera Sacred Heart Hospital
TN	3339	TriStar Centennial Medical Center
TN	3351	CHI Memorial Hospital Chattanooga
TN	3372	Erlanger Baroness Hospital
TN	3404	Johnson City Medical Center
TN	3414	The University of Tennessee Medical Center
TN	3427	Baptist Memorial Hospital - Memphis
TN	3432	Methodist University Hospital
TN	3447	Ascension Saint Thomas West
TN	3448	Vanderbilt University Medical Center
TN	3471	West Tennessee Healthcare Jackson-Madison County General Hospital
TX	5256	Childrens Medical Center Dallas
TX	5297	William P Clements Jr University Hospital
TX	5357	Cook Childrens Medical Center
TX	5390	Memorial Hermann - Texas Medical Center
TX	5394	University of Texas MD Anderson Cancer Center
TX	5396	Houston Methodist Hospital
TX	5402	Texas Childrens Hospital
TX	5502	Methodist Hospital
TX	5522	Baylor Scott & White Medical Center - Temple
TX	7279	Memorial Hermann Southwest Hospital
UT	5994	Logan Regional Hospital
UT	6000	Intermountain Medical Center
UT	6003	McKay-Dee Hospital
UT	6008	Utah Valley Hospital
UT	6011	St George Regional Hospital
UT	6012	LDS Hospital
UT	6014	Primary Childrens Hospital - Salt Lake City
UT	6015	St Marks Hospital
UT	6016	Holy Cross Hospital - Jordan Valley
UT	6018	University of Utah Hospital
VA	1453	Centra Lynchburg General Hospital
VA	1513	Inova Fairfax Hospital
VA	1517	Riverside Regional Medical Center
VA	1523	Sentara Norfolk General Hospital
VA	1538	VCU Medical Center Main Hospital
VA	1541	St Marys Hospital
VA	1544	Carilion Roanoke Memorial Hospital
VA	1559	Winchester Medical Center
VA	1564	University Hospital
VA	6871	Johnston-Willis Hospital
VT	610	The University of Vermont Medical Center
VT	611	Central Vermont Medical Center
VT	612	Southwestern Vermont Medical Center
VT	613	Brattleboro Memorial Hospital
VT	618	Gifford Medical Center
VT	619	Rutland Regional Medical Center
VT	620	Northwestern Medical Center
VT	622	Springfield Hospital
VT	623	Grace Cottage Hospital
VT	625	Mt Ascutney Hospital and Health Center
WA	6099	Providence Regional Medical Center Everett - Colby Campus
WA	6101	Swedish Medical Center - First Hill Campus
WA	6159	Valley Medical Center
WA	6163	Seattle Childrens Hospital
WA	6165	Harborview Medical Center
WA	6171	UW Medical Center - Montlake
WA	6172	Virginia Mason Medical Center
WA	6181	Providence Sacred Heart Medical Center
WA	6186	MultiCare Tacoma General Hospital
WA	6193	PeaceHealth Southwest Medical Center
WI	3067	Aurora St Lukes Medical Center of Aurora Health Care Metro
WI	3113	Mayo Clinic Health System - Eau Claire
WI	3121	Bellin Hospital Green Bay
WI	3124	HSHS St Vincent Hospital
WI	3132	Gundersen Lutheran Medical Center
WI	3139	UW Health University Hospital
WI	3142	Marshfield Medical Center - Marshfield
WI	3150	Childrens Wisconsin
WI	3199	Aspirus Wausau Hospital
WI	3200	Froedtert Hospital
WV	1572	JW Ruby Memorial Hospital
WV	1585	United Hospital Center
WV	1594	Cabell Huntington Hospital
WV	1597	St Marys Medical Center
WV	1604	Berkeley Medical Center
WV	1607	Mon Health Medical Center
WV	1610	Camden Clark Medical Center - Memorial Campus
WV	1615	Princeton Community Hospital
WV	1621	Thomas Health - Thomas Memorial Hospital
WV	1630	WVU Medicine Wheeling Hospital
WY	5676	Cheyenne Regional Medical Center - West Campus
WY	5681	Banner Wyoming Medical Center
WY	5684	Cody Regional Health
WY	5688	Campbell County Memorial Hospital
WY	5690	St Johns Health
WY	5692	Ivinson Memorial Hospital
WY	5696	Powell Valley Healthcare
WY	5698	Memorial Hospital of Sweetwater County
WY	5699	SageWest - Riverton
WY	5700	Sheridan Memorial Hospital

Limitations:

• Remits table has payer-level specificity but not network-level
• Stoploss applies to outliers and outliers are rare
• Unclear methodology to calculate claim-level total allowed amounts and total charges
  • if we need to use total_claim_charge_amount from medical_headers, then remits table adds 0 value