Employer: Aetna POS

Background

Employer team wishes to add Aetna CHOICE POS II to the list of networks in Clear Rates.

Do CHOICE POS II rates differ from OPEN ACCESS MANAGED CHOICE?

Core Rates

Methodology:

1. Sample 100,000 rates that appear in both networks for the same provider-code combination.
2. For each provider-code combination, compute the max rate for each network.
3. Compute the absolute and percent difference between the max rates for each network.
4. Report the 90th, 95th, and 99th percentiles of the absolute and percent differences, grouped by billing class and billing code type.

Results:

The rates are identical across both networks.

billing_class	billing_code_type	max_diff_p90	max_diff_p95	max_diff_p99	max_diff_pct_p90	max_diff_pct_p95	max_diff_pct_p99
institutional	HCPCS	0	0	0	0	0	0
institutional	MS-DRG	0	0	0	0	0	0
professional	HCPCS	0	0	0	0	0	0
professional	MS-DRG	0	0	0	0	0	0

Code

WITH samples AS (
SELECT 
    provider_id,
    billing_class,
    billing_code_type,
    billing_code,
    count(*) AS n
FROM tq_production.public_2025_05.core_rates cr
WHERE payer_id IN ('861','7')
AND product_network_label IN ('OPEN ACCESS MANAGED CHOICE', 'CHOICE POS II')
AND billing_code_modifier = ''
AND billing_code_type IN ('HCPCS', 'MS-DRG')
AND taxonomy_grouping = 'Hospitals'
GROUP BY 1,2,3,4
HAVING count(*) > 1
AND count(DISTINCT product_network_label) = 2
AND count(DISTINCT payer_id) = 2
ORDER BY rand()
LIMIT 100000
),
per_label AS (
SELECT 
    cr.provider_id,
    cr.billing_class,
    cr.billing_code_type,
    cr.billing_code,
    MIN(CASE WHEN cr.product_network_label = 'OPEN ACCESS MANAGED CHOICE' THEN cr.negotiated_rate END) AS min_rate_open_access,
    MAX(CASE WHEN cr.product_network_label = 'OPEN ACCESS MANAGED CHOICE' THEN cr.negotiated_rate END) AS max_rate_open_access,
    MIN(CASE WHEN cr.product_network_label = 'CHOICE POS II' THEN cr.negotiated_rate END) AS min_rate_choice_pos,
    MAX(CASE WHEN cr.product_network_label = 'CHOICE POS II' THEN cr.negotiated_rate END) AS max_rate_choice_pos
FROM tq_production.public_2025_05.core_rates cr
JOIN samples s
ON cr.provider_id = s.provider_id
AND cr.billing_class = s.billing_class
AND cr.billing_code_type = s.billing_code_type
AND cr.billing_code = s.billing_code
WHERE cr.payer_id IN ('861','7')
AND cr.product_network_label IN ('OPEN ACCESS MANAGED CHOICE', 'CHOICE POS II')
AND cr.billing_code_modifier = ''
AND cr.billing_code_type IN ('HCPCS', 'MS-DRG')
GROUP BY 1,2,3,4
),
diffs AS (
SELECT
    provider_id,
    billing_class,
    billing_code_type,
    billing_code,
    -- replicate pivoted metrics you used downstream
    min_rate_open_access,
    max_rate_open_access,
    min_rate_choice_pos,
    max_rate_choice_pos,
    ABS(max_rate_choice_pos - max_rate_open_access) AS max_diff,
    1.000 * ABS(max_rate_choice_pos - max_rate_open_access) / NULLIF(max_rate_open_access, 0) AS max_diff_pct
FROM per_label
WHERE max_rate_open_access IS NOT NULL
AND max_rate_choice_pos IS NOT NULL
)
SELECT
billing_class,
billing_code_type,
approx_percentile(max_diff, 0.90) AS max_diff_p90,
approx_percentile(max_diff, 0.95) AS max_diff_p95,
approx_percentile(max_diff, 0.99) AS max_diff_p99,
approx_percentile(max_diff_pct, 0.90) AS max_diff_pct_p90,
approx_percentile(max_diff_pct, 0.95) AS max_diff_pct_p95,
approx_percentile(max_diff_pct, 0.99) AS max_diff_pct_p99
FROM diffs
WHERE max_diff IS NOT NULL
GROUP BY 1,2
ORDER BY 1,2

Swarmplot:

In the analysis above, we compare the difference in max rates of each provider-network-code. But are there differences in the rates that are not the min and max rates? It does not appear to be the case.

Code

q = """
WITH samples AS (
    SELECT 
        provider_id,
        billing_class,
        billing_code_type,
        billing_code,
        negotiated_type,
        count(*) AS n
    FROM tq_production.public_2025_05.core_rates cr
    WHERE payer_id IN ('861','7')
    AND product_network_label IN ('OPEN ACCESS MANAGED CHOICE', 'CHOICE POS II')
    AND billing_code_modifier = ''
    AND billing_code_type IN ('HCPCS', 'MS-DRG')
    AND taxonomy_grouping = 'Hospitals'
    GROUP BY 1,2,3,4,5
    HAVING count(*) > 2
    AND count(DISTINCT product_network_label) = 2
    AND count(DISTINCT payer_id) = 2
    ORDER BY rand()
    LIMIT 20
)
SELECT 
    cr.provider_id,
    cr.billing_class,
    cr.billing_code_type,
    cr.billing_code,
    cr.product_network_label,
    cr.negotiated_type,
    cr.negotiated_rate
FROM tq_production.public_2025_05.core_rates cr
JOIN samples s
    ON cr.provider_id = s.provider_id
    AND cr.billing_class = s.billing_class
    AND cr.billing_code_type = s.billing_code_type
    AND cr.billing_code = s.billing_code
    AND cr.negotiated_type = s.negotiated_type
WHERE cr.payer_id IN ('861','7')
    AND cr.product_network_label IN ('OPEN ACCESS MANAGED CHOICE', 'CHOICE POS II')
    AND cr.billing_code_modifier = ''
    AND cr.billing_code_type IN ('HCPCS', 'MS-DRG')
"""

# %%
print(q)
df = pd.read_sql(q, con=trino_conn)

# %%
sns.swarmplot(data=df, x='billing_code', y='negotiated_rate', hue='product_network_label')

Core Rates Physician Groups

Methodology:

1. Sample 100,000 rates that appear in both networks for the same provider-code combination.
2. For each provider-code combination, compute the min and max rate for each network.
3. Compute the absolute and percent difference between the max rates for each network.
4. Report the 90th, 95th, and 99th percentiles of the absolute and percent differences, grouped by billing class and billing code type.

Results:

The rates are identical across both networks.

billing_class	billing_code_type	max_diff_p90	max_diff_p95	max_diff_p99	max_diff_pct_p90	max_diff_pct_p95	max_diff_pct_p99
professional	HCPCS	0	0	0	0	0	0
professional	MS-DRG	0	0	0	0	0	0

Code

WITH samples AS (
    SELECT 
        provider_group_id,
        billing_class,
        billing_code_type,
        billing_code,
        count(*) AS n
    FROM tq_production.public_2025_05.core_rates_physician_groups cr
    WHERE payer_id IN ('861','7')
    AND product_network_label IN ('OPEN ACCESS MANAGED CHOICE', 'CHOICE POS II')
    AND billing_code_modifier = ''
    AND billing_code_type IN ('HCPCS', 'MS-DRG')
    GROUP BY 1,2,3,4
    HAVING count(*) > 1
    AND count(DISTINCT product_network_label) = 2
    AND count(DISTINCT payer_id) = 2
    ORDER BY rand()
    LIMIT 100000
),
per_label AS (
    SELECT 
        cr.provider_group_id,
        cr.billing_class,
        cr.billing_code_type,
        cr.billing_code,
        MIN(CASE WHEN cr.product_network_label = 'OPEN ACCESS MANAGED CHOICE' THEN cr.negotiated_rate END) AS min_rate_open_access,
        MAX(CASE WHEN cr.product_network_label = 'OPEN ACCESS MANAGED CHOICE' THEN cr.negotiated_rate END) AS max_rate_open_access,
        MIN(CASE WHEN cr.product_network_label = 'CHOICE POS II' THEN cr.negotiated_rate END) AS min_rate_choice_pos,
        MAX(CASE WHEN cr.product_network_label = 'CHOICE POS II' THEN cr.negotiated_rate END) AS max_rate_choice_pos
    FROM tq_production.public_2025_05.core_rates_physician_groups cr
    JOIN samples s
    ON cr.provider_group_id = s.provider_group_id
    AND cr.billing_class = s.billing_class
    AND cr.billing_code_type = s.billing_code_type
    AND cr.billing_code = s.billing_code
    WHERE cr.payer_id IN ('861','7')
    AND cr.product_network_label IN ('OPEN ACCESS MANAGED CHOICE', 'CHOICE POS II')
    AND cr.billing_code_modifier = ''
    AND cr.billing_code_type IN ('HCPCS', 'MS-DRG')
    GROUP BY 1,2,3,4
),
diffs AS (
    SELECT
        provider_group_id,
        billing_class,
        billing_code_type,
        billing_code,
        -- replicate pivoted metrics you used downstream
        min_rate_open_access,
        max_rate_open_access,
        min_rate_choice_pos,
        max_rate_choice_pos,
        ABS(max_rate_choice_pos - max_rate_open_access) AS max_diff,
        1.000 * ABS(max_rate_choice_pos - max_rate_open_access) / NULLIF(max_rate_open_access, 0) AS max_diff_pct
    FROM per_label
    WHERE max_rate_open_access IS NOT NULL
    AND max_rate_choice_pos IS NOT NULL
)
SELECT
    billing_class,
    billing_code_type,
    approx_percentile(max_diff, 0.90) AS max_diff_p90,
    approx_percentile(max_diff, 0.95) AS max_diff_p95,
    approx_percentile(max_diff, 0.99) AS max_diff_p99,
    approx_percentile(max_diff_pct, 0.90) AS max_diff_pct_p90,
    approx_percentile(max_diff_pct, 0.95) AS max_diff_pct_p95,
    approx_percentile(max_diff_pct, 0.99) AS max_diff_pct_p99
FROM diffs
WHERE max_diff IS NOT NULL
GROUP BY 1,2
ORDER BY 1,2

Compressed Schema

As an additional sanity check, let's query directly from the compressed schema.

Methodology:

1. Network Check uses UNITED AIRLINESAETNA CHOICE POS as the product_network_label for Aetna TPAs. We'll use just one of its largest files, file_hash D8A9FEE844A0ABA0 (mrf_healthsparq_com_aetnacvs_egress_nophi_kyruushsq_com_prd_mrf_aetnacvs_i_alicsi_2025_06_05_innetworkrates_2025_06_05_pl_2k3_tr25_ba521fe5eefe81f5d10ac2c595fd24a4313506f2b43f92f279e786f70426b457_aetna_life_insurance_company_json_gz)
2. Identify files labeled with OPEN ACCESS MANAGED CHOICE or UNITED AIRLINESAETNA CHOICE POS (using the file_hash).
3. Try to look for provider-codes that appear in both files.
4. Compare the rates for these provider-codes across the two files.

Results:

Code

WITH 
file_labels AS (
    SELECT distinct file_hash
    FROM tq_production.public_2025_06.compressed_idx_file_label
    WHERE (
        product_network_label = 'OPEN ACCESS MANAGED CHOICE'
        OR file_hash = 'D8A9FEE844A0ABA0'
    )
    AND payer_id IN ('7','861')
),
files_references AS (
    SELECT payer_id, file_hash, provider_reference_hash, rate_hash
    FROM tq_production.public_2025_06.compressed_rates_files_references fr
    WHERE fr.provider_reference_hash IS NOT NULL
    AND payer_id IN ('7','861')
    AND fr.file_hash IN (
        SELECT file_hash from file_labels
    )
),
providers AS (
    SELECT *
    FROM (
        SELECT 
            p.npi, 
            array_agg(distinct provider_reference_hash) as provider_reference_hashes,
            count(distinct payer_id)
        FROM tq_production.public_2025_06.compressed_providers p
        WHERE payer_id IN ('7','861')
        GROUP BY 1
        ORDER BY 2 DESC
        LIMIT 200
    ), unnest(provider_reference_hashes) AS t(provider_reference_hash)
),
rates AS (
    SELECT *
    FROM (
        SELECT 
            service_code,
            billing_code_modifier,
            billing_code, 
            billing_code_type, 
            negotiated_rate, 
            negotiated_type,
            array_agg(distinct rate_hash) as rate_hashes,
            count(distinct payer_id) as n_payers
        FROM tq_production.public_2025_06.compressed_rates
        WHERE 
            payer_id IN ('7','861')
            AND negotiated_type IN ('fee schedule', 'negotiated', 'percentage', 'derived', 'per diem')
            AND (
                (negotiated_type != 'percentage' AND negotiated_rate > 1) OR 
                (negotiated_type = 'percentage' AND negotiated_rate > 0 AND negotiated_rate <= 100)
            )
            AND (billing_code_modifier[1] = '' OR billing_code_modifier IS NULL)
        GROUP BY 1,2,3,4,5,6
        HAVING count(distinct payer_id) > 1
        ORDER BY n_payers DESC
        LIMIT 1000
    ), unnest(rate_hashes) AS t(rate_hash)
)
SELECT 
    p.npi,
    r.service_code,
    r.billing_code,
    r.billing_code_type,
    r.billing_code_modifier,
    r.negotiated_rate,
    r.negotiated_type,
    fr.payer_id
FROM files_references fr
JOIN rates r
    ON r.rate_hash = fr.rate_hash
JOIN providers p
    ON p.provider_reference_hash = fr.provider_reference_hash

Checking Work

Fact Checks

    
# %%
from IPython import get_ipython

if get_ipython() is not None:
    get_ipython().run_line_magic("load_ext", "autoreload")
    get_ipython().run_line_magic("autoreload", "2")
from dotenv import load_dotenv
import pandas as pd
from tqds.sql import db, sql
import logging

import pandas as pd
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt
sns.set_theme(style='whitegrid', rc={'figure.figsize': (14, 12)})

pd.options.display.max_columns = None
pd.options.display.max_rows = 100
pd.options.display.max_colwidth = None
pd.set_option('display.float_format', lambda x: '%.3f' % x)
trino_conn = db.TrinoClient().conn
redshift_conn = db.RedshiftClient().conn
query = sql.Query()

logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s',
    handlers=[
        logging.FileHandler('fact_check.log'),
        logging.StreamHandler()
    ]
)
logger = logging.getLogger(__name__)


# %% [markdown]
"""
Fact Check
"""

# %%
samples = pd.read_sql(f"""
SELECT 
    provider_id,
    billing_class,
    billing_code_type,
    billing_code,
    negotiated_type,
    count(*) AS n
FROM tq_production.public_2025_05.core_rates cr
WHERE payer_id IN ('861','7')
    AND product_network_label IN ('OPEN ACCESS MANAGED CHOICE', 'CHOICE POS II')
    AND billing_code_modifier = ''
    AND billing_code_type IN ('HCPCS', 'MS-DRG')
    AND taxonomy_grouping = 'Hospitals'
GROUP BY 1,2,3,4,5
HAVING count(*) > 1
    AND count(DISTINCT product_network_label) = 2
    AND count(DISTINCT payer_id) = 2
ORDER BY rand()
LIMIT 20
""", con=trino_conn)
samples

# %%
for _,sample in samples.iterrows():

    test = pd.read_sql(f"""
    SELECT *
    FROM tq_production.public_2025_05.core_rates cr
    WHERE payer_id = '7'
        AND product_network_label = 'OPEN ACCESS MANAGED CHOICE'
        AND billing_code_modifier = ''
        AND taxonomy_grouping = 'Hospitals'
        AND billing_code_type = '{sample.billing_code_type}'
        AND billing_code = '{sample.billing_code}'
        AND provider_id = '{sample.provider_id}'
        AND negotiated_type = '{sample.negotiated_type}'
    LIMIT 10
    """, con=trino_conn)

    test2 = pd.read_sql(f"""
    SELECT *
    FROM tq_production.public_2025_05.core_rates cr
    WHERE payer_id = '861'
        AND product_network_label = 'CHOICE POS II'
        AND billing_code_modifier = ''
        AND taxonomy_grouping = 'Hospitals'
        AND billing_code_type = '{sample.billing_code_type}'
        AND billing_code = '{sample.billing_code}'
        AND provider_id = '{sample.provider_id}'
        AND negotiated_type = '{sample.negotiated_type}'
    ORDER BY negotiated_rate DESC
    LIMIT 10
    """, con=trino_conn)
    test2

    if any(test2['negotiated_rate'].isin(test['negotiated_rate'])) & any(test['negotiated_rate'].isin(test2['negotiated_rate'])):
        print('MATCH')
        logger.info(f'MATCH found for provider_id={sample.provider_id}, billing_code={sample.billing_code}, billing_code_type={sample.billing_code_type}, negotiated_type={sample.negotiated_type}')
    else:
        print('NO MATCH')
        logger.info(f'NO MATCH for provider_id={sample.provider_id}, billing_code={sample.billing_code}, billing_code_type={sample.billing_code_type}, negotiated_type={sample.negotiated_type}')

    print(test['id'])
    print(test2['id'])
    print(any(test2['negotiated_rate'].isin(test['negotiated_rate'])))
    print(any(test['negotiated_rate'].isin(test2['negotiated_rate'])))
    
    logger.info(f'Test1 IDs: {list(test["id"])}, Test2 IDs: {list(test2["id"])}')
    logger.info(f'Test2 rates in Test1: {any(test2["negotiated_rate"].isin(test["negotiated_rate"]))}, Test1 rates in Test2: {any(test["negotiated_rate"].isin(test2["negotiated_rate"]))}')

    print('---')

2025-08-23 16:46:06,164 - INFO - MATCH found for provider_id=1890, billing_code=32506, billing_code_type=HCPCS, negotiated_type=fee schedule
2025-08-23 16:46:06,177 - INFO - Test1 IDs: ['DFA5E3B6D73285895D2C13480B6ADAA4'], Test2 IDs: ['3AFEFF40B9DAFA861FA16E79AC272403']
2025-08-23 16:46:06,178 - INFO - Test2 rates in Test1: True, Test1 rates in Test2: True
MATCH
0    DFA5E3B6D73285895D2C13480B6ADAA4
Name: id, dtype: object
0    3AFEFF40B9DAFA861FA16E79AC272403
Name: id, dtype: object
True
True
---
2025-08-23 16:46:25,952 - INFO - MATCH found for provider_id=3106, billing_code=62180, billing_code_type=HCPCS, negotiated_type=negotiated
2025-08-23 16:46:25,956 - INFO - Test1 IDs: ['CE9CB414446DA22333AC01DA943FBA55'], Test2 IDs: ['66AC1456D55CFD8253F40831CAEB9788']
2025-08-23 16:46:25,957 - INFO - Test2 rates in Test1: True, Test1 rates in Test2: True
MATCH
0    CE9CB414446DA22333AC01DA943FBA55
Name: id, dtype: object
0    66AC1456D55CFD8253F40831CAEB9788
Name: id, dtype: object
True
True
---
2025-08-23 16:46:39,090 - INFO - MATCH found for provider_id=6578, billing_code=0757T, billing_code_type=HCPCS, negotiated_type=percentage
2025-08-23 16:46:39,093 - INFO - Test1 IDs: ['A39ED393E8E9656FEAFB65282F421F1A'], Test2 IDs: ['68C43296E91E12E9FB25A1322F01509C']
2025-08-23 16:46:39,094 - INFO - Test2 rates in Test1: True, Test1 rates in Test2: True
MATCH
0    A39ED393E8E9656FEAFB65282F421F1A
Name: id, dtype: object
0    68C43296E91E12E9FB25A1322F01509C
Name: id, dtype: object
True
True
---
2025-08-23 16:46:56,506 - INFO - MATCH found for provider_id=4736, billing_code=30520, billing_code_type=HCPCS, negotiated_type=negotiated
2025-08-23 16:46:56,512 - INFO - Test1 IDs: ['A926BFD9D931221F4F2D823459406EBE', 'F5C64B85F74C3E8450F6B2940C22B247', '93C51E1ABBC01B183DC7486773E1795E', 'D227BBAC8948C39E3FC8D44481751F66', 'F39176469FDC2FED25150F2564005DBD'], Test2 IDs: ['C5FC389A0A649C882DAF02AB318F32ED', '3DBE64782100BCFD978DC8A8BDC8E885', '180946773B223F9FA38B9E493FF687D9', '93910C8D0D3E14B11960269C6373B105', '71788A5F5BEEBFBFFB7CB389DE2BCD88']
2025-08-23 16:46:56,512 - INFO - Test2 rates in Test1: True, Test1 rates in Test2: True
MATCH
0    A926BFD9D931221F4F2D823459406EBE
1    F5C64B85F74C3E8450F6B2940C22B247
2    93C51E1ABBC01B183DC7486773E1795E
3    D227BBAC8948C39E3FC8D44481751F66
4    F39176469FDC2FED25150F2564005DBD
Name: id, dtype: object
0    C5FC389A0A649C882DAF02AB318F32ED
1    3DBE64782100BCFD978DC8A8BDC8E885
2    180946773B223F9FA38B9E493FF687D9
3    93910C8D0D3E14B11960269C6373B105
4    71788A5F5BEEBFBFFB7CB389DE2BCD88
Name: id, dtype: object
True
True
---
2025-08-23 16:47:25,107 - INFO - MATCH found for provider_id=2276, billing_code=72052, billing_code_type=HCPCS, negotiated_type=percentage
2025-08-23 16:47:25,121 - INFO - Test1 IDs: ['5051AF7740683278A26D45D43900EA12'], Test2 IDs: ['4D91473EB95035BB740ED99C90EA6DF2']
2025-08-23 16:47:25,123 - INFO - Test2 rates in Test1: True, Test1 rates in Test2: True
MATCH
0    5051AF7740683278A26D45D43900EA12
Name: id, dtype: object
0    4D91473EB95035BB740ED99C90EA6DF2
Name: id, dtype: object
True
True
---
2025-08-23 16:47:44,396 - INFO - MATCH found for provider_id=2377, billing_code=6010F, billing_code_type=HCPCS, negotiated_type=fee schedule
2025-08-23 16:47:44,401 - INFO - Test1 IDs: ['B52DC7A76E1E7ED5EB6C66BA32FDED4F'], Test2 IDs: ['D294A46276FD4C6C8FD2102A9A52713C']
2025-08-23 16:47:44,402 - INFO - Test2 rates in Test1: True, Test1 rates in Test2: True
MATCH
0    B52DC7A76E1E7ED5EB6C66BA32FDED4F
Name: id, dtype: object
0    D294A46276FD4C6C8FD2102A9A52713C
Name: id, dtype: object
True
True
---
2025-08-23 16:47:57,934 - INFO - MATCH found for provider_id=2698, billing_code=88331, billing_code_type=HCPCS, negotiated_type=percentage
2025-08-23 16:47:57,947 - INFO - Test1 IDs: ['0A0C405B9309FDED9D423F992D35FC59'], Test2 IDs: ['6EECB9D449A651D9979DD420B1FBDF9A']
2025-08-23 16:47:57,949 - INFO - Test2 rates in Test1: True, Test1 rates in Test2: True
MATCH
0    0A0C405B9309FDED9D423F992D35FC59
Name: id, dtype: object
0    6EECB9D449A651D9979DD420B1FBDF9A
Name: id, dtype: object
True
True
---
2025-08-23 16:48:21,638 - INFO - MATCH found for provider_id=4486, billing_code=J7120, billing_code_type=HCPCS, negotiated_type=percentage
2025-08-23 16:48:21,657 - INFO - Test1 IDs: ['6781185C1F5E55AFFCB045849E64BBF3', '8D34BE94D43C83681C2B830006766907'], Test2 IDs: ['EB6DFF5C18F871CAD39EA8044A0869F3', '6DF1926749FBD790375640E85D73AC8F']
2025-08-23 16:48:21,662 - INFO - Test2 rates in Test1: True, Test1 rates in Test2: True
MATCH
0    6781185C1F5E55AFFCB045849E64BBF3
1    8D34BE94D43C83681C2B830006766907
Name: id, dtype: object
0    EB6DFF5C18F871CAD39EA8044A0869F3
1    6DF1926749FBD790375640E85D73AC8F
Name: id, dtype: object
True
True
---
2025-08-23 16:48:37,042 - INFO - MATCH found for provider_id=4999, billing_code=0060U, billing_code_type=HCPCS, negotiated_type=fee schedule
2025-08-23 16:48:37,048 - INFO - Test1 IDs: ['CDD7EACAB0FB6D66128DFCA79238F88B'], Test2 IDs: ['44556901E6FF1A123FE95670715453B9']
2025-08-23 16:48:37,049 - INFO - Test2 rates in Test1: True, Test1 rates in Test2: True
MATCH
0    CDD7EACAB0FB6D66128DFCA79238F88B
Name: id, dtype: object
0    44556901E6FF1A123FE95670715453B9
Name: id, dtype: object
True
True
---
2025-08-23 16:48:53,049 - INFO - MATCH found for provider_id=3730, billing_code=33675, billing_code_type=HCPCS, negotiated_type=fee schedule
2025-08-23 16:48:53,051 - INFO - Test1 IDs: ['523AB1E1595E10815B3A93BC1572B936'], Test2 IDs: ['B246E6A02459869BEEEF81E6EB42AF45']
2025-08-23 16:48:53,052 - INFO - Test2 rates in Test1: True, Test1 rates in Test2: True
MATCH
0    523AB1E1595E10815B3A93BC1572B936
Name: id, dtype: object
0    B246E6A02459869BEEEF81E6EB42AF45
Name: id, dtype: object
True
True
---
2025-08-23 16:49:02,684 - INFO - MATCH found for provider_id=6140, billing_code=63282, billing_code_type=HCPCS, negotiated_type=negotiated
2025-08-23 16:49:02,693 - INFO - Test1 IDs: ['1DC469DB8B1D624178E75BB4802A6F72'], Test2 IDs: ['55F7E2D37722C5C0FC007544B0A80762']
2025-08-23 16:49:02,693 - INFO - Test2 rates in Test1: True, Test1 rates in Test2: True
MATCH
0    1DC469DB8B1D624178E75BB4802A6F72
Name: id, dtype: object
0    55F7E2D37722C5C0FC007544B0A80762
Name: id, dtype: object
True
True
---
2025-08-23 16:49:14,325 - INFO - MATCH found for provider_id=1982, billing_code=36217, billing_code_type=HCPCS, negotiated_type=percentage
2025-08-23 16:49:14,329 - INFO - Test1 IDs: ['710BF4FCA2B4094485BCBE0DEF563A92', '8252DEA4CBEBBB0B48E86D525515DEBC'], Test2 IDs: ['AF59319D4128F3EE42D1B63218017623', '28A3DBF2A5BBC5E3AEF6FD90DD64B61A']
2025-08-23 16:49:14,330 - INFO - Test2 rates in Test1: True, Test1 rates in Test2: True
MATCH
0    710BF4FCA2B4094485BCBE0DEF563A92
1    8252DEA4CBEBBB0B48E86D525515DEBC
Name: id, dtype: object
0    AF59319D4128F3EE42D1B63218017623
1    28A3DBF2A5BBC5E3AEF6FD90DD64B61A
Name: id, dtype: object
True
True
---
2025-08-23 16:49:27,498 - INFO - MATCH found for provider_id=6223, billing_code=0586T, billing_code_type=HCPCS, negotiated_type=percentage
2025-08-23 16:49:27,505 - INFO - Test1 IDs: ['A2BA3CA49B9099EA7453FEA793F86D2C'], Test2 IDs: ['7BDB19FA0E2B8747C39A99C9AB2BBCA7']
2025-08-23 16:49:27,506 - INFO - Test2 rates in Test1: True, Test1 rates in Test2: True
MATCH
0    A2BA3CA49B9099EA7453FEA793F86D2C
Name: id, dtype: object
0    7BDB19FA0E2B8747C39A99C9AB2BBCA7
Name: id, dtype: object
True
True
---
2025-08-23 16:49:38,332 - INFO - MATCH found for provider_id=5077, billing_code=54408, billing_code_type=HCPCS, negotiated_type=negotiated
2025-08-23 16:49:38,334 - INFO - Test1 IDs: ['2B1D81ABE897735AC70FF7B6FD93FE33', '3AAC28B7EC1EBAAD8C4A190EF6C96E52'], Test2 IDs: ['92ADB2C0CB5A4454203E3B0AC2F48281', 'E92BE4A57DC6B48630A68833C9CFB9FA']
2025-08-23 16:49:38,335 - INFO - Test2 rates in Test1: True, Test1 rates in Test2: True
MATCH
0    2B1D81ABE897735AC70FF7B6FD93FE33
1    3AAC28B7EC1EBAAD8C4A190EF6C96E52
Name: id, dtype: object
0    92ADB2C0CB5A4454203E3B0AC2F48281
1    E92BE4A57DC6B48630A68833C9CFB9FA
Name: id, dtype: object
True
True
---
2025-08-23 16:49:44,663 - INFO - MATCH found for provider_id=2772, billing_code=77799, billing_code_type=HCPCS, negotiated_type=percentage
2025-08-23 16:49:44,665 - INFO - Test1 IDs: ['372694BAFE5E8F6EBE35C0D140BDFC89'], Test2 IDs: ['5CFFC4D498E00D00E8787E93A0BE7FBE']
2025-08-23 16:49:44,666 - INFO - Test2 rates in Test1: True, Test1 rates in Test2: True
MATCH
0    372694BAFE5E8F6EBE35C0D140BDFC89
Name: id, dtype: object
0    5CFFC4D498E00D00E8787E93A0BE7FBE
Name: id, dtype: object
True
True
---
2025-08-23 16:49:56,349 - INFO - MATCH found for provider_id=6378, billing_code=31520, billing_code_type=HCPCS, negotiated_type=negotiated
2025-08-23 16:49:56,351 - INFO - Test1 IDs: ['44D08ABB74E4061FB22E7ABDBFBC59B9'], Test2 IDs: ['E7B92C2A7E8B18BBE16E42A9DBDD08DB']
2025-08-23 16:49:56,352 - INFO - Test2 rates in Test1: True, Test1 rates in Test2: True
MATCH
0    44D08ABB74E4061FB22E7ABDBFBC59B9
Name: id, dtype: object
0    E7B92C2A7E8B18BBE16E42A9DBDD08DB
Name: id, dtype: object
True
True
---
2025-08-23 16:50:10,828 - INFO - MATCH found for provider_id=2870, billing_code=85045, billing_code_type=HCPCS, negotiated_type=negotiated
2025-08-23 16:50:10,839 - INFO - Test1 IDs: ['5EA159D7B56DFF0FDD95A28CEAFEBD2F', 'D6297A6B2A0B39EA8970749C9F902C82'], Test2 IDs: ['85213AC2F317C6434D057E8A07252495', '67538B49F6AD88312013966D50D994FC']
2025-08-23 16:50:10,839 - INFO - Test2 rates in Test1: True, Test1 rates in Test2: True
MATCH
0    5EA159D7B56DFF0FDD95A28CEAFEBD2F
1    D6297A6B2A0B39EA8970749C9F902C82
Name: id, dtype: object
0    85213AC2F317C6434D057E8A07252495
1    67538B49F6AD88312013966D50D994FC
Name: id, dtype: object
True
True
---
2025-08-23 16:50:21,679 - INFO - MATCH found for provider_id=4687, billing_code=83857, billing_code_type=HCPCS, negotiated_type=negotiated
2025-08-23 16:50:21,688 - INFO - Test1 IDs: ['3A5B24A16FD123D188A83E1A090EB773'], Test2 IDs: ['012E7CD356F32CFAA3BD400999FFCD08']
2025-08-23 16:50:21,688 - INFO - Test2 rates in Test1: True, Test1 rates in Test2: True
MATCH
0    3A5B24A16FD123D188A83E1A090EB773
Name: id, dtype: object
0    012E7CD356F32CFAA3BD400999FFCD08
Name: id, dtype: object
True
True
---
2025-08-23 16:50:38,169 - INFO - MATCH found for provider_id=2686, billing_code=28575, billing_code_type=HCPCS, negotiated_type=fee schedule
2025-08-23 16:50:38,176 - INFO - Test1 IDs: ['3DDD055BC6AC6539FE6DC189C1D5DDB1', 'D8537D3823EF9CD207A081E515B1FAF7'], Test2 IDs: ['7CE9907673EE8685AE619CC2633514B2', '3CEC27DA0AA0B9642A562AD92141FBA9']
2025-08-23 16:50:38,178 - INFO - Test2 rates in Test1: True, Test1 rates in Test2: True
MATCH
0    3DDD055BC6AC6539FE6DC189C1D5DDB1
1    D8537D3823EF9CD207A081E515B1FAF7
Name: id, dtype: object
0    7CE9907673EE8685AE619CC2633514B2
1    3CEC27DA0AA0B9642A562AD92141FBA9
Name: id, dtype: object
True
True
---
2025-08-23 16:50:52,192 - INFO - MATCH found for provider_id=2544, billing_code=33978, billing_code_type=HCPCS, negotiated_type=fee schedule
2025-08-23 16:50:52,214 - INFO - Test1 IDs: ['BD224664741C8EAC68E2D2151E93B4FE'], Test2 IDs: ['E6E8E40E23B77EF4A79DFED1E17E407D']
2025-08-23 16:50:52,215 - INFO - Test2 rates in Test1: True, Test1 rates in Test2: True
MATCH
0    BD224664741C8EAC68E2D2151E93B4FE
Name: id, dtype: object
0    E6E8E40E23B77EF4A79DFED1E17E407D
Name: id, dtype: object
True
True
---

Test Two Networks That Are Totally Different

Instead of comparing OPEN ACCESS MANAGED CHOICE and CHOICE POS II, let's compare OPEN ACCESS MANAGED CHOICE and Cigna's NATIONAL OAP (which are totally different networks).

billing_class	billing_code_type	max_diff_p90	max_diff_p95	max_diff_p99	max_diff_pct_p90	max_diff_pct_p95	max_diff_pct_p99
institutional	HCPCS	1941.13	6053.43	24118.8	4.90765	13.8672	149.933
institutional	MS-DRG	27137.3	41891.5	92336.3	0.889177	241.672	1297.89
professional	HCPCS	1837.06	3346.09	11102.3	1.38561	2.02383	6.03381

WITH samples AS (
    SELECT 
        provider_id,
        billing_class,
        billing_code_type,
        billing_code,
        count(*) AS n
    FROM tq_production.public_2025_05.core_rates cr
    WHERE payer_id IN ('76','7')
    AND product_network_label IN ('OPEN ACCESS MANAGED CHOICE', 'NATIONAL OAP')
    AND billing_code_modifier = ''
    AND billing_code_type IN ('HCPCS', 'MS-DRG')
    AND taxonomy_grouping = 'Hospitals'
    GROUP BY 1,2,3,4
    HAVING count(*) > 1
    AND count(DISTINCT product_network_label) = 2
    AND count(DISTINCT payer_id) = 2
    ORDER BY rand()
    LIMIT 100000
),
per_label AS (
    SELECT 
        cr.provider_id,
        cr.billing_class,
        cr.billing_code_type,
        cr.billing_code,
        MIN(CASE WHEN cr.product_network_label = 'OPEN ACCESS MANAGED CHOICE' THEN cr.negotiated_rate END) AS min_rate_open_access,
        MAX(CASE WHEN cr.product_network_label = 'OPEN ACCESS MANAGED CHOICE' THEN cr.negotiated_rate END) AS max_rate_open_access,
        MIN(CASE WHEN cr.product_network_label = 'NATIONAL OAP' THEN cr.negotiated_rate END) AS min_rate_choice_pos,
        MAX(CASE WHEN cr.product_network_label = 'NATIONAL OAP' THEN cr.negotiated_rate END) AS max_rate_choice_pos
    FROM tq_production.public_2025_05.core_rates cr
    JOIN samples s
    ON cr.provider_id = s.provider_id
    AND cr.billing_class = s.billing_class
    AND cr.billing_code_type = s.billing_code_type
    AND cr.billing_code = s.billing_code
    WHERE cr.payer_id IN ('76','7')
    AND cr.product_network_label IN ('OPEN ACCESS MANAGED CHOICE', 'NATIONAL OAP')
    AND cr.billing_code_modifier = ''
    AND cr.billing_code_type IN ('HCPCS', 'MS-DRG')
    GROUP BY 1,2,3,4
),
diffs AS (
    SELECT
        provider_id,
        billing_class,
        billing_code_type,
        billing_code,
        -- replicate pivoted metrics you used downstream
        min_rate_open_access,
        max_rate_open_access,
        min_rate_choice_pos,
        max_rate_choice_pos,
        ABS(max_rate_choice_pos - max_rate_open_access) AS max_diff,
        ABS(max_rate_choice_pos - max_rate_open_access) / NULLIF(max_rate_open_access, 0) AS max_diff_pct
    FROM per_label
    WHERE max_rate_open_access IS NOT NULL
    AND max_rate_choice_pos IS NOT NULL
)
SELECT
    billing_class,
    billing_code_type,
    approx_percentile(max_diff, 0.90) AS max_diff_p90,
    approx_percentile(max_diff, 0.95) AS max_diff_p95,
    approx_percentile(max_diff, 0.99) AS max_diff_p99,
    approx_percentile(max_diff_pct, 0.90) AS max_diff_pct_p90,
    approx_percentile(max_diff_pct, 0.95) AS max_diff_pct_p95,
    approx_percentile(max_diff_pct, 0.99) AS max_diff_pct_p99
FROM diffs
WHERE max_diff IS NOT NULL
GROUP BY 1,2
ORDER BY 1,2