PriceMedic Core Examples

Overview

These examples demonstrate common analytical patterns using the February 2026 hospital release. All queries use the providers, rates, and V_PROVIDER_RATES view.

Setup

USE DATABASE PRICEMEDIC_CORE_HOSPITAL_HS;
USE SCHEMA SNAPSHOT_FEB_2026;

Example Queries

1. Hospital System Footprint Analysis

Identify the largest hospital systems by provider count and payer coverage.

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;

Sample Output:

IMPUTED_EIN_NAME	HOSPITALS	PAYERS	NETWORKS	SCHEDULES
PRISMA HEALTH-UPSTATE	74	4	4	38
NEW YORK CITY HEALTH AND HOSPITALS CORPORATION	69	4	4	82
UNIVERSITY OF CALIFORNIA SAN FRANCISCO MEDICAL CENTER	68	5	5	31
CATHOLIC HEALTH INITIATIVES COLORADO	42	5	5	39

Use Case: Market intelligence on which health systems have the widest footprint and payer relationships.

2. Payer Coverage Analysis

Analyze provider footprint by payer to understand market coverage.

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;

Sample Output:

PAYER_SLUG	NPIS	HOSPITAL_SYSTEMS	SCHEDULES
uhc	10,033	4,902	6,956
aetna	8,142	5,300	9,070
cigna	6,263	4,266	3,610
anthem_bcbs_in	993	657	994
bcbs_tx	684	482	517

Use Case: Identify which payers have the broadest provider networks and most comprehensive transparency data.

3. Procedure Rate Benchmarking

Compare institutional rates for knee replacement (CPT 27447) across states.

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;

Sample Output:

NPI_STATE	N_EINS	N_OBSERVATIONS	P25_RATE	MEDIAN_RATE	P75_RATE
WY	16	224	9,104	11,187	28,493
WV	41	1,724	10,005	20,326	27,729
WI	83	2,648	13,200	17,239	22,764
WA	55	1,818	8,618	21,959	52,252
VT	5	10	14,228	16,640	17,148
VA	62	3,230	8,420	17,009	26,911
UT	23	1,216	6,795	23,998	32,594
TX	331	11,624	4,480	8,674	14,522
TN	87	2,776	4,739	7,660	12,857
SD	20	676	10,020	13,194	18,147
SC	41	8,602	9,584	18,429	29,224
RI	10	186	4,505	6,768	15,150

Use Case: Rate benchmarking - understand regional pricing variations and market positioning.

4. Fee Schedule Extraction

Extract complete fee schedules for a specific provider-payer combination.

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

Sample Output:

PAYER_SLUG	NETWORK_SLUG	NEGOTIATED_RATE	NEGOTIATED_TYPE	BILLING_CODE	BILLING_CODE_MODIFIER
aetna	aetna_exchange_aetna_ppo	0	fee schedule	99202	[“55”]
aetna	aetna_exchange_aetna_ppo	56	fee schedule	99202	null
aetna	aetna_exchange_aetna_ppo	0	fee schedule	99202	[“52”]
aetna	aetna_exchange_aetna_ppo	0	fee schedule	99202	[“56”]
aetna	aetna_exchange_aetna_ppo	43	fee schedule	99202	null
aetna	aetna_exchange_aetna_ppo	32	fee schedule	99202	null
aetna	aetna_exchange_aetna_ppo	37	fee schedule	99202	null
aetna	aetna_exchange_aetna_ppo	66	fee schedule	99202	null
aetna	aetna_exchange_aetna_ppo	50	fee schedule	99202	null
aetna	aetna_exchange_aetna_ppo	88	fee schedule	99203	null

Use Case: Quickly pull complete fee schedules to verify negotiated rates.

Tips for Working with PriceMedic Core

Filter by negotiated_type: Exclude 'percentage' rates when calculating dollar amount statistics
Use taxonomy codes: Filter to specific facility types using npi_taxonomy_code (e.g., 282N% for hospitals)
Leverage the view: Use V_PROVIDER_RATES to avoid manual joins between providers and rates
Watch for outliers: Apply reasonable min/max filters on negotiated_rate to exclude data errors

PriceMedic Core

Reports

Data Integrations

Overview

Setup

Example Queries

1. Hospital System Footprint Analysis

2. Payer Coverage Analysis

3. Procedure Rate Benchmarking

4. Fee Schedule Extraction

Tips for Working with PriceMedic Core

​Overview

​Setup

​Example Queries

​1. Hospital System Footprint Analysis

​2. Payer Coverage Analysis

​3. Procedure Rate Benchmarking

​4. Fee Schedule Extraction

​Tips for Working with PriceMedic Core

Overview

Setup

Example Queries

1. Hospital System Footprint Analysis

2. Payer Coverage Analysis

3. Procedure Rate Benchmarking

4. Fee Schedule Extraction

Tips for Working with PriceMedic Core