Utilizations

ComponentSchema

---

SQL

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


CREATE TABLE {{ schema_name }}.tmp_ref_utilizations_{{ sub_version }}
WITH (
    PARTITIONING = ARRAY['payer_id']
)
AS
WITH
npi_code_utilization AS (
    SELECT 
        provider_id,
        billing_code,
        billing_code_type,
        case
            when claim_type_code = 'institutional'
                and setting = 'Inpatient'
                then 'Inpatient'
            when claim_type_code = 'institutional'
                and setting = 'Outpatient'
                then 'Outpatient'
        end as bill_type,
        sum(count_encounters) as count_encounters
    FROM {{ cld_params.Tables.UTILIZATION_NPI.value }}
    WHERE claim_type_code IN ('institutional')
        AND npi_source = 'hco'
        AND service_year = 2023
    GROUP BY 1, 2, 3, 4
),
komodo_state_commercial_code_taxonomy_utilization AS (
    SELECT
        state,
        taxonomy_grouping,
        billing_code,
        billing_code_type,
        case
            when claim_type_code = 'institutional'
                and setting = 'Inpatient'
                then 'Inpatient'
            when claim_type_code = 'institutional'
                and setting = 'Outpatient'
                then 'Outpatient'
        end as bill_type,
        sum(median_count_encounters) as count_encounters
    FROM {{ cld_params.Tables.UTILIZATION_STATE_PAYERCHANNEL.value }}
    WHERE claim_type_code IN ('institutional')
        AND npi_source = 'hco'
        AND payer_channel = 'Commercial'
        AND service_year = 2023
    GROUP BY 1, 2, 3, 4, 5
),
komodo_national_commercial_code_taxonomy_utilization AS (
    SELECT
        taxonomy_grouping,
        billing_code,
        billing_code_type,
        case
            when claim_type_code = 'institutional'
                and setting = 'Inpatient'
                then 'Inpatient'
            when claim_type_code = 'institutional'
                and setting = 'Outpatient'
                then 'Outpatient'
        end as bill_type,
        sum(median_count_encounters) as count_encounters
    FROM {{ cld_params.Tables.UTILIZATION_NATIONAL_PAYERCHANNEL.value }}
    WHERE claim_type_code IN ('institutional')
        AND npi_source = 'hco'
        AND payer_channel = 'Commercial'
        AND service_year = 2023
    GROUP BY 1, 2, 3, 4
),
komodo_national_payer_code_taxonomy_utilization AS (
    SELECT
        CAST(tq_payer_id AS VARCHAR) as payer_id,
        taxonomy_grouping,
        billing_code,
        billing_code_type,
        case
            when claim_type_code = 'institutional'
                and setting = 'Inpatient'
                then 'Inpatient'
            when claim_type_code = 'institutional'
                and setting = 'Outpatient'
                then 'Outpatient'
        end as bill_type,
        sum(median_count_encounters) as count_encounters
    FROM {{ cld_params.Tables.UTILIZATION_NATIONAL_PAYER.value }}
    WHERE claim_type_code IN ('institutional')
        AND npi_source = 'hco'
        AND payer_channel = 'Commercial'
        AND service_year = 2023
    GROUP BY 1, 2, 3, 4, 5
),
clear_market_view AS (
    SELECT
        payer_id,
        state,
        provider_id,
        billing_code,
        billing_code_type,
        utilization_imputed_commercial,
        utilization_imputed_medicare,
        utilization_imputed_medicaid
    FROM {{ cld_params.Tables.DPR_IMPUTED_UTILIZATIONS.value }}
)
SELECT
    ros.roid,
    ros.network_id,
    ros.provider_id,
    ps.state,
    ros.taxonomy_grouping,
    ros.bill_type,
    ros.billing_code,
    ros.billing_code_type,
    ncu.count_encounters as npi_commercial_code_encounters,
    ksc.count_encounters as state_commercial_code_taxonomy_encounters,  
    ku.count_encounters as national_commercial_code_taxonomy_encounters,
    kpc.count_encounters as national_payer_code_taxonomy_encounters,
    cmv.utilization_imputed_commercial as clear_market_utilization_commercial,
    cmv.utilization_imputed_medicare as clear_market_utilization_medicare,
    cmv.utilization_imputed_medicaid as clear_market_utilization_medicaid,
    ros.payer_id
FROM {{ schema_name }}.tmp_rate_object_space_{{ sub_version }} ros
LEFT JOIN {{ schema_name }}.tmp_ref_provider_spine_{{ sub_version }} ps
    ON ros.provider_id = ps.provider_id
LEFT JOIN npi_code_utilization ncu
    ON CAST(ncu.provider_id AS VARCHAR) = ros.provider_id
    AND ncu.bill_type = ros.bill_type
    AND ncu.billing_code = ros.billing_code
    AND ncu.billing_code_type = ros.billing_code_type
LEFT JOIN komodo_state_commercial_code_taxonomy_utilization ksc
    ON ksc.state = ps.state
    AND ksc.bill_type = ros.bill_type
    AND ksc.billing_code = ros.billing_code
    AND ksc.billing_code_type = ros.billing_code_type
    AND ksc.taxonomy_grouping = ros.taxonomy_grouping
LEFT JOIN komodo_national_commercial_code_taxonomy_utilization ku
    ON ku.bill_type = ros.bill_type
    AND ku.billing_code = ros.billing_code
    AND ku.billing_code_type = ros.billing_code_type
    AND ku.taxonomy_grouping = ros.taxonomy_grouping
LEFT JOIN komodo_national_payer_code_taxonomy_utilization kpc
    ON kpc.payer_id = ros.payer_id
    AND kpc.bill_type = ros.bill_type
    AND kpc.billing_code = ros.billing_code
    AND kpc.billing_code_type = ros.billing_code_type
    AND kpc.taxonomy_grouping = ros.taxonomy_grouping
LEFT JOIN clear_market_view cmv
    ON cmv.payer_id = ros.payer_id
    AND cmv.provider_id = ros.provider_id
    AND cmv.billing_code = ros.billing_code
    AND cmv.billing_code_type = ros.billing_code_type
WHERE
(
    ku.count_encounters IS NOT NULL
    OR ksc.count_encounters IS NOT NULL
    OR ncu.count_encounters IS NOT NULL
    OR cmv.utilization_imputed_commercial IS NOT NULL
    OR cmv.utilization_imputed_medicare IS NOT NULL
    OR cmv.utilization_imputed_medicaid IS NOT NULL
)