Michal KuderskiDATA ANALYST · B.S. STATISTICS
← Selected work

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.

SQL · PostgreSQL · Excel · Weighted KPIsCMS · Illinois psychiatry providers · 2023 data

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
CMS · Illinois psychiatry · 2023 data
PostgreSQL · SQL · Excel

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.
$27.2MMEDICARE PAYMENT
$35.6MALLOWED AMOUNT
$51.03PAYMENT PER SERVICE UNIT
532,675PUBLISHED SERVICE UNITS

Five codes account for 55.6% of payment.

Medicare payment · bars relative to the largest code

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
Source: workbook → Procedure Analysis. Share uses total cohort Medicare payment.
CodeServicePaymentShare
99214Established outpatient visit$5,645,811.8820.8%
99213Established outpatient visit$3,070,132.3811.3%
99232Subsequent hospital care$2,690,318.199.9%
K1034COVID-19 test provision$1,933,779.127.1%
90833Psychotherapy with E&M$1,766,533.386.5%

Three measures. Three different meanings.

Reimbursement amounts · bars scaled to submitted charges

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
Source: workbook → Specialty Summary. Totals reconstructed from published average amounts.
MeasureAmount
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.

99441–99443 service units ÷ all published service units in that city · 0–100% scale

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
Source: Telehealth Access and Provider Outliers sheets. “Phone providers” counts providers billing the selected telephone codes.
Provider cityAll providersPhone providersPhone unitsAll unitsShare
Elk Grove Village1136848076.7%
DeKalb1126250152.3%
Plainfield5123993425.6%
Morris2114668221.4%
Pekin112191,16818.8%
Chicago188221,40973,5311.9%
What the denominator changes: CMS beneficiary counts are distinct within each published row. Summing them can count the same person more than once. I use payment per service unit in the headline KPIs and keep beneficiary-based outputs labeled as approximate. Higher payment per unit also does not establish lower efficiency or worse care.
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.

providers (NPI)utilization (fact)hcpcs_codes (code)

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
PostgreSQL · 77 lines
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
PostgreSQL · 28 lines
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
PostgreSQL · 13 lines
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.

← GridCast forecastingGraphOS analytics demo →