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 OR REPLACE 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,
            r.is_lab_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,
            'medical'
        ) }}
    {% 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,
            'medical'
        ) }}
    {% 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,
            'medical'
        ) }}
    {% 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,
            'medical'
        ) }}
    {% 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

accuracy_brit

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

CREATE OR REPLACE TABLE {{ schema_name }}.tmp_int_accuracy_brit_{{ sub_version }}
WITH (
    PARTITIONING = ARRAY['payer_id']
)
AS
SELECT * FROM {{ schema_name }}.tmp_int_accuracy_brit_drugs_{{ sub_version }}
UNION ALL
SELECT * FROM {{ schema_name }}.tmp_int_accuracy_brit_labs_{{ sub_version }}
UNION ALL
SELECT * FROM {{ schema_name }}.tmp_int_accuracy_brit_medical_{{ sub_version }}
UNION ALL
SELECT * FROM {{ schema_name }}.tmp_int_accuracy_brit_physician_groups_{{ sub_version }}

accuracy_brit Components

Drugs

-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_int_accuracy_brit_drugs_{{ 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 OR REPLACE TABLE {{ schema_name }}.tmp_int_accuracy_brit_drugs_{{ sub_version }}
WITH (
    PARTITIONING = ARRAY['payer_id']
)
AS
WITH
combined_rates AS (
    SELECT
        r.*,
        -- 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,

        -- Pre-compute normal_cdf_of_range for all rate columns
        {% for col in payer_columns %}
            {{ normal_cdf_of_range(col) }} AS {{ col }}_cdf,
        {% endfor %}
        {% for col in hospital_columns %}
            {{ normal_cdf_of_range(col) }} AS {{ col }}_cdf,
        {% endfor %}
        {% for col in posted_imputation_cols %}
            {{ normal_cdf_of_range(col) }} AS {{ col }}_cdf,
        {% endfor %}
        {% for col in imputation_cols %}
            {{ normal_cdf_of_range(col) }} AS {{ col }}_cdf{% if not loop.last %},{% endif %}
        {% endfor %}
    FROM {{ schema_name }}.tmp_int_combined_brit_{{ sub_version }} 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
    WHERE r.is_drug_code = True
)
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,
            'drug'
        ) }}
    {% endfor %}

    -- =====================================
    -- HOSPITAL columns + their accuracy scores
    -- =====================================
    {% for col in hospital_columns %}
        {{ accuracy_score(
            col,
            payer_columns,
            scores,
            True,
            'r',
            column_dict,
            'drug'
        ) }}
    {% endfor %}

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

    {% for col in imputation_cols %}
        {{ accuracy_score(
            col,
            [],
            imputation_scores,
            False,
            'r',
            column_dict,
            'drug'
        ) }}
    {% 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

Labs

-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_int_accuracy_brit_labs_{{ 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 OR REPLACE TABLE {{ schema_name }}.tmp_int_accuracy_brit_labs_{{ sub_version }}
WITH (
    PARTITIONING = ARRAY['payer_id']
)
AS
WITH
combined_rates AS (
    SELECT
        r.*,
        -- 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,

        -- Pre-compute normal_cdf_of_range for all rate columns
        {% for col in payer_columns %}
            {{ normal_cdf_of_range(col) }} AS {{ col }}_cdf,
        {% endfor %}
        {% for col in hospital_columns %}
            {{ normal_cdf_of_range(col) }} AS {{ col }}_cdf,
        {% endfor %}
        {% for col in posted_imputation_cols %}
            {{ normal_cdf_of_range(col) }} AS {{ col }}_cdf,
        {% endfor %}
        {% for col in imputation_cols %}
            {{ normal_cdf_of_range(col) }} AS {{ col }}_cdf{% if not loop.last %},{% endif %}
        {% endfor %}
    FROM {{ schema_name }}.tmp_int_combined_brit_{{ sub_version }} 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
    WHERE r.is_lab_code = True
    AND r.is_drug_code = False
),
lab_network_rate_distributions AS (
    SELECT 
        provider_id,
        network_id,
        (
            APPROX_PERCENTILE(i.rate / b.medicare_rate, 0.95) - 
            APPROX_PERCENTILE(i.rate / b.medicare_rate, 0.05)
        ) < 0.25 as has_consistent_pct_of_medicare_rate
    FROM {{ schema_name }}.tmp_int_imputations_long_rates_{{ sub_version }} i
    LEFT JOIN {{ schema_name }}.tmp_int_benchmarks_{{ sub_version }} b
        ON i.roid = b.roid
        AND i.payer_id = b.payer_id
    WHERE 
        i.provider_type = 'Laboratory'
    GROUP BY 1,2
    HAVING COUNT(*) >= 30
)
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,
            [],
            scores,
            True,
            'r',
            column_dict,
            'lab'
        ) }}
    {% endfor %}

    -- =====================================
    -- HOSPITAL columns + their accuracy scores
    -- =====================================
    {% for col in hospital_columns %}
        {{ accuracy_score(
            col,
            [],
            scores,
            True,
            'r',
            column_dict,
            'lab'
        ) }}
    {% endfor %}

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

    {% for col in imputation_cols %}
        {{ accuracy_score(
            col,
            [],
            scores,
            True,
            'r',
            column_dict,
            'lab'
        ) }}
    {% 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
LEFT JOIN lab_network_rate_distributions lnd
    ON r.provider_id = lnd.provider_id
    AND r.network_id = lnd.network_id

Medical

-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_int_accuracy_brit_medical_{{ 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 OR REPLACE TABLE {{ schema_name }}.tmp_int_accuracy_brit_medical_{{ sub_version }}
WITH (
    PARTITIONING = ARRAY['payer_id']
)
AS
WITH
combined_rates AS (
    SELECT
        r.*,
        -- 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,

        -- Pre-compute normal_cdf_of_range for all rate columns
        {% for col in payer_columns %}
            {{ normal_cdf_of_range(col) }} AS {{ col }}_cdf,
        {% endfor %}
        {% for col in hospital_columns %}
            {{ normal_cdf_of_range(col) }} AS {{ col }}_cdf,
        {% endfor %}
        {% for col in posted_imputation_cols %}
            {{ normal_cdf_of_range(col) }} AS {{ col }}_cdf,
        {% endfor %}
        {% for col in imputation_cols %}
            {{ normal_cdf_of_range(col) }} AS {{ col }}_cdf{% if not loop.last %},{% endif %}
        {% endfor %}
    FROM {{ schema_name }}.tmp_int_combined_brit_{{ sub_version }} 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
    WHERE r.is_drug_code = False
    AND r.is_lab_code = False
    AND r.provider_type NOT IN ('Physician Group')
)
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,
            'medical'
        ) }}
    {% endfor %}

    -- =====================================
    -- HOSPITAL columns + their accuracy scores
    -- =====================================
    {% for col in hospital_columns %}
        {{ accuracy_score(
            col,
            payer_columns,
            scores,
            True,
            'r',
            column_dict,
            'medical'
        ) }}
    {% endfor %}

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

    {% for col in imputation_cols %}
        {{ accuracy_score(
            col,
            [],
            imputation_scores,
            False,
            'r',
            column_dict,
            'medical'
        ) }}
    {% 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

Physician Groups

-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_int_accuracy_brit_physician_groups_{{ 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 OR REPLACE TABLE {{ schema_name }}.tmp_int_accuracy_brit_physician_groups_{{ sub_version }}
WITH (
    PARTITIONING = ARRAY['payer_id']
)
AS
WITH
combined_rates AS (
    SELECT
        r.*,
        -- 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,

        -- Pre-compute normal_cdf_of_range for all rate columns
        {% for col in payer_columns %}
            {{ normal_cdf_of_range(col) }} AS {{ col }}_cdf,
        {% endfor %}
        {% for col in hospital_columns %}
            {{ normal_cdf_of_range(col) }} AS {{ col }}_cdf,
        {% endfor %}
        {% for col in posted_imputation_cols %}
            {{ normal_cdf_of_range(col) }} AS {{ col }}_cdf,
        {% endfor %}
        {% for col in imputation_cols %}
            {{ normal_cdf_of_range(col) }} AS {{ col }}_cdf{% if not loop.last %},{% endif %}
        {% endfor %}
    FROM {{ schema_name }}.tmp_int_combined_brit_{{ sub_version }} 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
    WHERE r.provider_type = 'Physician Group'
    AND r.is_drug_code = False
    AND r.is_lab_code = False
),
physician_group_network_rate_distributions AS (
    SELECT
        provider_id,
        network_id,
        (
            APPROX_PERCENTILE(i.rate / b.medicare_rate, 0.95) -
            APPROX_PERCENTILE(i.rate / b.medicare_rate, 0.05)
        ) < 0.25 as has_consistent_pct_of_medicare_rate
    FROM {{ schema_name }}.tmp_int_imputations_long_rates_{{ sub_version }} i
    LEFT JOIN {{ schema_name }}.tmp_int_benchmarks_{{ sub_version }} b
        ON i.roid = b.roid
        AND i.payer_id = b.payer_id
    WHERE
        i.provider_type = 'Physician Group'
    GROUP BY 1,2
    HAVING COUNT(*) >= 30
)
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,
            [],
            scores,
            True,
            'r',
            column_dict,
            'physician_group'
        ) }}
    {% endfor %}

    -- =====================================
    -- HOSPITAL columns + their accuracy scores
    -- =====================================
    {% for col in hospital_columns %}
        {{ accuracy_score(
            col,
            [],
            scores,
            True,
            'r',
            column_dict,
            'physician_group'
        ) }}
    {% endfor %}

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

    {% for col in imputation_cols %}
        {{ accuracy_score(
            col,
            [],
            scores,
            True,
            'r',
            column_dict,
            'physician_group'
        ) }}
    {% 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
LEFT JOIN physician_group_network_rate_distributions pgnd
    ON r.provider_id = pgnd.provider_id
    AND r.network_id = pgnd.network_id

accuracy_raw_union

CREATE OR REPLACE TABLE {{ schema_name }}.tmp_int_accuracy_raw_{{ sub_version }} 
WITH (
    PARTITIONING = ARRAY['payer_id']
)
AS
{% for n_chunk in n_chunks %}
SELECT * FROM {{ schema_name }}.tmp_int_accuracy_raw_{{ n_chunk }}_{{ sub_version }}
{% if not loop.last %}UNION ALL{% endif %}
{% endfor %}