CREATE OR REPLACE TABLE {{ schema_name }}.tmp_whisper_network_{{ sub_version }} AS
WITH
payer_score_card_payer AS (
SELECT
payer_id,
product_network_label,
overall_score as payer_overall_score,
ip_hospital_score as payer_mrf_ip_hospital_score,
op_hospital_score as payer_mrf_op_hospital_score,
hospital_score as payer_mrf_hospital_score,
asc_score as payer_mrf_asc_score,
imaging_center_score as payer_mrf_imaging_center_score,
network_size_rank
FROM {{ cld_params.Tables.PAYER_MRF_NETWORK_SCORES.value }}
WHERE month_run = '{{ sub_version }}'
),
payer_score_card_state AS (
SELECT
payer_id,
product_network_label,
state,
overall_score as payer_overall_score_state,
ip_hospital_score as payer_mrf_ip_hospital_score_state,
op_hospital_score as payer_mrf_op_hospital_score_state,
hospital_score as payer_mrf_hospital_score_state,
asc_score as payer_mrf_asc_score_state,
imaging_center_score as payer_mrf_imaging_center_score_state,
network_size_rank
FROM {{ cld_params.Tables.PAYER_MRF_NETWORK_STATE_SCORES.value }}
WHERE month_run = '{{ sub_version }}'
),
network_expected_states AS (
SELECT
payer_id,
product_network_label,
CASE WHEN MAX(national) = 1 THEN 'National' ELSE ARRAY_JOIN(ARRAY_AGG(state ORDER BY state ASC),', ') END as payer_mrf_expected_states
FROM {{ cld_params.Tables.PAYER_MRF_EXPECTED_GEOGRAPHIES.value }}
WHERE month_run = '{{ sub_version }}'
GROUP BY 1,2
),
payer_market_share_state AS (
SELECT DISTINCT
state,
payer_id,
payer_name,
state_payer_covered_lives,
state_covered_lives,
state_market_share,
state_rank
FROM {{ schema_name }}.tmp_ref_policy_reporter_{{ sub_version }}
)
SELECT
s.payer_id,
p.payer_name,
s.network_id,
s.network_name,
t.network_state,
es.payer_mrf_expected_states,
pscp.payer_overall_score as payer_network_overall_score,
pscp.payer_mrf_ip_hospital_score as payer_network_mrf_ip_hospital_score,
pscp.payer_mrf_op_hospital_score as payer_network_mrf_op_hospital_score,
pscp.payer_mrf_hospital_score as payer_network_mrf_hospital_score,
pscp.payer_mrf_asc_score as payer_network_mrf_asc_score,
pscp.payer_mrf_imaging_center_score as payer_network_mrf_imaging_center_score,
pscs.payer_overall_score_state as payer_network_overall_score_state,
pscs.payer_mrf_ip_hospital_score_state as payer_network_mrf_ip_hospital_score_state,
pscs.payer_mrf_op_hospital_score_state as payer_network_mrf_op_hospital_score_state,
pscs.payer_mrf_hospital_score_state as payer_network_mrf_hospital_score_state,
pscs.payer_mrf_asc_score_state as payer_network_mrf_asc_score_state,
pscs.payer_mrf_imaging_center_score_state as payer_network_mrf_imaging_center_score_state,
pscp.network_size_rank,
pr.state_payer_covered_lives,
pr.state_market_share as state_payer_covered_lives_market_share,
pr.state_rank as state_payer_covered_lives_rank
FROM {{ schema_name }}.tmp_ref_network_spine_{{ sub_version }} s
LEFT JOIN {{ schema_name }}.tmp_ref_payer_spine_{{ sub_version }} p
ON s.payer_id = p.payer_id
JOIN
UNNEST(
CASE
WHEN cardinality(s.network_state) = 1 AND s.network_state[1] = 'NATIONAL'
THEN ARRAY['AL', 'AK', 'AZ', 'AR', 'CA', 'CO', 'CT', 'DC', 'DE', 'FL', 'GA',
'HI', 'ID', 'IL', 'IN', 'IA', 'KS', 'KY', 'LA', 'ME', 'MD',
'MA', 'MI', 'MN', 'MS', 'MO', 'MT', 'NE', 'NV', 'NH', 'NJ',
'NM', 'NY', 'NC', 'ND', 'OH', 'OK', 'OR', 'PA', 'RI', 'SC',
'SD', 'TN', 'TX', 'UT', 'VT', 'VA', 'WA', 'WV', 'WI', 'WY']
ELSE s.network_state
END
) AS t(network_state)
ON TRUE
LEFT JOIN payer_market_share_state pr
ON pr.payer_id = s.payer_id
AND pr.state = t.network_state
LEFT JOIN payer_score_card_payer pscp
ON pscp.payer_id = s.payer_id
AND pscp.product_network_label = element_at(s.payer_data_network, 1)
LEFT JOIN payer_score_card_state pscs
ON pscs.payer_id = s.payer_id
AND pscs.product_network_label = element_at(s.payer_data_network, 1)
AND pscs.state = t.network_state
LEFT JOIN network_expected_states es
ON es.payer_id = s.payer_id
AND es.product_network_label = element_at(s.payer_data_network, 1)