PriceMedic Core Examples

USE DATABASE PRICEMEDIC_CORE_HOSPITAL_HS;
USE SCHEMA SNAPSHOT_FEB_2026;

SELECT
  imputed_ein_name,
  COUNT(DISTINCT npi_number)   AS hospitals,
  COUNT(DISTINCT payer_slug)   AS payers,
  COUNT(DISTINCT network_slug) AS networks,
  COUNT(DISTINCT schedule_id)  AS schedules
FROM providers
GROUP BY imputed_ein_name
ORDER BY hospitals DESC;

SELECT
  payer_slug,
  COUNT(DISTINCT npi_number)  AS npis,
  COUNT(DISTINCT imputed_ein) AS hospital_systems,
  COUNT(DISTINCT schedule_id) AS schedules
FROM providers
GROUP BY payer_slug
ORDER BY npis DESC;

SELECT
  npi_state,
  COUNT(DISTINCT imputed_ein) as n_eins,
  COUNT(*) AS n_observations,
  ROUND(APPROX_PERCENTILE(negotiated_rate, 0.25), 0) AS p25_rate,
  ROUND(APPROX_PERCENTILE(negotiated_rate, 0.50), 0) AS median_rate,
  ROUND(APPROX_PERCENTILE(negotiated_rate, 0.75), 0) AS p75_rate
FROM V_PROVIDER_RATES
WHERE billing_code      = '27447'
  AND billing_class     = 'institutional'
  AND negotiated_rate   > 100
  AND negotiated_type  != 'percentage'
  AND npi_taxonomy_code LIKE '282N%'  -- General Acute Care Hospitals
GROUP BY npi_state
HAVING COUNT(*) >= 5
ORDER BY npi_state DESC;

SELECT DISTINCT
  payer_slug,
  network_slug,
  billing_code,
  billing_code_modifier,
  negotiated_rate,
  negotiated_type
FROM V_PROVIDER_RATES
WHERE imputed_ein = '390813418'   -- Mayo Clinic Arizona EIN
  AND payer_slug = 'uhc'
ORDER BY billing_code