Clear Rates

Labs in Clear Rates

As of v2.3.0 we started referencing the new Lab spines in the rate object space. Previously, the top lab NPIs by claim count were included without national payer coverage or clean standard naming/provider IDs.

Steps in provider.sql:

• Array associated NPIs from the two spines tables to the provider_id level.

labs_spines AS (
    SELECT npi, provider_id
    FROM {{ cld_params.Tables.SPINES_PROVIDER_LABS.value }}
    UNION
    SELECT npi, provider_id
    FROM {{ cld_params.Tables.SPINES_PROVIDER_LABS_ADDITIONAL_NPIS.value }}
),

• Filter to NPIs from spines that have over 1,000 encounter keys in Komodo data. Then sort and take the top 1,000 NPIs.

labs_filtered AS (
    SELECT
        hco_1_npi,
        count(distinct encounter_key) as n
    FROM tq_intermediate.external_komodo.medical_headers k
        JOIN labs_spines s
            ON k.hco_1_npi = s.npi
    GROUP BY 1
    HAVING count(distinct encounter_key) > 1000
    ORDER BY n DESC
    LIMIT 1000
),

• Array NPIs to the provider_id level.

labs_array_npi as (
    SELECT
        provider_id,
        ARRAY_SORT(ARRAY_AGG(DISTINCT cast(npi as varchar))) as npi
    FROM labs_spines
    GROUP BY provider_id

),

• The final laboratories CTE, uses temp CTEs that only surface provider_ids with more than 1,000 encounters for any of their NPIs. There is an override to always include the national_payer_coverage labs.

    WHERE (
        provider_id IN (
            SELECT provider_id
            FROM labs_spines
            JOIN labs_filtered
                ON labs_spines.npi = labs_filtered.hco_1_npi
        )
        OR
        national_payer_coverage = TRUE
    )

laboratories AS (
    SELECT
        l.provider_id,
        CASE
            WHEN npis.npi[1] IS NULL THEN NULL
            ELSE npis.npi
        END as npi,
        NULL as definitive_id,
        l.provider_name,
        NULL as medicare_provider_id,
        NULL as ein,
        l.city,
        l.state,
        geo_state.state_name || ' (' || l.state || ')' as state_name_full,
        l.cbsa,
        cbsa_name,
        NULL as county,
        NULL as zip_code,
        NULL as zip3,
        NULL as carrier_locality,
        l.address as street_address,
        NULL as hq_latitude,
        NULL as hq_longitude,
        NULL as website_url,
        NULL as health_system_id,
        NULL as health_system_name,
        NULL as health_system_type,
        NULL as health_system_city,
        NULL as health_system_state,
        l.provider_type,
        l.provider_type as provider_type_new,
        l.provider_type as provider_subtype,
        l.taxonomy_code,
        l.taxonomy_grouping,
        NULL as total_beds,
        NULL as operating_room_count,
        NULL as services_lab,
        NULL as services_radiology,
        NULL as services_cardiology,
        NULL as services_dental,
        NULL as services_dermatology,
        NULL as services_ent,
        NULL as services_general_other,
        NULL as services_endoscopy,
        NULL as services_nephrology,
        NULL as services_neurology,
        NULL as services_obgyn,
        NULL as services_opthalmology,
        NULL as services_orthopedic,
        NULL as services_pain,
        NULL as services_plastic_surgery,
        NULL as services_podiatry,
        NULL as services_pulmonary,
        NULL as services_urology,
        NULL as services_mra,
        NULL as services_ct,
        NULL as services_mri,
        NULL as services_mammography,
        NULL as services_cta,
        NULL as services_xray,
        NULL as services_ultrasound,
        NULL as services_spect,
        NULL as services_pet,
        NULL as services_imrt,
        l.national_payer_coverage
    FROM pd_lab_agg l
    LEFT JOIN geo_state
        ON l.state = geo_state.state_abbreviation
    LEFT JOIN labs_array_npi as npis
        ON l.provider_id = npis.provider_id
),