Accuracy

ComponentSchema

---

accuracy_raw

-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_int_accuracy_raw_{{ n_chunk }}_{{ sub_version }}
-- Subversion: {{ sub_version }}
-- Payer Network List: {{ payer_network_list }}
-- N Chunk: {{ n_chunk }}

-- 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 posted_imputation_cols = [] %}

-- Column dictionary for accuracy_score()
{% set column_dict = {
    'hospital_raw_cols': hospital_raw_cols,
    'payer_raw_cols': payer_raw_cols,
    'posted_imputation_cols': posted_imputation_cols,
    'hospital_transformation_cols': hospital_transformation_cols,
    'payer_transformation_cols': payer_transformation_cols
} %}

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

-- CHARGES
{% set gross_charges = cld_params.GROSS_CHARGE_COLS %}

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

{% from 'utils/accuracy_macros.sql' import outlier_bounds, normal_cdf_of_range, accuracy_score %}

------------------------
-- ACCURACY SCORES
------------------------


CREATE TABLE {{ schema_name }}.tmp_int_accuracy_raw_{{ n_chunk }}_{{ sub_version }} 
WITH (
    PARTITIONING = ARRAY['payer_id', 'provider_type']
)
AS
WITH 
    rates AS (
        SELECT 
            r.roid,
            r.network_id,
            r.provider_id,
            r.payer_id,
            r.bill_type,
            r.facility,
            r.billing_code,
            r.billing_code_type,
            r.provider_type,
            r.is_surg_code,
            r.is_drug_code,
            -- PAYER raw and transformed columns array
            ARRAY[
                {% for col in payer_raw_cols %}
                    r.{{ col }},
                {% endfor %}
                {% for col in payer_transformation_cols %}
                    t.{{ col }}{% if not loop.last %}, {% endif %}
                {% endfor %}
            ] as payer_rates_array,
            -- HOSPITAL raw and transformed columns array
            ARRAY[
                {% for col in hospital_raw_cols %}
                    r.{{ col }},
                {% endfor %}
                {% for col in hospital_transformation_cols %}
                    t.{{ col }}{% if not loop.last %}, {% endif %}
                {% endfor %}
            ] as hospital_rates_array,
            {% for col in gross_charges %}
                r.{{ col }},
            {% endfor %}
            {% for col in hospital_raw_cols %}
                r.{{ col }},
            {% endfor %}
            {% for col in payer_raw_cols %}
                r.{{ col }},
            {% endfor %}
            {% for col in payer_transformation_cols %}
                t.{{ col }},
            {% endfor %}
            {% for col in hospital_transformation_cols %}
                t.{{ col }}
                {% if not loop.last %}, {% endif %}
            {% endfor %}
        FROM {{ schema_name }}.tmp_int_combined_raw_{{ sub_version }} r
        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_ref_network_spine_{{ sub_version }} n
            ON r.network_id = n.network_id
        WHERE (
            {% for item in payer_network_list %}
                (r.payer_id = '{{ item.payer_id }}'
                {% if item.payer_id == '643' and item.network_type %}
                    AND n.network_type = '{{ item.network_type }}'
                {% endif %}
                )
                {% if not loop.last %} OR {% endif %}
            {% endfor %}
        )
    ),
    with_cdf AS (
        SELECT
            r.*,
            -- Pre-compute normal_cdf_of_range for all rate columns
            {% for col in payer_raw_cols %}
                {{ normal_cdf_of_range(col) }} AS {{ col }}_cdf,
            {% endfor %}
            {% for col in hospital_raw_cols %}
                {{ normal_cdf_of_range(col) }} AS {{ col }}_cdf,
            {% endfor %}
            {% for col in payer_transformation_cols %}
                {{ normal_cdf_of_range(col) }} AS {{ col }}_cdf,
            {% endfor %}
            {% for col in hospital_transformation_cols %}
                {{ normal_cdf_of_range(col) }} AS {{ col }}_cdf{% if not loop.last %},{% endif %}
            {% endfor %}
        FROM rates r
        LEFT JOIN {{ cld_params.Tables.RATE_OUTLIER_TABLE.value }} ob
            ON r.provider_id = ob.provider_id
            AND r.bill_type = ob.bill_type
            AND COALESCE(r.facility, true) = COALESCE(ob.facility, true)
            AND r.billing_code_type = ob.billing_code_type
            AND r.billing_code = ob.billing_code
    )
SELECT 
    r.roid,
    r.network_id,
    r.provider_id,
    r.bill_type,
    r.billing_code,
    r.billing_code_type,
    r.provider_type,
    
    {% for col in gross_charges %}
        r.{{ col }},
    {% endfor %}

    {% for col in outlier_bounds_cols %}
        ob.{{ col }},
    {% endfor %}

    -- PAYER columns + their accuracy scores
    {% for col in payer_raw_cols %}
        {{ accuracy_score(
            col, 
            hospital_raw_cols + hospital_transformation_cols, 
            scores, 
            True, 
            'b', 
            column_dict
        ) }}
    {% endfor %}

    -- HOSPITAL columns + their accuracy scores
    {% for col in hospital_raw_cols %}
        {{ accuracy_score(
            col, 
            payer_raw_cols + payer_transformation_cols, 
            scores, 
            True, 
            'b', 
            column_dict
        ) }}
    {% endfor %}

    -- TRANSFORMATION: payer columns + their accuracy scores
    {% for col in payer_transformation_cols %}
        {{ accuracy_score(
            col, 
            hospital_raw_cols + hospital_transformation_cols, 
            scores, 
            True, 
            'b', 
            column_dict
        ) }}
    {% endfor %}

    -- TRANSFORMATION: hospital columns + their accuracy scores
    {% for col in hospital_transformation_cols %}
        {{ accuracy_score(
            col, 
            payer_raw_cols + payer_transformation_cols, 
            scores, 
            True, 
            'b', 
            column_dict
        ) }}
    {% endfor %}

    -- BENCHMARKS
    {% for col in benchmark_cols %}
        b.{{ col }}{{ ', ' if not loop.last }}
    {% endfor %},

    r.payer_id

FROM with_cdf r
LEFT JOIN {{ schema_name }}.tmp_int_benchmarks_{{ sub_version }} b
    ON r.roid = b.roid
    AND r.payer_id = b.payer_id
LEFT JOIN {{ cld_params.Tables.RATE_OUTLIER_TABLE.value }} ob
    ON r.provider_id = ob.provider_id
    AND r.bill_type = ob.bill_type
    AND COALESCE(r.facility, true) = COALESCE(ob.facility, true)
    AND r.billing_code_type = ob.billing_code_type
    AND r.billing_code = ob.billing_code