Hospital Plan Bridge
Plan Bridge Analysis​
PPO​
{% from 'raw/plan_bridge/variables_bh_exclusions.sql' import bh_exclusions %}
{% set ppo_general_keywords = [
'ALLCOMMERCIAL',
'COMMERCIAL',
'BROAD',
'ALLPLAN',
'ALLPRODUCT',
'ALLPAY',
'ALLCHOICEPLANS',
'ALLOTHER',
] %}
{% set ppo_network_keywords = [
'PPO',
'PREFERRED'
] %}
{% set ppo_payer_keywords = {
7: [
'COMM',
'POS'
],
76: [
'OAP',
'OPENACCESSPLUS',
'BROAD'
],
42: [
'PREFERRED',
'ACCESS',
'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: [
'IBCPPO'
],
168: [],
299: [],
317: [],
300: [],
286: [],
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: [],
768: [],
567: [],
624: [],
779: [],
305: [],
728: [],
784: [],
699: [],
356: [],
27: [],
971: [],
97: [],
108: [],
638: [],
464: []
} %}
{% set ppo_exclusion_exceptions = [
'HMO/PPO/POS/INDEMNITY',
] %}
{% set ppo_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',
'INDIVIDUAL',
'IFP',
] + bh_exclusions %}
{% set ppo_exclusion_exact_matches = [
'BH',
'HMO',
'MM',
'PMAP',
'MAP',
'MA',
'MM',
'PCB'
] %}
{% set ppo_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',
'UMRFIRSTHEALTH'
],
(42, 'NY'): [
'BLUECARD',
'BCBSWNY',
],
(42, 'CA'): [
'HEALTHNET',
'UNITED',
],
(42, 'NATIONAL'): [
'ANTHEMHLTHKEEPMEDICIAD',
'MGMCD',
'BCBSFEP',
'MISSOURICAREMGMCD',
],
(56, 'PA'): [
'IBCHMO',
'DVACOOTHERCOMMERCIALPLAN',
'IBCADMINISTRATORSPPO',
'IBCINDEMNITYTRADITIONAL'
],
(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 ppo_payer_ids = ppo_payer_keywords.keys() %}
{% set ppo_multiple_network_payers = [] %}
{% set ppo_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 OPEN ACCESS',
'NH': 'NH OPEN ACCESS',
'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'
},
56: 'QCC Commercial PPO/EPO',
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: {
'WA': 'PBC PPO (Heritage) Network',
'AK': 'Alaska Heritage Network',
},
160: 'Preferred PPO',
52: 'Preferred Provider Network',
392: 'PPO',
398: 'PPO',
50: 'Aware PPO',
391: 'PPO - Preferred Care PPO',
43: 'PPO',
454: 'True Blue PPO',
461: {
'IA':'IA PPO',
'SD':'SD PPO',
},
456: 'Preferred Provider Network',
720: 'Choicenet PPO',
636: 'Value/Care Tiered POS - UT',
53: 'Blue Preferred PPO',
47: 'Preferredcare Blue',
168: 'PPO - Preferred Blue',
299: 'PPO',
317: 'PPO MA',
300: 'PEBB Choice',
286: 'MVP EPO / PPO',
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',
768: 'PPO',
567: 'Oscar Health',
624: 'Navigator Gold',
779: 'PPO',
305: 'PPO',
728: 'PPO',
784: 'PPO',
699: 'PPO',
356: 'PPO',
464: 'PPO',
638: {
'OH': 'SCCONNECT'
}
} %}
HMO​
{% from 'raw/plan_bridge/variables_bh_exclusions.sql' import bh_exclusions %}
{% set hmo_general_keywords = [
'ALLCOMMERCIAL',
'COMMERCIAL',
'BROAD',
'ALLPLAN',
'ALLPRODUCT',
'ALLPAY',
'ALLOTHER',
] %}
{% set hmo_network_keywords = [
'HMO',
'HEALTHMAINTENANCE'
] %}
{% set hmo_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: [
'IBCHMO'
],
168: [],
299: [],
317: [],
300: [],
286: [],
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 hmo_exclusion_exceptions = [
'HMO/HMO/POS/INDEMNITY',
] %}
{% set hmo_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',
'INDIVIDUAL',
'IFP',
] + bh_exclusions %}
{% set hmo_exclusion_exact_matches = [
'BH',
'MM',
'PMAP',
'MAP',
'MA',
'MM',
'PCB'
] %}
{% set hmo_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',
],
(56, 'PA'): [
'IBCPPO',
'DVACOOTHERCOMMERCIALPLAN',
'IBCADMINISTRATORSPPO',
'IBCINDEMNITYTRADITIONAL'
],
(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 hmo_payer_ids = hmo_payer_keywords.keys() %}
{% set hmo_multiple_network_payers = [] %}
{% set hmo_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'
},
56: 'KHPE Commercial HMO/POS',
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 TX',
'GA': 'Ambetter GA',
'FL': 'Ambetter FL',
'OH': 'Ambetter OH',
'MI': 'Ambetter MI',
'AR': 'Ambetter AR',
'AZ': 'Ambetter AZ',
'MS': 'Ambetter MS',
'WA': 'Ambetter WA',
'KS': 'Ambetter KS',
'NC': 'Ambetter NC',
'TN': 'Ambetter TN',
'SC': 'Ambetter SC',
'CA': 'Ambetter CA',
'NE': 'Ambetter NE',
'IL': 'Ambetter IL',
'MO': 'Ambetter MO',
'IN': 'Ambetter IN',
'PA': 'Ambetter PA',
'LA': 'Ambetter LA',
'AL': 'Ambetter AL',
'OK': 'Ambetter OK',
'KY': 'Ambetter KY',
'NV': 'Ambetter NV'
},
774: {
'NY': 'HMO'
},
813: {
'MI': 'HMO'
}
} %}
Union Analysis​
-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_ref_plan_bridge_analysis_{{ sub_version }}
-- Subversion: {{ sub_version }}
CREATE OR REPLACE 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_combined_{{ 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 OR REPLACE TABLE {{ schema_name }}.tmp_ref_plan_bridge_{{ n_chunk }}_{{ sub_version }}
WITH (
PARTITIONING = ARRAY['payer_id']
)
AS
WITH
extracted_combined_data AS (
SELECT
payer_id,
network_name,
provider_id,
network_type,
CASE
WHEN has_network_plan THEN ids_with_network_plan
ELSE ids
END AS array_ids
FROM {{ schema_name }}.tmp_ref_plan_bridge_analysis_combined_{{ n_chunk }}_{{ sub_version }}
WHERE (
is_single_rate = True OR
is_single_plan = True OR
has_network_plan = True OR
low_rate_variability = True
)
AND is_mapped = True
)
SELECT
distinct
network_name,
id,
provider_id,
payer_id,
network_type
FROM extracted_combined_data,
UNNEST(array_ids) AS t(id)
WHERE
network_name IS NOT NULL
AND id IS NOT NULL
Prepare Base​
-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_ref_plan_bridge_base_{{ n_chunk }}_{{ sub_version }}
-- Subversion: {{ sub_version }}
-- Chunk: {{ n_chunk }}
{% 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]',
]
} %}
{% from 'raw/plan_bridge/variables_ppo.sql' import
ppo_general_keywords,
ppo_network_keywords,
ppo_payer_keywords,
ppo_exclusion_exceptions,
ppo_exclusions,
ppo_exclusion_exact_matches,
ppo_payer_exclusions,
ppo_payer_ids,
ppo_multiple_network_payers,
ppo_product_network_mapping
%}
{% from 'raw/plan_bridge/variables_hmo.sql' import
hmo_general_keywords,
hmo_network_keywords,
hmo_payer_keywords,
hmo_exclusion_exceptions,
hmo_exclusions,
hmo_exclusion_exact_matches,
hmo_payer_exclusions,
hmo_payer_ids,
hmo_multiple_network_payers,
hmo_product_network_mapping
%}
{% from 'raw/plan_bridge/variables_narrow.sql' import
narrow_general_keywords,
narrow_network_keywords,
narrow_payer_keywords,
narrow_exclusion_exceptions,
narrow_exclusions,
narrow_exclusion_exact_matches,
narrow_payer_exclusions,
narrow_payer_ids,
narrow_multiple_network_payers,
narrow_product_network_mapping
%}
{% from 'raw/plan_bridge/variables_exchange.sql' import
exchange_general_keywords,
exchange_network_keywords,
exchange_payer_keywords,
exchange_exclusion_exceptions,
exchange_exclusions,
exchange_exclusion_exact_matches,
exchange_payer_exclusions,
exchange_payer_ids,
exchange_multiple_network_payers,
exchange_product_network_mapping
%}
{% set all_payer_ids = (ppo_payer_keywords.keys() | list)
+ (hmo_payer_keywords.keys() | list)
+ (narrow_payer_keywords.keys() | list)
+ (exchange_payer_keywords.keys() | list) %}
{% set all_payer_ids = all_payer_ids | unique | list %}
{% set multiple_network_payers = (ppo_multiple_network_payers + hmo_multiple_network_payers + narrow_multiple_network_payers + exchange_multiple_network_payers) | unique | list %}
CREATE OR REPLACE TABLE {{ schema_name }}.tmp_ref_plan_bridge_base_{{ n_chunk }}_{{ sub_version }}
WITH (
PARTITIONING = ARRAY['provider_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.
-------------------------------------------------------
-- drug codes
drug_codes AS (
SELECT billing_code
FROM {{ schema_name }}.tmp_ref_code_spine_{{ sub_version }}
WHERE is_drug_code = True
),
------------------------------------
-- NDC Normalization for HCPCS Crosswalking
-- Normalized NDCs are preprocessed in ndc_derived_hcpcs task
------------------------------------
------------------------------------
-- Identify hospitals posting ALL HCPCS as Inpatient only
------------------------------------
hospitals_hcpcs_inpatient_only AS (
SELECT DISTINCT hr.provider_id
FROM {{ hospital_schema }}.hospital_rates hr
WHERE hr.billing_code_type = 'HCPCS'
AND hr.setting = 'Inpatient'
AND hr.billing_code IS NOT NULL
AND NOT EXISTS (
SELECT 1
FROM {{ hospital_schema }}.hospital_rates
WHERE provider_id = hr.provider_id
AND billing_code_type = 'HCPCS'
AND (
setting IN ('Outpatient', 'Inpatient & Outpatient')
OR
setting IS NULL
)
AND billing_code IS NOT NULL
)
),
base_hospital_data_hospital_rates AS (
SELECT
id,
hospital_rates.provider_id,
provider_name,
provider_state,
payer_id,
payer_name,
plan_name,
raw_payer_name,
contract_methodology,
billing_class,
setting,
CASE
WHEN billing_code_type = 'DRG' THEN 'MS-DRG'
WHEN billing_code_type IS NULL AND ndc IS NOT NULL THEN 'HCPCS'
ELSE billing_code_type
END as billing_code_type,
CASE
WHEN billing_code_type = 'MS-DRG' THEN lpad(billing_code,3,'0')
ELSE COALESCE(billing_code, ndc_h.derived_hcpcs_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
LEFT JOIN {{ schema_name }}.tmp_ndc_derived_hcpcs_{{ sub_version }} ndc_h
ON hospital_rates.ndc = ndc_h.hospital_ndc
LEFT JOIN hospitals_hcpcs_inpatient_only hosp_ip_only
ON hospital_rates.provider_id = hosp_ip_only.provider_id
WHERE
CAST(hospital_rates.provider_id AS VARCHAR) IN ({{ provider_ids }})
AND
(
COALESCE(billing_class, '') != 'Professional'
OR
billing_code IN (
SELECT billing_code
FROM drug_codes
)
OR (billing_code IS NULL AND ndc IS NOT NULL)
)
AND (
negotiated_dollar > 0
OR negotiated_percentage > 0
OR (estimated_allowed_amount > 0 AND estimated_allowed_amount < 10000000)
)
AND
(
payer_id IN (
{{ all_payer_ids | join(',') }}
)
)
-- setting + billing code type filter
AND (
(
billing_code_type IS NULL
AND ndc IS NOT NULL
)
OR (
billing_code_type IN ('HCPCS', 'APC', 'EAPG') AND
setting != 'Inpatient'
)
OR (
billing_code_type IN ('MS-DRG', 'APR-DRG', 'DRG')
AND setting != 'Outpatient'
)
OR (
billing_code_type = 'HCPCS'
AND hosp_ip_only.provider_id IS NOT NULL
)
)
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
payer_id,
provider_id,
billing_code,
billing_code_type
FROM hospital_data_hospital_rates
WHERE billing_code_type IN ('HCPCS', 'MS-DRG')
GROUP BY 1,2,3,4
HAVING MAX(CASE WHEN revenue_code IS NULL THEN 1 ELSE 0 END) = 0
),
combined AS (
SELECT *
FROM hospital_data_hospital_rates
WHERE
revenue_code IS NULL
AND billing_code_modifiers IS NULL
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
AND hr.billing_code_type = he.billing_code_type
WHERE
hr.billing_code_type IN ('HCPCS', 'MS-DRG')
),
ranked AS (
SELECT
*,
CASE
WHEN payer_id = 42 AND provider_state = 'MO'
THEN RANK() OVER (
PARTITION BY payer_id, provider_id, billing_code_type, billing_code, contract_methodology
ORDER BY
CASE
WHEN upper(plan_name) = 'BLUE ACCESS' THEN 1
WHEN upper(plan_name) = 'BLUE ACCESS CHOICE' THEN 2
WHEN upper(plan_name) = 'PPO' THEN 3
WHEN upper(plan_name) = 'BLUE PREFERRED' THEN 4
ELSE 5
END
)
-- Dedupe: some of these combinations have > 100000 unique IDs
-- Caused array memory issues in plan bridge consruction
ELSE ROW_NUMBER() OVER (
PARTITION BY
provider_id,
payer_id,
billing_code_type,
billing_code,
plan_name,
raw_payer_name,
contract_methodology,
billing_class,
setting,
negotiated_dollar,
negotiated_percentage
ORDER BY id ASC
)
END AS rn
FROM combined
)
SELECT *
FROM ranked
WHERE billing_code IN (
SELECT distinct billing_code
FROM {{ schema_name }}.tmp_ref_code_spine_{{ sub_version }}
) AND CAST(payer_id AS VARCHAR) IN (
SELECT DISTINCT payer_id
FROM {{ schema_name }}.tmp_ref_payer_spine_{{ sub_version }}
)
AND rn = 1
Prepare Base Union​
-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_ref_plan_bridge_base_{{ sub_version }}
-- Subversion: {{ sub_version }}
CREATE OR REPLACE TABLE {{ schema_name }}.tmp_ref_plan_bridge_base_{{ sub_version }}
WITH (
PARTITIONING = ARRAY['provider_id']
)
AS
{% for n_chunk in n_chunks %}
SELECT * FROM {{ schema_name }}.tmp_ref_plan_bridge_base_{{ n_chunk }}_{{ sub_version }}
{% if not loop.last %}UNION ALL{% endif %}
{% endfor %}
Prepare Plan Bridge Combined​
-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_ref_plan_bridge_analysis_combined_{{ n_chunk }}_{{ sub_version }}
-- Subversion: {{ sub_version }}
-- n_chunk: {{ n_chunk }}
{% from 'raw/plan_bridge/variables_ppo.sql' import
ppo_general_keywords,
ppo_network_keywords,
ppo_payer_keywords,
ppo_exclusion_exceptions,
ppo_exclusions,
ppo_exclusion_exact_matches,
ppo_payer_exclusions,
ppo_payer_ids,
ppo_multiple_network_payers,
ppo_product_network_mapping
%}
{% from 'raw/plan_bridge/variables_hmo.sql' import
hmo_general_keywords,
hmo_network_keywords,
hmo_payer_keywords,
hmo_exclusion_exceptions,
hmo_exclusions,
hmo_exclusion_exact_matches,
hmo_payer_exclusions,
hmo_payer_ids,
hmo_multiple_network_payers,
hmo_product_network_mapping
%}
{% from 'raw/plan_bridge/variables_narrow.sql' import
narrow_general_keywords,
narrow_network_keywords,
narrow_payer_keywords,
narrow_exclusion_exceptions,
narrow_exclusions,
narrow_exclusion_exact_matches,
narrow_payer_exclusions,
narrow_payer_ids,
narrow_multiple_network_payers,
narrow_product_network_mapping
%}
{% from 'raw/plan_bridge/variables_exchange.sql' import
exchange_general_keywords,
exchange_network_keywords,
exchange_payer_keywords,
exchange_exclusion_exceptions,
exchange_exclusions,
exchange_exclusion_exact_matches,
exchange_payer_exclusions,
exchange_payer_ids,
exchange_multiple_network_payers,
exchange_product_network_mapping
%}
{% set all_payer_ids = (ppo_payer_keywords.keys() | list)
+ (hmo_payer_keywords.keys() | list)
+ (narrow_payer_keywords.keys() | list)
+ (exchange_payer_keywords.keys() | list) %}
{% set all_payer_ids = all_payer_ids | unique | list %}
{% set multiple_network_payers = (ppo_multiple_network_payers + hmo_multiple_network_payers + narrow_multiple_network_payers + exchange_multiple_network_payers) | unique | list %}
CREATE OR REPLACE TABLE {{ schema_name }}.tmp_ref_plan_bridge_analysis_combined_{{ n_chunk }}_{{ sub_version }}
WITH (
PARTITIONING = ARRAY['payer_id']
)
AS
WITH
base AS (
SELECT *
FROM {{ schema_name }}.tmp_ref_plan_bridge_base_{{ sub_version }}
WHERE provider_id IN ({{ provider_ids }})
),
rates_base 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)
)
),
rates AS (
-- Regular payers: one record per network type
SELECT *, NULL as specific_network_name, 'PPO' as network_type, 'Commercial' as network_class
FROM rates_base
WHERE payer_class_name = 'Commercial'
AND {% if ppo_multiple_network_payers %}payer_id NOT IN ({{ ppo_multiple_network_payers | join(',') }}){% else %}1=1{% endif %}
UNION ALL
SELECT *, NULL as specific_network_name, 'HMO' as network_type, 'Commercial' as network_class
FROM rates_base
WHERE payer_class_name = 'Commercial'
AND {% if hmo_multiple_network_payers %}payer_id NOT IN ({{ hmo_multiple_network_payers | join(',') }}){% else %}1=1{% endif %}
UNION ALL
SELECT *, NULL as specific_network_name, 'NARROW' as network_type, 'Commercial' as network_class
FROM rates_base
WHERE payer_class_name = 'Commercial'
AND {% if narrow_multiple_network_payers %}payer_id NOT IN ({{ narrow_multiple_network_payers | join(',') }}){% else %}1=1{% endif %}
UNION ALL
-- Exchange plans: HMO network_type with Exchange network_class
SELECT *, NULL as specific_network_name, 'HMO' as network_type, 'Exchange' as network_class
FROM rates_base
WHERE {% if exchange_multiple_network_payers %}payer_id NOT IN ({{ exchange_multiple_network_payers | join(',') }}){% else %}1=1{% endif %}
-- Multiple network payers: create separate records for each network name
{% for payer_id in ppo_multiple_network_payers %}
{% for network_name in ppo_product_network_mapping[payer_id] %}
UNION ALL
SELECT *, '{{ network_name }}' as specific_network_name, 'PPO' as network_type, 'Commercial' as network_class
FROM rates_base
WHERE payer_class_name = 'Commercial'
AND payer_id = {{ payer_id }}
{% endfor %}
{% endfor %}
{% for payer_id in hmo_multiple_network_payers %}
{% if payer_id in hmo_product_network_mapping %}
{% for network_name in hmo_product_network_mapping[payer_id] %}
UNION ALL
SELECT *, '{{ network_name }}' as specific_network_name, 'HMO' as network_type, 'Commercial' as network_class
FROM rates_base
WHERE payer_class_name = 'Commercial'
AND payer_id = {{ payer_id }}
{% endfor %}
{% endif %}
{% endfor %}
{% for payer_id in narrow_multiple_network_payers %}
{% for network_name in narrow_product_network_mapping[payer_id] %}
UNION ALL
SELECT *, '{{ network_name }}' as specific_network_name, 'NARROW' as network_type, 'Commercial' as network_class
FROM rates_base
WHERE payer_class_name = 'Commercial'
AND payer_id = {{ payer_id }}
{% endfor %}
{% endfor %}
{% for payer_id in exchange_multiple_network_payers %}
{% for network_name in exchange_product_network_mapping[payer_id] %}
UNION ALL
SELECT *, '{{ network_name }}' as specific_network_name, 'HMO' as network_type, 'Exchange' as network_class
FROM rates_base
WHERE payer_id = {{ payer_id }}
{% endfor %}
{% endfor %}
),
-------------------------------------------------------
-- 2. Compute Metrics For Each Payer-Provider-Code Combination
-------------------------------------------------------
df AS (
SELECT
provider_id,
payer_id,
billing_code_type,
billing_code,
specific_network_name,
network_type,
network_class,
contract_methodology,
billing_class,
setting,
rate_type,
ANY_VALUE(provider_state) as provider_state,
count(distinct rate) as unique_rates,
(max(rate) - min(rate)) / avg(rate) as rate_spread,
array_agg(DISTINCT raw_payer_name) as raw_payer_names,
array_agg(DISTINCT plan_name) as unique_plans,
array_agg(DISTINCT id) as ids,
CASE
WHEN network_type = 'PPO' AND network_class = 'Commercial' THEN
MAX(
COALESCE(
CASE
{% for payer_id in all_payer_ids %}
{% if payer_id in ppo_payer_keywords %}
WHEN payer_id = {{ payer_id }} THEN
regexp_like(
plan_name_std,
'{{ "|".join(ppo_general_keywords + ppo_network_keywords + ppo_payer_keywords[payer_id]) }}'
)
{% endif %}
{% endfor %}
ELSE False
END,
False
)
)
WHEN network_type = 'HMO' AND network_class = 'Commercial' THEN
MAX(
COALESCE(
CASE
{% for payer_id in all_payer_ids %}
{% if payer_id in hmo_payer_keywords %}
WHEN payer_id = {{ payer_id }} THEN
regexp_like(
plan_name_std,
'{{ "|".join(hmo_general_keywords + hmo_network_keywords + hmo_payer_keywords[payer_id]) }}'
)
{% endif %}
{% endfor %}
ELSE False
END,
False
)
)
WHEN network_type = 'HMO' AND network_class = 'Exchange' THEN
MAX(
COALESCE(
CASE
{% for payer_id in all_payer_ids %}
{% if payer_id in exchange_payer_keywords %}
WHEN payer_id = {{ payer_id }} THEN
regexp_like(
plan_name_std,
'{{ "|".join(exchange_general_keywords + exchange_network_keywords + exchange_payer_keywords[payer_id]) }}'
)
{% endif %}
{% endfor %}
ELSE False
END,
False
)
)
WHEN network_type = 'NARROW' AND network_class = 'Commercial' THEN
MAX(
COALESCE(
CASE
{% for payer_id in all_payer_ids %}
{% if payer_id in narrow_payer_keywords %}
-- {{ payer_id }}
WHEN payer_id = {{ payer_id }} THEN
regexp_like(
plan_name_std,
'{{ "|".join(narrow_general_keywords + narrow_network_keywords + narrow_payer_keywords[payer_id]) }}'
)
{% endif %}
{% endfor %}
ELSE False
END,
False
)
)
END as has_network_plan,
CASE
WHEN network_type = 'PPO' AND network_class = 'Commercial' THEN
array_agg(
DISTINCT
CASE
{% for payer_id in all_payer_ids %}
{% if payer_id in ppo_payer_keywords %}
WHEN
payer_id = {{ payer_id }}
AND
regexp_like(
plan_name_std,
'{{ "|".join(ppo_general_keywords + ppo_network_keywords + ppo_payer_keywords[payer_id]) }}'
)
THEN id
{% endif %}
{% endfor %}
ELSE NULL
END
)
WHEN network_type = 'HMO' AND network_class = 'Commercial' THEN
array_agg(
DISTINCT
CASE
{% for payer_id in all_payer_ids %}
{% if payer_id in hmo_payer_keywords %}
WHEN
payer_id = {{ payer_id }}
AND
regexp_like(
plan_name_std,
'{{ "|".join(hmo_general_keywords + hmo_network_keywords + hmo_payer_keywords[payer_id]) }}'
)
THEN id
{% endif %}
{% endfor %}
ELSE NULL
END
)
WHEN network_type = 'HMO' AND network_class = 'Exchange' THEN
array_agg(
DISTINCT
CASE
{% for payer_id in all_payer_ids %}
{% if payer_id in exchange_payer_keywords %}
WHEN
payer_id = {{ payer_id }}
AND
regexp_like(
plan_name_std,
'{{ "|".join(exchange_general_keywords + exchange_network_keywords + exchange_payer_keywords[payer_id]) }}'
)
THEN id
{% endif %}
{% endfor %}
ELSE NULL
END
)
WHEN network_type = 'NARROW' AND network_class = 'Commercial' THEN
array_agg(
DISTINCT
CASE
{% for payer_id in all_payer_ids %}
{% if payer_id in narrow_payer_keywords %}
WHEN
payer_id = {{ payer_id }}
AND
regexp_like(
plan_name_std,
'{{ "|".join(narrow_general_keywords + narrow_network_keywords + narrow_payer_keywords[payer_id]) }}'
)
THEN id
{% endif %}
{% endfor %}
ELSE NULL
END
)
END as ids_with_network_plan,
CASE
WHEN network_type = 'PPO' AND network_class = 'Commercial' THEN
array_agg(
DISTINCT
CASE
{% for payer_id in all_payer_ids %}
{% if payer_id in ppo_payer_keywords %}
WHEN
payer_id = {{ payer_id }}
AND
regexp_like(
plan_name_std,
'{{ "|".join(ppo_general_keywords + ppo_network_keywords + ppo_payer_keywords[payer_id]) }}'
)
THEN plan_name
{% endif %}
{% endfor %}
ELSE NULL
END
)
WHEN network_type = 'HMO' AND network_class = 'Commercial' THEN
array_agg(
DISTINCT
CASE
{% for payer_id in all_payer_ids %}
{% if payer_id in hmo_payer_keywords %}
WHEN
payer_id = {{ payer_id }}
AND
regexp_like(
plan_name_std,
'{{ "|".join(hmo_general_keywords + hmo_network_keywords + hmo_payer_keywords[payer_id]) }}'
)
THEN plan_name
{% endif %}
{% endfor %}
ELSE NULL
END
)
WHEN network_type = 'HMO' AND network_class = 'Exchange' THEN
array_agg(
DISTINCT
CASE
{% for payer_id in all_payer_ids %}
{% if payer_id in exchange_payer_keywords %}
WHEN
payer_id = {{ payer_id }}
AND
regexp_like(
plan_name_std,
'{{ "|".join(exchange_general_keywords + exchange_network_keywords + exchange_payer_keywords[payer_id]) }}'
)
THEN plan_name
{% endif %}
{% endfor %}
ELSE NULL
END
)
WHEN network_type = 'NARROW' AND network_class = 'Commercial' THEN
array_agg(
DISTINCT
CASE
{% for payer_id in all_payer_ids %}
{% if payer_id in narrow_payer_keywords %}
WHEN
payer_id = {{ payer_id }}
AND
regexp_like(
plan_name_std,
'{{ "|".join(narrow_general_keywords + narrow_network_keywords + narrow_payer_keywords[payer_id]) }}'
)
THEN plan_name
{% endif %}
{% endfor %}
ELSE NULL
END
)
END as plans_with_network_plan
FROM rates
WHERE
CASE
WHEN network_type = 'PPO' AND network_class = 'Commercial' THEN
(
NOT regexp_like(
plan_name_std,
'{{ ppo_exclusions | join("|") }}'
)
OR
plan_name_std IN (
{% for exclusion in ppo_exclusion_exceptions %}
'{{ exclusion }}'{% if not loop.last %}, {% endif %}
{% endfor %}
)
)
{% for exclusion in ppo_exclusion_exact_matches %}
AND UPPER(plan_name) != '{{ exclusion }}'
{% endfor %}
{% for payer_spec, exclusion_list in ppo_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 %}
WHEN network_type = 'HMO' AND network_class = 'Commercial' THEN
(
NOT regexp_like(
plan_name_std,
'{{ hmo_exclusions | join("|") }}'
)
OR
plan_name_std IN (
{% for exclusion in hmo_exclusion_exceptions %}
'{{ exclusion }}'{% if not loop.last %}, {% endif %}
{% endfor %}
)
)
{% for exclusion in hmo_exclusion_exact_matches %}
AND UPPER(plan_name) != '{{ exclusion }}'
{% endfor %}
{% for payer_spec, exclusion_list in hmo_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 %}
WHEN network_type = 'HMO' AND network_class = 'Exchange' THEN
(
NOT regexp_like(
plan_name_std,
'{{ exchange_exclusions | join("|") }}'
)
OR
plan_name_std IN (
{% for exclusion in exchange_exclusion_exceptions %}
'{{ exclusion }}'{% if not loop.last %}, {% endif %}
{% endfor %}
)
)
{% for exclusion in exchange_exclusion_exact_matches %}
AND UPPER(plan_name) != '{{ exclusion }}'
{% endfor %}
{% for payer_spec, exclusion_list in exchange_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 %}
WHEN network_type = 'NARROW' AND network_class = 'Commercial' THEN
(
NOT regexp_like(
plan_name_std,
'{{ narrow_exclusions | join("|") }}'
)
OR
plan_name_std IN (
{% for exclusion in narrow_exclusion_exceptions %}
'{{ exclusion }}'{% if not loop.last %}, {% endif %}
{% endfor %}
)
)
{% for exclusion in narrow_exclusion_exact_matches %}
AND UPPER(plan_name) != '{{ exclusion }}'
{% endfor %}
{% for payer_spec, exclusion_list in narrow_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 %}
END
GROUP BY 1,2,3,4,5,6,7,8,9,10,11
),
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,
network_type,
network_class,
specific_network_name,
SLICE(ids, 1, 100) as ids,
unique_plans,
raw_payer_names,
low_rate_variability,
is_single_rate,
is_single_plan,
has_network_plan,
SLICE(ids_with_network_plan, 1, 100) as ids_with_network_plan,
plans_with_network_plan,
CASE
WHEN
(
is_single_plan = True
OR is_single_rate = True
OR low_rate_variability = True
OR has_network_plan = True
)
THEN True
ELSE False
END as is_mapped,
COALESCE(
specific_network_name,
CASE
WHEN network_type = 'PPO' AND network_class = 'Commercial' THEN
CASE
{% for payer_id, value in ppo_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 %}
{% if value is not iterable or value is string %}
WHEN payer_id = {{ payer_id }} THEN '{{ value }}'
{% endif %}
{% endif %}
{% endfor %}
END
WHEN network_type = 'HMO' AND network_class = 'Commercial' THEN
CASE
{% for payer_id, value in hmo_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
WHEN network_type = 'HMO' AND network_class = 'Exchange' THEN
CASE
{% for payer_id, value in exchange_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 %}
{% if value is not iterable or value is string %}
WHEN payer_id = {{ payer_id }} THEN '{{ value }}'
{% endif %}
{% endif %}
{% endfor %}
END
WHEN network_type = 'NARROW' AND network_class = 'Commercial' THEN
CASE
{% for payer_id, value in narrow_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 %}
{% if value is not iterable or value is string %}
WHEN payer_id = {{ payer_id }} THEN '{{ value }}'
{% endif %}
{% endif %}
{% endfor %}
END
END
) as network_name,
CAST(payer_id AS VARCHAR) as payer_id
FROM df_with_flags
Combine All​
-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_ref_plan_bridge_{{ sub_version }}
-- Subversion: {{ sub_version }}
CREATE OR REPLACE 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 }}
UNION ALL
{% endfor %}
{% for n_chunk in n_chunks %}
SELECT * FROM {{ schema_name }}.tmp_ref_plan_bridge_custom_{{ n_chunk }}_{{ sub_version }}
{% if not loop.last %}UNION ALL{% endif %}
{% endfor %}
Physician Group Plan Bridge​
Build Plan Bridge​
-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_ref_pg_plan_bridge_{{ n_chunk }}_{{ sub_version }}
-- Subversion: {{ sub_version }}
CREATE OR REPLACE TABLE {{ schema_name }}.tmp_ref_pg_plan_bridge_{{ n_chunk }}_{{ sub_version }}
WITH (
PARTITIONING = ARRAY['payer_id']
)
AS
WITH
extracted_combined_data AS (
SELECT
payer_id,
network_name,
provider_id,
network_type,
CASE
WHEN has_network_plan THEN ids_with_network_plan
ELSE ids
END AS array_ids
FROM {{ schema_name }}.tmp_ref_pg_plan_bridge_analysis_combined_{{ n_chunk }}_{{ sub_version }}
WHERE (
is_single_rate = True OR
is_single_plan = True OR
has_network_plan = True OR
low_rate_variability = True
)
AND is_mapped = True
)
SELECT
distinct
network_name,
id,
provider_id,
payer_id,
network_type
FROM extracted_combined_data,
UNNEST(array_ids) AS t(id)
WHERE
network_name IS NOT NULL
AND id IS NOT NULL
Prepare Plan Bridge Combined​
-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_ref_pg_plan_bridge_analysis_combined_{{ n_chunk }}_{{ sub_version }}
-- Subversion: {{ sub_version }}
-- n_chunk: {{ n_chunk }}
{% 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]',
]
} %}
{% from 'raw/plan_bridge/variables_ppo.sql' import
ppo_general_keywords,
ppo_network_keywords,
ppo_payer_keywords,
ppo_exclusion_exceptions,
ppo_exclusions,
ppo_exclusion_exact_matches,
ppo_payer_exclusions,
ppo_payer_ids,
ppo_multiple_network_payers,
ppo_product_network_mapping
%}
{% from 'raw/plan_bridge/variables_hmo.sql' import
hmo_general_keywords,
hmo_network_keywords,
hmo_payer_keywords,
hmo_exclusion_exceptions,
hmo_exclusions,
hmo_exclusion_exact_matches,
hmo_payer_exclusions,
hmo_payer_ids,
hmo_multiple_network_payers,
hmo_product_network_mapping
%}
{% from 'raw/plan_bridge/variables_narrow.sql' import
narrow_general_keywords,
narrow_network_keywords,
narrow_payer_keywords,
narrow_exclusion_exceptions,
narrow_exclusions,
narrow_exclusion_exact_matches,
narrow_payer_exclusions,
narrow_payer_ids,
narrow_multiple_network_payers,
narrow_product_network_mapping
%}
{% from 'raw/plan_bridge/variables_exchange.sql' import
exchange_general_keywords,
exchange_network_keywords,
exchange_payer_keywords,
exchange_exclusion_exceptions,
exchange_exclusions,
exchange_exclusion_exact_matches,
exchange_payer_exclusions,
exchange_payer_ids,
exchange_multiple_network_payers,
exchange_product_network_mapping
%}
{% set all_payer_ids = (ppo_payer_keywords.keys() | list)
+ (hmo_payer_keywords.keys() | list)
+ (narrow_payer_keywords.keys() | list)
+ (exchange_payer_keywords.keys() | list) %}
{% set all_payer_ids = all_payer_ids | unique | list %}
{% set multiple_network_payers = (ppo_multiple_network_payers + hmo_multiple_network_payers + narrow_multiple_network_payers + exchange_multiple_network_payers) | unique | list %}
CREATE OR REPLACE TABLE {{ schema_name }}.tmp_ref_pg_plan_bridge_analysis_combined_{{ 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.
-------------------------------------------------------
-- drug codes
drug_codes AS (
SELECT billing_code
FROM {{ schema_name }}.tmp_ref_code_spine_{{ sub_version }}
WHERE is_drug_code = True
),
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
billing_class = 'Professional'
AND (
negotiated_dollar > 0
OR negotiated_percentage > 0
OR (estimated_allowed_amount > 0 AND estimated_allowed_amount < 10000000)
)
AND payer_id IN (
{{ all_payer_ids | join(',') }}
)
-- setting
AND billing_code_type IN ('HCPCS', 'APC', 'EAPG')
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')
)
OR
billing_code_type IN ('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
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_base 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)
)
),
rates AS (
-- Regular payers: one record per network type
SELECT *, NULL as specific_network_name, 'PPO' as network_type, 'Commercial' as network_class
FROM rates_base
WHERE payer_class_name = 'Commercial'
AND {% if ppo_multiple_network_payers %}payer_id NOT IN ({{ ppo_multiple_network_payers | join(',') }}){% else %}1=1{% endif %}
UNION ALL
SELECT *, NULL as specific_network_name, 'HMO' as network_type, 'Commercial' as network_class
FROM rates_base
WHERE payer_class_name = 'Commercial'
AND {% if hmo_multiple_network_payers %}payer_id NOT IN ({{ hmo_multiple_network_payers | join(',') }}){% else %}1=1{% endif %}
UNION ALL
SELECT *, NULL as specific_network_name, 'NARROW' as network_type, 'Commercial' as network_class
FROM rates_base
WHERE payer_class_name = 'Commercial'
AND {% if narrow_multiple_network_payers %}payer_id NOT IN ({{ narrow_multiple_network_payers | join(',') }}){% else %}1=1{% endif %}
UNION ALL
-- Exchange plans: HMO network_type with Exchange network_class
SELECT *, NULL as specific_network_name, 'HMO' as network_type, 'Exchange' as network_class
FROM rates_base
WHERE {% if exchange_multiple_network_payers %}payer_id NOT IN ({{ exchange_multiple_network_payers | join(',') }}){% else %}1=1{% endif %}
-- Multiple network payers: create separate records for each network name
{% for payer_id in ppo_multiple_network_payers %}
{% for network_name in ppo_product_network_mapping[payer_id] %}
UNION ALL
SELECT *, '{{ network_name }}' as specific_network_name, 'PPO' as network_type, 'Commercial' as network_class
FROM rates_base
WHERE payer_class_name = 'Commercial'
AND payer_id = {{ payer_id }}
{% endfor %}
{% endfor %}
{% for payer_id in hmo_multiple_network_payers %}
{% if payer_id in hmo_product_network_mapping %}
{% for network_name in hmo_product_network_mapping[payer_id] %}
UNION ALL
SELECT *, '{{ network_name }}' as specific_network_name, 'HMO' as network_type, 'Commercial' as network_class
FROM rates_base
WHERE payer_class_name = 'Commercial'
AND payer_id = {{ payer_id }}
{% endfor %}
{% endif %}
{% endfor %}
{% for payer_id in narrow_multiple_network_payers %}
{% for network_name in narrow_product_network_mapping[payer_id] %}
UNION ALL
SELECT *, '{{ network_name }}' as specific_network_name, 'NARROW' as network_type, 'Commercial' as network_class
FROM rates_base
WHERE payer_class_name = 'Commercial'
AND payer_id = {{ payer_id }}
{% endfor %}
{% endfor %}
{% for payer_id in exchange_multiple_network_payers %}
{% for network_name in exchange_product_network_mapping[payer_id] %}
UNION ALL
SELECT *, '{{ network_name }}' as specific_network_name, 'HMO' as network_type, 'Exchange' as network_class
FROM rates_base
WHERE payer_id = {{ payer_id }}
{% endfor %}
{% endfor %}
),
-------------------------------------------------------
-- 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,
network_type,
network_class,
specific_network_name,
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,
CASE
WHEN network_type = 'PPO' AND network_class = 'Commercial' THEN
MAX(
COALESCE(
CASE
{% for payer_id in all_payer_ids %}
{% if payer_id in ppo_payer_keywords %}
WHEN payer_id = {{ payer_id }} THEN
regexp_like(
plan_name_std,
'{{ "|".join(ppo_general_keywords + ppo_network_keywords + ppo_payer_keywords[payer_id]) }}'
)
{% endif %}
{% endfor %}
ELSE False
END,
False
)
)
WHEN network_type = 'HMO' AND network_class = 'Commercial' THEN
MAX(
COALESCE(
CASE
{% for payer_id in all_payer_ids %}
{% if payer_id in hmo_payer_keywords %}
WHEN payer_id = {{ payer_id }} THEN
regexp_like(
plan_name_std,
'{{ "|".join(hmo_general_keywords + hmo_network_keywords + hmo_payer_keywords[payer_id]) }}'
)
{% endif %}
{% endfor %}
ELSE False
END,
False
)
)
WHEN network_type = 'HMO' AND network_class = 'Exchange' THEN
MAX(
COALESCE(
CASE
{% for payer_id in all_payer_ids %}
{% if payer_id in exchange_payer_keywords %}
WHEN payer_id = {{ payer_id }} THEN
regexp_like(
plan_name_std,
'{{ "|".join(exchange_general_keywords + exchange_network_keywords + exchange_payer_keywords[payer_id]) }}'
)
{% endif %}
{% endfor %}
ELSE False
END,
False
)
)
WHEN network_type = 'NARROW' AND network_class = 'Commercial' THEN
MAX(
COALESCE(
CASE
{% for payer_id in all_payer_ids %}
{% if payer_id in narrow_payer_keywords %}
WHEN payer_id = {{ payer_id }} THEN
regexp_like(
plan_name_std,
'{{ "|".join(narrow_general_keywords + narrow_network_keywords + narrow_payer_keywords[payer_id]) }}'
)
{% endif %}
{% endfor %}
ELSE False
END,
False
)
)
END as has_network_plan,
CASE
WHEN network_type = 'PPO' AND network_class = 'Commercial' THEN
array_agg(
DISTINCT
CASE
{% for payer_id in all_payer_ids %}
{% if payer_id in ppo_payer_keywords %}
WHEN
payer_id = {{ payer_id }}
AND
regexp_like(
plan_name_std,
'{{ "|".join(ppo_general_keywords + ppo_network_keywords + ppo_payer_keywords[payer_id]) }}'
)
THEN id
{% endif %}
{% endfor %}
ELSE NULL
END
)
WHEN network_type = 'HMO' AND network_class = 'Commercial' THEN
array_agg(
DISTINCT
CASE
{% for payer_id in all_payer_ids %}
{% if payer_id in hmo_payer_keywords %}
WHEN
payer_id = {{ payer_id }}
AND
regexp_like(
plan_name_std,
'{{ "|".join(hmo_general_keywords + hmo_network_keywords + hmo_payer_keywords[payer_id]) }}'
)
THEN id
{% endif %}
{% endfor %}
ELSE NULL
END
)
WHEN network_type = 'HMO' AND network_class = 'Exchange' THEN
array_agg(
DISTINCT
CASE
{% for payer_id in all_payer_ids %}
{% if payer_id in exchange_payer_keywords %}
WHEN
payer_id = {{ payer_id }}
AND
regexp_like(
plan_name_std,
'{{ "|".join(exchange_general_keywords + exchange_network_keywords + exchange_payer_keywords[payer_id]) }}'
)
THEN id
{% endif %}
{% endfor %}
ELSE NULL
END
)
WHEN network_type = 'NARROW' AND network_class = 'Commercial' THEN
array_agg(
DISTINCT
CASE
{% for payer_id in all_payer_ids %}
{% if payer_id in narrow_payer_keywords %}
WHEN
payer_id = {{ payer_id }}
AND
regexp_like(
plan_name_std,
'{{ "|".join(narrow_general_keywords + narrow_network_keywords + narrow_payer_keywords[payer_id]) }}'
)
THEN id
{% endif %}
{% endfor %}
ELSE NULL
END
)
END as ids_with_network_plan,
CASE
WHEN network_type = 'PPO' AND network_class = 'Commercial' THEN
array_agg(
DISTINCT
CASE
{% for payer_id in all_payer_ids %}
{% if payer_id in ppo_payer_keywords %}
WHEN
payer_id = {{ payer_id }}
AND
regexp_like(
plan_name_std,
'{{ "|".join(ppo_general_keywords + ppo_network_keywords + ppo_payer_keywords[payer_id]) }}'
)
THEN plan_name
{% endif %}
{% endfor %}
ELSE NULL
END
)
WHEN network_type = 'HMO' AND network_class = 'Commercial' THEN
array_agg(
DISTINCT
CASE
{% for payer_id in all_payer_ids %}
{% if payer_id in hmo_payer_keywords %}
WHEN
payer_id = {{ payer_id }}
AND
regexp_like(
plan_name_std,
'{{ "|".join(hmo_general_keywords + hmo_network_keywords + hmo_payer_keywords[payer_id]) }}'
)
THEN plan_name
{% endif %}
{% endfor %}
ELSE NULL
END
)
WHEN network_type = 'HMO' AND network_class = 'Exchange' THEN
array_agg(
DISTINCT
CASE
{% for payer_id in all_payer_ids %}
{% if payer_id in exchange_payer_keywords %}
WHEN
payer_id = {{ payer_id }}
AND
regexp_like(
plan_name_std,
'{{ "|".join(exchange_general_keywords + exchange_network_keywords + exchange_payer_keywords[payer_id]) }}'
)
THEN plan_name
{% endif %}
{% endfor %}
ELSE NULL
END
)
WHEN network_type = 'NARROW' AND network_class = 'Commercial' THEN
array_agg(
DISTINCT
CASE
{% for payer_id in all_payer_ids %}
{% if payer_id in narrow_payer_keywords %}
WHEN
payer_id = {{ payer_id }}
AND
regexp_like(
plan_name_std,
'{{ "|".join(narrow_general_keywords + narrow_network_keywords + narrow_payer_keywords[payer_id]) }}'
)
THEN plan_name
{% endif %}
{% endfor %}
ELSE NULL
END
)
END as plans_with_network_plan
FROM rates
WHERE
CASE
WHEN network_type = 'PPO' AND network_class = 'Commercial' THEN
(
NOT regexp_like(
plan_name_std,
'{{ ppo_exclusions | join("|") }}'
)
OR
plan_name_std IN (
{% for exclusion in ppo_exclusion_exceptions %}
'{{ exclusion }}'{% if not loop.last %}, {% endif %}
{% endfor %}
)
)
{% for exclusion in ppo_exclusion_exact_matches %}
AND UPPER(plan_name) != '{{ exclusion }}'
{% endfor %}
{% for payer_spec, exclusion_list in ppo_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 %}
WHEN network_type = 'HMO' AND network_class = 'Commercial' THEN
(
NOT regexp_like(
plan_name_std,
'{{ hmo_exclusions | join("|") }}'
)
OR
plan_name_std IN (
{% for exclusion in hmo_exclusion_exceptions %}
'{{ exclusion }}'{% if not loop.last %}, {% endif %}
{% endfor %}
)
)
{% for exclusion in hmo_exclusion_exact_matches %}
AND UPPER(plan_name) != '{{ exclusion }}'
{% endfor %}
{% for payer_spec, exclusion_list in hmo_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 %}
WHEN network_type = 'HMO' AND network_class = 'Exchange' THEN
(
NOT regexp_like(
plan_name_std,
'{{ exchange_exclusions | join("|") }}'
)
OR
plan_name_std IN (
{% for exclusion in exchange_exclusion_exceptions %}
'{{ exclusion }}'{% if not loop.last %}, {% endif %}
{% endfor %}
)
)
{% for exclusion in exchange_exclusion_exact_matches %}
AND UPPER(plan_name) != '{{ exclusion }}'
{% endfor %}
{% for payer_spec, exclusion_list in exchange_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 %}
WHEN network_type = 'NARROW' AND network_class = 'Commercial' THEN
(
NOT regexp_like(
plan_name_std,
'{{ narrow_exclusions | join("|") }}'
)
OR
plan_name_std IN (
{% for exclusion in narrow_exclusion_exceptions %}
'{{ exclusion }}'{% if not loop.last %}, {% endif %}
{% endfor %}
)
)
{% for exclusion in narrow_exclusion_exact_matches %}
AND UPPER(plan_name) != '{{ exclusion }}'
{% endfor %}
{% for payer_spec, exclusion_list in narrow_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 %}
END
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12
),
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,
network_type,
network_class,
specific_network_name,
ids,
unique_payers,
unique_plans,
rate_array,
low_rate_variability,
is_single_rate,
is_single_plan,
has_network_plan,
ids_with_network_plan,
plans_with_network_plan,
CASE
WHEN
(
is_single_plan = True
OR is_single_rate = True
OR low_rate_variability = True
OR has_network_plan = True
)
THEN True
ELSE False
END as is_mapped,
COALESCE(
specific_network_name,
CASE
WHEN network_type = 'PPO' AND network_class = 'Commercial' THEN
CASE
{% for payer_id, value in ppo_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 %}
{% if value is not iterable or value is string %}
WHEN payer_id = {{ payer_id }} THEN '{{ value }}'
{% endif %}
{% endif %}
{% endfor %}
END
WHEN network_type = 'HMO' AND network_class = 'Commercial' THEN
CASE
{% for payer_id, value in hmo_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
WHEN network_type = 'HMO' AND network_class = 'Exchange' THEN
CASE
{% for payer_id, value in exchange_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 %}
{% if value is not iterable or value is string %}
WHEN payer_id = {{ payer_id }} THEN '{{ value }}'
{% endif %}
{% endif %}
{% endfor %}
END
WHEN network_type = 'NARROW' AND network_class = 'Commercial' THEN
CASE
{% for payer_id, value in narrow_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 %}
{% if value is not iterable or value is string %}
WHEN payer_id = {{ payer_id }} THEN '{{ value }}'
{% endif %}
{% endif %}
{% endfor %}
END
END
) as network_name,
CAST(payer_id AS VARCHAR) as payer_id
FROM df_with_flags
Union​
-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_ref_pg_plan_bridge_{{ sub_version }}
-- Subversion: {{ sub_version }}
CREATE OR REPLACE TABLE {{ schema_name }}.tmp_ref_pg_plan_bridge_{{ sub_version }}
WITH (
PARTITIONING = ARRAY['payer_id']
)
AS
{% for n_chunk in n_chunks %}
SELECT * FROM {{ schema_name }}.tmp_ref_pg_plan_bridge_{{ n_chunk }}_{{ sub_version }}
{% if not loop.last %}UNION ALL{% endif %}
{% endfor %}
Union Analysis​
-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_ref_pg_plan_bridge_analysis_{{ sub_version }}
-- Subversion: {{ sub_version }}
CREATE OR REPLACE TABLE {{ schema_name }}.tmp_ref_pg_plan_bridge_analysis_{{ sub_version }}
WITH (
PARTITIONING = ARRAY['payer_id']
)
AS
{% for n_chunk in n_chunks %}
SELECT * FROM {{ schema_name }}.tmp_ref_pg_plan_bridge_analysis_combined_{{ n_chunk }}_{{ sub_version }}
{% if not loop.last %}UNION ALL{% endif %}
{% endfor %}
Variables​
Exchange​
{% from 'raw/plan_bridge/variables_bh_exclusions.sql' import bh_exclusions %}
{% set exchange_general_keywords = [
'ESSENTIAL',
'EXCHANGE',
] %}
{% set exchange_network_keywords = [
'ESSENTIAL',
'ESSENTIALPLAN'
] %}
{% set exchange_payer_keywords = {
723: [
'ESS_PLAN_PROG_HOSP_OP_DEPT',
'ESS_PLAN_PROG_AMB_SURG',
'ESS_PLAN_PROG_MR/DD/TBI PTS',
'NYSTATEOFHEALTH PLANS',
'ESSENTIAL'
],
796: [
'ESSENTIAL'
],
958: [
'FFS',
'ESSENTIAL'
],
} %}
{% set exchange_exclusion_exceptions = [
'ESSENTIAL'
] %}
{% set exchange_exclusions = [
'OUTOF',
'ADVANTAGE',
'DUAL',
'INDEMNITY',
'OON',
'MEDICARE',
'MEDICAID',
'BEHAV',
'INTERNATIONAL',
'FEDERAL',
'ALTERNATE',
'CHP',
'MMAI',
'VACCN',
'OOS',
'GVT',
'COVID19',
'MULTIPLAN',
'PHCS',
'CHIPKM',
'TRIWESTGOV',
'MGMCD',
'MMC',
'MCAREPFFS',
'MCAREADV',
'VACCA',
'MEDICAREHMO',
'DUALELIGIBLES',
'MAHMOANDPPO',
'MCDHMO',
'UHCMEDIADVAN',
'AARP',
'MGDM/CARE',
'UNITEDHEALTHCARECOMMUNITY',
'STATEGOVERNMENTPROGRAM',
'MCAREHMO',
'MEDIBLUE',
'DSNP',
'MMOMEDICARE',
'GOVERNMENT',
'PPO',
'HMO',
'EPO',
'POS'
] + bh_exclusions %}
{% set exchange_exclusion_exact_matches = [
'BH',
'MM',
'PMAP',
'MAP',
'MA',
'PCB'
] %}
{% set exchange_payer_exclusions = {
(723, 'NY'): [],
(796, 'NY'): [],
(958, 'NY'): [
'GOLD',
'GOLDCARE'
],
} %}
{% set exchange_payer_ids = exchange_payer_keywords.keys() %}
{% set exchange_multiple_network_payers = [] %}
{% set exchange_product_network_mapping = {
723: 'Essential Plan',
796: 'Essential Plan',
958: 'Essential Plan',
643: 'Exchange',
7: 'Exchange',
76: 'Exchange',
151: 'Exchange',
174: 'Exchange',
111: 'Exchange',
911: 'Exchange',
462: 'Exchange',
971: 'Exchange',
42: 'Exchange',
43: 'Exchange',
44: 'Exchange',
47: 'Exchange',
49: 'Exchange',
50: 'Exchange',
51: 'Exchange',
52: 'Exchange',
53: 'Exchange',
54: 'Exchange',
56: 'Exchange',
61: 'Exchange',
101: 'Exchange',
160: 'Exchange',
165: 'Exchange',
166: 'Exchange',
168: 'Exchange',
169: 'Exchange',
174: 'Exchange',
229: 'Exchange',
383: 'Exchange',
388: 'Exchange',
389: 'Exchange',
390: 'Exchange',
391: 'Exchange',
392: 'Exchange',
397: 'Exchange',
398: 'Exchange',
403: 'Exchange',
454: 'Exchange',
455: 'Exchange',
456: 'Exchange',
458: 'Exchange',
461: 'Exchange',
464: 'Exchange',
628: 'Exchange',
} %}
Narrow​
{% from 'raw/plan_bridge/variables_bh_exclusions.sql' import bh_exclusions %}
{% set narrow_general_keywords = [
'ALLCOMMERCIAL',
'COMMERCIAL',
'BROAD',
'ALLPLAN',
'ALLPRODUCT',
'ALLPAY',
'ALLOTHER',
] %}
{% set narrow_network_keywords = [
'NARROW',
'LIMITED',
'TIER',
'TIERED',
'SELECT',
'ESSENTIAL',
'BASIC'
] %}
{% set narrow_payer_keywords = {
76: [
'LOCALPLUS'
],
643: [
'NEXUS',
'NAVIGATE'
],
} %}
{% set narrow_exclusion_exceptions = [
'HMO/PPO/POS/INDEMNITY',
] %}
{% set narrow_exclusions = [
'PPO',
'HMO',
'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',
'NARROWANDHMO',
'MCDHMO',
'UHCMEDIADVAN',
'COVEREDCA',
'NYSTATEOFHEALTH',
'AARP',
'EXCHANG',
'MGDM/CARE',
'UNITEDHEALTHCARECOMMUNITY',
'STATEGOVERNMENTPROGRAM',
'MCAREHMO',
'MEDIBLUE',
'HIX',
'DSNP',
'MMOMEDICARE',
'GOVERNMENT',
'STATEPLAN',
'STATEHEALTH',
'INDIVIDUAL',
'IFP',
] + bh_exclusions %}
{% set narrow_exclusion_exact_matches = [
'BH',
'MM',
'PMAP',
'MAP',
'MA',
'MM',
'PCB'
] %}
{% set narrow_payer_exclusions = {
(76, 'NATIONAL'): [
'SAR',
'SHAREDADMIN',
'STAR',
'SPRING',
'FEHB',
'MGMCD',
'PSYCHIATRIC',
'MDCPSEMPLOYEE',
'OAP',
'OPENACCESSPLUS',
'PPO',
'HMO'
],
(643, 'NATIONAL'): [
'HARP',
'STARPLUS',
'STARKM',
'STARKIDS',
'STARPERINATE',
'MEDIADVAN',
'VACCNMM',
'UNITEDHEALTHYKIDS',
'VACCN/OPTUM',
'STARKIDKM',
'VACCN',
'PSYCHIATRIC',
'FEHB',
'VACNN',
'UHCVA',
'MEDICA',
'VACOMMUNITYCARENETWORK',
'CHOICE',
'CORE',
'PPO',
'HMO'
]
} %}
{% set narrow_payer_ids = narrow_payer_keywords.keys() %}
{% set narrow_multiple_network_payers = [
643
] %}
{% set narrow_product_network_mapping = {
76: 'LOCALPLUS',
643: ['NAVIGATE', 'NEXUS ACO OAP']
} %}
Behavioral Health Exclusions​
{% set bh_exclusions = [
'ALLCARELON(FORMERLYBEACON)BHUM[13]PLANS',
'VALUEBHIPONLY-ALLPLANS',
'MAGELLANBHALTERNATEPLAN[10016301]',
'OPTUMEAP&BH[10880069]',
'BLUESHIELDMHSABH[10140049]',
'SELFINSURED-BHCADC',
'FULLYINSURED-BHCADC',
'FULLYINSURED-BHMD',
'SELFINSURED-BHPHD',
'SELFINSURED-BHMD',
'FULLYINSURED-BHML',
'SELFINSURED-BHMASTERS',
'FULLYINSURED-BHPHD',
'PACIFHMOBHEMPLOYEES/DEPI/O',
'PARTNERSBHSTATE',
'MAGELLANBHOPONLY-ALLPLANS',
'LIFESYNCHBHCOMM-ALLOTHERPLANS',
'UHCBHCOMMERCIAL',
'8258CENPATICOBHSTIN,VAIN20230101',
'CIGNABHPPOEVERNORTH[5330017]',
'CIGNABHPOSALT[18840025]',
'CIGNABHHMOALT[18840024]',
'AETNABH[5110024]',
'CARELONBHCOMMERCIAL[410801]',
'SANBERNARDINOBHIPONLY-ALLPLANS',
'CIGNABHDUKEEMP[110715005]',
'BEACONBH-ALLPLANS',
'ALLCARELON(FORMERLYBEACON)BHMH[14]PLANS',
'MOLINABHSWH',
'MAGELLANBHIPONLY-ALLPLANS',
'UNITEDBHCOMM-ALLOTHERPLANS',
'CARELONBHCOMM-ALLOTHERPLANS',
'MAGELLANBH-ALLPLANS',
'CIGNABHOPONLY',
'HUMANABHOPONLY',
'OTHERBHPAYOR[50014]CHAHBCATCH-ALLCONTRACT[93]',
'ALLCARELON(FORMERLYBEACON)BHHR[15]PLANS',
'ANTHEMBLUECROSSBH[495903]',
'HUMANABHNETWORK',
'BCBS-BH-BLUECARD',
'BCBSBHFEDERAL',
'CBHNPCOMBHHCNETWORK',
'CARELONBHOPTIONS',
'CIGNABHRTC',
'CIGNABHACUTE',
'HUMANABHHMOXPPOXSMALLGROUP',
'UNITEDBHCOMM',
'ALLCARELON(FORMERLYBEACON)BHHA[38]PLANS',
'BCBSMEDINONBHN',
'OPTUMEAP&BH[10880069]',
'UHCTNCAREBHO-UHCTNCAREBHO',
'CIGNABH',
'CIGNABHHMOALT[18840024]',
'AETNAJVBHN',
'AETNABH[5110024]',
'MOLINABHSWH',
'BCBS-BH-BLUECARD',
'ANTHEMBHS1',
'UNITEDMEDICALRESOURCE-BHOPTUM[100112601]',
'BCBSMEDIBHN',
'MOLINABHPHD',
'BLUESHIELDMHSABH[10140049]',
'CIGNABHPOSALT[18840025]',
'UNITEDBHCOMM-ALLOTHERPLANS',
'CIGNABHPPOEVERNORTH[5330017]',
'EVERNORTHBH',
'COMMUNITEDBH',
'UNIBHNMOLINASILVER250[51431]',
'CIGNABHDUKEEMP[110715005]',
'UNIBHNMOLINABRONZE[51427]',
'UNIBHNMOLINASILVER200[51430]',
'CIGNABHOPONLY',
'UNIBHNMOLINASILVER100[51428]',
'UNIBHNMOLINAGOLD[51432]',
'BLUECAREBH',
'UNIBHNMOLINASILVER150[51429]',
'CIGNABHARNP',
'CIGNABHMA',
'CIGNABHHN',
'CIGNABHMD'
]%}