Clear Rates

Urgent Care in Clear Rates

Source: PR #5163

Steps in provider.sql:

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

urgent_care_array_npi as (
    SELECT
        provider_id,
        ARRAY_SORT(ARRAY_AGG(DISTINCT cast(npi as varchar))) as npi
    FROM (
        SELECT provider_id, npi
        FROM {{ cld_params.Tables.SPINES_PROVIDER_URGENT_CARE.value }}
        UNION
        SELECT provider_id, npi
        FROM {{ cld_params.Tables.SPINES_PROVIDER_URGENT_CARE_ADDITIONAL_NPIS.value }}
    )
    WHERE npi IS NOT NULL
    GROUP BY provider_id
),

• The urgent_care CTE reads from the spines table and joins arrayed NPIs, geo lookups (state, CBSA), zip-to-carrier-locality mapping, and NUCC taxonomy. All spines providers are included (no Komodo encounter filtering).

urgent_care AS (
    SELECT
        p.provider_id,
        CASE
            WHEN npis.npi[1] IS NULL THEN NULL
            ELSE npis.npi
        END as npi,
        p.provider_name as provider_name,
        p.provider_city AS city,
        p.provider_state AS state,
        p.provider_cbsa_code AS cbsa,
        'Urgent Care' as provider_type,
        p.taxonomy_code,
        z.carrier_locality,
        p.national_payer_coverage
        -- ...
    FROM {{ cld_params.Tables.SPINES_PROVIDER_URGENT_CARE.value }} p
    LEFT JOIN urgent_care_array_npi as npis ON p.provider_id = npis.provider_id
    LEFT JOIN nucc ON p.taxonomy_code = nucc.taxonomy_code
    LEFT JOIN geo_state ON p.provider_state = geo_state.state_abbreviation
    LEFT JOIN geo_cbsa ON p.provider_cbsa_code = geo_cbsa.cbsa_id
    LEFT JOIN zip_carrier_locality as z ON p.provider_zip5 = z.zip_code
),

• The result is unioned into the final df CTE alongside hospitals, ASCs, labs, PGs, dialysis centers, infusion centers, etc.
• In lookback runs, Urgent Care is excluded from the state filter (same as ASCs, PGs, and Dialysis Centers).

Codeset (Rate Object Space)

• A dedicated urgent_care_codeset table defines which billing codes are plausible for Urgent Care. It is built from two sources:

  1. MPFS codes — ~250 high-volume urgent care HCPCS codes that exist in the Medicare Physician Fee Schedule (e.g. 99213, 99214, 87804, 99203, 87880, etc.)
  2. Manual additions — ~130 codes not in MPFS (e.g. S9083, S9088, G2211, J3301, etc.)

• In the rate object space, Urgent Care codes are filtered to bill_type = 'Professional' and matched against the codeset:

SELECT DISTINCT
    cs.bill_type,
    cs.billing_code_type,
    cs.billing_code,
    cs.facility,
    'Urgent Care' as provider_type,
    NULL as provider_subtype,
    NULL as provider_id,
    '' as billing_code_modifier
FROM {{ schema_name }}.tmp_ref_code_spine_{{ sub_version }} cs
WHERE cs.bill_type = 'Professional'
    AND cs.billing_code IN (
        SELECT billing_code
        FROM {{ cld_params.Tables.URGENT_CARE_CODESET.value }}
    )

Accuracy Scoring

• Urgent Care has its own dedicated accuracy scoring method (accuracy_score_urgent_care), separate from the standard medical method.
• Outlier bounds: rate must be between 0.5x and 5.5x Medicare rate (or 1.0x to $200 for anesthesia codes).
• Scoring hierarchy (from best to worst):
  1. Validated — rate matches a comparison column within ±20%
  2. Score 5 — has_consistent_pct_of_medicare_rate = true AND rate is between 0.9x and 3.5x Medicare
  3. Score 4 — has_consistent_pct_of_medicare_rate = true AND rate is between 0.5x and 5.5x Medicare
  4. Score 3 — rate is between 0.9x and 3.5x Medicare (without consistency check)
  5. Score 2 — within outlier bounds but doesn't meet higher tiers
• QA thresholds: minimum 3.0% of providers must have rates.