SQL — Imputations

SQL Files

File	Description
imputations/imputations_long_rates_raw_columns.sql	Pivots raw rate columns to long format; one row per non-NULL rate value
imputations/imputations_long_rates_transformed_columns.sql	Pivots transformed rate columns to long format
imputations/imputations_long_rates_combined.sql	UNIONs raw and transformed long rates; filters to non-outlier rates only
imputations/imputations_rc_global.sql	RC Global: applies revenue code family-level rates to all HCPCS in that family
imputations/imputations_rc_hcpcs.sql	RC HCPCS: uses validated RC → HCPCS crosswalk to impute HCPCS rates from RC rates
imputations/imputations_rc_carveouts.sql	RC Carveouts: handles specific codes with overriding prices
imputations/imputations.sql	Core imputation; 5-tier aggregation from most specific (provider + payer + network + code) to least specific (code nationally); chunked by payer
imputations/imputations_derived.sql	Derived imputations for Hospital and ASC ROIDs using percentage-of-charges structures; applies OPG schedules and RC affiliations
imputations/imputations_derived_union.sql	UNIONs derived imputation chunks into tmp_int_imputations_derived
imputations/imputations_cstm.sql	CSTM imputations; surgical grouper-based for UHC, Aetna, Cigna, and others
imputations/imputations_aprdrg.sql	APR-DRG imputations; aggregates derived and CSTM through the APR-DRG → MS-DRG crosswalk
imputations/imputations_union.sql	UNIONs all imputation chunks into tmp_int_imputations

Input Tables (params.py)

Variable	Table	Used in
RC_HCPCS	tq_intermediate.cld_utils.rc_to_hcpcs_cross_validated_75	imputations_rc_global.sql, imputations_rc_hcpcs.sql
MSDRG_WEIGHTS_TABLE	tq_intermediate.cld_utils.msdrg_weights_by_year	imputations_derived.sql
RC_MSDRG_AFFILIATIONS	tq_intermediate.cld_utils.rc_msdrg_affiliations	imputations_derived.sql
OPG_SCHEDULES_UNITED	tq_intermediate.cld_utils.contract_surg_grouper_united_v0	imputations_derived.sql
OPG_SCHEDULES_AETNA	tq_intermediate.cld_utils.contract_surg_grouper_aetna_v0	imputations_derived.sql
OPG_SCHEDULES_CIGNA	tq_intermediate.cld_utils.contract_surg_grouper_cigna_v0	imputations_derived.sql
OPG_SCHEDULES_BCBS_CA	tq_intermediate.cld_utils.contract_surg_grouper_bcbs_ca_v0	imputations_derived.sql
OPG_SCHEDULES_CAREFIRST	tq_intermediate.cld_utils.contract_surg_grouper_carefirst_v0	imputations_derived.sql
OPG_SCHEDULES_ANTHEM_CA	tq_intermediate.cld_utils.contract_surg_grouper_anthem_ca_v0	imputations_derived.sql
OPG_SCHEDULES_FLORIDA_BLUE	tq_intermediate.cld_utils.contract_surg_grouper_florida_blue_v0	imputations_derived.sql
CSTM_ALL_XWALK_TABLE	tq_intermediate.cld_utils.cstm_all	imputations_cstm.sql
APR_DRG_XWALK_TABLE	tq_production.reference_legacy.drg_crosswalk	imputations_aprdrg.sql