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 IDs: {{ payer_ids }}
-- 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
        WHERE r.payer_id IN ({{ payer_ids }})
    )
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 rates 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

accuracy_brit

-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_int_accuracy_brit_{{ sub_version }} 
-- Subversion: {{ sub_version }}


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

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

{% set imputation_cols = cld_params.IMPUTATION_COLS_NO_POSTED %}
{% set posted_imputation_cols = cld_params.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
------------------------
{% set scores = cld_params.SCORES %}
{% set imputation_scores = cld_params.IMPUTATION_SCORES %}


CREATE TABLE {{ schema_name }}.tmp_int_accuracy_brit_{{ sub_version }} 
WITH (
    PARTITIONING = ARRAY['payer_id']
)
AS
WITH
combined_rates AS (
    SELECT 
        *,
        -- PAYER raw and transformed columns array
        ARRAY[
            {% for col in payer_raw_cols %}
                r.{{ col }},
            {% endfor %}
            {% for col in payer_transformation_cols %}
                r.{{ 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 %}
                r.{{ col }}{% if not loop.last %}, {% endif %}
            {% endfor %}
        ] as hospital_rates_array
    FROM {{ schema_name }}.tmp_int_combined_brit_{{ sub_version }} r
)
SELECT 
    r.roid,
    r.network_id,
    r.provider_id,
    r.bill_type,
    r.billing_code,
    r.billing_code_type,
    r.provider_type,
    r.facility,

    {% 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_columns %}
        {{ accuracy_score(
            col, 
            hospital_columns, 
            scores, 
            True, 
            'r', 
            column_dict
        ) }}
    {% endfor %}
    
    -- =====================================
    -- HOSPITAL columns + their accuracy scores
    -- =====================================
    {% for col in hospital_columns %}
        {{ accuracy_score(
            col, 
            payer_columns, 
            scores, 
            True, 
            'r', 
            column_dict
        ) }}
    {% endfor %}

    -- =====================================
    -- IMPUTATION columns + their accuracy scores
    -- =====================================
    {% for col in posted_imputation_cols %}
        {{ accuracy_score(
            col, 
            [], 
            scores, 
            False, 
            'r', 
            column_dict
        ) }}
    {% endfor %}
    
    {% for col in imputation_cols %}
        {{ accuracy_score(
            col, 
            [], 
            imputation_scores, 
            False, 
            'r', 
            column_dict
        ) }}
    {% endfor %}

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

    r.payer_id
    
FROM combined_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