SQL — Accuracy

SQL Files

Benchmarks

File	Description
benchmarks/benchmarks.sql	Builds Medicare benchmark columns per ROID; sources from IPPS, OPPS, MPFS, ASC, anesthesia, lab, and APR-DRG reference tables
benchmarks/benchmarks_union.sql	UNIONs benchmark chunks into tmp_int_benchmarks

Raw Accuracy

File	Description
accuracy/accuracy_raw.sql	Scores individual raw rate columns 0–7 per ROID using outlier bounds and benchmark comparisons; chunked by payer
accuracy/accuracy_raw_union.sql	UNIONs accuracy_raw chunks into tmp_int_accuracy_raw

BRIT Accuracy (by provider type)

File	Description
accuracy/accuracy_brit_medical_rates.sql	Scores raw and transformed rate columns for Hospital/ASC/Outpatient ROIDs
accuracy/accuracy_brit_medical_imputations.sql	Scores imputation columns for Hospital/ASC ROIDs
accuracy/accuracy_brit_medical_join.sql	JOINs benchmarks + raw accuracy + imputations into the wide BRIT row for Hospital/ASC ROIDs
accuracy/accuracy_brit_physician_groups.sql	BRIT assembly for Physician Group ROIDs
accuracy/accuracy_brit_dme.sql	BRIT assembly for DME ROIDs
accuracy/accuracy_brit_drugs.sql	BRIT assembly for Drug ROIDs
accuracy/accuracy_brit_labs.sql	BRIT assembly for Lab ROIDs
accuracy/accuracy_brit_urgent_care.sql	BRIT assembly for Urgent Care ROIDs
accuracy/accuracy_brit_union.sql	UNIONs all BRIT chunks into tmp_int_accuracy_brit
utils/accuracy_macros.sql	Shared Jinja2 macros used across all accuracy files (counterparty validation, CDF scoring)

Input Tables (params.py)

Variable	Table	Used in
RATE_OUTLIER_TABLE	tq_intermediate.cld_utils.outlier_bounds_negotiated_rate_2025_11_07	accuracy_raw.sql, accuracy_brit_medical_rates.sql