Hospital Plan Bridge
Plan Bridge Analysis​
PPO​
-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_ref_plan_bridge_analysis_ppo_{{ n_chunk }}_{{ sub_version }}
-- Subversion: {{ sub_version }}
-- n_chunk: {{ n_chunk }}
{% set general_keywords = [
'ALLCOMMERCIAL',
'COMMERCIAL',
'BROAD',
'ALLPLAN',
'ALLPRODUCT',
'ALLPAY',
'ALLCHOICEPLANS',
'ALLOTHER',
] %}
{% set ppo_keywords = [
'PPO',
'PREFERRED'
] %}
{% set payer_keywords = {
7: [
'COMM',
'POS'
],
76: [
'OAP',
'OPENACCESSPLUS',
'BROAD'
],
42: [
'PREFERRED',
'CHOICE'
],
229: [],
49: [],
44: [],
643: [
'CHOICE',
'CORE',
],
169: [],
403: [],
389: [
'PREFERRED',
'CHOICE'
],
388: [
'PPC',
'OPTIONS',
'BLUECROSS'
],
101: [],
61: [],
628: [],
160: [],
52: [],
392: [],
398: [],
50: [],
391: [],
43: [],
454: [],
461: [],
456: [],
720: [],
636: [],
53: [],
47: [],
56: [],
168: [],
299: [],
317: [],
300: [],
286: [],
723: [],
354: [],
390: [],
51: [],
397: [],
458: [],
799: [],
111: [],
166: [],
770: [],
174: [],
383: [],
455: [],
165: [],
54: [],
462: [],
522: [],
151: [],
772: [],
37: [],
633: [],
774: [],
813: [],
504: [],
849: [],
121: [],
272: [],
552: [],
277: [],
791: [],
768: [],
567: [],
624: [],
779: [],
305: [],
728: [],
784: [],
699: [],
356: [],
27: [],
971: [],
97: [],
108: [],
638: [],
} %}
{% set exclusion_exceptions = [
'HMO/PPO/POS/INDEMNITY',
] %}
{% set exclusions = [
'OUTOF',
'ADVANTAGE',
'DUAL',
'INDEMNITY',
'OON',
'MEDICARE',
'MEDICAID',
'BEHAV',
'INTERNATIONAL',
'FEDERAL',
'ALTERNATE',
'CTY',
'CHP',
'MMAI',
'VACCN',
'OOS',
'GVT',
'COVID19',
'BCBS FEP',
'MULTIPLAN',
'PHCS',
'CHIPKM',
'TRIWESTGOV',
'MGMCD',
'MMC',
'MCAREPFFS',
'MCAREADV',
'VACCA',
'MEDICAREHMO',
'DUALELIGIBLES',
'MAHMOANDPPO',
'MCDHMO',
'UHCMEDIADVAN',
'COVEREDCA',
'NYSTATEOFHEALTH',
'AARP',
'EXCHANG',
'MGDM/CARE',
'UNITEDHEALTHCARECOMMUNITY',
'STATEGOVERNMENTPROGRAM',
'MCAREHMO',
'MEDIBLUE',
'HIX',
'DSNP',
'MMOMEDICARE',
'GOVERNMENT'
] %}
{% set exclusion_exact_matches = [
'BH',
'HMO',
'MM',
'PMAP',
'MAP',
'MA',
'MM',
'PCB'
] %}
{% set payer_exclusions = {
(7, 'NATIONAL'): [
'SIGN',
'ASA',
'TRAD',
'ADMIN',
'NB',
'STARKM',
'CHIPKM',
'FEHB'
],
(76, 'NATIONAL'): [
'SAR',
'SHAREDADMIN',
'STAR',
'SPRING',
'FEHB',
'MGMCD',
'PSYCHIATRIC',
'MDCPSEMPLOYEE'
],
(643, 'NATIONAL'): [
'HARP',
'STARPLUS',
'STARKM',
'STARKIDS',
'STARPERINATE',
'MEDIADVAN',
'VACCNMM',
'UNITEDHEALTHYKIDS',
'VACCN/OPTUM',
'STARKIDKM',
'VACCN',
'PSYCHIATRIC',
'FEHB',
'VACNN',
'UHCVA',
'MEDICA',
'VACOMMUNITYCARENETWORK'
],
(42, 'NY'): [
'BLUECARD',
'BCBSWNY',
],
(42, 'CA'): [
'HEALTHNET',
'UNITED',
],
(42, 'NATIONAL'): [
'ANTHEMHLTHKEEPMEDICIAD',
'MGMCD',
'BCBSFEP',
'MISSOURICAREMGMCD',
],
(403, 'CA'): [
'HEALTHNET',
'UNITED',
'CARD',
'OUT',
'STUDENT',
'PROMISE',
'TRIWEST',
'FEP'
],
(169, 'TX'): [
'BELL',
'CITY',
],
(389, 'IL'): [
'DIRECT',
'MYBLUE',
'FOCUS',
'CHICAGO',
'PRECISION',
'ADVOCATE',
'DUAL',
],
(388, 'NATIONAL'): [],
(49, 'NATIONAL'): [],
(229, 'NATIONAL'): [
'SECUREHORIZONS',
'HORIZONNJHEALTHFAMILYKIDCARE',
],
(44, 'NATIONAL'): [],
(101, 'NATIONAL'): [],
(522, 'NATIONAL'): [
'KAISERHMO',
'KAISER-REHAB',
'CIGNAKAISERPERMANENTE[100618]'
]
} %}
{% set raw_payer_name_exclusions = {
522: [
'MULTIPLAN',
'MULTIPLAN',
'COMMERCIAL 1028',
'AETNA[200]',
'KAISERMEDIWASHINGTON',
'KAISERPERMANENTE[100299]',
'KAISERPERMANENTE 1560]',
'KAISERFOUNDATION EALTH',
'KAISERPERMANENTE 253]',
'KAISERPERMANENTE 253]',
'KAISERMCAL',
'KAISERPERMANENTE[1560]',
'CIGNA[1006]',
'KAISERPERMANENTE_FFS',
'KAISERMCALPEDIATRIC',
'CIGNA[1006]', 'PHCSMULTIPLAN[1022]',
'CIGNA[1006]', 'PHCSMULTIPLAN[1022]',
'PHCSMULTIPLAN[1022]', 'CIGNA[1006]',
'PHCSMULTIPLAN[1022]', 'CIGNA[1006]',
]
} %}
{% set payer_ids = payer_keywords.keys() %}
{% set product_network_mapping = {
7: 'Open Access Managed Choice',
76: 'National OAP',
643: 'Choice Plus',
403: 'Group PPO',
42: {
'NY': 'NY PPO',
'CA': 'CA Blue Cross PPO',
'OH': 'OH PPO',
'CO': 'CO Blue Preferred PPO',
'MO': 'MO Blue Access PPO',
'IN': 'IN Blue Access PPO',
'GA': 'GA Blue Choice PPO',
'NH': 'NH PPO',
'KY': 'KY PPO',
'VA': 'VA Keycare PPO',
'NV': 'NV Choice PPO',
'WI': 'WI Blue Access PPO',
'ME': 'ME Blue Choice PPO',
'CT': 'CT Century Preferred PPO'
},
169: 'Blue Choice PPO',
229: 'PPO',
388: 'BlueChoice PPO',
389: 'PPO Participating Provider Options',
49: 'PPO',
44: 'Preferred Provider Network (PPO)',
101: 'Excellus Bluecross Blueshield',
61: {
'UT': 'UT PPO',
'ID': 'ID PPO',
'WA': 'WA PPO',
'OR': 'OR PPO'
},
628: 'PBC PPO (Heritage) Network',
160: 'Preferred PPO',
52: 'Preferred Provider Network',
392: 'PPO',
398: 'Preferred',
50: 'Aware PPO',
391: 'PPO - Preferred Care PPO',
43: 'PPO',
454: 'True Blue PPO',
461: 'IA HMO',
456: 'Preferred Provider Network',
720: 'Choicenet PPO',
636: 'Value/Care Tiered POS - UT',
53: 'Blue Preferred PPO',
47: 'Preferredcare Blue',
56: 'QCC Commercial PPO/EPO',
168: 'PPO - Preferred Blue',
299: 'PPO',
317: 'PPO MA',
300: 'PEBB Choice',
628: 'Alaska Heritage Network',
160: 'Preferred PPO',
52: 'Preferred Provider Network',
392: 'PPO',
398: 'Preferred',
50: 'Aware PPO',
391: 'PPO - Preferred Care PPO',
43: 'PPO',
454: 'True Blue PPO',
461: 'SD PPO',
456: 'Preferred Provider Network',
720: 'Choicenet PPO',
636: 'Value/Care Tiered POS - UT',
53: 'Blue Preferred PPO',
47: 'Preferredcare Blue',
56: 'KHPE Commercial HMO/POS',
168: 'PPO - Preferred Blue',
299: 'PPO',
317: 'PPO MA',
300: 'PEBB Choice',
286: 'MVP EPO / PPO',
723: 'Essential Plan',
354: 'Premium',
390: 'Blue Choice',
51: 'Networkblue PPO',
397: 'PPO',
458: 'PPO',
799: 'Quartz Tiered Choice Plus (QHBPC WI): Quartz',
111: 'PPO',
166: 'Preferred Provider Organization PPO',
770: 'Commercial PPO',
174: 'PPO',
383: 'PPO',
455: 'PPO',
165: 'Blue Preferred PPO',
54: 'PPO',
462: 'PPO',
522: {
'CO': 'Kaiser Health Plan (CO)',
'GA': 'Kaiser Health Plan (GA)',
'HI': 'Kaiser Health Plan (HI)',
'CA': 'Kaiser Health Plan (CA)',
'DC': 'Kaiser Health Plan Mid Atlantic',
'MD': 'Kaiser Health Plan Mid Atlantic',
'VA': 'Kaiser Health Plan Mid Atlantic',
'OR': 'Kaiser Health Plan Northwest',
'WA': 'Kaiser Health Plan Northwest',
},
151: 'Ambetter',
772: 'RealValue',
37: 'Avera Health',
633: 'PPO',
774: 'PPO',
813: 'PPO',
504: 'Health Net',
849: 'PPO',
121: 'HealthPartners',
272: 'SuperMed',
552: 'Connexus',
277: 'Molina Healthcare',
791: 'MotivNet',
768: 'PPO',
567: 'Oscar Health',
624: 'Navigator Gold',
779: 'PPO',
305: 'PPO',
728: 'PPO',
784: 'PPO',
699: 'PPO',
356: 'PPO'
} %}
CREATE TABLE {{ schema_name }}.tmp_ref_plan_bridge_analysis_ppo_{{ n_chunk }}_{{ sub_version }}
WITH (
PARTITIONING = ARRAY['payer_id']
)
AS
WITH
-------------------------------------------------------
-- 1. Prepare Hospital Data
--
-- Almost all MSDRG payer-provider-code combinations have a rate available
-- with both NULL revenue code and NULL modifier.
-- But about almost half of HCPCS payer-provider-code combinations do not have a
-- rate available with both NULL revenue code and NULL modifier.
-------------------------------------------------------
base_hospital_data_hospital_rates AS (
SELECT
id,
provider_id,
provider_state,
payer_id,
plan_name,
raw_payer_name,
contract_methodology,
billing_class,
setting,
CASE
WHEN billing_code_type = 'DRG' THEN 'MS-DRG'
ELSE billing_code_type
END as billing_code_type,
CASE
WHEN billing_code_type = 'MS-DRG' THEN lpad(billing_code,3,'0')
ELSE billing_code
END as billing_code,
billing_code_modifiers,
revenue_code,
negotiated_dollar,
negotiated_percentage,
estimated_allowed_amount,
payer_class_name,
REPLACE(REPLACE(UPPER(plan_name),'_',' '),' ','') as plan_name_std
FROM {{ hospital_schema }}.hospital_rates
WHERE
COALESCE(billing_class, '') != 'Professional'
AND payer_class_name = 'Commercial'
AND (
negotiated_dollar > 0
OR negotiated_percentage > 0
OR (estimated_allowed_amount > 0 AND estimated_allowed_amount < 10000000)
)
AND payer_id IN (
{{ payer_ids | join(',') }}
)
-- setting
AND (
(
billing_code_type IN ('HCPCS', 'APC', 'EAPG') AND
setting != 'Inpatient'
)
OR (
billing_code_type IN ('MS-DRG', 'APR-DRG', 'DRG')
AND setting != 'Outpatient'
)
)
AND provider_id IN ({{ provider_ids }})
AND
NOT (
{% for payer_id, name_list in raw_payer_name_exclusions.items() %}
(payer_id = {{ payer_id }} AND raw_payer_name IN (
{% for name in name_list | unique %}
'{{ name }}'{% if not loop.last %}, {% endif %}
{% endfor %}
)){% if not loop.last %} OR {% endif %}
{% endfor %}
)
),
hospital_data_hospital_rates AS (
SELECT *
FROM base_hospital_data_hospital_rates
WHERE
-- filter codes
(
billing_code IN (
SELECT distinct billing_code
FROM {{ schema_name }}.tmp_rate_object_space_{{ sub_version }}
)
AND
billing_code_type IN ('HCPCS', 'MS-DRG')
)
OR
billing_code_type IN ('APR-DRG', 'APC', 'EAPG')
),
hcpcs_exceptions AS (
SELECT *
FROM (
SELECT
payer_id,
provider_id,
billing_code,
SUM(
CASE WHEN revenue_code IS NOT NULL THEN 1 ELSE 0 END
) as has_revenue_code,
count(*) as n
FROM hospital_data_hospital_rates
WHERE
billing_code_type = 'HCPCS'
GROUP BY 1,2,3
)
WHERE has_revenue_code = n
),
base AS (
SELECT *
FROM hospital_data_hospital_rates
WHERE
revenue_code IS NULL
AND billing_code_modifiers IS NULL
AND payer_class_name = 'Commercial'
UNION ALL
SELECT hr.*
FROM hospital_data_hospital_rates hr
JOIN hcpcs_exceptions he
ON hr.payer_id = he.payer_id
AND hr.provider_id = he.provider_id
AND hr.billing_code = he.billing_code
WHERE
billing_code_type = 'HCPCS'
),
rates AS (
SELECT *, 'dollar' as rate_type, negotiated_dollar as rate
FROM base
WHERE negotiated_dollar > 0
UNION ALL
SELECT *, 'percentage' as rate_type, negotiated_percentage as rate
FROM base
WHERE negotiated_percentage > 0
UNION ALL
SELECT *, 'estimated' as rate_type, estimated_allowed_amount as rate
FROM base
WHERE (
negotiated_dollar IS NULL
AND negotiated_percentage IS NULL
AND (estimated_allowed_amount > 0 AND estimated_allowed_amount < 10000000)
)
),
-------------------------------------------------------
-- 2. Compute Metrics For Each Payer-Provider-Code Combination
-------------------------------------------------------
df AS (
SELECT
provider_id,
provider_state,
payer_id,
contract_methodology,
billing_class,
setting,
billing_code_type,
billing_code,
rate_type,
count(distinct rate) as unique_rates,
array_distinct(array_agg(rate)) as rate_array,
(max(rate) - min(rate)) / avg(rate) as rate_spread,
array_agg(DISTINCT raw_payer_name) as unique_payers,
array_agg(DISTINCT plan_name) as unique_plans,
array_agg(DISTINCT id) as ids,
MAX(
COALESCE(
CASE
{% for payer_id in payer_ids %}
WHEN payer_id = {{ payer_id }} THEN
regexp_like(
plan_name_std,
'{{ "|".join(general_keywords + ppo_keywords + payer_keywords[payer_id]) }}'
)
{% endfor %}
ELSE False
END,
False
)
) as has_ppo_plan,
array_agg(
DISTINCT
CASE
{% for payer_id in payer_ids %}
WHEN
payer_id = {{ payer_id }}
AND
regexp_like(
plan_name_std,
'{{ "|".join(general_keywords + ppo_keywords + payer_keywords[payer_id]) }}'
)
THEN id
{% endfor %}
ELSE NULL
END
) as ids_with_ppo_plan,
array_agg(
DISTINCT
CASE
{% for payer_id in payer_ids %}
WHEN
payer_id = {{ payer_id }}
AND
regexp_like(
plan_name_std,
'{{ "|".join(general_keywords + ppo_keywords + payer_keywords[payer_id]) }}'
)
THEN plan_name
{% endfor %}
ELSE NULL
END
) as plans_with_ppo_plan
FROM rates
WHERE
(
NOT regexp_like(
plan_name_std,
'{{ exclusions | join("|") }}'
)
OR
plan_name_std IN (
{% for exclusion in exclusion_exceptions %}
'{{ exclusion }}'{% if not loop.last %}, {% endif %}
{% endfor %}
)
)
{% for exclusion in exclusion_exact_matches %}
AND UPPER(plan_name) != '{{ exclusion }}'
{% endfor %}
{% for payer_spec, exclusion_list in payer_exclusions.items() %}
{% if exclusion_list %}
AND NOT (
payer_id = {{ payer_spec[0] }}
{% if payer_spec[1] != 'NATIONAL' %}
AND provider_state = '{{ payer_spec[1] }}'
{% endif %}
AND regexp_like(
plan_name_std,
'{{ exclusion_list | join("|") }}'
)
)
{% endif %}
{% endfor %}
GROUP BY 1,2,3,4,5,6,7,8,9
),
df_with_flags AS (
SELECT
*,
rate_spread < 0.1 as low_rate_variability,
unique_rates = 1 as is_single_rate,
CARDINALITY(unique_plans) = 1 as is_single_plan
FROM df
)
-------------------------------------------------------
-- 3. Build Plan Bridge
-------------------------------------------------------
SELECT
provider_id,
provider_state,
rate_type,
billing_code,
billing_code_type,
contract_methodology,
billing_class,
setting,
ids,
unique_payers,
unique_plans,
rate_array,
low_rate_variability,
is_single_rate,
is_single_plan,
has_ppo_plan,
ids_with_ppo_plan,
plans_with_ppo_plan,
CASE
WHEN
(
is_single_plan = True
OR is_single_rate = True
OR low_rate_variability = True
OR has_ppo_plan = True
)
THEN True
ELSE False
END as is_mapped,
CASE
{% for payer_id, value in product_network_mapping.items() %}
{% if value is mapping %}
{% for state, label in value.items() %}
WHEN payer_id = {{ payer_id }} AND provider_state = '{{ state }}' THEN '{{ label }}'
{% endfor %}
{% else %}
WHEN payer_id = {{ payer_id }} THEN '{{ value }}'
{% endif %}
{% endfor %}
END as network_name,
CAST(payer_id AS VARCHAR) as payer_id
FROM df_with_flags
HMO​
-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_ref_plan_bridge_analysis_hmo_{{ n_chunk }}_{{ sub_version }}
-- Subversion: {{ sub_version }}
-- n_chunk: {{ n_chunk }}
{% set general_keywords = [
'ALLCOMMERCIAL',
'COMMERCIAL',
'BROAD',
'ALLPLAN',
'ALLPRODUCT',
'ALLPAY',
'ALLOTHER',
] %}
{% set hmo_keywords = [
'HMO',
'HEALTHMAINTENANCE'
] %}
{% set payer_keywords = {
7: [
'COMM',
],
76: [
],
42: [
],
229: [],
49: [],
44: [],
643: [
'CHARTER'
],
169: [],
403: [],
389: [],
388: [
'BLUECAREHMO'
],
101: [],
61: [],
628: [],
160: [],
52: [],
392: [],
398: [],
50: [],
391: [],
43: [],
454: [],
461: [],
456: [],
720: [],
636: [],
53: [],
47: [],
56: [],
168: [],
299: [],
317: [],
300: [],
286: [],
723: [],
354: [],
390: [],
51: [],
397: [],
458: [],
799: [],
111: [],
166: [],
770: [],
174: [],
383: [],
455: [],
165: [],
54: [],
462: [],
151: ['AMBETTER'],
774: ['CDPHP', 'HEALTHY'],
813: ['HAP', 'HEALTH', 'ALLIANCE'],
} %}
{% set exclusion_exceptions = [
'HMO/HMO/POS/INDEMNITY',
] %}
{% set exclusions = [
'PPO',
'OUTOF',
'ADVANTAGE',
'DUAL',
'INDEMNITY',
'OON',
'MEDICARE',
'MEDICAID',
'BEHAV',
'INTERNATIONAL',
'FEDERAL',
'ALTERNATE',
'CTY',
'CHP',
'MMAI',
'VACCN',
'OOS',
'GVT',
'COVID19',
'BCBS FEP',
'MULTIPLAN',
'PHCS',
'CHIPKM',
'TRIWESTGOV',
'MGMCD',
'MMC',
'MCAREPFFS',
'MCAREADV',
'VACCA',
'MEDICAREHMO',
'DUALELIGIBLES',
'MAHMOANDHMO',
'MCDHMO',
'UHCMEDIADVAN',
'COVEREDCA',
'NYSTATEOFHEALTH',
'AARP',
'EXCHANG',
'MGDM/CARE',
'UNITEDHEALTHCARECOMMUNITY',
'STATEGOVERNMENTPROGRAM',
'MCAREHMO',
'MEDIBLUE',
'HIX',
'DSNP',
'MMOMEDICARE',
'GOVERNMENT',
'STATEPLAN',
'STATEHEALTH'
] %}
{% set exclusion_exact_matches = [
'BH',
'MM',
'PMAP',
'MAP',
'MA',
'MM',
'PCB'
] %}
{% set payer_exclusions = {
(7, 'NATIONAL'): [
'SIGN',
'ASA',
'TRAD',
'ADMIN',
'NB',
'STARKM',
'CHIPKM',
'FEHB',
'POS'
],
(76, 'NATIONAL'): [
'SAR',
'SHAREDADMIN',
'STAR',
'SPRING',
'FEHB',
'MGMCD',
'PSYCHIATRIC',
'MDCPSEMPLOYEE',
'OAP',
'OPENACCESSPLUS',
'BROAD'
],
(643, 'NATIONAL'): [
'HARP',
'STARPLUS',
'STARKM',
'STARKIDS',
'STARPERINATE',
'MEDIADVAN',
'VACCNMM',
'UNITEDHEALTHYKIDS',
'VACCN/OPTUM',
'STARKIDKM',
'VACCN',
'PSYCHIATRIC',
'FEHB',
'VACNN',
'UHCVA',
'MEDICA',
'VACOMMUNITY CARE NETWORK',
'CHOICE',
'CORE',
],
(42, 'NY'): [
'BLUECARD',
'BCBSWNY',
],
(42, 'CA'): [
'HEALTHNET',
'UNITED',
'PREFERRED',
'CHOICE'
],
(42, 'NATIONAL'): [
'ANTHEMHLTHKEEPMEDICIAD',
'MGMCD',
'BCBSFEP',
'MISSOURICAREMGMCD',
],
(403, 'CA'): [
'HEALTHNET',
'UNITED',
'CARD',
'OUT',
'STUDENT',
'PROMISE',
'TRIWEST',
'FEP'
],
(169, 'TX'): [
'BELL',
'CITY',
],
(389, 'IL'): [
'DIRECT',
'MYBLUE',
'FOCUS',
'CHICAGO',
'PRECISION',
'ADVOCATE',
'DUAL',
'PREFERRED',
'CHOICE'
],
(388, 'NATIONAL'): [
'PPC',
'OPTIONS',
'BCBSFLSIMPLYBLUEHMO'
],
(49, 'NATIONAL'): [],
(229, 'NATIONAL'): [
'SECUREHORIZONS',
'HORIZONNJHEALTHFAMILYKIDCARE',
],
(44, 'NATIONAL'): [],
(101, 'NATIONAL'): [],
} %}
{% set payer_ids = payer_keywords.keys() %}
{% set product_network_mapping = {
7: {
'FL': 'FL HMO',
'NC': 'NC HMO',
'DE': 'DE HMO',
'MD': 'MD HMO',
'DC': 'MD HMO',
'VA': 'VA HMO',
'TX': 'TX HMO',
'NV': 'NV HMO',
'IL': 'IL HMO',
'CA': 'CA HMO',
'PA': 'PA HMO',
'GA': 'GA HMO'
},
42: {
'KY': 'KY HMO',
'CT': 'CT HMO',
'NH': 'NH HMO',
'ME': 'ME HMO',
'VA': 'VA HMO',
'IN': 'IN HMO',
'GA': 'GA HMO',
'CA': 'CA HMO',
'NV': 'NV HMO'
},
43: {
'AZ': 'HMO'
},
44: {
'MD': 'HMO',
'DC': 'HMO',
'VA': 'HMO'
},
49: {
'MI': 'HMO'
},
50: {
'MN': 'HMO'
},
52: {
'NC': 'HMO'
},
53: {
'OK': 'HMO'
},
76: {
'ME': 'ME HMO',
'TX': 'TX HMO',
'CA': 'CA HMO',
'TN': 'TN HMO',
'MO': 'MO HMO',
'NY': 'NY HMO',
'MA': 'New England HMO',
'CT': 'New England HMO',
'RI': 'New England HMO',
'NH': 'NH HMO',
'VT': 'New England HMO',
'NJ': 'NJ HMO',
'GA': 'GA HMO',
'FL': 'FL HMO',
'AZ': 'AZ HMO',
'AL': 'AL HMO',
'VA': 'VA HMO',
'NC': 'NC HMO'
},
111: {
'PA': 'HMO'
},
166: {
'NM': 'HMO'
},
168: {
'SC': 'HMO'
},
169: {
'TX': 'HMO'
},
229: {
'NJ': 'OMNIA'
},
286: {
'NY': 'HMO'
},
299: {
'MI': 'HMO'
},
317: {
'MA': 'HMO'
},
388: {
'FL': 'HMO'
},
389: {
'IL': 'HMO'
},
391: {
'LA': 'HMO'
},
392: {
'MA': 'HMO'
},
403: {
'CA': 'HMO'
},
454: {
'AR': 'HMO'
},
455: {
'HI': 'HMO'
},
461: {
'IA': 'HMO'
},
628: {
'WA': 'HMO'
},
636: {
'UT': 'HMO',
'NV': 'HMO'
},
643: 'HMO',
720: {
'MA': 'HMO'
},
770: {
'MA': 'HMO'
},
151: {
'TX': 'Ambetter HMO',
'GA': 'Ambetter HMO',
'FL': 'Ambetter HMO',
'OH': 'Ambetter HMO',
'MI': 'Ambetter HMO',
'AR': 'Ambetter HMO',
'AZ': 'Ambetter HMO',
'MS': 'Ambetter HMO',
'WA': 'Ambetter HMO',
'KS': 'Ambetter HMO',
'NC': 'Ambetter HMO',
'TN': 'Ambetter HMO',
'SC': 'Ambetter HMO',
'CA': 'Ambetter HMO',
'NE': 'Ambetter HMO',
'IL': 'Ambetter HMO',
'MO': 'Ambetter HMO',
'IN': 'Ambetter HMO',
'PA': 'Ambetter HMO',
'LA': 'Ambetter HMO',
'AL': 'Ambetter HMO',
'OK': 'Ambetter HMO',
'KY': 'Ambetter HMO',
'NV': 'Ambetter HMO'
},
774: {
'NY': 'HMO'
},
813: {
'MI': 'HMO'
}
} %}
CREATE TABLE {{ schema_name }}.tmp_ref_plan_bridge_analysis_hmo_{{ n_chunk }}_{{ sub_version }}
WITH (
PARTITIONING = ARRAY['payer_id']
)
AS
WITH
-------------------------------------------------------
-- 1. Prepare Hospital Data
--
-- Almost all MSDRG payer-provider-code combinations have a rate available
-- with both NULL revenue code and NULL modifier.
-- But about almost half of HCPCS payer-provider-code combinations do not have a
-- rate available with both NULL revenue code and NULL modifier.
-------------------------------------------------------
base_hospital_data_hospital_rates AS (
SELECT
id,
provider_id,
provider_state,
payer_id,
plan_name,
raw_payer_name,
contract_methodology,
billing_class,
setting,
CASE
WHEN billing_code_type = 'DRG' THEN 'MS-DRG'
ELSE billing_code_type
END as billing_code_type,
CASE
WHEN billing_code_type = 'MS-DRG' THEN lpad(billing_code,3,'0')
ELSE billing_code
END as billing_code,
billing_code_modifiers,
revenue_code,
negotiated_dollar,
negotiated_percentage,
estimated_allowed_amount,
payer_class_name,
REPLACE(REPLACE(UPPER(plan_name),'_',' '),' ','') as plan_name_std
FROM {{ hospital_schema }}.hospital_rates
WHERE
COALESCE(billing_class, '') != 'Professional'
AND payer_class_name = 'Commercial'
AND (
negotiated_dollar > 0
OR negotiated_percentage > 0
OR (estimated_allowed_amount > 0 AND estimated_allowed_amount < 10000000)
)
AND payer_id IN (
{{ payer_ids | join(',') }}
)
-- setting
AND (
(
billing_code_type IN ('HCPCS', 'APC', 'EAPG') AND
setting != 'Inpatient'
)
OR (
billing_code_type IN ('MS-DRG', 'APR-DRG', 'DRG')
AND setting != 'Outpatient'
)
)
AND provider_id IN ({{ provider_ids }})
),
hospital_data_hospital_rates AS (
SELECT *
FROM base_hospital_data_hospital_rates
WHERE
-- filter codes
(
billing_code IN (
SELECT distinct billing_code
FROM {{ schema_name }}.tmp_rate_object_space_{{ sub_version }}
)
AND
billing_code_type IN ('HCPCS', 'MS-DRG')
)
OR
billing_code_type IN ('APR-DRG', 'APC', 'EAPG')
),
hcpcs_exceptions AS (
SELECT *
FROM (
SELECT
payer_id,
provider_id,
billing_code,
SUM(
CASE WHEN revenue_code IS NOT NULL THEN 1 ELSE 0 END
) as has_revenue_code,
count(*) as n
FROM hospital_data_hospital_rates
WHERE
billing_code_type = 'HCPCS'
GROUP BY 1,2,3
)
WHERE has_revenue_code = n
),
base AS (
SELECT *
FROM hospital_data_hospital_rates
WHERE
revenue_code IS NULL
AND billing_code_modifiers IS NULL
AND payer_class_name = 'Commercial'
UNION ALL
SELECT hr.*
FROM hospital_data_hospital_rates hr
JOIN hcpcs_exceptions he
ON hr.payer_id = he.payer_id
AND hr.provider_id = he.provider_id
AND hr.billing_code = he.billing_code
WHERE
billing_code_type = 'HCPCS'
),
rates AS (
SELECT *, 'dollar' as rate_type, negotiated_dollar as rate
FROM base
WHERE negotiated_dollar > 0
UNION ALL
SELECT *, 'percentage' as rate_type, negotiated_percentage as rate
FROM base
WHERE negotiated_percentage > 0
UNION ALL
SELECT *, 'estimated' as rate_type, estimated_allowed_amount as rate
FROM base
WHERE (
negotiated_dollar IS NULL
AND negotiated_percentage IS NULL
AND (estimated_allowed_amount > 0 AND estimated_allowed_amount < 10000000)
)
),
-------------------------------------------------------
-- 2. Compute Metrics For Each Payer-Provider-Code Combination
-------------------------------------------------------
df AS (
SELECT
provider_id,
provider_state,
payer_id,
contract_methodology,
billing_class,
setting,
billing_code_type,
billing_code,
rate_type,
count(distinct rate) as unique_rates,
array_agg(DISTINCT rate) as rate_array,
(max(rate) - min(rate)) / avg(rate) as rate_spread,
array_agg(DISTINCT raw_payer_name) as unique_payers,
array_agg(DISTINCT plan_name) as unique_plans,
array_agg(id) as ids,
MAX(
COALESCE(
CASE
{% for payer_id in payer_ids %}
WHEN payer_id = {{ payer_id }} THEN
regexp_like(
plan_name_std,
'{{ "|".join(general_keywords + hmo_keywords + payer_keywords[payer_id]) }}'
)
{% endfor %}
ELSE False
END,
False
)
) as has_hmo_plan,
array_agg(
DISTINCT
CASE
{% for payer_id in payer_ids %}
WHEN
payer_id = {{ payer_id }}
AND
regexp_like(
plan_name_std,
'{{ "|".join(general_keywords + hmo_keywords + payer_keywords[payer_id]) }}'
)
THEN id
{% endfor %}
ELSE NULL
END
) as ids_with_hmo_plan,
array_agg(
DISTINCT
CASE
{% for payer_id in payer_ids %}
WHEN
payer_id = {{ payer_id }}
AND
regexp_like(
plan_name_std,
'{{ "|".join(general_keywords + hmo_keywords + payer_keywords[payer_id]) }}'
)
THEN plan_name
{% endfor %}
ELSE NULL
END
) as plans_with_hmo_plan
FROM rates
WHERE
(
NOT regexp_like(
plan_name_std,
'{{ exclusions | join("|") }}'
)
OR
plan_name_std IN (
{% for exclusion in exclusion_exceptions %}
'{{ exclusion }}'{% if not loop.last %}, {% endif %}
{% endfor %}
)
)
{% for exclusion in exclusion_exact_matches %}
AND UPPER(plan_name) != '{{ exclusion }}'
{% endfor %}
{% for payer_spec, exclusion_list in payer_exclusions.items() %}
{% if exclusion_list %}
AND NOT (
payer_id = {{ payer_spec[0] }}
{% if payer_spec[1] != 'NATIONAL' %}
AND provider_state = '{{ payer_spec[1] }}'
{% endif %}
AND regexp_like(
plan_name_std,
'{{ exclusion_list | join("|") }}'
)
)
{% endif %}
{% endfor %}
GROUP BY 1,2,3,4,5,6,7,8,9
),
df_with_flags AS (
SELECT
*,
rate_spread < 0.1 as low_rate_variability,
unique_rates = 1 as is_single_rate,
CARDINALITY(unique_plans) = 1 as is_single_plan
FROM df
)
-------------------------------------------------------
-- 3. Build Plan Bridge
-------------------------------------------------------
SELECT
provider_id,
provider_state,
rate_type,
billing_code,
billing_code_type,
contract_methodology,
billing_class,
setting,
ids,
unique_payers,
unique_plans,
rate_array,
low_rate_variability,
is_single_rate,
is_single_plan,
has_hmo_plan,
ids_with_hmo_plan,
plans_with_hmo_plan,
CASE
WHEN
(
is_single_plan = True
OR is_single_rate = True
OR low_rate_variability = True
OR has_hmo_plan = True
)
THEN True
ELSE False
END as is_mapped,
CASE
{% for payer_id, value in product_network_mapping.items() %}
{% if value is mapping %}
{% for state, label in value.items() %}
WHEN payer_id = {{ payer_id }} AND provider_state = '{{ state }}' THEN '{{ label }}'
{% endfor %}
{% else %}
WHEN payer_id = {{ payer_id }} THEN '{{ value }}'
{% endif %}
{% endfor %}
END as network_name,
CAST(payer_id AS VARCHAR) as payer_id
FROM df_with_flags
Union Analysis​
-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_ref_plan_bridge_analysis_{{ sub_version }}
-- Subversion: {{ sub_version }}
CREATE TABLE {{ schema_name }}.tmp_ref_plan_bridge_analysis_{{ sub_version }}
WITH (
PARTITIONING = ARRAY['payer_id']
)
AS
{% for n_chunk in n_chunks %}
SELECT * FROM {{ schema_name }}.tmp_ref_plan_bridge_analysis_hmo_{{ n_chunk }}_{{ sub_version }}
UNION ALL
SELECT * FROM {{ schema_name }}.tmp_ref_plan_bridge_analysis_ppo_{{ n_chunk }}_{{ sub_version }}
{% if not loop.last %}UNION ALL{% endif %}
{% endfor %}
Plan Bridge Build​
-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_ref_plan_bridge_{{ n_chunk }}_{{ sub_version }}
-- Subversion: {{ sub_version }}
CREATE TABLE {{ schema_name }}.tmp_ref_plan_bridge_{{ n_chunk }}_{{ sub_version }}
WITH (
PARTITIONING = ARRAY['payer_id']
)
AS
WITH
extracted_ppo_data AS (
SELECT
payer_id,
network_name,
provider_id,
CASE
WHEN has_ppo_plan THEN ids_with_ppo_plan
ELSE ids
END AS array_ids
FROM {{ schema_name }}.tmp_ref_plan_bridge_analysis_ppo_{{ n_chunk }}_{{ sub_version }}
WHERE (
is_single_rate = True OR
is_single_plan = True OR
has_ppo_plan = True OR
low_rate_variability = True
)
AND is_mapped = True
),
extracted_hmo_data AS (
SELECT
payer_id,
network_name,
provider_id,
CASE
WHEN has_hmo_plan THEN ids_with_hmo_plan
ELSE ids
END AS array_ids
FROM {{ schema_name }}.tmp_ref_plan_bridge_analysis_hmo_{{ n_chunk }}_{{ sub_version }}
WHERE (
is_single_rate = True OR
is_single_plan = True OR
has_hmo_plan = True OR
low_rate_variability = True
)
AND is_mapped = True
)
SELECT
distinct
network_name,
id,
provider_id,
payer_id
FROM extracted_ppo_data,
UNNEST(array_ids) AS t(id)
WHERE
network_name IS NOT NULL
AND id IS NOT NULL
UNION ALL
SELECT
distinct
network_name,
id,
provider_id,
payer_id
FROM extracted_hmo_data,
UNNEST(array_ids) AS t(id)
WHERE
network_name IS NOT NULL
AND id IS NOT NULL
Combine All​
-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_ref_plan_bridge_{{ sub_version }}
-- Subversion: {{ sub_version }}
CREATE TABLE {{ schema_name }}.tmp_ref_plan_bridge_{{ sub_version }}
WITH (
PARTITIONING = ARRAY['payer_id']
)
AS
{% for n_chunk in n_chunks %}
SELECT * FROM {{ schema_name }}.tmp_ref_plan_bridge_{{ n_chunk }}_{{ sub_version }}
{% if not loop.last %}UNION ALL{% endif %}
{% endfor %}