> ## Documentation Index
> Fetch the complete documentation index at: https://docs.pricemedic.com/llms.txt
> Use this file to discover all available pages before exploring further.

# PriceMedic Core Examples

> Example queries and use cases for PriceMedic Core

## Overview

These examples demonstrate common analytical patterns using the February 2026 hospital release. All queries use the [`providers`](/data/core/tables/providers), [`rates`](/data/core/tables/rates), and `V_PROVIDER_RATES` view.

## Setup

```sql theme={null}
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.

```sql theme={null}
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.

```sql theme={null}
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.

```sql theme={null}
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.

```sql theme={null}
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