Healthcare analytics · Independent portfolio project
Medicare | SQL and Excel Spending Analysis
A PostgreSQL analysis and Excel dashboard that explain payment concentration and utilization while keeping the source data’s grain and metric definitions explicit.
These published CMS records summarize provider × procedure × place-of-service activity; they are not individual claims or unique patients. Findings apply to the defined Illinois psychiatry cohort.
01 / Findings and interpretation
Follow the spending.
Question the denominator.
Medicare Provider Spending Analysis
Which services drive payment,
and what deserves a closer look?
I organized 2,460 published provider–procedure–place-of-service records into three related tables, wrote analytical SQL, and built a seven-sheet Excel workbook.
The aim was to explain spending concentration and provider variation with consistent metrics, then identify questions that the source alone cannot answer.
- Finding
- CPT 99214 accounts for $5.65M, or 20.8% of payment. The top five codes account for 55.6% of the cohort’s Medicare payment.
- Interpretation
- Submitted charges, allowed amounts, and Medicare payments answer different questions. Payment per service unit is a more defensible primary metric here than payment per summed beneficiary count.
- Next decision
- Examine high-volume codes and procedure mix first. Treat provider and city differences as leads for investigation before making staffing or access recommendations.
Five codes account for 55.6% of payment.
K1034 is a COVID-19 test code. Its 164,437 service units came from one provider. Procedure mix matters: a service unit is not necessarily a patient visit.
View exact values
| Code | Service | Payment | Share |
|---|---|---|---|
| 99214 | Established outpatient visit | $5,645,811.88 | 20.8% |
| 99213 | Established outpatient visit | $3,070,132.38 | 11.3% |
| 99232 | Subsequent hospital care | $2,690,318.19 | 9.9% |
| K1034 | COVID-19 test provision | $1,933,779.12 | 7.1% |
| 90833 | Psychotherapy with E&M | $1,766,533.38 | 6.5% |
Three measures. Three different meanings.
Medicare payment is 38.4% of submitted charges. The remaining charge amount is not automatically unpaid revenue or a loss; Medicare payment alone does not represent all provider receipts.
View exact values
| Measure | Amount |
|---|---|
| Submitted charges | $70,702,611.15 |
| Allowed amount | $35,599,258.32 |
| Medicare payment | $27,182,697.04 |
Telephone-coded share varies sharply by provider city.
This describes billing-provider cities in a Medicare psychiatry cohort. It does not measure all psychiatric care, all telehealth, patient residence, or transportation barriers. Elk Grove Village and DeKalb each have one provider in this cohort, so their percentages need particular care.
Inspect denominators & provider counts
| Provider city | All providers | Phone providers | Phone units | All units | Share |
|---|---|---|---|---|---|
| Elk Grove Village | 1 | 1 | 368 | 480 | 76.7% |
| DeKalb | 1 | 1 | 262 | 501 | 52.3% |
| Plainfield | 5 | 1 | 239 | 934 | 25.6% |
| Morris | 2 | 1 | 146 | 682 | 21.4% |
| Pekin | 1 | 1 | 219 | 1,168 | 18.8% |
| Chicago | 188 | 22 | 1,409 | 73,531 | 1.9% |
Explore the method & limitations
Model the source grain
One row per provider NPI × HCPCS code × place of service. Providers and procedure codes are reference tables; utilization contains volumes and payment measures.
Weight the metrics correctly
Estimated payment = sum of average payment × service units. Payment per unit = total payment ÷ total units. This differs from an unweighted average of provider averages.
Screen variation, then investigate
CTEs and window functions compare providers within specialty. Distribution summaries are descriptive screens; procedure mix, volume, and case mix need further analysis before labeling a provider inefficient.
Separate patterns from causes
The file cannot establish access barriers, clinical outcomes, appointment availability, or why telephone use differs. Provider geography is not patient geography. Follow-up needs new data and a defined comparison.
Source: CMS Medicare Physician & Other Practitioners — by Provider and Service, 2023. Charts are static snapshots verified against the original workbook. The original workbook and PDF are retained; the SQL download includes a revised per-service comparison, the city-share query, and validation checks. It has not been rerun against the original source CSV as part of this website revision.
02 / Inspect the SQL
The reasoning is in the code.
Readable PostgreSQL, with the method beside it. Expand an example, wrap long lines, or copy the query to inspect it yourself.
Provider outlier screeningCTEs · weighted mean · RANK() window function
WITH provider_metrics AS (
-- Individual provider-level payment per service
SELECT
p.npi,
p.first_name,
p.last_org_name,
p.credentials,
p.city,
p.provider_type AS specialty,
SUM(u.tot_srvcs) AS total_services,
SUM(u.tot_benes) AS sum_benes_approx,
ROUND(SUM(u.avg_medicare_payment * u.tot_srvcs), 2) AS total_payment,
-- Provider's own payment per service
ROUND(
SUM(u.avg_medicare_payment * u.tot_srvcs) /
NULLIF(SUM(u.tot_srvcs), 0),
2) AS provider_pay_per_svc,
-- Specialty average payment per service (window function over specialty group)
ROUND(
SUM(SUM(u.avg_medicare_payment * u.tot_srvcs)) OVER (PARTITION BY p.provider_type) /
NULLIF(SUM(SUM(u.tot_srvcs)) OVER (PARTITION BY p.provider_type), 0),
2) AS specialty_avg_pay_per_svc
FROM utilization u
JOIN providers p ON u.npi = p.npi
GROUP BY p.npi, p.first_name, p.last_org_name, p.credentials, p.city, p.provider_type
),
-- Add deviation from specialty mean and rank within specialty
ranked_providers AS (
SELECT
*,
-- Absolute dollar deviation from specialty average
ROUND(provider_pay_per_svc - specialty_avg_pay_per_svc, 2) AS deviation_from_avg,
-- Percent deviation from specialty average
ROUND(
(provider_pay_per_svc - specialty_avg_pay_per_svc) /
NULLIF(specialty_avg_pay_per_svc, 0) * 100,
1) AS pct_deviation,
-- Rank within specialty (highest payment per service = rank 1)
RANK() OVER (
PARTITION BY specialty
ORDER BY provider_pay_per_svc DESC
) AS specialty_rank
FROM provider_metrics
)
-- Final output: filter to providers who deviate > 50% from specialty mean
-- and have at least 10 services (low-volume providers can appear as outliers by chance)
SELECT
specialty,
specialty_rank,
npi,
CONCAT(first_name, ' ', last_org_name) AS provider_name,
credentials,
city,
total_services,
sum_benes_approx,
total_payment,
provider_pay_per_svc,
specialty_avg_pay_per_svc,
deviation_from_avg,
pct_deviation,
CASE
WHEN pct_deviation > 50 THEN 'HIGH OUTLIER'
WHEN pct_deviation < -50 THEN 'LOW OUTLIER'
ELSE 'Within Range'
END AS outlier_flag
FROM ranked_providers
WHERE ABS(pct_deviation) > 50
AND total_services >= 10
ORDER BY specialty, pct_deviation DESC;
A ±50% threshold and a minimum of 10 service units are screening choices, not a formal significance test. A flagged provider needs further review of volume and procedure mix.
Telephone-coded share by cityConditional aggregation · explicit denominator · provider counts
WITH city_services AS (
SELECT
INITCAP(TRIM(p.city)) AS city,
COUNT(DISTINCT p.npi) AS total_provider_count,
COUNT(DISTINCT p.npi) FILTER (
WHERE u.hcpcs_cd IN ('99441', '99442', '99443')
) AS telephone_provider_count,
SUM(u.tot_srvcs) AS total_service_units,
COALESCE(SUM(u.tot_srvcs) FILTER (
WHERE u.hcpcs_cd IN ('99441', '99442', '99443')
), 0) AS telephone_service_units,
COALESCE(SUM(u.avg_medicare_payment * u.tot_srvcs) FILTER (
WHERE u.hcpcs_cd IN ('99441', '99442', '99443')
), 0) AS telephone_medicare_payment
FROM utilization u
JOIN providers p ON p.npi = u.npi
WHERE p.state = 'IL' AND p.provider_type = 'Psychiatry'
GROUP BY INITCAP(TRIM(p.city))
)
SELECT
city, total_provider_count, telephone_provider_count,
total_service_units, telephone_service_units,
ROUND(telephone_medicare_payment, 2) AS telephone_medicare_payment,
ROUND(100.0 * telephone_service_units
/ NULLIF(total_service_units, 0), 1) AS telephone_service_pct
FROM city_services
WHERE telephone_service_units > 0
ORDER BY telephone_service_units DESC, city;
Numerator: 99441–99443 service units. Denominator: all published service units in that provider city. Includes both total providers and telephone-code providers.
Check the source grainDuplicates · service volume · missing or negative payment
SELECT npi, hcpcs_cd, place_of_service, COUNT(*) AS row_count
FROM utilization
GROUP BY npi, hcpcs_cd, place_of_service
HAVING COUNT(*) > 1;
-- Null, non-positive volume, and negative-payment checks.
SELECT
COUNT(*) AS published_rows,
COUNT(*) FILTER (WHERE tot_srvcs IS NULL OR tot_srvcs <= 0)
AS invalid_service_rows,
COUNT(*) FILTER (WHERE avg_medicare_payment IS NULL
OR avg_medicare_payment < 0) AS invalid_payment_rows
FROM utilization;
These checks look for data problems before interpreting results. They do not certify the full dataset or establish clinical validity.