Skip to main content

Inpatient Base Rates QA

In prod_clickhouse_abridged, prod_combined_abridged, inpatient base rates are stored in the provider_network_msdrg_base_rate column. We want to QA this column so that it is UX ready.

Base Rate Selection​

Background:

We have two canonical rate types that use an inferred inpatient base rate:

  • msdrg_base_rate: base rate identified by dividing dollar by CMS weight
    • at least 10 DRGs (excluding obstetrics and nursery) must have a common base rate AND this base rate must be shared by 90% of DRGs
  • msdrg_mrf_base_rate: base rate identified because MRF posted identical rate for sufficiently many MS-DRGs
    • at least 100 DRGs posted with this base rate

It's also possible that a base rate was inferred but, canonical rate type uses the raw rate. In this case, we would still want to surface the base rate in provider_network_msdrg_base_rate.

Heuristic:

  1. If msdrg_mrf_base_rate was used for any rate for a contract, use that as provider_network_msdrg_base_rate.
  2. If msdrg_base_rate was used for any rate for a contract, use that as provider_network_msdrg_base_rate.
  3. If neither of the above, but msdrg_mrf_base_rate was inferred, use that as provider_network_msdrg_base_rate.
  4. If neither of the above, but msdrg_base_rate was inferred, use that as provider_network_msdrg_base_rate.
  5. Else, provider_network_msdrg_base_rate is NULL

Notes:

  • For all steps, the base rate must be between 1500 and 50000.
  • For steps 1 and 2, the canonical_rate_score must be above 1.
  • For steps 3 and 4, the validation_score must be above 1 (since these were not selected as canonical_rate, they do not have a canonical_rate_score)

alt text

Multiple Base Rates​

There are 23 contracts (provider-payer-network) that use conflicting inpatient base rates. With the methodology outlined above, we would not have multiple base rates and we would select the MRF-reported base rate if it exists.

However, the canonical_rate may still be derived from multiple base rates. They are rare: 23 contracts and within these contracts, usually just a handful of canonical_rates are computed using the "other" base rate.

These are likely because the imputed rate using one base rate falls outside of Medicare bounds, but the imputed rate using the other base rate does not.

We can investigate these more.

Code
SELECT
    provider_id, 
    payer_id, 
    network_id,
    ANY_VALUE(msdrg_mrf_base_rate) AS msdrg_mrf_base_rate,
    ANY_VALUE(msdrg_base_rate) AS msdrg_base_rate,
    COUNT(DISTINCT canonical_rate_type) AS canonical_rate_type_count,
    ARRAY_AGG(DISTINCT canonical_rate_type) AS canonical_rate_types,
    SUM(
        CASE 
            WHEN canonical_rate_type LIKE '%msdrg_base%' THEN 1
            ELSE NULL
        END
    ) as msdrg_base_rate_count,
    SUM(
        CASE 
            WHEN canonical_rate_type LIKE '%msdrg_mrf_base%' THEN 1
            ELSE NULL
        END
    ) as msdrg_mrf_base_rate_count
FROM tq_dev.internal_dev_csong_cld_v2_4_0.prod_combined_all
WHERE bill_type = 'Inpatient'
AND provider_type = 'Hospital'
AND (
    canonical_rate_type LIKE '%msdrg_base%'
    OR canonical_rate_type LIKE '%msdrg_mrf_base%'
)
AND canonical_rate_score > 1
GROUP BY 1,2,3
HAVING COUNT(DISTINCT canonical_rate_type) > 1
On this page: