Main Tables

---

BRIT

-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_int_combined_brit_{{ n_chunk }}_{{ sub_version }} 
-- Subversion: {{ sub_version }}
-- Payer IDs: {{ payer_ids }}
-- Chunk: {{ n_chunk }}


-- CHARGES + ALLOWED AMOUNTS
{% set gross_charges = cld_params.GROSS_CHARGE_COLS + cld_params.GROSS_CHARGE_STATS  %}
{% set cash_charges = cld_params.CASH_COLS %}

-- RATE SOURCES
{% set hospital_raw_cols = cld_params.HOSPITAL_RAW_COLS %}
{% set payer_raw_cols = cld_params.PAYER_RAW_COLS %}
{% set hospital_transformation_cols = cld_params.HOSPITAL_TRANSFORMATION_COLS %}
{% set payer_transformation_cols = cld_params.PAYER_TRANSFORMATION_COLS %}
{% set imputation_cols = cld_params.IMPUTATION_COLS %}

{% set original_nontransformed_cols = cld_params.ORIGINAL_NONTRANSFORMED_COLS %}

-- ====================
-- COMBINE
-- ====================
{% set raw_cols = hospital_raw_cols + payer_raw_cols %}
{% set rate_cols = hospital_raw_cols + hospital_transformation_cols 
    + payer_raw_cols + payer_transformation_cols + imputation_cols %}

-- Prov Spine Cols
{% set prov_spine_cols = cld_params.PROV_SPINE_COLS %}

-- TRANSFORMATION UTILS
{% set transformation_utils = cld_params.TRANSFORMATION_COLS %}

-- BENCHMARKS
{% set benchmark_cols = cld_params.BENCHMARK_COLS %}

-- IMPUTATION STATS
{% set imputation_stats = cld_params.IMPUTATION_STATS_COLS %}


-- Benchmarks, Rates, Imputations, and Transformations
CREATE TABLE {{ schema_name }}.tmp_int_combined_brit_{{ n_chunk }}_{{ sub_version }} 
WITH (
    PARTITIONING = ARRAY['payer_id']
)
AS
WITH
-- imputations - derived
ide AS (
    SELECT
        roid,
        {% for col in cld_params.DERIVED_IMPUTATION_COLS %}
            {{ col }},
        {% endfor %}
        {% for col in cld_params.DERIVED_IMPUTATION_STATS_COLS %}
            {{ col }},
        {% endfor %}
        payer_id
    FROM {{ schema_name }}.tmp_int_imputations_derived_{{ sub_version }}
    UNION ALL
    SELECT
        roid,
        {% for col in cld_params.DERIVED_IMPUTATION_COLS %}
            {{ col }},
        {% endfor %}
        {% for col in cld_params.DERIVED_IMPUTATION_STATS_COLS %}
            {{ col }},
        {% endfor %}
        payer_id
    FROM {{ schema_name }}.tmp_int_imputations_aprdrg_{{ sub_version }}
),
-- imputations - cstm
icstm AS (
    SELECT
        roid,
        {% for col in cld_params.CSTM_IMPUTATION_COLS %}
            {{ col }},
        {% endfor %}
        {% for col in cld_params.CSTM_IMPUTATION_STATS_COLS %}
            {{ col }},
        {% endfor %}
        payer_id
    FROM {{ schema_name }}.tmp_int_imputations_cstm_{{ sub_version }}
    UNION ALL
    SELECT
        roid,
        {% for col in cld_params.CSTM_IMPUTATION_COLS %}
            {{ col }},
        {% endfor %}
        {% for col in cld_params.CSTM_IMPUTATION_STATS_COLS %}
            {{ col }},
        {% endfor %}
        payer_id
    FROM {{ schema_name }}.tmp_int_imputations_aprdrg_{{ sub_version }}

)
SELECT 
    r.roid,
    r.network_id,
    r.provider_id,
    r.bill_type,
    r.billing_code,
    r.billing_code_type,
    r.facility,
    code_spine.is_surg_code,
    code_spine.is_drug_code,
    {% for col in prov_spine_cols %}
        prov_spine.{{ col }},
    {% endfor %}

    {% for col in cash_charges %}
        r.{{ col }},
    {% endfor %}
    
    {% for col in gross_charges %}
        r.{{ col }},
    {% endfor %}
    
    {% for col in rate_cols + original_nontransformed_cols %}
        {{ col }},
    {% endfor %}

    {% for col in raw_cols + original_nontransformed_cols %}
        {{ col }}_id,
    {% endfor %}

    {% for col in raw_cols + original_nontransformed_cols %}
        {{ col }}_original_billing_codes,
        {{ col }}_original_billing_code_type,
    {% endfor %}

    {% for col in benchmark_cols %}
        b.{{ col }},
    {% endfor %}

    {% for col in transformation_utils %}
        t.{{ col }},
    {% endfor %}

    {% for col in imputation_stats %}
        {{ col }},
    {% endfor %}

    r.payer_id
FROM {{ schema_name }}.tmp_int_combined_raw_{{ sub_version }} r
LEFT JOIN {{ schema_name }}.tmp_ref_provider_spine_{{ sub_version }} prov_spine
    ON r.provider_id = prov_spine.provider_id
LEFT JOIN {{ schema_name }}.tmp_ref_code_spine_{{ sub_version }} code_spine
    ON r.bill_type = code_spine.bill_type
    AND r.billing_code = code_spine.billing_code
    AND r.billing_code_type = code_spine.billing_code_type
    AND (
        r.facility = code_spine.facility
        OR (r.facility IS NULL AND code_spine.facility IS NULL)
    )
LEFT JOIN {{ schema_name }}.tmp_int_transformations_{{ sub_version }} t
    ON r.roid = t.roid
    AND r.payer_id = t.payer_id
LEFT JOIN {{ schema_name }}.tmp_int_imputations_{{ sub_version }} i
    ON r.roid = i.roid
    AND r.payer_id = i.payer_id
LEFT JOIN ide
    ON r.roid = ide.roid
    AND r.payer_id = ide.payer_id
LEFT JOIN icstm
    ON r.roid = icstm.roid
    AND r.payer_id = icstm.payer_id
LEFT JOIN {{ schema_name }}.tmp_int_benchmarks_{{ sub_version }} b
    ON r.roid = b.roid
    AND r.payer_id = b.payer_id
WHERE
    r.payer_id IN ({{ payer_ids }})

All

-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_int_combined_no_whisp_{{ n_chunk }}_{{ sub_version }} 
-- Subversion: {{ sub_version }}
-- Payer IDs: {{ payer_ids }}
-- n_chunk: {{ n_chunk }}

-- SPINES
{% set payer_spine_cols = cld_params.PAYER_SPINE_COLS %}
{% set network_spine_cols = cld_params.NETWORK_SPINE_COLS %}
{% set prov_spine_cols = cld_params.PROV_SPINE_COLS %}
{% set code_spine_cols = cld_params.CODE_SPINE_COLS %}

-- CHARGES + ALLOWED AMOUNTS
{% set gross_charges = cld_params.GROSS_CHARGE_COLS + cld_params.GROSS_CHARGE_STATS  %}
{% set cash_charges = cld_params.CASH_COLS %}

-- RATE SOURCES
{% set payer_raw_cols = cld_params.PAYER_RAW_COLS %}
{% set payer_transformation_cols = cld_params.PAYER_TRANSFORMATION_COLS %}

{% set hospital_raw_cols = cld_params.HOSPITAL_RAW_COLS %}
{% set hospital_transformation_cols = cld_params.HOSPITAL_TRANSFORMATION_COLS %}

{% set imputation_cols = cld_params.IMPUTATION_COLS %}

{% set original_nontransformed_cols = cld_params.ORIGINAL_NONTRANSFORMED_COLS %}

-- TRANSFORMATION UTILS
{% set transformation_utils = cld_params.TRANSFORMATION_COLS %}

-- BENCHMARKS
{% set benchmark_cols = cld_params.BENCHMARK_COLS %}

-- OUTLIER BOUNDS
{% set outlier_bounds = cld_params.OUTLIER_BOUNDS %}

-- original billing codes name mapping
{% set original_billing_code_maps = cld_params.ORIGINAL_BILLING_CODE_MAPS %}

-- original billing_code_type mapping
{% set original_billing_code_type_maps = cld_params.ORIGINAL_BILLING_CODE_TYPE_MAPS %}

-- original non-transformed column mapping
{% set non_transformed_column_maps = cld_params.NON_TRANSFORMED_COLUMN_MAPS %}

-- IMPUTATION STATS
{% set imputation_stats = cld_params.IMPUTATION_STATS_COLS %}

-- ====================
-- COMBINE
-- ====================
{% set ip_rate_cols = cld_params.INPATIENT_COLS_FULL %}
{% set non_ip_rate_cols = cld_params.NON_INPATIENT_COLS_FULL %}

-- print IP rate hierarchy
{% for col in ip_rate_cols %}
    -- {{ col }}
{% endfor %}
{% set ip_rate_cols_no_impute = cld_params.INPATIENT_COLS_RAW_TRANSFORM %}
{% set non_ip_rate_cols_no_impute = cld_params.NON_INPATIENT_COLS_RAW_TRANSFORM %}

-- source mapping
{% set source_mapping = {} %}
{% for col in hospital_raw_cols + hospital_transformation_cols + payer_raw_cols + payer_transformation_cols %}
    {% if col.startswith("payer") %}
        {% set _ = source_mapping.update({col: "payer"}) %}
    {% elif col.startswith("hosp") or col.startswith("drug") %}
        {% set _ = source_mapping.update({col: "hospital"}) %}
    {% endif %}
{% endfor %}
{% for col in imputation_cols %}
    {% set _ = source_mapping.update({col: "imputation"}) %}
{% endfor %}

-- rate_type mapping
{% set rate_type_mapping = {} %}
{% for col in hospital_raw_cols + payer_raw_cols %}
    {% set _ = rate_type_mapping.update({col: "raw: " + col}) %}
{% endfor %}
{% for col in hospital_transformation_cols + payer_transformation_cols %}
    {% set _ = rate_type_mapping.update({col: "transform: " + col}) %}
{% endfor %}
{% for col in imputation_cols %}
    {% set _ = rate_type_mapping.update({col: "impute: " + col}) %}
{% endfor %}


-- methodology_mapping
-- per_diem, negotiated, derived columns depend on other fields and their methodologies are 
-- specified below when methodology_mapping is called
{% set methodology_mapping = {} %}
{% for col in hospital_raw_cols + payer_raw_cols + 
    hospital_transformation_cols + payer_transformation_cols + imputation_cols %}
    {% if 'case_rate' in col or 'base_rate' in col or 'cstm_negotiated_rate' in col %}
        {% set _ = methodology_mapping.update({col: "Case Rate"}) %}
    {% endif %}
    {% if 
        ('hospital_perc' in col) or 
        ('payer_' in col and 'perc_to_dol' in col) or 
        (col.startswith('cstm_') and 'perc_to_dol' in col) or 
        (col.startswith('rc_') and 'perc_to_dol' in col) or
        (col.startswith('op_') and 'perc_to_dol' in col)
    %}
        {% set _ = methodology_mapping.update({col: "Percent of Total Billed Charges"}) %}
    {% endif %}
    {% if 'fee_schedule' in col %}
        {% set _ = methodology_mapping.update({col: "Fee Schedule"}) %}
    {% endif %}
{% endfor %}


-- rate_class mapping
{% set rate_class_mapping = {} %}
{% for col in hospital_raw_cols + payer_raw_cols %}
    {% set _ = rate_class_mapping.update({col: "Raw"}) %}
{% endfor %}
{% for col in hospital_transformation_cols + payer_transformation_cols %}
    {% set _ = rate_class_mapping.update({col: "Transform"}) %}
{% endfor %}
{% for col in imputation_cols %}
    {% set _ = rate_class_mapping.update({col: "Impute"}) %}
{% endfor %}


-- imputation method mapping
{% set imputation_method_mapping = {} %}
{% for col in imputation_cols %}
    
    {% if "msdrg_mrf_base" in col %}
        {% set _ = imputation_method_mapping.update({col: "MRF-Reported Base Rate"}) %}
    {% elif "msdrg_base_rate" in col or "msdrg_gc" in col or "op_gc" in col %}
        {% set _ = imputation_method_mapping.update({col: "Inferred Base Rate"}) %}
    {% elif "rc_family" in col %}
        {% set _ = imputation_method_mapping.update({col: "Inferred RC Family Rate"}) %}
    {% elif "rc_global" in col %}
        {% set _ = imputation_method_mapping.update({col: "Inferred RC Global Rate"}) %}
    {% elif "opg" in col %}
        {% set _ = imputation_method_mapping.update({col: "Inferred Outpatient Procedure Grouper"}) %}
    {% elif "cstm_" in col %}
        {% set _ = imputation_method_mapping.update({col: "MRF-CSTM"}) %}
    {% endif %}
{% endfor %}

-- transformation method mapping
{% set transformation_method_mapping = {} %}
{% for col in hospital_transformation_cols + payer_transformation_cols + imputation_cols %}
    {% if "per_diem" in col %}
        {% set _ = transformation_method_mapping.update({col: "Per Diem to Dollar"}) %}
    {% elif "perc_to_dol" in col %}
        {% set _ = transformation_method_mapping.update({col: "Percentage to Dollar"}) %}
    {% endif %}
{% endfor %}


-- gross_charge_type mapping
{% set gross_charge_type_mapping = {} %}
{% for col in hospital_transformation_cols + payer_transformation_cols + imputation_cols %}
    {% if "_cbsa" in col %}
        {% set _ = gross_charge_type_mapping.update({col: "CBSA Median"}) %}
    {% elif "_state" in col %}
        {% set _ = gross_charge_type_mapping.update({col: "State Median"}) %}
    {% elif "gc_hosp" in col %}
        {% set _ = gross_charge_type_mapping.update({col: "Hospital"}) %}
    {% elif "gc_komodo" in col %}
        {% set _ = gross_charge_type_mapping.update({col: "Hospital"}) %}
    {% elif "drug_dosage_" in col %}
        {% set _ = gross_charge_type_mapping.update({col: "Hospital"}) %}
    {% endif %}
{% endfor %}

-- gross_charge_source_mapping
{% set gross_charge_source_mapping = {} %}
{% for col in hospital_transformation_cols + payer_transformation_cols + imputation_cols %}
    {% if "gc_hosp" in col %}
        {% set _ = gross_charge_source_mapping.update({col: "Hospital"}) %}
    {% elif "gc_komodo" in col %}
        {% set _ = gross_charge_source_mapping.update({col: "Claims"}) %}
    {% elif "drug_dosage_" in col %}
        {% set _ = gross_charge_source_mapping.update({col: "Hospital"}) %}
    {% endif %}
{% endfor %}



-- ====================
-- Rate Arrays
-- ====================

{% macro rate_score_array(rate_cols) %}
ARRAY[
    -1,
    {% for c in (rate_cols) %}
        a.{{ c }}_validation_score{% if not loop.last %}, {% endif %}
    {% endfor %}
]
{% endmacro %}

{% macro rate_array(rate_cols) %}
ARRAY[
    NULL,
    {% for c in (rate_cols) %}
        c.{{ c }}{% if not loop.last %}, {% endif %}
    {% endfor %}
]
{% endmacro %}

{% macro percent_of_medicare_benchmark_array(rate_cols, benchmark_column) %}
ARRAY[
    NULL,
    {% for c in (rate_cols) %}
        CASE 
            WHEN p.exclude_medicare_calc = true
                THEN NULL
            WHEN c.{{ benchmark_column }} > 0
                THEN c.{{ c }} / c.{{ benchmark_column }}
            ELSE NULL
        END{% if not loop.last %}, {% endif %}
    {% endfor %}
]
{% endmacro %}

{% macro percent_of_medicare_array(rate_cols) %}
{{ percent_of_medicare_benchmark_array(rate_cols, 'medicare_rate') }}
{% endmacro %}

{% macro percent_of_state_avg_medicare_array(rate_cols) %} 
{{ percent_of_medicare_benchmark_array(rate_cols, 'state_avg_medicare_rate') }}
{% endmacro %}

{% macro percent_of_national_avg_medicare_array(rate_cols) %} 
{{ percent_of_medicare_benchmark_array(rate_cols, 'national_avg_medicare_rate') }}
{% endmacro %}

{% macro percent_of_cbsa_avg_medicare_array(rate_cols) %} 
{{ percent_of_medicare_benchmark_array(rate_cols, 'cbsa_avg_medicare_rate') }}
{% endmacro %}

{% macro percent_of_asc_medicare_array(rate_cols) %} 
{{ percent_of_medicare_benchmark_array(rate_cols, 'asc_avg_medicare_rate') }}
{% endmacro %}

{% macro percent_of_lab_medicare_array(rate_cols) %} 
{{ percent_of_medicare_benchmark_array(rate_cols, 'lab_avg_medicare_rate') }}
{% endmacro %}

{% macro percent_of_list_array(rate_cols) %}
ARRAY[
    NULL,
    {% for c in (rate_cols) %}
        c.{{ c }} / c.mrf_gross_charge_provider{% if not loop.last %}, {% endif %}
    {% endfor %}
]
{% endmacro %}

{% macro source_array(rate_cols) %}
ARRAY[
    NULL,
    {% for c in (rate_cols) %}
        '{{ source_mapping[c] }}'{% if not loop.last %}, {% endif %}
    {% endfor %}
]
{% endmacro %}

{% macro rate_type_array(rate_cols) %}
ARRAY[
    NULL,
    {% for c in (rate_cols) %}
        '{{ rate_type_mapping[c] }}'{% if not loop.last %}, {% endif %}
    {% endfor %}
]
{% endmacro %}

{% macro methodology_array(rate_cols) %}
ARRAY[
    NULL,
    {% for c in (rate_cols) %}
        {% if c in methodology_mapping %}
            '{{ methodology_mapping[c] }}'
        {% else %}
            CASE
                WHEN {{ 'payer_negotiated' in c or 'payer_derived' in c }} AND (c.bill_type = 'Professional') 
                THEN 'Fee Schedule'
                WHEN {{ 'payer_negotiated' in c or 'payer_derived' in c }} AND (is_surg_code = True OR c.bill_type = 'Inpatient')
                THEN 'Case Rate'
                WHEN {{ 'payer_negotiated' in c or 'payer_derived' in c }} AND COALESCE(is_surg_code, False) = False
                THEN 'Fee Schedule'
                WHEN {{ 'per_diem' in c }} AND c.bill_type NOT IN ('Inpatient')
                THEN 'Case Rate'
                WHEN {{ 'per_diem' in c }} AND c.bill_type IN ('Inpatient')
                THEN 'Per Diem'
                ELSE 'Unknown'
            END
        {% endif %}
        {% if not loop.last %}, {% endif %}
    {% endfor %}
]
{% endmacro %}

{% macro rate_class_array(rate_cols) %}
ARRAY[
    NULL,
    {% for c in (rate_cols) %}
        '{{ rate_class_mapping[c] }}'{% if not loop.last %}, {% endif %}
    {% endfor %}
]
{% endmacro %}

{% macro imputation_method_array(rate_cols) %}
ARRAY[
    NULL,
    {% for c in (rate_cols) %}
        {% if c in imputation_method_mapping %}
            '{{ imputation_method_mapping[c] }}'
        {% else %}
            NULL
        {% endif %}
        {% if not loop.last %}, {% endif %}
    {% endfor %}
]
{% endmacro %}

{% macro transformation_method_array(rate_cols) %}
ARRAY[
    NULL,
    {% for c in (rate_cols) %}
        {% if c in transformation_method_mapping %}
            '{{ transformation_method_mapping[c] }}'
        {% else %}
            NULL
        {% endif %}
        {% if not loop.last %}, {% endif %}
    {% endfor %}
]
{% endmacro %}

{% macro gross_charge_type_array(rate_cols) %}
ARRAY[
    NULL,
    {% for c in (rate_cols) %}
        {% if c in gross_charge_type_mapping %}
            '{{ gross_charge_type_mapping[c] }}'
        {% else %}
            NULL
        {% endif %}
        {% if not loop.last %}, {% endif %}
    {% endfor %}
]
{% endmacro %}

{% macro gross_charge_source_array(rate_cols) %}
ARRAY[
    NULL,
    {% for c in (rate_cols) %}  
        {% if c in gross_charge_source_mapping %}
            '{{ gross_charge_source_mapping[c] }}'
        {% else %}
            NULL
        {% endif %}
        {% if not loop.last %}, {% endif %}
    {% endfor %}
]
{% endmacro %}

{% macro rate_id_array(rate_cols_no_impute) %}
ARRAY[
    NULL,
    {% for c in (rate_cols_no_impute) %}
        {% if c in non_transformed_column_maps %}
            c.{{ non_transformed_column_maps[c] }}_id
        {% else %}
            c.{{ c.replace('_anesthesia_cf', '') }}_id
        {% endif %}
        {% if not loop.last %}, {% endif %}
    {% endfor %}
]
{% endmacro %}

{% macro original_billing_codes_array(rate_cols) %}
ARRAY[
    NULL,
    {% for c in rate_cols %}
        {% if c in original_billing_code_maps %}
            {{ original_billing_code_maps[c] }}
        {% else %}
            NULL
        {% endif %}
        {% if not loop.last %}, {% endif %}
    {% endfor %}
]
{% endmacro %}

{% macro original_billing_code_type_array(rate_cols) %}
ARRAY[
    NULL,
    {% for c in rate_cols %}
        {% if c in original_billing_code_type_maps %}
            {{ original_billing_code_type_maps[c] }}
        {% else %}
            NULL
        {% endif %}
        {% if not loop.last %}, {% endif %}
    {% endfor %}
]
{% endmacro %}

{% macro gross_charge_hierarchy_fallback() %}
COALESCE(
    {% for col in cld_params.GROSS_CHARGE_COLS %}
        c.{{ col }}{% if not loop.last %},{% endif %}
    {% endfor %}
)
{% endmacro %}

{% macro gross_charge_type_hierarchy_fallback() %}
CASE
    {% for col in cld_params.GROSS_CHARGE_COLS %}
        WHEN c.{{ col }} IS NOT NULL THEN 
            {% if col == 'mrf_gross_charge_provider' %}'mrf_gross_charge_provider'
            {% elif col == 'komodo_gross_charge_provider' %}'komodo_gross_charge_provider'
            {% elif col == 'mrf_gross_charge_state_median' %}'mrf_gross_charge_state_median'
            {% elif col == 'komodo_gross_charge_cbsa_median' %}'komodo_gross_charge_cbsa_median'
            {% elif col == 'komodo_gross_charge_state_median' %}'komodo_gross_charge_state_median'
            {% elif col == 'mrf_gross_charge_cbsa_median' %}'mrf_gross_charge_cbsa_median'
            {% endif %}
    {% endfor %}
    ELSE NULL
END
{% endmacro %}

{% macro gross_charge_source_hierarchy_fallback() %}
CASE
    {% for col in cld_params.GROSS_CHARGE_COLS %}
        WHEN c.{{ col }} IS NOT NULL THEN 
            {% if col == 'mrf_gross_charge_provider' %}'Hospital'
            {% elif col == 'komodo_gross_charge_provider' %}'Claims'
            {% elif col == 'mrf_gross_charge_state_median' %}'Hospital'
            {% elif col == 'komodo_gross_charge_cbsa_median' %}'Claims'
            {% elif col == 'komodo_gross_charge_state_median' %}'Claims'
            {% elif col == 'mrf_gross_charge_cbsa_median' %}'Hospital'
            {% endif %}
    {% endfor %}
    ELSE NULL
END
{% endmacro %}

-- Payer Only Rates and Scores
-- ====================

{% macro payer_only_rate_score_array(rate_cols_no_impute) %}
ARRAY[
    -1,
    {% for c in (rate_cols_no_impute) %}
        {% if c.startswith("payer_") %}
            a.{{ c }}_validation_score
        {% else %}
            -1
        {% endif %}    
        {% if not loop.last %}, {% endif %}
    {% endfor %}
]
{% endmacro %}

{% macro payer_only_rate_type_array(rate_cols_no_impute) %}
ARRAY[
    NULL,
    {% for c in (rate_cols_no_impute) %}
        {% if c.startswith("payer_") %}
            '{{ rate_type_mapping[c] }}'
        {% else %}
            NULL
        {% endif %}    
        {% if not loop.last %}, {% endif %}
    {% endfor %}
]
{% endmacro %}

{% macro payer_only_rate_array(rate_cols_no_impute) %}
ARRAY[
    NULL,
    {% for c in (rate_cols_no_impute) %}
        {% if c.startswith("payer_") %}
            c.{{ c }}
        {% else %}
            NULL
        {% endif %}
        {% if not loop.last %}, {% endif %}
    {% endfor %}
]
{% endmacro %}

-- Hospital Only Rates and Scores
-- ====================

{% macro hospital_only_rate_score_array(rate_cols_no_impute) %}
ARRAY[
    -1,
    {% for c in (rate_cols_no_impute ) %}
        {% if c.startswith("hosp") or c.startswith("drug") %}
            a.{{ c }}_validation_score
        {% else %}
            -1
        {% endif %}
        {% if not loop.last %}, {% endif %}
    {% endfor %}
]
{% endmacro %}

{% macro hospital_only_rate_type_array(rate_cols_no_impute) %}
ARRAY[
    NULL,
    {% for c in (rate_cols_no_impute) %}
        {% if c.startswith("hosp") or c.startswith("drug") %}
            '{{ rate_type_mapping[c] }}'
        {% else %}
            NULL
        {% endif %}    
        {% if not loop.last %}, {% endif %}
    {% endfor %}
]
{% endmacro %}


{% macro hospital_only_rate_array(rate_cols_no_impute) %}
ARRAY[
    NULL,
    {% for c in (rate_cols_no_impute) %}
        {% if c.startswith("hosp") or c.startswith("drug") %}
            c.{{ c }}
        {% else %}
            NULL
        {% endif %}
        {% if not loop.last %}, {% endif %}
    {% endfor %}
]
{% endmacro %}


CREATE TABLE {{ schema_name }}.tmp_int_combined_no_whisp_{{ n_chunk }}_{{ sub_version }} 
WITH (
    PARTITIONING = ARRAY['provider_type', 'payer_id']
)
AS
WITH
pct_of_medicare_exclusions AS (
    SELECT 
        bill_type,
        facility,
        billing_code,
        avg(avg_medicare_rate) * 90 < max(exp(p99)) as exclude_medicare_calc
    FROM {{ cld_params.Tables.RATE_OUTLIER_TABLE.value }}
    GROUP BY 1,2,3
),
combined_with_rate_arrays AS (
    SELECT
        c.*,

        -- ====================
        -- Accuracy columns
        -- ====================
        {% for col in ip_rate_cols %}
            a.{{ col }}_validation_score,
        {% endfor %}

        -- ====================
        -- Outlier bounds
        -- ====================
        {% for col in outlier_bounds %}
            a.{{ col }},
        {% endfor %}

        CASE WHEN c.bill_type = 'Inpatient' 
        THEN {{ rate_score_array(ip_rate_cols) }} ELSE {{ rate_score_array(non_ip_rate_cols) }}
        END AS rate_score_array,

        CASE WHEN c.bill_type = 'Inpatient' 
        THEN {{ rate_score_array(ip_rate_cols_no_impute) }} ELSE {{ rate_score_array(non_ip_rate_cols_no_impute) }}
        END AS rate_score_array_no_impute,

        CASE WHEN c.bill_type = 'Inpatient' 
        THEN {{ rate_array(ip_rate_cols) }} ELSE {{ rate_array(non_ip_rate_cols) }}
        END AS rate_array,

        CASE WHEN c.bill_type = 'Inpatient' 
        THEN {{ percent_of_medicare_array(ip_rate_cols) }} ELSE {{ percent_of_medicare_array(non_ip_rate_cols) }}
        END AS percent_of_medicare_array,

        CASE WHEN c.bill_type = 'Inpatient' 
        THEN {{ percent_of_cbsa_avg_medicare_array(ip_rate_cols) }} ELSE {{ percent_of_cbsa_avg_medicare_array(non_ip_rate_cols) }}
        END AS percent_of_cbsa_avg_medicare_array,

        CASE WHEN c.bill_type = 'Inpatient' 
        THEN {{ percent_of_state_avg_medicare_array(ip_rate_cols) }} ELSE {{ percent_of_state_avg_medicare_array(non_ip_rate_cols) }}
        END AS percent_of_state_avg_medicare_array,

        CASE WHEN c.bill_type = 'Inpatient' 
        THEN {{ percent_of_national_avg_medicare_array(ip_rate_cols) }} ELSE {{ percent_of_national_avg_medicare_array(non_ip_rate_cols) }}
        END AS percent_of_national_avg_medicare_array,

        CASE WHEN c.bill_type = 'Inpatient' 
        THEN {{ percent_of_asc_medicare_array(ip_rate_cols) }} ELSE {{ percent_of_asc_medicare_array(non_ip_rate_cols) }}
        END AS percent_of_asc_medicare_array,

        CASE WHEN c.bill_type = 'Inpatient' 
        THEN {{ percent_of_lab_medicare_array(ip_rate_cols) }} ELSE {{ percent_of_lab_medicare_array(non_ip_rate_cols) }}
        END AS percent_of_lab_medicare_array,

        CASE WHEN c.bill_type = 'Inpatient' 
        THEN {{ percent_of_list_array(ip_rate_cols) }} ELSE {{ percent_of_list_array(non_ip_rate_cols) }}
        END AS percent_of_list_array,

        CASE WHEN c.bill_type = 'Inpatient' 
        THEN {{ source_array(ip_rate_cols) }} ELSE {{ source_array(non_ip_rate_cols) }}
        END AS source_array,

        CASE WHEN c.bill_type = 'Inpatient' 
        THEN {{ rate_type_array(ip_rate_cols) }} ELSE {{ rate_type_array(non_ip_rate_cols) }}
        END AS rate_type_array,

        CASE WHEN c.bill_type = 'Inpatient' 
        THEN {{ methodology_array(ip_rate_cols) }} ELSE {{ methodology_array(non_ip_rate_cols) }}
        END AS methodology_array,

        CASE WHEN c.bill_type = 'Inpatient' 
        THEN {{ rate_class_array(ip_rate_cols) }} ELSE {{ rate_class_array(non_ip_rate_cols) }}
        END AS rate_class_array,

        CASE WHEN c.bill_type = 'Inpatient' 
        THEN {{ imputation_method_array(ip_rate_cols) }} ELSE {{ imputation_method_array(non_ip_rate_cols) }}
        END AS imputation_method_array,

        CASE WHEN c.bill_type = 'Inpatient' 
        THEN {{ transformation_method_array(ip_rate_cols) }} ELSE {{ transformation_method_array(non_ip_rate_cols) }}
        END AS transformation_method_array,

        CASE WHEN c.bill_type = 'Inpatient' 
        THEN {{ gross_charge_type_array(ip_rate_cols) }} ELSE {{ gross_charge_type_array(non_ip_rate_cols) }}
        END AS gross_charge_type_array,

        CASE WHEN c.bill_type = 'Inpatient' 
        THEN {{ gross_charge_source_array(ip_rate_cols) }} ELSE {{ gross_charge_source_array(non_ip_rate_cols) }}
        END AS gross_charge_source_array,

        CASE WHEN c.bill_type = 'Inpatient' 
        THEN {{ rate_id_array(ip_rate_cols_no_impute) }} ELSE {{ rate_id_array(non_ip_rate_cols_no_impute) }}
        END AS rate_id_array,

        CASE WHEN c.bill_type = 'Inpatient' 
        THEN {{ original_billing_codes_array(ip_rate_cols) }} ELSE {{ original_billing_codes_array(non_ip_rate_cols) }}
        END AS original_billing_codes_array,

        CASE WHEN c.bill_type = 'Inpatient' 
        THEN {{ original_billing_code_type_array(ip_rate_cols) }} ELSE {{ original_billing_code_type_array(non_ip_rate_cols) }}
        END AS original_billing_code_type_array,

        -- ====================
        -- Payer Only Rates and Scores
        -- ====================
        
        CASE WHEN c.bill_type = 'Inpatient' 
        THEN {{ payer_only_rate_score_array(ip_rate_cols_no_impute) }} ELSE {{ payer_only_rate_score_array(non_ip_rate_cols_no_impute) }}
        END AS payer_rate_score_array,

        CASE WHEN c.bill_type = 'Inpatient' 
        THEN {{ payer_only_rate_type_array(ip_rate_cols_no_impute) }} ELSE {{ payer_only_rate_type_array(non_ip_rate_cols_no_impute) }}
        END AS payer_rate_type_array,

        CASE WHEN c.bill_type = 'Inpatient' 
        THEN {{ payer_only_rate_array(ip_rate_cols_no_impute) }} ELSE {{ payer_only_rate_array(non_ip_rate_cols_no_impute) }}
        END AS payer_rate_array,

        -- ====================
        -- Hospital Only Rates and Scores
        -- ====================

        CASE WHEN c.bill_type = 'Inpatient' 
        THEN {{ hospital_only_rate_score_array(ip_rate_cols_no_impute) }} ELSE {{ hospital_only_rate_score_array(non_ip_rate_cols_no_impute) }}
        END AS hospital_rate_score_array,

        CASE WHEN c.bill_type = 'Inpatient' 
        THEN {{ hospital_only_rate_type_array(ip_rate_cols_no_impute) }} ELSE {{ hospital_only_rate_type_array(non_ip_rate_cols_no_impute) }}
        END AS hospital_rate_type_array,

        CASE WHEN c.bill_type = 'Inpatient' 
        THEN {{ hospital_only_rate_array(ip_rate_cols_no_impute) }} ELSE {{ hospital_only_rate_array(non_ip_rate_cols_no_impute) }}
        END AS hospital_rate_array

    FROM {{ schema_name }}.tmp_int_combined_brit_{{ sub_version }} c
    LEFT JOIN {{ schema_name }}.tmp_int_accuracy_brit_{{ sub_version }} a
        ON c.roid = a.roid
        AND c.payer_id = a.payer_id
    LEFT JOIN pct_of_medicare_exclusions p
        ON c.billing_code = p.billing_code
        AND c.bill_type = p.bill_type
        AND COALESCE(c.facility, true) = COALESCE(p.facility, true)
)
SELECT 
    c.roid,

    {% for col in payer_spine_cols %}
        payer_spine.{{ col }},
    {% endfor %}
    
    c.network_id,
    {% for col in network_spine_cols %}
        network_spine.{{ col }},
    {% endfor %}
    payer_spine.payer_name || ' ' || network_spine.network_name as payer_network_name,
    
    COALESCE(prov_spine.state, '') || ' ' || 
    COALESCE(prov_spine.cbsa, '') || ' ' || 
    COALESCE(prov_spine.provider_name, '') || ' ' || 
    COALESCE(network_spine.network_name, '') || ' ' || 
    COALESCE(payer_spine.payer_name, '') as generic_search,
    
    c.provider_id,
    {% for col in prov_spine_cols %}
        prov_spine.{{ col }},
    {% endfor %}

    {% for col in code_spine_cols %}
        code_spine.{{ col }},
    {% endfor %}
    
    c.bill_type,
    c.billing_code,
    c.billing_code_type,
    c.facility,

    {% for col in cash_charges %}
        c.{{ col }},
    {% endfor %}
    
    {% for col in gross_charges %}
        c.{{ col }},
    {% endfor %}

    {% for col in outlier_bounds %}
        c.{{ col }},
    {% endfor %}

    {% for col in ip_rate_cols %}
        c.{{ col }},
    {% endfor %}
    {% for col in ip_rate_cols %}
        c.{{ col }}_validation_score,
    {% endfor %}
    {% for col in original_nontransformed_cols %}
        c.{{ col }},
    {% endfor %}

    -- ====================
    -- Canonical rate
    -- ====================

    rate_array[
        ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
    ] as canonical_rate,

    rate_score_array[
        ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
    ] as canonical_rate_validation_score,

    CASE
        {% for vscore, label in cld_params.VALIDATION_MAPPING.items() %}
            WHEN FLOOR(array_max(rate_score_array)) = {{ vscore }} THEN '{{ label }}'
        {% endfor %}
        ELSE NULL
    END as canonical_rate_validation_method,

    CASE
        -- map validation score to confidence score
        {% for vscore, cscore in cld_params.CONFIDENCE_SCORES.items() %}
            WHEN FLOOR(array_max(rate_score_array)) = {{ vscore }} THEN {{ cscore }}
        {% endfor %}
        ELSE 0
    END as canonical_rate_score,

    percent_of_medicare_array[
        ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
    ] as canonical_rate_percent_of_medicare,

    percent_of_cbsa_avg_medicare_array[
        ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
    ] as canonical_rate_percent_of_cbsa_avg_medicare,

    percent_of_state_avg_medicare_array[
        ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
    ] as canonical_rate_percent_of_state_avg_medicare,

    percent_of_national_avg_medicare_array[
        ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
    ] as canonical_rate_percent_of_national_avg_medicare,

    percent_of_asc_medicare_array[
        ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
    ] as canonical_rate_percent_of_asc_medicare,

    percent_of_lab_medicare_array[
        ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
    ] as canonical_rate_percent_of_lab_medicare,

    percent_of_list_array[
        ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
    ] as canonical_rate_percent_of_list,

    source_array[
        ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
    ] as canonical_rate_source,
    
    rate_type_array[
        ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
    ] as canonical_rate_type,

    methodology_array[
        ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
    ] as canonical_contract_methodology,

    rate_class_array[
        ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
    ] as canonical_rate_class,

    imputation_method_array[
        ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
    ] as canonical_imputation_method,

    transformation_method_array[
        ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
    ] as canonical_transformation_method,

    CASE
        WHEN 
            original_billing_code_type_array[
                ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
            ] IS NOT NULL
        THEN
            'Crosswalked From ' || original_billing_code_type_array[
                ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
            ] || ' to ' || c.billing_code_type
    END as canonical_crosswalk_method,

    CASE
        WHEN rate_type_array[
                ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
            ] like '%gc_hosp_cbsa%'
            THEN 'mrf_gross_charge_cbsa_median'
        WHEN rate_type_array[
                ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
            ] like '%gc_hosp_state%'
            THEN 'mrf_gross_charge_state_median'
        WHEN rate_type_array[
                ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
            ] like '%gc_hosp%'
            THEN 'mrf_gross_charge_provider'
        WHEN rate_type_array[
                ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
            ] like '%gc_komodo_cbsa%'
            THEN 'komodo_gross_charge_cbsa_median'
        WHEN rate_type_array[
                ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
            ] like '%gc_komodo_state%'
            THEN 'komodo_gross_charge_state_median'
        WHEN rate_type_array[
                ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
            ] like '%gc_komodo%'
            THEN 'komodo_gross_charge_provider'
        WHEN 
            rate_type_array[
                ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
            ] like '%drug_dosage_%'
            THEN 'mrf_gross_charge_provider'
        ELSE {{ gross_charge_type_hierarchy_fallback() }}
    END as canonical_gross_charge_type,

    CASE
        WHEN rate_type_array[
                ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
            ] like '%gc_hosp_cbsa%'
            THEN 'Hospital'
        WHEN rate_type_array[
                ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
            ] like '%gc_hosp_state%'
            THEN 'Hospital'
        WHEN rate_type_array[
                ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
            ] like '%gc_hosp%'
            THEN 'Hospital'
        WHEN rate_type_array[
                ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
            ] like '%gc_komodo_cbsa%'
            THEN 'Claims'
        WHEN rate_type_array[
                ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
            ] like '%gc_komodo_state%'
            THEN 'Claims'
        WHEN rate_type_array[
                ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
            ] like '%gc_komodo%'
            THEN 'Claims'
        WHEN 
            rate_type_array[
                ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
            ] like '%drug_dosage_%'
            THEN 'Hospital'
        ELSE {{ gross_charge_source_hierarchy_fallback() }}
    END as canonical_gross_charge_source,

    rate_categories.canonical_rate_category,

    CASE
        {% for k,v in traceability_params.items() %}
            WHEN rate_type_array[
                ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
            ] LIKE '%{{ k }}%' 
            {% if v %}THEN {{ v }}{% else %}THEN NULL{% endif %}
        {% endfor %}
    END as canonical_method_params,

    CASE
        WHEN rate_type_array[
                ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
            ] like '%gc_hosp_cbsa%'
            THEN mrf_gross_charge_cbsa_median
        WHEN rate_type_array[
                ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
            ] like '%gc_hosp_state%'
            THEN mrf_gross_charge_state_median
        WHEN rate_type_array[
                ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
            ] like '%gc_hosp%'
            THEN mrf_gross_charge_provider
        WHEN rate_type_array[
                ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
            ] like '%gc_komodo_cbsa%'
            THEN komodo_gross_charge_cbsa_median
        WHEN rate_type_array[
                ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
            ] like '%gc_komodo_state%'
            THEN komodo_gross_charge_state_median
        WHEN rate_type_array[
                ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
            ] like '%gc_komodo%'
            THEN komodo_gross_charge_provider
        WHEN rate_type_array[
                ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
            ] like '%drug_dosage_%'
            THEN mrf_gross_charge_provider
        ELSE {{ gross_charge_hierarchy_fallback() }}
    END as canonical_gross_charge,

    CASE
        WHEN rate_type_array[
                ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
            ] like '%gc_hosp%'
            THEN mrf_gross_charge_provider_original_billing_code
        ELSE NULL
    END as canonical_rate_gross_charge_original_billing_codes,

    CASE
        WHEN rate_type_array[
                ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
            ] like '%gc_hosp%'
            THEN mrf_gross_charge_provider_original_billing_code_type
        ELSE NULL
    END as canonical_rate_gross_charge_original_billing_code_type,

    rate_id_array[
        ARRAY_POSITION(rate_score_array_no_impute, array_max(rate_score_array_no_impute))
    ] as canonical_rate_id,
        
    original_billing_codes_array[
        ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
    ] as canonical_rate_original_billing_codes,

    original_billing_code_type_array[
        ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
    ] as canonical_rate_original_billing_code_type,

    -- ====================
    -- Best payer and hospital rates
    -- ====================

    payer_rate_array[
        ARRAY_POSITION(payer_rate_score_array, array_max(payer_rate_score_array))
    ] as best_payer_rate,

    payer_rate_type_array[
        ARRAY_POSITION(payer_rate_score_array, array_max(payer_rate_score_array))
    ] as best_payer_rate_type,

    payer_rate_score_array[
        ARRAY_POSITION(payer_rate_score_array, array_max(payer_rate_score_array))
    ] as best_payer_rate_validation_score,

    CASE
        {% for vscore, cscore in cld_params.CONFIDENCE_SCORES.items() %}
            WHEN FLOOR(array_max(payer_rate_score_array)) = {{ vscore }} THEN {{ cscore }}
        {% endfor %}
        ELSE 0
    END as best_payer_rate_score,

    hospital_rate_array[
        ARRAY_POSITION(hospital_rate_score_array, array_max(hospital_rate_score_array))
    ] as best_hospital_rate,

    hospital_rate_type_array[
        ARRAY_POSITION(hospital_rate_score_array, array_max(hospital_rate_score_array))
    ] as best_hospital_rate_type,

    hospital_rate_score_array[
        ARRAY_POSITION(hospital_rate_score_array, array_max(hospital_rate_score_array))
    ] as best_hospital_rate_validation_score,

    CASE
        {% for vscore, cscore in cld_params.CONFIDENCE_SCORES.items() %}
            WHEN FLOOR(array_max(hospital_rate_score_array)) = {{ vscore }} THEN {{ cscore }}
        {% endfor %}
        ELSE 0
    END as best_hospital_rate_score,

    -- ====================
    -- Imputation stats columns
    -- ====================
    {% for col in allowed_amount_stats %}
        c.{{ col }},
    {% endfor %}
    
    {% for col in imputation_stats %}
        c.{{ col }},
    {% endfor %}


    -- ====================
    -- Benchmark columns
    -- ====================
    {% for col in benchmark_cols %}
        c.{{ col }},
    {% endfor %}

    -- ====================
    -- Transformation utils columns
    -- ====================
    {% for col in transformation_utils %}
        c.{{ col }},
    {% endfor %}

    c.payer_id
    
FROM combined_with_rate_arrays c
LEFT JOIN {{ schema_name }}.tmp_ref_provider_spine_{{ sub_version }} prov_spine
    ON c.provider_id = prov_spine.provider_id
LEFT JOIN {{ schema_name }}.tmp_ref_payer_spine_{{ sub_version }} payer_spine
    ON c.payer_id = payer_spine.payer_id
LEFT JOIN {{ schema_name }}.tmp_ref_network_spine_{{ sub_version }} network_spine
    ON c.network_id = network_spine.network_id
LEFT JOIN {{ schema_name }}.tmp_ref_code_spine_{{ sub_version }} code_spine
    ON c.bill_type = code_spine.bill_type
    AND c.billing_code = code_spine.billing_code
    AND c.billing_code_type = code_spine.billing_code_type
    AND (
        c.facility = code_spine.facility
        OR (c.facility IS NULL AND code_spine.facility IS NULL)
    )
LEFT JOIN {{ cld_params.Tables.RATE_TYPE_CATEGORIES.value }} rate_categories
    ON rate_type_array[
        ARRAY_POSITION(rate_score_array, array_max(rate_score_array))
    ] = rate_categories.canonical_rate_type
WHERE
    c.payer_id IN ({{ payer_ids }})

All w/ Whispers

-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_int_combined_{{ n_chunk }}_{{ sub_version }} 
-- Subversion: {{ sub_version }}
-- Payer IDs: {{ payer_ids }}
-- n_chunk: {{ n_chunk }}

-- WHISPERS

-- settings: dags/core_licensable_data_sub_dag/utils/params.py
{% set payer_whispers = cld_params.PAYER_WHISPERS %}
{% set network_whispers = cld_params.NETWORK_WHISPERS %}
{% set provider_whispers = cld_params.PROVIDER_WHISPERS %}
{% set code_whispers = cld_params.CODE_WHISPERS %}
{% set provider_code_whispers = cld_params.PROVIDER_CODE_WHISPERS %}
{% set provider_payer_whispers = cld_params.PROVIDER_PAYER_WHISPERS %}
{% set provider_payer_network_whispers = cld_params.PROVIDER_PAYER_NETWORK_WHISPERS %}
{% set payer_code_whispers = cld_params.PAYER_CODE_WHISPERS %}

-- UTILIZATIONS
{% set utilization_cols = cld_params.UTILIZATION_COLS %}

CREATE TABLE {{ schema_name }}.tmp_int_combined_{{ n_chunk }}_{{ sub_version }} 
WITH (
    PARTITIONING = ARRAY['payer_id', 'provider_type', 'bill_type']
)
AS
SELECT 
    c.*,

    -- ====================
    -- Whispers
    -- ====================
    {% for col in payer_whispers %}
        whisper_payer.{{ col }},
    {% endfor %}
    {% for col in network_whispers %}
        whisper_network.{{ col }},
    {% endfor %}
    {% for col in provider_whispers %}
        whisper_provider.{{ col }},
    {% endfor %}
    {% for col in code_whispers %}
        whisper_code.{{ col }},
    {% endfor %}
    {% for col in provider_code_whispers %}
        whisper_prov_code.{{ col }},
    {% endfor %}
    {% for col in payer_code_whispers %}
        whisper_payer_code.{{ col }},
    {% endfor %}
    {% for col in provider_payer_whispers %}
        whisper_prov_payer.{{ col }},
    {% endfor %}
    {% for col in provider_payer_network_whispers %}
        whisper_prov_payer_network.{{ col }},
    {% endfor %}

    -- ====================
    -- Utilization columns
    -- ====================
    {% for col in utilization_cols %}
        utilization.{{ col }}{% if not loop.last %}, {% endif %}
    {% endfor %}

FROM {{ schema_name }}.tmp_int_combined_no_whisp_{{ sub_version }} c
LEFT JOIN {{ schema_name }}.tmp_whisper_payer_{{ sub_version }} whisper_payer
    ON c.payer_id = whisper_payer.payer_id
    AND c.cbsa = whisper_payer.cbsa
    AND c.state = whisper_payer.state
LEFT JOIN {{ schema_name }}.tmp_whisper_network_{{ sub_version }} whisper_network
    ON c.payer_id = whisper_network.payer_id
    AND c.network_id = whisper_network.network_id
    AND c.state = whisper_network.network_state
LEFT JOIN {{ schema_name }}.tmp_whisper_provider_{{ sub_version }} whisper_provider
    ON c.provider_id = whisper_provider.provider_id
LEFT JOIN {{ schema_name }}.tmp_whisper_code_{{ sub_version }} whisper_code
    ON c.bill_type = whisper_code.bill_type
    AND c.billing_code = whisper_code.billing_code
    AND c.billing_code_type = whisper_code.billing_code_type
    AND c.taxonomy_grouping = whisper_code.taxonomy_grouping
LEFT JOIN {{ schema_name }}.tmp_whisper_provider_code_{{ sub_version }} whisper_prov_code
    ON c.provider_id = whisper_prov_code.provider_id
    AND c.bill_type = whisper_prov_code.bill_type
    AND c.billing_code = whisper_prov_code.billing_code
    AND c.billing_code_type = whisper_prov_code.billing_code_type
LEFT JOIN {{ schema_name }}.tmp_whisper_payer_code_{{ sub_version }} whisper_payer_code
    ON c.payer_id = whisper_payer_code.payer_id
    AND c.bill_type = whisper_payer_code.bill_type
    AND c.billing_code = whisper_payer_code.billing_code
    AND c.billing_code_type = whisper_payer_code.billing_code_type
    AND c.taxonomy_grouping = whisper_payer_code.taxonomy_grouping
LEFT JOIN {{ schema_name }}.tmp_whisper_provider_payer_{{ sub_version }} whisper_prov_payer
    ON c.payer_id = whisper_prov_payer.payer_id
    AND c.provider_id = whisper_prov_payer.provider_id
LEFT JOIN {{ schema_name }}.tmp_whisper_provider_payer_network_{{ sub_version }} whisper_prov_payer_network
    ON c.provider_id = whisper_prov_payer_network.provider_id
    AND c.payer_id = whisper_prov_payer_network.payer_id
    AND c.network_id = whisper_prov_payer_network.network_id
LEFT JOIN {{ schema_name }}.tmp_ref_utilizations_{{ sub_version }} utilization
    ON c.roid = utilization.roid
    AND c.payer_id = utilization.payer_id
WHERE 
    c.payer_id IN ({{ payer_ids }})