Provider outlier screeningCTEs · weighted mean · RANK() window function
WITH provider_metrics AS (
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,
ROUND(
SUM(u.avg_medicare_payment * u.tot_srvcs) /
NULLIF(SUM(u.tot_srvcs), 0),
2) AS provider_pay_per_svc,
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
),
ranked_providers AS (
SELECT
*,
ROUND(provider_pay_per_svc - specialty_avg_pay_per_svc, 2) AS deviation_from_avg,
ROUND(
(provider_pay_per_svc - specialty_avg_pay_per_svc) /
NULLIF(specialty_avg_pay_per_svc, 0) * 100,
1) AS pct_deviation,
RANK() OVER (
PARTITION BY specialty
ORDER BY provider_pay_per_svc DESC
) AS specialty_rank
FROM provider_metrics
)
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;
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.